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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy