sql - How do I view grants on Redshift

Question

I'd like to view grants on redshifts.

I found this view for postgres:

CREATE OR REPLACE VIEW view_all_grants AS 
SELECT 
  use.usename as subject, 
  nsp.nspname as namespace, 
  c.relname as item, 
  c.relkind as type, 
  use2.usename as owner, 
  c.relacl, 
  (use2.usename != use.usename and c.relacl::text !~ ('({|,)' || use.usename || '=')) as public
FROM 
  pg_user use 
  cross join pg_class c 
  left join pg_namespace nsp on (c.relnamespace = nsp.oid) 
  left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE 
  c.relowner = use.usesysid or 
  c.relacl::text ~ ('({|,)(|' || use.usename || ')=') 
ORDER BY 
  subject, 
  namespace, 
  item 

Which doesn't work because the ::text cast of relacl fails with the following:

ERROR: cannot cast type aclitem[] to character varying [SQL State=42846] 

Modifying the query to

CREATE OR REPLACE VIEW view_all_grants AS 
SELECT 
  use.usename as subject, 
  nsp.nspname as namespace, 
  c.relname as item, 
  c.relkind as type, 
  use2.usename as owner, 
  c.relacl 
  -- , (use2.usename != use.usename and c.relacl::text !~ ('({|,)' || use.usename || '=')) as public
FROM 
  pg_user use 
  cross join pg_class c 
  left join pg_namespace nsp on (c.relnamespace = nsp.oid) 
  left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE 
  c.relowner = use.usesysid 
  -- or c.relacl::text ~ ('({|,)(|' || use.usename || ')=') 
ORDER BY 
  subject, 
  namespace, 
  item 

Allows the view to be created, but I'm concerned that this is not showing all relevant data.

How can I modify the view to work on redshift or is there an better/alternative way to view grants on redshift ?

UPDATE: Redshift has the HAS_TABLE_PRIVILEGE function to check grants. (see here)

Answer 1

Another variation be like:

SELECT * 
FROM 
    (
    SELECT 
        schemaname
        ,objectname
        ,usename
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'select') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS sel
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'insert') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS ins
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'update') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS upd
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'delete') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS del
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'references') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS ref
    FROM
        (
        SELECT schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj FROM pg_tables
        WHERE schemaname not in ('pg_internal')
        UNION
        SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
        WHERE schemaname not in ('pg_internal')
        ) AS objs
        ,(SELECT * FROM pg_user) AS usrs
    ORDER BY fullobj
    )
WHERE (sel = true or ins = true or upd = true or del = true or ref = true)
and schemaname='<opt schema>'
and usename = '<opt username>';