Create a read-only user on Redshift

create_read_only_user_redshift.sql

-- Create Read-Only Group
 
CREATE GROUP ro_group;
 
-- Create User
 
CREATE USER ro_user WITH password 'XXX';
 
-- Add User to Read-Only Group
 
ALTER GROUP ro_group ADD USER ro_user;
 
-- Grant Usage permission to Read-Only Group to specific Schema
 
GRANT USAGE ON SCHEMA "ro_schema" TO GROUP ro_group;
 
-- Grant Select permission to Read-Only Group to specific Schema
 
GRANT SELECT ON ALL TABLES IN SCHEMA "ro_schema" TO GROUP ro_group;
 
-- Alter Default Privileges to maintain the permissions on new tables
 
ALTER DEFAULT PRIVILEGES IN SCHEMA "ro_schema" GRANT SELECT ON TABLES TO GROUP ro_group;
 
-- Revoke CREATE privileges from group
 
REVOKE CREATE ON SCHEMA "ro_schema" FROM GROUP ro_group;