CODEWITHSUNDEEP
sqlpostgresql
CatGirl19
CatGirl19

Reputation: 209

Postgresql query that prints database level permissions

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;

Upvotes: 0

Views: 360

Answers (1)

Timmus Agersea
Timmus Agersea

Reputation: 32

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;

Upvotes: 2

Related Questions