Обсуждение: permission denied for schema even as superuser.

Could it be triggering a function that is defined with "SECURITY
DEFINER" and the definer of the function does not have the right
permissions?

On Thu, May 5, 2011 at 4:03 AM, Chris Young <chris@chris.net.au> wrote:
> 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.
> In case it's relevant, the events leading up to this error are that I tried
> migrating all my tables and sequences from the public schama to my newly
> created nms schema. I simply did this:
> create schema nms;
> alter table foo set schema nms;
> alter sequence foo_id_seq set schema nms;
> For all my tables and sequences.
> The query worked when the tables were in the public schema, but not after I
> tried to change them to the new schema.
> I already searched the mailing list for this issue, and I could only find
> old posts (circa 2007) that suggested it might be a bug, and I expect it's
> more likely I've just done something stupid or missed something out.
> Cheers,
> Chris Young



--
---
John L Cheng

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