Prototype row-security write trigger

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