Privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that have been granted to a superuser

Query goal:

Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that have been granted to a superuser. Superuser can do anything in the database and thus does not need the privileges. The result is a sign that perhaps the executed GRANT statements were incorrect (wrong username) or the grantee later got superuser status (that it shouldn't have).

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 the privileges or remove superuser status from the grantee.

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 IN (SELECT usename
FROM pg_user
WHERE usesuper=TRUE)
AND a_grantor.rolname<>a_grantee.rolname
ORDER BY object_type, object_name, privilege_type;

SQL statements for generating SQL statements that help us to fix the problem

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 %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 IN (SELECT usename FROM pg_user WHERE usesuper=TRUE) AND a_grantor.rolname<>a_grantee.rolname ORDER BY object_type, object_name, privilege_type;	Revoke the privileges.

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 %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 IN (SELECT usename
FROM pg_user
WHERE usesuper=TRUE)
AND a_grantor.rolname<>a_grantee.rolname
ORDER BY object_type, object_name, privilege_type;

Revoke the privileges.

Collections where the query belongs to

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 .

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 .

Categories where the query belongs to

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.

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.