How to provide Read only and Read write access to Redshift Database users?

 Syntax For read only access –

grant usage on schema <schemaname> to redshift_readonly_dev;

grant select on ALL tables in schema <schemaname> to redshift_readonly_dev;

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT select ON TABLES TO group redshift_readonly_dev;

Syntax for Read write access –

grant usage on schema <schemaname> to nga_redshift_readwrite_dev;

grant select,insert,update,delete on ALL tables in schema <schemaname> to redshift_readwrite_dev;

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT select,insert,update,delete ON TABLES TO group redshift_readwrite_dev;

add the users to respective groups and they will inherit the privileges.

alter group redshift_readwrite_dev add user <userid>;