Redshift User

A practical runbook for establishing a read-only database user in Amazon Redshift.

Key Steps

The tutorial covers creating a dedicated user account, organizing permissions through a group structure, and implementing layered access controls. I establish a "data_viewers" group and systematically grant permissions at the schema level.

Notable Implementation

The script includes commands to grant access to future tables in the schema via default privilege modifications, ensuring new tables automatically receive appropriate permissions without manual intervention.

Script

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

Attribution Note

I sourced this information from elsewhere online but neglected to save the reference. If you recognize the original source, please let me know so I can provide proper credit.

---

Contact: 318.749.7373 | allen@73prime.io