Find excessive privileges on databases, schemas, domains, collations, sequences, foreign data wrappers, and foreign servers that are probably not needed by a typical application.
Type
Problem detection (Each row in the result could represent a flaw in the design)
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, 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 '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 ((object_type='DATABASE' AND privilege_type<>'CONNECT')
OR object_type IN ('COLLATION','DOMAIN', 'FOREIGN DATA WRAPPER', 'FOREIGN SERVER', 'SEQUENCE')
OR (object_type='SCHEMA' AND privilege_type<>'USAGE')
)
AND a_grantee.rolsuper=FALSE
ORDER BY object_type, object_name;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
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 '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 %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 ((object_type='DATABASE' AND privilege_type<>'CONNECT')
OR object_type IN ('DOMAIN','TYPE','LANGUAGE','FOREIGN DATA WRAPPER','FOREIGN SERVER')
OR (object_type='SCHEMA' AND privilege_type<>'USAGE')
)
AND a_grantee.rolsuper=FALSE
ORDER BY object_type, object_name, privilege_type;
Revoke the privilege.
Collections
This query belongs to the following collections:
Name
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 .
Categories
This query is classified under the following categories:
Name
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.