Re: permission denied for schema even as superuser.

From: John Cheng <johnlicheng(at)gmail(dot)com>
To: Chris Young <chris(at)chris(dot)net(dot)au>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: permission denied for schema even as superuser.
Date: 2011-05-05 15:21:22
Message-ID: BANLkTi=rKkDqZYUtnMpsKYYPPSsu6e_=xQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)chris(dot)net(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2011-05-05 15:22:07 Re: postgres segfaulting on pg_restore
Previous Message John Cheng 2011-05-05 15:02:46 Cannot reproduce why a query is slow