Re: Trigger violates foreign key constraint

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

In response to

Browse pgsql-hackers by date

  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