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

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