From: | Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru> |
---|---|
To: | Aleksander Alekseev <aleksander(at)timescale(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Cc: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: Trigger violates foreign key constraint |
Date: | 2023-12-22 09:01:05 |
Message-ID: | 03b69167-a4c7-4b8d-a064-4e42d3311189@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Xiaoran Wang | 2023-12-22 09:05:35 | Re: Avoid computing ORDER BY junk columns unnecessarily |
Previous Message | ywgrit | 2023-12-22 08:20:43 | Re: planner chooses incremental but not the best one |