Add a read-only user to Amazon Redshift

by Andrew Cave
May 14, 2018

 

Creating a read-only user is one of those simple tasks you do once in a blue moon. Normally you can Google it and find somebody else’s script and away you go.

Not the case with AWS Redshift! It’s spread all over the place, so I thought I would publish a simple script to add a read-only user on Redshift. In this case, assume the user wants to access tables in ‘public’ schema

Run the following as a super-user. (Replace values in ‘<username>’ and ‘<password>’ as appropriate)

 

— create user

create user <username> with password ‘<password>’;

 

— create group to take permissions

create group data_viewers;

 

— add user to group

alter group data_viewers add user <username>;

 

— revoke default create rights on public schema

revoke create on schema public from group data_viewers;

 

— grant access to schema

grant usage on schema public to group data_viewers;

 

— grant access to current tables in schema

grant select on all tables in schema public to group data_viewers;

 

— grant access to future tables in the schema

alter default privileges in schema public grant select on tables to group data_viewers

 

I hope this helps you (or even future me).

More blog posts

Information Security and the BizCubed Ways and Values

We’ve blogged previously about the BizCubed Ways and Values.  Many companies have versions of this – they may call them values, guiding principles or another phrase.  While many in large organisations struggle to see them as more than “corporate buzzwords”, we rely on them heavily and incorporate them daily.

read more

BizCubed’s Journey to ISO27001 Certification

A year ago, we blogged about Data Security as a Practice. Since then, we have continued the practices we talked about there, integrating it even further into our daily practice, and incorporating new processes and aspects into our existing cadence.

read more