Error 42501 permission denied for schema
Dear all
I have 2 schemas , schema1 and schema 2.
1. GRANT USAGE ON SCHEMA schema1 TO schema2;
I am trying to create a function in shema2, In that function I need to
access some tables from schema1.
p
I am getting the following error when I compile the function
Search path set to schema2,schema1,public
Can you please help? Appreciate your time
Regards
akp geek <akpgeek@gmail.com> 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
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@sss.pgh.pa.us> wrote:
Show quoted text
akp geek <akpgeek@gmail.com> 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
akp geek <akpgeek@gmail.com> writes:
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
Well, keep in mind that what normally counts for a function's queries
is the permissions of the *caller*, not the owner. If you want the
function to be able to do things regardless of who's calling it, mark
it SECURITY DEFINER, and then it runs with the owner's permissions.
Beware that malicious users might be able to subvert a SECURITY DEFINER
function to make it do something unintended ...
regards, tom lane
