+61 2 9007 9887 contact@bizcubed.com.au

 

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