Re: Error 42501 permission denied for schema

akp geek <akpgeek(at)gmail(dot)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