CODEWITHSUNDEEP
sqlpostgresql
s97712
s97712

Reputation: 515

How postgresql give permission what execute a function in schema to user

I have a function named schema.func, I want to give a permission what execute schema.func to the user.

I try to use

GRANT EXECUTE ON schema.func to my_user;

but it is not working. When I perform this function it throws the error:

permission denied for schema ex

I know I can use GRANT USAGE ON SCHEMA, but this is not what I want, I just need permissions for specific functions, not all functions in the schema.

Can I do that?

Upvotes: 10

Views: 29271

Answers (2)

Domenico Campagnolo
Domenico Campagnolo

Reputation: 194

As per PostregSQL documentation GRANT

The FUNCTION syntax works for plain functions, aggregate functions, and window functions, but not for procedures; use PROCEDURE for those. Alternatively, use ROUTINE to refer to a function, aggregate function, window function, or procedure regardless of its precise type. ... ALL FUNCTIONS also affects aggregate and window functions, but not procedures, again just like the specific-object GRANT command. Use ALL ROUTINES to include procedures.

try:

GRANT EXECUTE ON ROUTINE my_schema.my_function(text) TO my_user;

assuming that your function signature is something like:

my_schema.my_function(my_par text)

Upvotes: 2

clemens
clemens

Reputation: 17721

First, you should allow my_user to access the other schema:

GRANT USAGE ON SCHEMA my_schema TO my_user;

This allows the execution of functions but not the access to tables. So, if my_user executes the function, it still produces an access error, if the function accesses tables etc. in my_schema. To avoid this, you may define your function as security definer:

ALTER FUNCTION my_schema.my_function(...) SECURITY DEFINER SET search_path = public;

Edit: Writing SECURITY DEFINER Functions Safely also points a way to give execute permission to specific users:

REVOKE ALL ON FUNCTION my_schema.my_function(...) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION my_schema.my_function(...) TO my_user;

Please also note the hints on setting the search_path in this section.

Upvotes: 17

Related Questions