Re: Inheritance and trigger/FK propagation

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