Goal 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)
Reliability Medium (Medium number of false-positive results)
License MIT License
Fixing Suggestion Revoke the excessive privileges.
Data Source system catalog only
SQL Query
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 FixDescription
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:

NameDescription
Find problems automaticallyQueries, 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:

NameDescription
Data typesQueries of this category provide information about the data types and their usage.
Distributed databaseQueries of this category provide information about the foreign table mechanism.
DomainsQueries of this category provide information about reusable specifications of column properties.
SecurityQueries of this category provide information about the security measures.

Further reading and related materials:

Reference
https://www.postgresql.org/docs/current/ddl-priv.html