Re: Trigger violates foreign key constraint - Mailing list pgsql-hackers

From Pavel Luzanov
Subject Re: Trigger violates foreign key constraint
Date
Msg-id 03b69167-a4c7-4b8d-a064-4e42d3311189@postgrespro.ru
Whole thread Raw
In response to Re: Trigger violates foreign key constraint  (Pavel Luzanov <p.luzanov@postgrespro.ru>)
List pgsql-hackers
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




pgsql-hackers by date:

Previous
From: ywgrit
Date:
Subject: Re: planner chooses incremental but not the best one
Next
From: Xiaoran Wang
Date:
Subject: Re: Avoid computing ORDER BY junk columns unnecessarily