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 (opens in new tab)
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.

Fix Action #1
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.

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:

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 .

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:

Security

Queries of this category provide information about the security measures.

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