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 query | 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. |
Collection name | Collection 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 . |
Category name | Category description |
---|---|
Security | Queries of this category provide information about the security measures. |
Reference |
---|
https://www.postgresql.org/docs/current/ddl-priv.html |