Query to return user rights for each table with one right per result row

Question

I'd like to write query that dumps out the rights for each user against reach table. The reason for this search is so that I can snapshot table rights and then compare them across servers, or before and after I run a big GRANTS resetting script. I'm looking for output that is easy to compare, so something like this:

schema_name table_name   qualified_name  owner_name privilege   setting
api         base4        api.base4       postgres   delete      TRUE
api         bucket_test  api.bucket_test postgres   delete      TRUE

I've written a query that gets me part of the way there, but with the privilege names and settings as column pairs. I could cut the columns in half by naming them after the privilege, but I'm after the narrow row format above. That makes the comparisons I'll run granular to the table+user+privilege.

schema_name   table_name   qualified_name   owner_name   privilege   delete   privilege   insert   privilege    references  privilege setting   privilege   trigger   privilege   truncate   privilege   update
api           base4        api.base4        postgres     delete      TRUE     insert      TRUE     references   TRUE        select    TRUE      trigger     TRUE      truncate    TRUE       update      TRUE
api          bucket_test   api.bucket_test  postgres     delete      TRUE     insert      TRUE     references   TRUE        select    TRUE      trigger     TRUE      truncate    TRUE       update      TRUE

Can someone suggest the right join or unnest+join to rework the query I've got now?

And, yes, this query will generate a lot of result rows. That's okay, it's what I'm after.

with 
table_list as
(   select schemaname as schema_name,
            tablename as table_name,
        quote_ident(schemaname) || '.' || quote_ident(tablename) as qualified_name,
             tableowner as owner_name

      from pg_tables

     where schemaname in ('data','api')  

  order by 3),

user_list as 
(   select usename as user_name
      from pg_user
  order by 1)

     select table_list.schema_name,
            table_list.table_name,
            table_list.qualified_name,
            table_list.owner_name,
         'delete' as privilege, has_table_privilege(user_list.user_name, concat(table_list.qualified_name), 'delete') as delete,
         'insert' as privilege, has_table_privilege(user_list.user_name, concat(table_list.qualified_name), 'insert') as insert,
         'references' as privilege, has_table_privilege(user_list.user_name, concat(table_list.qualified_name), 'references') as references,
         'select' as privilege, has_table_privilege(user_list.user_name, concat(table_list.qualified_name), 'select') as select,
         'trigger' as privilege, has_table_privilege(user_list.user_name, concat(table_list.qualified_name), 'trigger') as trigger,
         'truncate' as privilege, has_table_privilege(user_list.user_name, concat(table_list.qualified_name), 'truncate') as truncate,
         'update' as privilege, has_table_privilege(user_list.user_name, concat(table_list.qualified_name), 'update') as update

       from table_list
 cross join user_list

I'm on Postgres 11.4 on RDS.

Follow-up

For anyone finding this question later, here's a version of the final query as a view:

DROP VIEW IF EXISTS data.table_grants;
CREATE OR REPLACE VIEW data.table_grants AS

with 
table_list as
(   select schemaname as schema_name,
            tablename as table_name,
             schemaname::text || '.' || tablename::text as qualified_name,
             tableowner as owner_name

      from pg_tables

     where schemaname in ('data','api')  

  order by 3),

user_list as 
(   select usename as user_name
      from pg_user
  order by 1)

select
  table_list.*,
  user_list.user_name,
  privilege,
  has_table_privilege(user_name, qualified_name, privilege) as setting
from
  table_list
  cross join user_list
  cross join (values
    ('delete'), ('insert'), ('references'), ('select'), ('trigger'), ('truncate'), ('update')
  ) as p(privilege);

ALTER TABLE data.table_grants
    OWNER TO user_change_structure;

This makes searching on table grants a bit simpler, like this one to see the rights granted on a table named item

  select *
    from table_grants 
   where table_name = 'item'

order by user_name,
         privilege;

Or this query to get a summarized view of user rights on a specific table:

  select qualified_name,
         owner_name,
         user_name,
         array_agg(privilege) as rights

    from table_grants 

   where table_name = 'item' and
         setting = true

group by qualified_name,
         owner_name,
         user_name;

The queries above aren't necessarily optimally efficient, and the view's product is tables * users * 8...but it's all instantaneous for me with less than 100 tables and about 15 roles.

Answer 1

Like Islingre already suggested in the comments, you can use

select
  table_list.*,
  privilege,
  has_table_privilege(user_name, qualified_name, privilege) as setting
from
  table_list
  cross join user_list
  cross join (values
    ('delete'), ('insert'), ('references'), ('select'), ('trigger'), ('truncate'), ('update')
  ) as p(privilege)