Permissions in Redshift administration

As with any database administration strategy, management of Redshift requires setting and revoking permissions.  When first setting up permissions, it is ideal to establish groups as the basic unit of user access.  This keeps us from having to manage hundreds of permissions as users enter and leave organizations.  If you haven’t done this early on and are managing permissions on the user level, you can leverage some of the queries below to make the migration to group based access easy.

Another advantage we see in managing by groups is for some data warehouses we want to exclude users from running reports during ETL runs in order to prevent contention or reporting on incomplete data.  All we have to do is run this query at the start of the jobs:

revoke usage on schema myreportschema from group report_group;

When the jobs are finished, we then grant usage again:

grant usage on schema myreportschema to group report_group;

It is easy to see users and group assignments via (note that a user can belong to more than one group):

select usesysid, usename, usesuper, nvl(groname,'default')
from pg_user u
left join pg_group g on ','||array_to_string(grolist,',')||',' like '%,'||cast(usesysid as varchar(10))||',%'
order by 3,2;

Grants in Redshift are ultimately at the object level.  That is to say while we can grant access to all existing objects within a schemas, those grants are stored at an object level.  That is why issuing this command works for all existing tables, but tables added after this command that have been run do not automatically have the select privilege granted:

grant select on all tables in schema myreportschema to group report_group;

While this is good for security granularity, it can be administratively painful.  Let us examine some strategies and tools for addressing this issue.

Redshift has the useful view, information_schema.table_privileges, that lists tables and their permissions for both users and groups.  Note that this also includes views despite the name.   AWS also provides some useful views in their Redshift utilities package in Github, most notably v_get_obj_priv_by_user which essentially flattens out information_schema.table_privileges and makes it much easier to read:

However, note that this view does not report on groups and the HAS_TABLE_PRIVILEGE function that the view uses has no equivalent for groups, so to examine group privileges we can parse the ACL:

select
namespace as schemaname , item as object, pu.groname as groupname
, decode(charindex('r',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)),0,0,1)  as select
, decode(charindex('w',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)),0,0,1)  as update
, decode(charindex('a',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)),0,0,1)  as insert
, decode(charindex('d',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)),0,0,1)  as delete
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')
)
join pg_group pu on array_to_string(relacl, '|') like '%'||pu.groname||'%'

One of the biggest challenges is to fill in missing grants.  We can do this by modifying the above query.  Here’s an example where we create the grant statements for all missing select grants for the report_group:

select 'grant select on '||namespace||'.'||item||' to group report_group;'
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 ='myreportschema'
and ((array_to_string(relacl, '|') not like '%report_group%' or relacl is null)
or
-- note the 'r' for the select privilege
(array_to_string(relacl, '|') like '%report_group%' and charindex('r', split_part( split_part( array_to_string( relacl, '|' ), 'admin_group', 2 ) , '/', 1 ) ) = 0 )
)
and c.relkind   <> 'i'  -- is not an index
);

Learn more about Pythian’s expertise in Database Managed Services.