Thread: Error 42501 permission denied for schema

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

bye
end