Thanks to alecm on freenode for the idea. Thanks also to Andres Freund for pointing out a hole in the first implementation.
Access control is an excellent reason for allowing writes to a VIEW but not to the underlying table. You would ideally like that VIEW to act exactly like a TABLE, but when you put an INSTEAD OF trigger in there, what's coming back isn't necessarily what you'd expect...
Monday, June 11. 2012
VIEW triggers RETURNING...
Here's how to make sure that what's coming back is what you'd expect:
As usual for deployment scripts, take advantage of PostgreSQL's
transaction DDL. You'll be glad you did
BEGIN;Next, create your TABLE and VIEW.
CREATE TABLE foo(id SERIAL PRIMARY KEY, t TEXT);Now, we'll create a trigger function intended to pass INSERTs on the
CREATE VIEW foo_v AS SELECT * FROM foo; /* Access control would go here */
VIEW to the TABLE it's based on. We'll ignore all other writes. We
also create a TRIGGER using this function.
CREATE FUNCTION foo_v_write()Now, this code works as expected!
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF tg_op <> 'INSERT' THEN /* Ignore all write actions other than INSERT */
RETURN NULL;
END IF;
WITH f AS (
INSERT INTO foo(t)
VALUES(NEW.t)
RETURNING id
)
SELECT INTO NEW.id id FROM f;
RETURN NEW;
END;
$$;
CREATE TRIGGER foo_v_write_trg
INSTEAD OF INSERT OR UPDATE OR DELETE
ON foo_v
FOR EACH ROW
EXECUTE PROCEDURE foo_v_write();
INSERT INTO foo_v (t) VALUES ('bar') RETURNING id;You did remember to commit, right?!?COMMIT;
I notice that the docs are slightly scarce on the subject of default values and INSTEAD OF triggers:
http://www.postgresql.org/docs/9.2/static/trigger-definition.html
It looks like it may be a common mistake.
More generally, this approach might also help when the final destination table has BEFORE triggers of its own that modify the tuple.
I expect that a lot of people will want updatable views, and we should have a canonical solution -- even if trigger based for now -- that's as complete as possible (including RETURNING).
Maybe this can make it into the docs, but until then, this post might be linked to a little more than you originally expected
Surely it's much simpler to do (possibly fine-grained) grants on the table (possibly columns) and use "before" triggers for any complex cases (eg updates are only allowed on records having status = "open")?
What cases require the usage of views? When is it a good idea to use views for access control?