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).
Andrew Cave
Andrew Cave is a senior data engineer with BizCubed. He has worked in network data, billing, telco credit and debt after a career in the welfare sector. He loves databases. Follow him on LinkedIn