The list of all the queries

Grantable privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers

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 statements for generating SQL statements that help us to fix the problem

SQL queryDescription
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.

Collections where the query belongs to

Collection nameCollection description
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 where the query belongs to

Category nameCategory description
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.
Sequence generatorsQueries of this category provide information about sequence generators and their usage.

Reference materials for further reading

Reference
https://en.wikipedia.org/wiki/Principle_of_least_privilege
https://www.postgresql.org/docs/current/ddl-priv.html

The list of all the queries