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
Type General Overview of some aspect of the database.
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
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

This query belongs to the following collections:

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 .

NameDescription
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

This query is classified under the following categories:

Security

Queries of this category provide information about the security measures.

NameDescription
SecurityQueries of this category provide information about the security measures.