Inheritance and trigger/FK propagation

On 15/07/10 16:05, Davor J. wrote:

It seems no secret that a child table will not fire a trigger defined on
it's parent table. Various posts comment on this. But nowhere could I find a
reason for this.

[snip]

I read in the change logs of 8.4: "Force child tables to inherit CHECK
constraints from parents (Alex Hunsaker, Nikhil Sontakke, Tom)." So why not
with triggers and FK's?

My understanding is that it's mostly an implementation limitation. In
other words, rather than any fundamental reason why it should not be
done, the issue is that nobody has gone and implemented it, tested it,
and ironed out the quirks and corner cases yet.

--
Craig Ringer

On Thu, Jul 15, 2010 at 4:05 AM, Davor J. <DavorJ@live.com> wrote:

It seems no secret that a child table will not fire a trigger defined on
it's parent table. Various posts comment on this. But nowhere could I find a
reason for this.

Do you want your trigger that redirects insert on parent table to the
proper child table should run on child tables too?

On Thu, Jul 15, 2010 at 4:05 AM, Davor J. <DavorJ@live.com> wrote:

It seems no secret that a child table will not fire a trigger defined on
it's parent table. Various posts comment on this. But nowhere could I

find a

reason for this.

Do you want your trigger that redirects insert on parent table to the
proper child table should run on child tables too?

Well, inheritance is not used for partitioning ONLY. So, yes, for *my*
use cases I would appreciate being able to tell triggers defined on
parent tables to run on child tables when an insert/update/delete
happens on a child table. (We use inheritance for auditing and for
data aggregation.)

But since I am not in a position to code the necessary infrastructure
I won't complain about the status quo.

Karsten

wiki.gnumed.de

--
GMX DSL: Internet-, Telefon- und Handy-Flat ab 19,99 EUR/mtl.
Bis zu 150 EUR Startguthaben inklusive! http://portal.gmx.net/de/go/dsl

"Craig Ringer" <craig@postnewspapers.com.au> wrote in message
news:4C3ED37C.1070007@postnewspapers.com.au...

My understanding is that it's mostly an implementation limitation. In
other words, rather than any fundamental reason why it should not be
done, the issue is that nobody has gone and implemented it, tested it,
and ironed out the quirks and corner cases yet.

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:

CREATE OR REPLACE FUNCTION rls_inherit_enforce()
RETURNS trigger AS
$BODY$
DECLARE
BEGIN

CASE TG_OP
WHEN 'UPDATE' THEN
IF NOT has_rowaccess(OLD.rls_update || OLD.rls_owner) THEN
RAISE EXCEPTION 'No permission for update of row';
END IF;
WHEN 'DELETE' THEN
IF NOT has_rowaccess(OLD.rls_delete || OLD.rls_owner) THEN
RAISE EXCEPTION 'No permission for deletion of row';
END IF;
ELSE
-- case when access type is not handled
RAISE EXCEPTION 'Access type % not handled', TG_OP;
END CASE;

RETURN NEW;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE

Function has_rowaccess(name[]) would check whether the current/session_user
is an admin or if he inherits any of the privileged passed-by users.

Now, with a "proper" (?) implementation of inheritance and trigger
propagation, RLS could be enforced on any table which would inherit from
rls_security.

In the end I dumped this approach and implemented something similar to Veil
through plpgsql. Personally, I am not really a fan of statement-like (or
constraint-based if you like) RLS, like "GRANT user_name privilege_type TO
query" or something similar. For table/column privileges it is OK, but once
you have to manage many users and many rows, such RLS systems tend to become
unmanageable. But then again, this is MHO, and not really a place to discuss
RLS. I just wanted to point out that inheritance might also be usable for
some RLS implementation.

Regards,
Davor

""Karsten Hilbert"" <Karsten.Hilbert@gmx.net> wrote in message
news:20100719182027.123920@gmx.net...

On Thu, Jul 15, 2010 at 4:05 AM, Davor J. <DavorJ@live.com> wrote:

It seems no secret that a child table will not fire a trigger defined
on
it's parent table. Various posts comment on this. But nowhere could I

find a

reason for this.

Do you want your trigger that redirects insert on parent table to the
proper child table should run on child tables too?

Well, inheritance is not used for partitioning ONLY. So, yes, for *my*
use cases I would appreciate being able to tell triggers defined on
parent tables to run on child tables when an insert/update/delete
happens on a child table. (We use inheritance for auditing and for
data aggregation.)

But since I am not in a position to code the necessary infrastructure
I won't complain about the status quo.

Karsten

For me Vick's question just proves that inheritance in relational databases
is a complex issue. It shows that trigger propagation is not always desired,
contrary to what I believed.

But I also have to concur with Karsten.

Regards,
Davor

On Tue, Jul 27, 2010 at 10:36:16AM +0200, Davor J. wrote:

For me Vick's question just proves that inheritance in relational databases
is a complex issue. It shows that trigger propagation is not always desired,

Now that's for sure :-)

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

"Karsten Hilbert" <Karsten.Hilbert@gmx.net> wrote in message
news:20100728182051.GJ2726@hermes.hilbert.loc...

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 :)

On Thu, Jul 29, 2010 at 10:50:02AM +0200, Davor J. wrote:

For completeness, I think this link
(http://projects.nocternity.net/index.py/en/psql-inheritance) provides some
scripts you mention.

Very interesting.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346