In documentation on 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?
2 Answers
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 ]
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
