pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: pg.org@realityexists.net
Subject: BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser
Date: Wed, 03 Mar 2021 17:51:46 +0000
Message-ID: <16911-ca792f6bbe244754@postgresql.org> (raw)

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".



view thread (4+ messages)  latest in thread

Message-ID: <16911-ca792f6bbe244754@postgresql.org>
Permalink:  ../16911-ca792f6bbe244754@postgresql.org/
Also on:    postgresql.org/message-id/16911-ca792f6bbe244754@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, pg.org@realityexists.net
  Subject: Re: BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser
  In-Reply-To: <16911-ca792f6bbe244754@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox