Permission denied on schema in PostgreSQL

Question

I can't find why I am getting an permission denied error in my database.

The role owns the schema and has access to the table, but still the log says:

ERROR: permission denied for schema myschema at character 20
QUERY: SELECT 1 FROM ONLY "myshema"."mytable" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

score 14 · Accepted Answer · answered Feb 13 '15 at 15:52

14

There is a foreign key in a table referring to a table in the schema in question, to which the table owner role does not have permission. Foreign key checks are done with the permissions of the role that own the table, not the role performing the query.

The query is actually doing the internal foreign key check.

Found an explanation on sharingtechknowledge.blogspot.fi

answered Feb 13 '15 at 15:52

jnas

826
10
14

2

Thank you, kind stranger! How is anybody supposed to conclude this from the given error message?... – jBuchholz Jun 27 '22 at 13:42
Error messages can be a bit misleading and motivated this little question and answer that you found. It seems to be valid still though quite old already. – jnas Aug 01 '22 at 06:17

Permission denied on schema in PostgreSQL

1 Answers1