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)

Sutter

What error do you get when you don't comment out those two lines? If it is definitely the cast that is failing, what is the type of the relacl column? – Venita

Updated with the actual error message from the cast. – Sutter

Does select 'postgres=arwdDxt/postgres'::aclitem::text work OK? How about select '{postgres=arwdDxt/postgres,=r/postgres}'::aclitem[]::text[]::text? If those work, then you should be able to change c.relacl::text to c.relacl::text[]::text. If not, you'll need to find another way of looking inside the ACLs (or create a custom cast). – Venita

Thanks for looking at this, select 'anmediaextract01=arwdxt/anmediaextract01'::aclitem::text (note no 'D') fails with same error. Same with the other variant. Don't thnk it's possible to create custom casts in redshift btw. – Sutter

Ah ha! - It seems that HAS_TABLE_PRIVILEGE function works on redshift: docs.aws.amazon.com/redshift/latest/dg/… – Sutter

Note that the HAS_TABLE_PRIVILEGE does not show privileges granted to groups which is why I had to build the query shown below. – Dashboard

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','pg_automv')
        UNION
        SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
        WHERE schemaname not in ('pg_internal','pg_automv')
        ) 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>';

Fitly

I'm receiving [3F000][500310] [Amazon](500310) Invalid operation: schema "pg_temp_12" does not exist;, any idea on what can be wrong? – Thomas

Answer 2

Something along the lines off:

select tablename, 
   HAS_TABLE_PRIVILEGE(tablename, 'select') as select,
   HAS_TABLE_PRIVILEGE(tablename, 'insert') as insert,
   HAS_TABLE_PRIVILEGE(tablename, 'update') as update,
   HAS_TABLE_PRIVILEGE(tablename, 'delete') as delete, 
   HAS_TABLE_PRIVILEGE(tablename, 'references') as references 
from pg_tables where schemaname='public' order by tablename;

gives me all I need.

Sutter

I receive error message [42P01][500310] [Amazon](500310) Invalid operation: relation "sql_features" does not exist; :/ – Thomas

Answer 3

A development on the answer from mike_pdb I came up with the following

 WITH object_list(schema_name,object_name,permission_info)
 AS (
    SELECT N.nspname, C.relname, array_to_string(relacl,',')
    FROM pg_class AS C
        INNER JOIN pg_namespace AS N
        ON C.relnamespace = N.oid
    WHERE C.relkind in ('v','r')
    AND  N.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
    AND C.relacl[1] IS NOT NULL
  ),
  object_permissions(schema_name,object_name,permission_string)
  AS (
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',1) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',2) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',3) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',4) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',5) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',6) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',7) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',8) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',9) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',10) FROM object_list
  ),
  permission_parts(schema_name, object_name,security_principal, permission_pattern)
  AS (
      SELECT
          schema_name,
          object_name,
          LEFT(permission_string ,CHARINDEX('=',permission_string)-1),
          SPLIT_PART(SPLIT_PART(permission_string,'=',2),'/',1)
      FROM object_permissions
      WHERE permission_string >''
  )
SELECT
    schema_name,
    object_name,
    'GRANT ' ||
    SUBSTRING(
        case when charindex('r',permission_pattern) > 0 then ',SELECT ' else '' end
      ||case when charindex('w',permission_pattern) > 0 then ',UPDATE ' else '' end
      ||case when charindex('a',permission_pattern) > 0 then ',INSERT ' else '' end
      ||case when charindex('d',permission_pattern) > 0 then ',DELETE ' else '' end
      ||case when charindex('R',permission_pattern) > 0 then ',RULE ' else '' end
      ||case when charindex('x',permission_pattern) > 0 then ',REFERENCES ' else '' end
      ||case when charindex('t',permission_pattern) > 0 then ',TRIGGER ' else '' end
      ||case when charindex('X',permission_pattern) > 0 then ',EXECUTE ' else '' end
      ||case when charindex('U',permission_pattern) > 0 then ',USAGE ' else '' end
      ||case when charindex('C',permission_pattern) > 0 then ',CREATE ' else '' end
      ||case when charindex('T',permission_pattern) > 0 then ',TEMPORARY ' else '' end
    ,2,10000
    )
    || ' ON ' || schema_name||'.'||object_name
     || ' TO ' || security_principal
     || ';' as grantsql
