From: | Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Inheritance and trigger/FK propagation |
Date: | 2010-07-28 18:20:51 |
Message-ID: | 20100728182051.GJ2726@hermes.hilbert.loc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2010-07-28 18:23:36 | Re: order in which rules are executed |
Previous Message | Karsten Hilbert | 2010-07-28 18:03:41 | Re: Inheritance and trigger/FK propagation |