Grant function execute to user in Postgres

Question

In documentation on https://www.postgresql.org/docs/9.1/sql-grant.html theres a syntax to GRANT command.

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

I trying to add execute permission to some function using this syntax but without success:

db=# GRANT EXECUTE ON FUNCTION some_function TO some_user;
ERROR:  syntax error at or near "TO"
LINE 1: GRANT EXECUTE ON FUNCTION some_function TO some_user...

Why there's so cohesion between documentation and reality? What I'm doing wrong?

Answer 1

There was lack of "argument data types" in brackets after function_name. Function name and arguments data types we can check that by \df command.

 Schema |        Name        | Result data type          |Argument data types |  Type  
--------+--------------------+---------------------------+--------------------+--------
 public | some_function      | TABLE(some_results,....)  | numeric            | normal

Command should be:

GRANT EXECUTE ON FUNCTION some_function(numeric) TO some_user;

But syntax has arguments in square brackets so they should be not necessary. But trying

GRANT EXECUTE ON FUNCTION some_function() TO some_user;

gives function some_function() does not exist error. So syntax to GRANT command should be:

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { FUNCTION function_name ( argmode arg_name arg_type [, ...] ] ) [, ...]
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

Answer 2

Please try this. The below example is how I granted execute privilege to efm user on pg_current_wal_lsn() system function.

postgres=# grant execute on function pg_current_wal_lsn() to efm;

GRANT

How to view the access granted on this function ?

postgres=# \x

Expanded display is on.

postgres=#

postgres=# \df+ pg_current_wal_lsn();

List of functions -[ RECORD 1 ]-------+--------------------------- Schema | pg_catalog Name | pg_current_wal_lsn Result data type | pg_lsn Argument data types | Type | func Volatility | volatile Parallel | safe Owner | kali Security | invoker Access privileges | =X/kali + | kali=X/kali + | efm=X/kali Language | internal Source code | pg_current_wal_lsn Description | current wal write location