FROM permission_parts

;

There are 3 common table expressions used here.

• object_list - Tables and views with their permissions array as a comma delimited string.
• object_permissions - schema/object (table or view) and a record per permission string. Note that the SPLIT_PART function does not allow a dynamic part position so the assumption here is that there are no more than 10 users or groups assigned direct permissions
• permission_parts The schema/object, security principal to whom permissions are granted and the security attributes that are set.

As per mike_pdb's solution the individual permission characters are converted into a concatenated list of grants. As we don't which grants will be used we use SUBSTRING from position 2 to discard the first comma in the list.

You can use exactly the same approach for scripting off schema permissions

WITH schema_list(schema_name, permission_info)
AS (
    SELECT nspname, array_to_string(nspacl,',')
    FROM pg_namespace
    WHERE nspacl[1] IS NOT NULL
    AND nspname NOT LIKE 'pg%' AND nspname NOT IN ('public','information_schema')
),
schema_permissions(schema_name,permission_string)
AS (
    SELECT schema_name,SPLIT_PART(permission_info,',',1) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',2) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',3) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',4) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',5) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',6) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',7) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',8) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',9) FROM schema_list
    UNION ALL
    SELECT schema_name,SPLIT_PART(permission_info,',',10) FROM schema_list
),
permission_parts(schema_name, security_principal, permission_pattern)
AS (
    SELECT
        schema_name,
        LEFT(permission_string ,CHARINDEX('=',permission_string)-1),
        SPLIT_PART(SPLIT_PART(permission_string,'=',2),'/',1)
    FROM schema_permissions
    WHERE permission_string >''
)
SELECT
    schema_name,
    'GRANT ' ||
    SUBSTRING(
        case when charindex('r',permission_pattern) > 0 then ',SELECT ' else '' end
      ||case when charindex('w',permission_pattern) > 0 then ',UPDATE ' else '' end
      ||case when charindex('a',permission_pattern) > 0 then ',INSERT ' else '' end
      ||case when charindex('d',permission_pattern) > 0 then ',DELETE ' else '' end
      ||case when charindex('R',permission_pattern) > 0 then ',RULE ' else '' end
      ||case when charindex('x',permission_pattern) > 0 then ',REFERENCES ' else '' end
      ||case when charindex('t',permission_pattern) > 0 then ',TRIGGER ' else '' end
      ||case when charindex('X',permission_pattern) > 0 then ',EXECUTE ' else '' end
      ||case when charindex('U',permission_pattern) > 0 then ',USAGE ' else '' end
      ||case when charindex('C',permission_pattern) > 0 then ',CREATE ' else '' end
      ||case when charindex('T',permission_pattern) > 0 then ',TEMPORARY ' else '' end
    ,2,10000
    )
    || ' ON SCHEMA ' || schema_name
     || ' TO ' || security_principal
     || ';' as grantsql
FROM permission_parts;

Hibernicism

Answer 4

The has_table_privilege function is handy, but doesn't always help in administration when you want to manage groups. I morphed your original query to create grant scripts for specific users or groups. This sample query can be easily morphed to accommodate your needs

