how to grant execute to pgcrypto digest function to user

Question

Using postgresql 9.6

I enabled pgcrypto using create extension pgcrypto using the postgres user. Now i want to grant execute rights to my other db user. Unfortunately i am not able to do it. Is this possible or do you have to be a superuser to use the digest function from pgcrypto.

postgres=# GRANT EXECUTE ON FUNCTION digest TO another_user;
ERROR:  syntax error at or near "digest"
LINE 1: GRANT EXECUTE ON FUNCTION digest TO another_user;

---

Using the answer below, I was able to successfully grant permission to execute the function. However another_user cannot execute the function. Are there other permissions that i need in order to execute this function using another_user?

another_user=> SELECT digest('whatisgoingon'::text, 'sha256'::text);
ERROR:  function digest(text, text) does not exist
LINE 1: SELECT digest('whatisgoingon'::text, 'sha256'::text);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Even though when i check permissions for the user i get back that i have permissions.

postgres=# select has_function_privilege('another_user', 'digest(text, text)', 'execute');
 has_function_privilege 
------------------------
 t
(1 row)

Thanks

Answer 1

Postgres supports overloading, i.e. multiple functions with the same name but different argument lists.

When calling the function in SQL, it figures out which version you meant based on the number of parameters and their types. But when referencing the function in a DDL command (DROP, ALTER, GRANT, etc.), you need to specify exactly which version you meant, by including a list of argument types after the function name.

This is quite relevant in the case of digest, because there are actually two versions, and you need to make it clear which one you're talking about. So either:

GRANT EXECUTE ON FUNCTION digest(text,text) TO another_user

...or:

GRANT EXECUTE ON FUNCTION digest(bytea,text) TO another_user

(...or both.)

---

As of Postgres 10, you're allowed to omit the argument list when the function is not overloaded. This doesn't help you much in the case of digest, but at least you get a more informative error message:

postgres=# GRANT EXECUTE ON FUNCTION digest TO another_user;
ERROR:  function name "digest" is not unique
HINT:  Specify the argument list to select the function unambiguously.

---

As for your follow-up question regarding the function does not exist error, try schema-qualifying the function name, e.g. SELECT my_schema.digest(...).

• If that works, it's a search path issue. You can either continue calling it with an explicit schema name, or update your search_path.

• If it responds with ERROR: permission denied for schema my_schema, then you just need to GRANT USAGE ON SCHEMA my_schema TO another_user.

• If it still says function my_schema.digest(text, text) does not exist, then you've probably connected to the wrong database by mistake.