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
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.
Type
Problem detection (Each row in the result could represent a flaw in the design)
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 that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
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
This query belongs to the following collections:
Name
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 .
Categories
This query is classified under the following categories:
Name
Description
Security
Queries of this category provide information about the security measures.