Re: Inheritance and trigger/FK propagation

"Karsten Hilbert" <Karsten(dot)Hilbert(at)gmx(dot)net> wrote in message
news:20100728182051(dot)GJ2726(at)hermes(dot)hilbert(dot)loc(dot)(dot)(dot)
> 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