Perhaps unnecessary privileges to use the database

Back to Catalog
Goal Find as to whether a database user (except postgres), who is not a superuser, has Create (C) or Temporary (T) privileges to use the database.
Notes The query excludes privileges granted to PUBLIC.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability High (Few or no false-positive results)
License MIT License
Fixing Suggestion Revoke unnecessary privileges.
Data Source system catalog only
SQL Query 
WITH db_privs AS (SELECT unnest(datacl)::text AS priv
FROM pg_database
WHERE datname=current_database()),
db_extracted_privs AS (SELECT 
left(priv, (position('=' IN priv)-1)) AS grantee,
substring(priv, position('=' IN priv)+1, position('/' IN priv)-position('=' IN priv)-1) AS privileges
FROM db_privs)
SELECT grantee, privileges
FROM db_extracted_privs AS dep
WHERE NOT EXISTS (SELECT 1
FROM pg_roles r
WHERE rolsuper=TRUE AND dep.grantee=r.rolname)
AND grantee<>''
AND privileges~'[CT]'
ORDER BY grantee;

SQL statements that help generate fixes for the identified problem.

SQL Query to Generate FixDescription
WITH db_privs AS (SELECT unnest(datacl)::text AS priv
FROM pg_database
WHERE datname=current_database()),
db_extracted_privs AS (SELECT 
left(priv, (position('=' IN priv)-1)) AS grantee,
substring(priv, position('=' IN priv)+1, position('/' IN priv)-position('=' IN priv)-1) AS privileges
FROM db_privs)
SELECT format('REVOKE CREATE, TEMPORARY ON DATABASE %1$I FROM %2$I;', current_database(), grantee) AS statements
FROM db_extracted_privs AS dep
WHERE NOT EXISTS (SELECT 1
FROM pg_roles r
WHERE rolsuper=TRUE AND dep.grantee=r.rolname)
AND grantee<>''
AND privileges~'[CT]'
ORDER BY grantee;
Revoke the CREATE and TEMPORARY privileges.
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
SecurityQueries of this category provide information about the security measures.