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).

Portrait of Maxx Silver
Andrew Cave

Andrew Cave is a senior data engineer with BizCubed. He has worked in network data, billing, telco credit and debt after a career in the welfare sector. He loves databases. Follow him on LinkedIn

More blog posts