BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: pg(dot)org(at)realityexists(dot)net
Subject: BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser
Date: 2021-03-03 17:51:46
Message-ID: 16911-ca792f6bbe244754@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 16911
Logged by: Evgeny Morozov
Email address: pg(dot)org(at)realityexists(dot)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".

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2021-03-03 19:04:53 Re: BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser
Previous Message PG Bug reporting form 2021-03-03 15:16:01 BUG #16910: I can't install postgresql