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)
- 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 ;-)
While a bit more cumbersome than (on-demand) trigger
propagation it is still a fairly clean and
close-to-the-ideal solution.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346