Query goal: | Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. |
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 WITH GRANT OPTION privilege. |
Data source: | system catalog only |
SQL query: | Click on query to copy it
WITH privs AS (SELECT 'SCHEMA' AS object_type, nspname AS object_name, string_to_array(translate(aclexplode(nspacl)::text,'()',''),',') AS privileges FROM pg_namespace AS nc INNER JOIN pg_authid AS a ON nc.nspowner=a.oid WHERE (nc.nspname='public' OR rolname<>'postgres') UNION SELECT 'DATABASE' AS object_type, datname AS object_name, string_to_array(translate(aclexplode(datacl)::text,'()',''),',') AS privileges FROM pg_database AS db INNER JOIN pg_authid AS a ON db.datdba=a.oid WHERE rolname<>'postgres' AND datname=current_database() UNION SELECT 'LANGUAGE' AS object_type, lanname AS object_name, string_to_array(translate(aclexplode(lanacl)::text,'()',''),',') AS privileges FROM pg_language UNION SELECT CASE WHEN typtype='d' THEN 'DOMAIN' ELSE 'TYPE' END AS object_type, nc.nspname || '.' || typname AS type_name, string_to_array(translate(aclexplode(typacl)::text,'()',''),',') AS privileges FROM pg_type AS t INNER JOIN pg_namespace AS nc ON t.typnamespace=nc.oid INNER JOIN pg_authid AS a ON nc.nspowner=a.oid WHERE (nc.nspname='public' OR rolname<>'postgres') UNION SELECT 'SEQUENCE' AS object_type, nc.nspname || '.' || relname AS object_name, string_to_array(translate(aclexplode(relacl)::text,'()',''),',') AS privileges FROM pg_class AS c INNER JOIN pg_namespace AS nc ON c.relnamespace=nc.oid INNER JOIN pg_authid AS a ON nc.nspowner=a.oid WHERE (nc.nspname='public' OR rolname<>'postgres') AND c.relkind='S' UNION SELECT 'FOREIGN SERVER' AS object_type, srvname AS object_name, string_to_array(translate(aclexplode(srvacl)::text,'()',''),',') AS privileges FROM pg_foreign_server UNION SELECT 'FOREIGN DATA WRAPPER' AS object_type, fdwname AS object_name, string_to_array(translate(aclexplode(fdwacl)::text,'()',''),',') AS privileges FROM pg_foreign_data_wrapper ), privs_cleaned AS (SELECT object_type, object_name, privileges[1]::oid AS grantor, privileges[2]::oid AS grantee, privileges[3] AS privilege_type, privileges[4]::boolean AS is_grantable FROM privs) SELECT object_type, object_name, a_grantor.rolname AS grantor, a_grantee.rolname AS grantee, privilege_type, is_grantable FROM privs_cleaned AS pc INNER JOIN pg_authid AS a_grantor ON pc.grantor=a_grantor.oid INNER JOIN pg_authid AS a_grantee ON pc.grantee=a_grantee.oid WHERE a_grantee.rolname<>'postgres' AND a_grantor.rolname<>a_grantee.rolname AND is_grantable=TRUE ORDER BY object_type, object_name, privilege_type; |
SQL query | Description |
---|---|
WITH privs AS (SELECT 'SCHEMA' AS object_type, nspname AS object_name, string_to_array(translate(aclexplode(nspacl)::text,'()',''),',') AS privileges FROM pg_namespace AS nc INNER JOIN pg_authid AS a ON nc.nspowner=a.oid WHERE (nc.nspname='public' OR rolname<>'postgres') UNION SELECT 'DATABASE' AS object_type, datname AS object_name, string_to_array(translate(aclexplode(datacl)::text,'()',''),',') AS privileges FROM pg_database AS db INNER JOIN pg_authid AS a ON db.datdba=a.oid WHERE rolname<>'postgres' AND datname=current_database() UNION SELECT 'LANGUAGE' AS object_type, lanname AS object_name, string_to_array(translate(aclexplode(lanacl)::text,'()',''),',') AS privileges FROM pg_language UNION SELECT CASE WHEN typtype='d' THEN 'DOMAIN' ELSE 'TYPE' END AS object_type, nc.nspname || '.' || typname AS type_name, string_to_array(translate(aclexplode(typacl)::text,'()',''),',') AS privileges FROM pg_type AS t INNER JOIN pg_namespace AS nc ON t.typnamespace=nc.oid INNER JOIN pg_authid AS a ON nc.nspowner=a.oid WHERE (nc.nspname='public' OR rolname<>'postgres') UNION SELECT 'SEQUENCE' AS object_type, nc.nspname || '.' || relname AS object_name, string_to_array(translate(aclexplode(relacl)::text,'()',''),',') AS privileges FROM pg_class AS c INNER JOIN pg_namespace AS nc ON c.relnamespace=nc.oid INNER JOIN pg_authid AS a ON nc.nspowner=a.oid WHERE (nc.nspname='public' OR rolname<>'postgres') AND c.relkind='S' UNION SELECT 'FOREIGN SERVER' AS object_type, srvname AS object_name, string_to_array(translate(aclexplode(srvacl)::text,'()',''),',') AS privileges FROM pg_foreign_server UNION SELECT 'FOREIGN DATA WRAPPER' AS object_type, fdwname AS object_name, string_to_array(translate(aclexplode(fdwacl)::text,'()',''),',') AS privileges FROM pg_foreign_data_wrapper ), privs_cleaned AS (SELECT object_type, object_name, privileges[1]::oid AS grantor, privileges[2]::oid AS grantee, privileges[3] AS privilege_type, privileges[4]::boolean AS is_grantable FROM privs) SELECT format('REVOKE GRANT OPTION FOR %1$s ON %2$s %3$s FROM %4$I;', privilege_type, object_type, object_name, a_grantee.rolname) AS statements FROM privs_cleaned AS pc INNER JOIN pg_authid AS a_grantor ON pc.grantor=a_grantor.oid INNER JOIN pg_authid AS a_grantee ON pc.grantee=a_grantee.oid WHERE a_grantee.rolname<>'postgres' AND a_grantor.rolname<>a_grantee.rolname AND is_grantable=TRUE ORDER BY object_type, object_name, privilege_type; | Revoke WITH GRANT OPTION privilege. |
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 |
---|---|
Data types | Queries of this category provide information about the data types and their usage. |
Distributed database | Queries of this category provide information about the foreign table mechanism. |
Domains | Queries of this category provide information about reusable specifications of column properties. |
Security | Queries of this category provide information about the security measures. |
Sequence generators | Queries of this category provide information about sequence generators and their usage. |
Reference |
---|
https://en.wikipedia.org/wiki/Principle_of_least_privilege |
https://www.postgresql.org/docs/current/ddl-priv.html |