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

Building a New Habit is HARD!

 “Building business logic is a low priority, from my perspective.” This was what my CEO said to me as I shared with him my priorities for the week. The funny part? I work for a data engineering firm...

read more