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 query | Description |
---|---|
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. |
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 . |
Category name | Category description |
---|---|
Security | Queries of this category provide information about the security measures. |