Query goal: | Check as to whether there are no unnecessary privileges. |
Notes about the query: | The query also finds privileges that the owner has towards the table. |
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 grantee, table_schema, table_name, table_type, array_agg(privilege_type::TEXT) AS privileges, Count(*) AS number_of_privileges FROM information_schema.table_privileges INNER JOIN information_schema.tables USING (table_schema, table_name) WHERE table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) GROUP BY grantee, table_schema, table_name, table_type ORDER BY Count(*) DESC, table_type, table_schema, 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://www.postgresql.org/docs/current/ddl-priv.html |