Re: Error 42501 permission denied for schema

[akp geek <akpgeek@xxxxxxxxx>]

Sorry for the confusion  that I have caused

• roles > role1 , role2
• schemas > schema1, schema2 
• GRANT USAGE ON SCHEMA schema1 TO role2;
• create function fnc_name(IN i_id numeric) 
• function is created using role2 

I ended up getting the error 

ERROR: permission denied for schema schema1

SQL state: 42501

Appreciate your help.

On Fri, Mar 26, 2010 at 11:45 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

akp geek <akpgeek@xxxxxxxxx> writes:
> I have 2 schemas , schema1 and schema 2.

> 1. GRANT USAGE ON SCHEMA schema1 TO schema2;

You seem to be confusing schemas and users --- they are not the same
thing at all.  The above grants the right to lookup objects in schema1
to the user (a/k/a role) named schema2; who doesn't necessarily have
anything to do with objects in schema2.

Also, although you failed to show the specific command that was failing,
I suspect what you are actually running into is lack of CREATE
permission not USAGE permission.

                       regards, tom lane