Re: permission denied for schema even as superuser. - Mailing list pgsql-general

Chris Young <chris@chris.net.au> writes:
> Greetings,
> I'm trying to perform the following query, but receive a perplexing error,
> even as superuser (postgres):

> umdb_db=# insert into mainview_teststatusevent
> (timestamp,host_id,test_id,old_status_id,new_status_id) values(now(),
> 726,4,6,1);
> ERROR:  permission denied for schema nms
> LINE 1: SELECT 1 FROM ONLY "nms"."mainview_status" x WHERE "id" OPER...
>                            ^
> QUERY:  SELECT 1 FROM ONLY "nms"."mainview_status" x WHERE "id"
> OPERATOR(pg_catalog.=) $1 FOR SHARE OF x

> Could you please let me know how I might troubleshoot and/or fix this
> 'permission denied' error? I'm running 9.0.4.

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 mainview_teststatusevent lacks permissions
to access the other table (or more specifically, the schema it's in).

            regards, tom lane