Search code examples
sqlpostgresqlfunctionpermissionsprivileges

How to grant EXECUTE permissions for yet to be defined functions

I'm running a SQL script that creates an user first then defines some functions. How can I grant permissions to EXECUTE the yet to be defined functions without giving the grant after defining the functions? And also functions that I define in the future?

I've tried unsuccessfully altering the default privileges like this:

alter default privileges for user admin in schema myschema
GRANT execute ON functions TO admin;

Giving the grant after running the script like this works, but I'd like to not have to run it each time I define new functions:

grant execute on all functions in schema myschema to admin;
Solution

  • You don't have to do anything. Just keep reading.

    Once you have executed ALTER DEFAULT PRIVILEGES, manual GRANT like you suggest is not needed any more. The defined privilege(s) are granted automatically, like you desire - for the defined creating role, the defined receiving role, in the defined schema.

    You do have admin twice in your statement, which does not make sense, as the creator is the initial owner of the function and, quoting the manual:

    Default privileges always include all privileges for the owner

    And yo may want to use ROUTINE instead of FUNCTION in modern Postgres (version 11 or later). The manual on 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.

    However (somewhat confusingly) the manual on ALTER DEFAULT PRIVILEGES has this to say:

    For this command, functions include aggregates and procedures. The words FUNCTIONS and ROUTINES are equivalent in this command. (ROUTINES is preferred going forward as the standard term for functions and procedures taken together. In earlier PostgreSQL releases, only the word FUNCTIONS was allowed. It is not possible to set default privileges for functions and procedures separately.)

    Either way, ROUTINES would typically be the right choice in modern Postgres. Run this after creating creating_role:

    ALTER DEFAULT PRIVILEGES FOR ROLE creating_role IN SCHEMA myschema
GRANT EXECUTE ON ROUTINES TO receiving_role;

    Then, receiving_role has the right to execute those functions. Except that in a standard setup, PUBLIC has the right to execute anyway. And every role is automatically member of PUBLIC. The manual:

    For other types of objects, the default privileges granted to PUBLIC are as follows: [...] EXECUTE privilege for functions and procedures;

    So you don't have to do anything.

    Are you aware that the bare privilege to EXECUTE does not give any privileges beyond that? In particular, tables etc. are accessed with the privileges of the executing role. You may be looking for SECURITY DEFINER functions. Related: