Wednesday, March 14, 2012

PostgreSQL - Foreign key checking. Permission denied for schema even as superuser

I was getting the following error:

wcm=> select version();
                                                      version                                                      
-------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.0.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit
(1 Zeile)

wcm=# delete from employee;
ERROR:  permission denied to the schema wcm
ZEILE 1: SELECT 1 FROM ONLY "wcm"."inventory" x WHERE $1 OPERATOR(pg_...
                            ^
ANFRAGE:  SELECT 1 FROM ONLY "wcm"."inventory" x WHERE $1 OPERATOR(pg_catalog.=) "employee_id" FOR SHARE OF x

The explanation goes as follows:

permission-denied-for-schema-even-as-superuser

That's a foreign key checking query.FK checks are done as the owner of
the target table
, not as the user who did the original query. So your
problem is that the owner of the table lacks permissions
to access the other table (or more specifically, the schema it's in).

Reference:
http://postgresql.1045698.n5.nabble.com/permission-denied-for-schema-even-as-superuser-td4373036.html

Thanks Tom Lane wherever you are, may the force be with you!!

No comments:

Post a Comment