Goal Find non-superusers who have a privilege to use a table or a routine but do not have the privilege to connect to the database.
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 (opens in new tab)
Fixing Suggestion Grant connect privilege to the user.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
WITH db_privs AS (SELECT datname, 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),
grantees AS (SELECT table_privileges.grantee
FROM information_schema.table_privileges
WHERE table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT routine_privileges.grantee
FROM information_schema.routine_privileges
WHERE routine_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT grantee
FROM grantees
WHERE NOT EXISTS (SELECT *
FROM db_extracted_privs AS dep
WHERE dep.grantee=grantees.grantee
AND privileges~'c')
AND grantee<>'PUBLIC'
AND NOT EXISTS (SELECT usename
FROM pg_user
WHERE pg_user.usename=grantees.grantee
AND usesuper=TRUE)
ORDER BY grantee;

SQL statements that help generate fixes for the identified problem.

Fix Action #1
WITH db_privs AS (SELECT datname, 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),
grantees AS (SELECT table_privileges.grantee
FROM information_schema.table_privileges
WHERE table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT routine_privileges.grantee
FROM information_schema.routine_privileges
WHERE routine_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT format('GRANT CONNECT ON DATABASE %1$I TO %2$I;', current_database(), grantee) AS statements
FROM grantees
WHERE NOT EXISTS (SELECT *
FROM db_extracted_privs AS dep
WHERE dep.grantee=grantees.grantee
AND privileges~'c')
AND grantee<>'PUBLIC'
AND NOT EXISTS (SELECT usename
FROM pg_user
WHERE pg_user.usename=grantees.grantee
AND usesuper=TRUE)
ORDER BY grantee;

Grant the connect privilege.

SQL Query to Generate FixDescription
WITH db_privs AS (SELECT datname, 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),
grantees AS (SELECT table_privileges.grantee
FROM information_schema.table_privileges
WHERE table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT routine_privileges.grantee
FROM information_schema.routine_privileges
WHERE routine_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT format('GRANT CONNECT ON DATABASE %1$I TO %2$I;', current_database(), grantee) AS statements
FROM grantees
WHERE NOT EXISTS (SELECT *
FROM db_extracted_privs AS dep
WHERE dep.grantee=grantees.grantee
AND privileges~'c')
AND grantee<>'PUBLIC'
AND NOT EXISTS (SELECT usename
FROM pg_user
WHERE pg_user.usename=grantees.grantee
AND usesuper=TRUE)
ORDER BY grantee;
Grant the connect privilege.

Collections

This query belongs to the following collections:

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 .

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:

Security

Queries of this category provide information about the security measures.

NameDescription
SecurityQueries of this category provide information about the security measures.