"Craig Ringer" <craig@xxxxxxxxxxxxxxxxxxxxx> wrote in message news:4C3ED37C.1070007@xxxxxxxxxxxxxxxxxxxxxxxx > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
