Problem with procedure permissions

[kkrasnov1]

Hi!

The following script from the code correctly detects insufficient permissions to procedures

WITH
grants AS (SELECT
        pronamespace, grantee, privilege_type,
        array_agg(DISTINCT proname ORDER BY proname) AS procs
        FROM (
                SELECT
                        pronamespace,
                        proname,
                        (aclexplode(COALESCE(proacl, acldefault('f', proowner)))).grantee,
                        (aclexplode(COALESCE(proacl, acldefault('f', proowner)))).privilege_type
                FROM pg_catalog.pg_proc
        ) AS grants
        GROUP BY 1, 2, 3
),
namespaces AS (
        SELECT
                nsp.oid, nsp.nspname,
                array_remove(array_agg(DISTINCT pro.proname ORDER BY pro.proname), NULL) AS procs
        FROM pg_catalog.pg_namespace nsp
        LEFT OUTER JOIN pg_catalog.pg_proc AS pro
                ON pro.pronamespace = nsp.oid
        WHERE nspname NOT LIKE 'pg\_%temp\_%' AND nspname <> 'pg_toast'
        GROUP BY 1, 2
)
SELECT
        COALESCE(privilege_type, '') AS "privilege",
        nspname AS "schema",
        COALESCE(rolname, 'public') AS grantee,
        nsp.procs <> COALESCE(grants.procs, ARRAY[]::name[]) AS "partial"
FROM namespaces AS nsp
LEFT OUTER JOIN grants
        ON pronamespace = nsp.oid
        AND privilege_type = ANY('{EXECUTE,EXECUTE}')
LEFT OUTER JOIN pg_catalog.pg_roles AS grantee ON grantee.oid = grants.grantee
WHERE NOT (array_length(nsp.procs, 1) IS NOT NULL AND grants.procs IS NULL)
ORDER BY 1, 2;

But the granting of privileges occurs only on functions, not on procedures:

Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Execute SQL query:
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "schema1_test" TO "db_pg_prod_dbname_dbname_db_service";
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Query terminated.                                duration=1.076821ms rows=0
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 CHANGE Grant privileges.                                grant="EXECUTE ON ALL FUNCTIONS IN SCHEMA myschema_prod TO db_pg_prod_dbname_dbname_db_service" database=dbname
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Execute SQL query:
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "myschema_prod" TO "db_pg_prod_dbname_dbname_db_service";
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Query terminated.                                duration=799.651µs rows=0
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 CHANGE Grant privileges.                                grant="EXECUTE ON ALL FUNCTIONS IN SCHEMA schema1_beta TO db_pg_prod_dbname_dbname_db_service" database=dbname
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Execute SQL query:
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "schema1_beta" TO "db_pg_prod_dbname_dbname_db_service";
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Query terminated.                                duration=243.104µs rows=0
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 CHANGE Grant privileges.                                grant="EXECUTE ON ALL FUNCTIONS IN SCHEMA myschema_test TO db_pg_prod_dbname_dbname_db_service" database=dbname
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Execute SQL query:
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "myschema_test" TO "db_pg_prod_dbname_dbname_db_service";
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Query terminated.                                duration=226.467µs rows=0
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 CHANGE Grant privileges.                                grant="EXECUTE ON ALL FUNCTIONS IN SCHEMA schema1_tests TO db_pg_prod_dbname_dbname_db_service" database=dbname
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Execute SQL query:
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "schema1_tests" TO "db_pg_prod_dbname_dbname_db_service";
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Query terminated.                                duration=615.19µs rows=0
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 CHANGE Grant privileges.                                grant="EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO db_pg_prod_dbname_dbname_db_service" database=dbname
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Execute SQL query:
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "public" TO "db_pg_prod_dbname_dbname_db_service";
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Query terminated.                                duration=2.554963ms rows=0
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 CHANGE Grant privileges.                                grant="EXECUTE ON ALL FUNCTIONS IN SCHEMA myschema_prod___old TO db_pg_prod_dbname_dbname_db_service" database=dbname
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Execute SQL query:
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "myschema_prod___old" TO "db_pg_prod_dbname_dbname_db_service";
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Query terminated.                                duration=1.15346ms rows=0
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 CHANGE Grant privileges.                                grant="EXECUTE ON ALL FUNCTIONS IN SCHEMA schema1_prod TO db_pg_prod_dbname_dbname_db_service" database=dbname
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Execute SQL query:
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "schema1_prod" TO "db_pg_prod_dbname_dbname_db_service";
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Query terminated.                                duration=593.278µs rows=0
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Privileges synchronized.                         acl="ALL FUNCTIONS IN SCHEMA" database=dbname
Apr 25 12:03:36 pgsrv-dbname-l.prod.dbs.example.com ldap2pg@15-dbname[552174]: 12:03:36 DEBUG  Stage 3: default privileges.                          

As a result, the privileges for the procedures are not granted. Such attempts are made endlessly.

Documentation says:
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.

Can you use GRANT EXECUTE ON ALL ROUTINES IN SCHEMA for granting for procedures and functions?

[bersace]

Hi @kkrasnov1 . Thanks for the feedback. Ok to use routine. Can you open a pull request with this ?

[kkrasnov1]

The ability to grant permissions to routines appeared in version 11 of PostgreSQL.
PostgreSQL versions prior to 11 will be unsupported. Is this acceptable?

[bersace]

The ability to grant permissions to routines appeared in version 11 of PostgreSQL. PostgreSQL versions prior to 11 will be unsupported. Is this acceptable?

Good point. This is not acceptable. Lets design this better.

[kkrasnov1]

Is it possible to determine the PostgreSQL version in the code for "ALL FUNCTIONS IN SCHEMA" and make a choice depending on the version?

This is not required for default privileges because 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.)