The list of all the queries

PUBLIC has the USAGE privilege of a schema

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 statements for generating SQL statements that help us to fix the problem

SQL queryDescription
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 where the query belongs to

Collection nameCollection description
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 where the query belongs to

Category nameCategory description
SecurityQueries of this category provide information about the security measures.

The list of all the queries