Query goal: | A user that corresponds to an application does not have to have privileges to use trigger functions. If it has these, then it violates the principle of least privilege. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | High (Few or no false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Revoke unnecessary privileges. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH privs AS (SELECT rp.routine_schema, rp.routine_name, pg_get_function_identity_arguments(translate(substring(rp.specific_name,'_[0-9]+$'),'_','')::int::oid) AS parameters, rp.grantee, rp.privilege_type, rp.is_grantable FROM information_schema.routine_privileges AS rp, information_schema.routines AS r WHERE rp.routine_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND rp.routine_schema=r.routine_schema AND rp.specific_name=r.specific_name AND r.data_type='trigger' AND grantee<>'PUBLIC' AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles r WHERE rolsuper=TRUE AND rp.grantee=r.rolname)) SELECT routine_schema, routine_name, parameters, grantee, privilege_type FROM privs ORDER BY routine_schema, routine_name, parameters, privilege_type; |
SQL query | Description |
---|---|
WITH privs AS (SELECT rp.routine_schema, rp.routine_name, pg_get_function_identity_arguments(translate(substring(rp.specific_name,'_[0-9]+$'),'_','')::int::oid) AS parameters, rp.grantee, rp.privilege_type, rp.is_grantable FROM information_schema.routine_privileges AS rp, information_schema.routines AS r WHERE rp.routine_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND rp.routine_schema=r.routine_schema AND rp.specific_name=r.specific_name AND r.data_type='trigger' AND grantee<>'PUBLIC' AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles r WHERE rolsuper=TRUE AND rp.grantee=r.rolname)) SELECT format('REVOKE %1$s ON FUNCTION %2$I.%3$I(%4$s) FROM %5$I;', privilege_type, routine_schema, routine_name, parameters, grantee) AS statements FROM privs ORDER BY routine_schema, routine_name; | Revoke unnecessary privileges. |
Collection name | Collection description |
---|---|
Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Category name | Category description |
---|---|
Security | Queries of this category provide information about the security measures. |
Triggers and rules | Queries of this category provide information about triggers and rules in a database. |
User-defined routines | Queries of this category provide information about the user-defined routines |
Reference |
---|
https://en.wikipedia.org/wiki/Principle_of_least_privilege |
https://www.postgresql.org/docs/current/ddl-priv.html |