"Karsten Hilbert" <Karsten.Hilbert@xxxxxxx> wrote in message news:20100728182051.GJ2726@xxxxxxxxxxxxxxxxxxxxx > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
