sb logoToday I Learned

1 post by joshmassover

Postgres Foreign Key checks permission denied

Foreign key checks are done as the owner as the target table, not as the user issuing the query.

This resulted in a permission error:

ProgrammingError: permission denied for schema example
LINE 1: SELECT 1 FROM ONLY "example"."table" x WHERE "id" OPERATOR(...
QUERY:  SELECT 1 FROM ONLY "example"."table" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

When dumping from one environment to local for testing, be sure that the owner of the table has permissions on your local postgres. Since it’s local, just give the owner of the table superuser perms.