Skip site navigation (1) Skip section navigation (2)

Re: Inheritance and trigger/FK propagation

From: "Davor J(dot)" <DavorJ(at)live(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Inheritance and trigger/FK propagation
Date: 2010-07-29 08:50:02
Message-ID: i2rfdj$4d8$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-general
"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 



In response to

Responses

pgsql-general by date

Next:From: Ivan Sergio BorgonovoDate: 2010-07-29 08:52:17
Subject: Re: Which CMS/Ecommerce/Shopping cart ?
Previous:From: Richard HuxtonDate: 2010-07-29 07:19:58
Subject: Re: Comparison of Oracle and PostgreSQL full text search

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group