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