Query goal: | Find schemas where PUBLIC has the usage privilege. |
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 the privilege. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH schema_privs AS (SELECT nspname AS schema_name, unnest(nspacl)::text AS priv FROM pg_namespace WHERE nspname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL)) SELECT schema_name FROM schema_privs WHERE priv ~ '^=[[:alpha:]]*U[[:alpha:]]*/postgres' ORDER BY schema_name; |
SQL query | Description |
---|---|
WITH schema_privs AS (SELECT nspname, unnest(nspacl)::text AS priv FROM pg_namespace WHERE nspname 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('REVOKE USAGE ON SCHEMA %1$I FROM PUBLIC;', nspname) AS statements FROM schema_privs WHERE priv='=U/postgres' ORDER BY nspname; | Revoke the 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. |