The list of all the queries

Privileges to use base table columns

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;

Collections where the query belongs to

Collection nameCollection description
Find problems automaticallyQueries, 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 where the query belongs to

Category nameCategory description
SecurityQueries of this category provide information about the security measures.

Reference materials for further reading

Reference
https://en.wikipedia.org/wiki/Principle_of_least_privilege
https://www.postgresql.org/docs/current/ddl-priv.html

The list of all the queries