The list of all the queries

Database connect privilege is missing

Query 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.
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: Grant connect privilege to the user.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

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

SQL queryDescription
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 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
SecurityQueries of this category provide information about the security measures.

The list of all the queries