Security

For the best security check SQL, check out Crunchy Data gitrepo for Crunchy Check Access.

Table Grants

SELECT grantee
      ,table_catalog
      ,table_schema
      ,table_name
      ,string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges
FROM information_schema.role_table_grants
WHERE grantee != 'postgres' 
      -- grantee NOT IN ('postgres','PUBLIC')
GROUP BY grantee, table_catalog, table_schema, table_name;

Who Can Connect to What Databases?

--By User:
select pgu.usename as user_name,
       (select string_agg(pgd.datname, ',' order by pgd.datname) 
        from pg_database pgd 
        where has_database_privilege(pgu.usename, pgd.datname, 'CONNECT')) as database_name
from pg_user pgu
order by pgu.usename;
 
--By Database:
select pgd.datname as database_name,
       (select string_agg(pgu.usename, ',' order by pgu.usename) 
        from pg_user pgu 
        where has_database_privilege(pgu.usename, pgd.datname, 'CONNECT')) as user_name
from pg_database pgd
order by pgd.datname;

HBA Rules

 select * from pg_catalog.pg_hba_file_rules;