The following trigger is a PL/PgSQL prototype of a row-security trigger to enforce row-security policy on writes.
I'm not proposing it for use as-is obviously, I'm just looking into how things work and things to fix. The biggest problem here is that the policy can by bypassed by a trigger that runs after this one, and PostgreSQL has no permissions model to force some "system" triggers to run first or last. A solution to this would be desirable to prevent users from breaking referential integrity constraint checks as well as to allow proper row security enforcement. The second problem is that performance is pretty ugly because of the need to look up the row security constraint each time. Moving this trigger into C and using the relcache should help with that, making it no better or worse than FK constraint checks. That'd also make for a faster superuser test than this version offers. Finally, while this will prevent rows that violate the table's RLS constraint from being inserted, it does NOT prevent probing for foreign key constraints because the FK check trigger doesn't respect RLS. Rather than try to implement those checks again in the RLS write trigger I'd like to teach FK triggers to respect RLS rules instead. Thoughts/comments? CREATE OR REPLACE FUNCTION rowsecurity_check() RETURNS TRIGGER AS $$ DECLARE rowsecurity text; rowcount integer; BEGIN IF (SELECT usesuper FROM pg_user WHERE usename = current_user) THEN RETURN NEW; END IF; rowsecurity = ( SELECT pg_catalog.pg_get_expr(rs.rsecqual, c.oid) FROM pg_class c INNER JOIN pg_rowsecurity rs ON (c.relhasrowsecurity AND rs.rsecrelid = c.oid) INNER JOIN pg_namespace n ON (c.relnamespace = n.oid) WHERE c.relname = TG_TABLE_NAME AND n.nspname = TG_TABLE_SCHEMA ); IF rowsecurity IS NOT NULL THEN -- for the NEW row, determine if it would be RLS-visible if written EXECUTE 'SELECT 1 FROM (SELECT ($1).*) x WHERE ' || rowsecurity USING new; GET DIAGNOSTICS rowcount = ROW_COUNT; RAISE NOTICE 'Blah %',rowcount; IF rowcount = 0 THEN RAISE insufficient_privilege USING MESSAGE = 'Row-security policy prohibits new tuple value'; END IF; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER zzzz_rowsecurity_check BEFORE INSERT OR UPDATE ON rls_regress_schema.document FOR EACH ROw EXECUTE PROCEDURE rowsecurity_check(); You'd usually use this in conjunction with another BEFORE trigger that modifies the row being written to ensure appropriate security attributes are set; something like: CREATE OR REPLACE FUNCTION set_userid_on_write() RETURNS trigger AS $$ BEGIN NEW.dauthor := current_user; RETURN new; END; $$ LANGUAGE PLPGSQL; CREATE TRIGGER zzza_set_current_user BEFORE INSERT OR UPDATE ON document FOR EACH ROW EXECUTE PROCEDURE set_userid_on_write(); ... or whatever is appropriate for your security model. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers