Goal Find privileges to use system catalog base tables or views that have been granted to public.
Type General (Overview of some aspect of the database.)
License MIT License
Data Source INFORMATION_SCHEMA only
SQL Query
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;

Collections

This query belongs to the following collections:

NameDescription
Find problems by overviewQueries 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 .
Categories

This query is classified under the following categories:

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