Query goal: | If you do give privileges to base tables, then these should follow the principle of least privilege - the smallest possible amount of privileges to the smallest possible set of columns |
Query type: | General (Overview of some aspect of the database.) |
Query license: | MIT License |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
SELECT cp.grantee, cp.table_schema, cp.table_name, cp.column_name, array_agg(cp.privilege_type::TEXT ORDER BY cp.privilege_type) AS privileges, Count(*) AS number_of_privileges FROM information_schema.column_privileges AS cp INNER JOIN information_schema.tables AS t ON t.table_schema=cp.table_schema AND t.table_name=cp.table_name WHERE t.table_type='BASE TABLE' AND cp.table_schema <>ALL (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND grantee <> ALL (SELECT tableowner FROM pg_catalog.pg_tables AS pt WHERE pt.schemaname=cp.table_schema AND pt.tablename=cp.table_name) GROUP BY cp.grantee, cp.table_schema, cp.table_name, cp.column_name ORDER BY Count(*) DESC, cp.grantee, cp.table_schema, cp.table_name; |
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://en.wikipedia.org/wiki/Principle_of_least_privilege |
https://www.postgresql.org/docs/current/ddl-priv.html |