Query goal: | Find privileges to use system catalog base tables or views that have been granted to public. |
Query type: | General (Overview of some aspect of the database.) |
Query license: | MIT License |
Data source: | INFORMATION_SCHEMA only |
SQL query: | Click on query to copy it
SELECT tp.grantee, t.table_type, tp.table_schema, tp.table_name, array_agg(tp.privilege_type::TEXT) AS privileges, Count(*) AS number_of_privileges FROM information_schema.table_privileges AS tp INNER JOIN information_schema.tables AS t ON t.table_schema=tp.table_schema AND t.table_name=tp.table_name AND tp.table_schema IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND grantee ='PUBLIC' GROUP BY tp.grantee, t.table_type, tp.table_schema, tp.table_name ORDER BY Count(*) DESC, grantee, tp.table_schema, t.table_type, tp.table_name; |
Collection name | Collection description |
---|---|
Find problems by overview | Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not . |
Category name | Category description |
---|---|
Security | Queries of this category provide information about the security measures. |
Reference |
---|
https://stackoverflow.com/questions/20554771/postgresql-revoking-permissions-from-pg-catalog-tables |