Redshift User

I had the need to create a read-only user for a certain database in Redshift. Below is a runbook on how to accomplish this.

-- create user in `published` database
create user readonly_user with password '<password in plain text>';

-- create group to take perms
create group data_viewers;

-- add user to group
alter group data_viewers add user readonly_user;

-- 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;

There. Short & sweet; Runbook for my future self. I found this elsewhere on the web, and neglected to save the reference. If you know where it came from, let me know, and I’ll ensure to give the appropriate citation. :)