PUBLIC has the USAGE privilege of a schema

Back to Catalog
Goal Find schemas where PUBLIC has the usage privilege.
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 License
Fixing Suggestion Revoke the privilege.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
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 statements that help generate fixes for the identified problem.

SQL Query to Generate FixDescription
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.
Collections

This query belongs to the following collections:

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:

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