7

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?

Noor A Shuvo
  • 2,639
  • 3
  • 23
  • 48
s97712
  • 455
  • 1
  • 4
  • 14

2 Answers2

13

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.

clemens
  • 16,716
  • 11
  • 50
  • 65
  • I had defined function as security definer, but it still throw permission denied when I use `GRANT EXECUTE ON schema.func to my_user;`. Can I allow specific function my_user to access rather than whole schema? – s97712 Oct 09 '18 at 05:03
  • The permission to access the function is usually not the problem. The problem is the access to the database objects (tables, etc.) which are used by the function. AFIK there is no way to allow detailed access to functions, but maybe you can use different schemes for each access group. – clemens Oct 09 '18 at 05:31
  • 1
    Thanks. Any chance you could mention the `search_path`? – Laurenz Albe Oct 09 '18 at 06:51
1

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)
Domenico Campagnolo
  • 174
  • 3
  • 11