select namespace||'.'||item as tablename , 
'grant ' || substring(
                case when charindex('r',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',select ' else '' end 
              ||case when charindex('w',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',update ' else '' end 
              ||case when charindex('a',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',insert ' else '' end 
              ||case when charindex('d',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',delete ' else '' end 
              ||case when charindex('R',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',rule ' else '' end 
              ||case when charindex('x',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',references ' else '' end 
              ||case when charindex('t',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',trigger ' else '' end 
              ||case when charindex('X',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',execute ' else '' end 
              ||case when charindex('U',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',usage ' else '' end 
              ||case when charindex('C',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',create ' else '' end 
              ||case when charindex('T',split_part(split_part(array_to_string(relacl, '|'),'group dw_developers=',2 ) ,'/',1)) > 0 then ',temporary ' else '' end 
           , 2,10000)
 || ' on '||namespace||'.'||item ||' to group dw_developers;' as grantsql
from 
(SELECT 
  use.usename as subject, 
  nsp.nspname as namespace, 
  c.relname as item, 
  c.relkind as type, 
  use2.usename as owner, 
  c.relacl 
 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  
  and  nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
 ORDER BY 
  subject,   namespace,   item 
 ) where relacl is not null
 and array_to_string(relacl, '|') like '%group dw_developers%' order by 1

Dashboard

....this seems to have a () closing problem in it somewhere, but I haven't tried to track it down. – Auer

Wouldn't this only get ownership relationships? So, if I create (and own) a schema and grant access to a group, it wouldn't appear, right? – Rrhagia

Answer 5

Here is another useful query to view grants on schema (usage, create) by user that I created based on the query above by @drtf:

SELECT * 
FROM 
    (
    SELECT 
        schemaname
        ,usename
        ,HAS_SCHEMA_PRIVILEGE(usrs.usename, schemaname, 'usage') AS usg
        ,HAS_SCHEMA_PRIVILEGE(usrs.usename, schemaname, 'create') AS crt
    FROM
        (
        SELECT distinct(schemaname) FROM pg_tables
        WHERE schemaname not in ('pg_internal')
        UNION
        SELECT distinct(schemaname) FROM pg_views
        WHERE schemaname not in ('pg_internal')
        ) AS objs
        ,(SELECT * FROM pg_user) AS usrs
    ORDER BY schemaname
    )
WHERE (usg = true or crt = true)
--and schemaname='<opt schemaname>'
--and usename = '<opt username>';

Fireweed

Answer 6

I struggled with this a lot and finally came up with a solution that gives me just what I want to see.

WITH tabledef as (
    SELECT schemaname,
        't' AS typename,
        tablename AS objectname,
        tableowner as owner,
        schemaname + '.' + tablename AS fullname
    FROM pg_tables
    UNION 
    SELECT schemaname,
        'v' AS typename,
        viewname AS objectname,
        viewowner as owner,
        schemaname + '.' + viewname AS fullname
    FROM pg_views
),
res AS (
    SELECT t.*,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'select')
    WHEN true THEN u.usename
    ELSE NULL END AS sel,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'insert')
    WHEN true THEN u.usename
    ELSE NULL END AS ins,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'update')
    WHEN true THEN u.usename
    ELSE NULL END AS upd,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'delete')
    WHEN true THEN u.usename
    ELSE NULL END AS del,
    CASE HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'references')
    WHEN true THEN u.usename
    ELSE NULL END AS ref
    FROM tabledef AS t
    JOIN pg_user AS u
    ON HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'select') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'insert') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'update') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'delete') = true
        OR HAS_TABLE_PRIVILEGE(u.usename, t.fullname, 'references') = true
        OR t.owner = u.usename
    WHERE t.schemaname = 'analytics'
)
SELECT schemaname, objectname, owner, sel, ins, upd, del, ref FROM res
WHERE sel not in ('rdsdb', '<superuser>')
ORDER BY schemaname, objectname;

The two important lines - One which points which schema to scan for access

WHERE t.schemaname = 'analytics'

And - Second which discards superuser permissions (They have complete permission anyway) from the results.

WHERE sel not in ('rdsdb', '<superuser>')

Parke

It's work in 2021 – Hammad

Answer 7

Because other proposed solutions have dependencies on pg tables and sometimes return odd error messages (reported in comments) I go with a more straightforward and manual approach:

select has_schema_privilege('my_user', 'my_schema', 'usage');
select has_table_privilege('my_user', 'my_schema.my_table', 'select');

which most of the times is good enough for me; based on:

https://docs.aws.amazon.com/redshift/latest/dg/r_HAS_SCHEMA_PRIVILEGE.html https://docs.aws.amazon.com/redshift/latest/dg/r_HAS_TABLE_PRIVILEGE.html

All other permutations can be achieved based on docs and joining with other tables (e.g. svv_table_info, given that pg tables are not always reliable).

Thomas