Re: Trigger violates foreign key constraint

One more not documented issue with system triggers. It might be worth
considering together.

CREATE ROLE app_owner;

CREATE TABLE t (
    id        int PRIMARY KEY,
    parent_id int REFERENCES t(id)
);

ALTER TABLE t OWNER TO app_owner;

-- No actions by application owner
REVOKE ALL ON t FROM app_owner;

INSERT INTO t VALUES (1,NULL);

DELETE FROM t;
ERROR:  permission denied for table t
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."t" x WHERE "id"
OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"

It is not at all obvious why the superuser cannot delete the row that he
just added. The reason is that system triggers are executed with the
rights of the table owner, not the current role. But I can't find a
description of this behavior in the documentation.

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com