Re: Inheritance and trigger/FK propagation

"Karsten Hilbert" <Karsten.Hilbert@gmx.net> wrote in message
news:20100728182051.GJ2726@hermes.hilbert.loc...
> On Tue, Jul 27, 2010 at 10:33:19AM +0200, Davor J. wrote:
>
>> Well... I found it out the hard way :). There are some extra caveats I
>> have
>> come along. There is the very clumsy ALTER TABLE table_name
>> INHERIT(parent_table) which simply presupposes the parent's columns, but
>> doesn't enforce it thereafter? So you can remove an inherited column from
>> the child table when inheritance is made after the child table creation.
>>
>> Anyhow, I thought it could be quite usable for development a row level
>> security system. For example, one could have a table  rls_security
>> (rls_owner name, rls_select name, rls_delete name, rls_update name) and a
>> simple trigger:
>
> While, as you found out, the trigger won't auto-propagate
> this approach is still useful !
>
> - let all tables inherit from a base table providing the rls fields
>
> - write a generic trigger that accesses the rls fields *only*
>  (the table oid of the child table is available in the parent table
>   row, fortunately, which will help making error messages better)

Interesting.

>
> - use an external script (or even plpgsql function) to
>  attach said generic trigger to each table - the script
>  does not need to know the list of relevant tables because
>  that can be derived from the schema metadata inside PostgreSQL
>  (they are children of the parent table ;-)

For completeness, I think this link
(http://projects.nocternity.net/index.py/en/psql-inheritance) provides some
scripts you mention. I haven't tested them, but I think they are great to
start with.

>
> While a bit more cumbersome than (on-demand) trigger
> propagation it is still a fairly clean and
> close-to-the-ideal solution.

Now if Postgres supported firing triggers on CREATE TABLE (so these scripts
could fire "auto-magically"), then it would have been even
closer-to-the-ideal :)

>
> Karsten