Re: Inheritance and trigger/FK propagation - Mailing list pgsql-general

From Karsten Hilbert
Subject Re: Inheritance and trigger/FK propagation
Date
Msg-id 20100728182051.GJ2726@hermes.hilbert.loc
Whole thread Raw
In response to Re: Inheritance and trigger/FK propagation  ("Davor J." <DavorJ@live.com>)
List 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

pgsql-general by date:

Previous
From: Vincenzo Romano
Date:
Subject: Re: How Big is Too Big for Tables?
Next
From: David Fetter
Date:
Subject: Re: order in which rules are executed