Query 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 about the query: | The query excludes privileges granted to PUBLIC. |
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 unnecessary privileges. |
Data source: | system catalog only |
SQL query: | Click on query to copy it
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 query | Description |
---|---|
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. |
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. |