Postgresql query that prints database level permissions

Question

Im trying to now only display the user, schema & tables but also show the database the schema & tables belong to. Any suggestions on what I should add to this query?

SELECT
    u.usename as user, t.schemaname as schema, t.tablename as table,
    has_table_privilege(u.usename,t.schemaname||'.'||t.tablename,'select') AS "Select",
    has_table_privilege(u.usename,t.schemaname||'.'||t.tablename,'insert') AS "Insert",
    has_table_privilege(u.usename,t.schemaname||'.'||t.tablename,'update') AS "Update",
    has_table_privilege(u.usename,t.schemaname||'.'||t.tablename,'delete') AS "Delete",
    has_table_privilege(u.usename,t.schemaname||'.'||t.tablename,'references') AS "Reference"
FROM pg_user u
CROSS JOIN pg_tables t
WHERE t.schemaname != 'information_schema' and t.schemaname != 'pg_internal' and t.schemaname != 'pg_catalog' and t.tablename not like '% %'
ORDER BY u.usename, t.schemaname, t.tablename;

Please add a tag for the specific type of SQL you're working with (mysql, oracle, ms sql, teradata, etc) — devlin carnate
– devlin carnate, Commented May 27, 2020 at 20:58

Timmus Agersea · Accepted Answer · 2020-05-28 05:45:46Z

I suggest looking into information_schema.*. These are sql standards and information_schema.tables should have what you want.

You can replace the use of pg_tables as such:

select
    u.usename                                                                           as user,
    t.table_catalog                                                                     as database,
    t.table_schema                                                                      as schema,
    t.table_name                                                                        as table,
    has_table_privilege(u.usename, t.table_schema || '.' || t.table_name, 'select')     as "Select",
    has_table_privilege(u.usename, t.table_schema || '.' || t.table_name, 'insert')     as "Insert",
    has_table_privilege(u.usename, t.table_schema || '.' || t.table_name, 'update')     as "Update",
    has_table_privilege(u.usename, t.table_schema || '.' || t.table_name, 'delete')     as "Delete",
    has_table_privilege(u.usename, t.table_schema || '.' || t.table_name, 'references') as "Reference"
from
    pg_user                              u
    cross join information_schema.tables t
where
    t.table_schema != 'information_schema' and t.table_schema != 'pg_internal' and t.table_schema != 'pg_catalog'
    and t.table_schema not like '% %'
order by
    u.usename, t.table_schema, t.table_name;

Collectives™ on Stack Overflow

Postgresql query that prints database level permissions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related