The list of all the queries

Missing USAGE privileges on schema

Query goal: If a user has a privilege to use a schema object, then the user must also have the usage privilege on the schema that contains the object.
Notes about the query: Query finds users who have a privilege to use a table, a table column, a routine, or a sequence but do not have the usage privilege on the schema that contains it. The query does not consider schema objects of the default schema public. The use of UNION ensures that duplicates are eliminated from the result.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Medium (Medium number of false-positive results)
Query license: MIT License
Fixing suggestion: Grant the privilege to use the schema.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH usage_privs AS (SELECT nspname, unnest(nspacl)::text AS priv  
FROM pg_namespace),
table_schema_usage AS (SELECT grantee, table_schema AS schema
FROM INFORMATION_SCHEMA.table_privileges tp
WHERE grantee<>'PUBLIC' AND table_schema NOT IN ('pg_catalog') AND
NOT EXISTS (SELECT 1
FROM pg_catalog.pg_roles r
WHERE rolsuper=TRUE AND tp.grantee=r.rolname) AND 
NOT EXISTS (SELECT 1
FROM usage_privs up WHERE tp.table_schema=up.nspname
AND up.priv LIKE '%' || tp.grantee || '=U%')),
column_schema_usage AS (SELECT grantee, table_schema AS schema
FROM INFORMATION_SCHEMA.column_privileges cp
WHERE grantee<>'PUBLIC' AND table_schema NOT IN ('pg_catalog') AND
NOT EXISTS (SELECT 1
FROM pg_catalog.pg_roles r
WHERE rolsuper=TRUE AND cp.grantee=r.rolname) AND 
NOT EXISTS (SELECT 1
FROM usage_privs up WHERE cp.table_schema=up.nspname
AND up.priv LIKE '%' || cp.grantee || '=U%')),
routine_schema_usage AS (SELECT grantee, specific_schema AS schema
FROM INFORMATION_SCHEMA.routine_privileges rp
WHERE grantee<>'PUBLIC' AND specific_schema NOT IN ('pg_catalog') AND
NOT EXISTS (SELECT 1
FROM pg_catalog.pg_roles r
WHERE rolsuper=TRUE AND rp.grantee=r.rolname) AND 
NOT EXISTS (SELECT 1
FROM usage_privs up WHERE rp.specific_schema=up.nspname
AND up.priv LIKE '%' || rp.grantee || '=U%')),
sequence_schema_usage AS (SELECT grantee, object_schema AS schema
FROM INFORMATION_SCHEMA.usage_privileges up
WHERE grantee<>'PUBLIC' 
AND object_type='SEQUENCE'
AND object_schema NOT IN ('pg_catalog') AND
NOT EXISTS (SELECT 1
FROM pg_catalog.pg_roles r
WHERE rolsuper=TRUE AND up.grantee=r.rolname) AND 
NOT EXISTS (SELECT 1
FROM usage_privs upr WHERE up.object_schema=upr.nspname
AND upr.priv LIKE '%' || up.grantee || '=U%'))
SELECT grantee, schema
FROM table_schema_usage
UNION SELECT grantee, schema
FROM column_schema_usage
UNION SELECT grantee, schema
FROM routine_schema_usage
UNION SELECT grantee, schema
FROM sequence_schema_usage
ORDER BY schema, grantee;

SQL statements for generating SQL statements that help us to fix the problem

SQL queryDescription
WITH usage_privs AS (SELECT nspname, unnest(nspacl)::text AS priv  
FROM pg_namespace),
table_schema_usage AS (SELECT grantee, table_schema AS schema
FROM INFORMATION_SCHEMA.table_privileges tp
WHERE grantee<>'PUBLIC' AND table_schema NOT IN ('public', 'pg_catalog') AND
NOT EXISTS (SELECT 1
FROM pg_catalog.pg_roles r
WHERE rolsuper=TRUE AND tp.grantee=r.rolname) AND 
NOT EXISTS (SELECT 1
FROM usage_privs up WHERE tp.table_schema=up.nspname
AND up.priv LIKE '%' || tp.grantee || '=U%')),
column_schema_usage AS (SELECT grantee, table_schema AS schema
FROM INFORMATION_SCHEMA.column_privileges cp
WHERE grantee<>'PUBLIC' AND table_schema NOT IN ('public', 'pg_catalog') AND
NOT EXISTS (SELECT 1
FROM pg_catalog.pg_roles r
WHERE rolsuper=TRUE AND cp.grantee=r.rolname) AND 
NOT EXISTS (SELECT 1
FROM usage_privs up WHERE cp.table_schema=up.nspname
AND up.priv LIKE '%' || cp.grantee || '=U%')),
routine_schema_usage AS (SELECT grantee, specific_schema AS schema
FROM INFORMATION_SCHEMA.routine_privileges rp
WHERE grantee<>'PUBLIC' AND specific_schema NOT IN ('public', 'pg_catalog') AND
NOT EXISTS (SELECT 1
FROM pg_catalog.pg_roles r
WHERE rolsuper=TRUE AND rp.grantee=r.rolname) AND 
NOT EXISTS (SELECT 1
FROM usage_privs up WHERE rp.specific_schema=up.nspname
AND up.priv LIKE '%' || rp.grantee || '=U%')),
sequence_schema_usage AS (SELECT grantee, object_schema AS schema
FROM INFORMATION_SCHEMA.usage_privileges up
WHERE grantee<>'PUBLIC' 
AND object_type='SEQUENCE'
AND object_schema NOT IN ('public', 'pg_catalog') AND
NOT EXISTS (SELECT 1
FROM pg_catalog.pg_roles r
WHERE rolsuper=TRUE AND up.grantee=r.rolname) AND 
NOT EXISTS (SELECT 1
FROM usage_privs upr WHERE up.object_schema=upr.nspname
AND upr.priv LIKE '%' || up.grantee || '=U%')),
lack_of_privileges AS (SELECT grantee, schema
FROM table_schema_usage
UNION SELECT grantee, schema
FROM column_schema_usage
UNION SELECT grantee, schema
FROM routine_schema_usage
UNION SELECT grantee, schema
FROM sequence_schema_usage)
SELECT format('GRANT USAGE ON SCHEMA %1$I TO %2$I;', schema, grantee) AS statements
FROM lack_of_privileges
ORDER BY schema, grantee;
Grant the privilege to use the schema.

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.

Reference materials for further reading

Reference
https://www.postgresql.org/docs/current/ddl-priv.html

The list of all the queries