BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser
The following bug has been logged on the website:
Bug reference: 16911
Logged by: Evgeny Morozov
Email address: pg.org@realityexists.net
PostgreSQL version: 13.2
Operating system: Windows 7 x64, Ubuntu 18.04 x64
Description:
Originally discovered on PostgreSQL 12.5 running on Ubuntu 18.04, but the
same happens on PostgreSQL 13.2 running on Windows 7. Create a blank
database and run the following script in it as a superuser:
CREATE ROLE test_owner_role; -- Needs to be a non-superuser
DROP SCHEMA IF EXISTS myschema CASCADE;
CREATE SCHEMA myschema;
CREATE TABLE myschema.pktable (id int NOT NULL PRIMARY KEY);
ALTER TABLE myschema.pktable OWNER TO test_owner_role; -- Any non-superuser
role
CREATE TABLE myschema.fktable (customer_id int REFERENCES
myschema.pktable(id));
INSERT INTO myschema.pktable VALUES (1);
DELETE FROM myschema.pktable;
Expected result: no errors; the row is successfully deleted from
myschema.pktable.
Actual result: the final DELETE fails with
ERROR: permission denied for schema myschema
LINE 1: SELECT 1 FROM ONLY "myschema"."pktable" x WHERE "id" OPERATO...
^
QUERY: SELECT 1 FROM ONLY "myschema"."pktable" x WHERE "id"
OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
SQL state: 42501
This does not happen if the table is owner by a superuser or if I grant
myself USAGE rights on schema "myschema".
PG Bug reporting form <noreply@postgresql.org> writes:
CREATE TABLE myschema.fktable (customer_id int REFERENCES
myschema.pktable(id));
INSERT INTO myschema.pktable VALUES (1);
DELETE FROM myschema.pktable;
Expected result: no errors; the row is successfully deleted from
myschema.pktable.
Actual result: the final DELETE fails with
ERROR: permission denied for schema myschema
This is operating as designed: the foreign key enforcement triggers
run as the table owner, not as the calling user. Changing that would
not be a good idea.
regards, tom lane
On Wednesday, March 3, 2021, Tom Lane <tgl@sss.pgh.pa.us> wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
CREATE TABLE myschema.fktable (customer_id int REFERENCES
myschema.pktable(id));
INSERT INTO myschema.pktable VALUES (1);
DELETE FROM myschema.pktable;Expected result: no errors; the row is successfully deleted from
myschema.pktable.
Actual result: the final DELETE fails with
ERROR: permission denied for schema myschemaThis is operating as designed: the foreign key enforcement triggers
run as the table owner, not as the calling user. Changing that would
not be a good idea.
I could have sworn this was documented but I couldn’t find it.
David J.
On 3/03/2021 8:04 pm, Tom Lane wrote:
Expected result: no errors; the row is successfully deleted from
myschema.pktable.
Actual result: the final DELETE fails with
ERROR: permission denied for schema myschemaThis is operating as designed: the foreign key enforcement triggers
run as the table owner, not as the calling user. Changing that would
not be a good idea.
I see, thanks. So basically the owner of any table should always be
given usage rights on the schema, too (and normally they would be, of
course).
It is quite a confusing error, though. Perhaps if it at least gave the
name of the user who was denied permission that would have given me a
clue. Even better if it explained that this user is the owner of a table
with a foreign key. Something like "permission denied for schema
myschema for role test_owner_role, owner of table myschema.pktable
referenced by a foreign key from myschema.fktable".
It would be nice to document this, too. Maybe in sections 5.9.4 (Schemas
and Privileges), with a note and link from 5.4.5 (Foreign Keys)?
Regards,
Evgeny Morozov