dalibo
Problem with procedure permissions
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?
Hi @kkrasnov1 . Thanks for the feedback. Ok to use routine. Can you open a pull request with this ?
The ability to grant permissions to routines appeared in version 11 of PostgreSQL. PostgreSQL versions prior to 11 will be unsupported. Is this acceptable?
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.
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.)