Re: Inheritance and trigger/FK propagation

"Craig Ringer" <craig(at)postnewspapers(dot)com(dot)au> wrote in message
news:4C3ED37C(dot)1070007(at)postnewspapers(dot)com(dot)au(dot)(dot)(dot)
> 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