pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser
4+ messages / 4 participants
[nested] [flat]

* BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser
@ 2021-03-03 17:51 PG Bug reporting form <noreply@postgresql.org>
  2021-03-03 19:04 ` Re: BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: PG Bug reporting form @ 2021-03-03 17:51 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: pg.org@realityexists.net

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser
  2021-03-03 17:51 BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser PG Bug reporting form <noreply@postgresql.org>
@ 2021-03-03 19:04 ` Tom Lane <tgl@sss.pgh.pa.us>
  2021-03-03 19:11   ` Re: BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser David G. Johnston <david.g.johnston@gmail.com>
  2021-03-03 21:36   ` Re: BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser Evgeny Morozov <pg.org@realityexists.net>
  0 siblings, 2 replies; 4+ messages in thread

From: Tom Lane @ 2021-03-03 19:04 UTC (permalink / raw)
  To: pg.org@realityexists.net; +Cc: pgsql-bugs@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser
  2021-03-03 17:51 BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser PG Bug reporting form <noreply@postgresql.org>
  2021-03-03 19:04 ` Re: BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-03-03 19:11   ` David G. Johnston <david.g.johnston@gmail.com>
  1 sibling, 0 replies; 4+ messages in thread

From: David G. Johnston @ 2021-03-03 19:11 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pg.org@realityexists.net <pg.org@realityexists.net>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

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 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.
>
>
I could have sworn this was documented but I couldn’t find it.

David J.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser
  2021-03-03 17:51 BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser PG Bug reporting form <noreply@postgresql.org>
  2021-03-03 19:04 ` Re: BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-03-03 21:36   ` Evgeny Morozov <pg.org@realityexists.net>
  1 sibling, 0 replies; 4+ messages in thread

From: Evgeny Morozov @ 2021-03-03 21:36 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org

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

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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2021-03-03 21:36 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-03 17:51 BUG #16911: "permission denied" error deleting rows as superuser from a table owned by a non-superuser PG Bug reporting form <noreply@postgresql.org>
2021-03-03 19:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-03-03 19:11   ` David G. Johnston <david.g.johnston@gmail.com>
2021-03-03 21:36   ` Evgeny Morozov <pg.org@realityexists.net>

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