How to create a Read-Only user in AWS Redshift

 

Even if you’re the only user of your data warehouse, it is not advised to use the root or admin password.  As a rule and as a precaution you should create additional credentials and a profile for any user that will have access to your DW.  Some systems provide an easier way of doing it than other.

In the case of Amazon’s Redshift, it is not very straight forward.  Because sometimes, flexibility comes with complexity.  And that’s what we encountered when we tried to create a user with read-only access to a specific schema.  Here’s what you will need to achieve this task:

Query by query

Create Read-Only Group

To do things in order we will first create the group that the user will belong to.


CREATE GROUP ro_group;

 

Create User

Then of course, create the user with a password.


CREATE USER ro_user WITH password PASSWORD;

 

Add User to Read-Only Group

We can now add the user to the group. No permissions have been set to this point.


ALTER GROUP ro_group ADD USER ro_user;

 

Grant Usage permission to Read-Only Group to specific Schema

Next, we need to grant usage on a specific schema to the group.  This is a necessary step before being able to grant any permissions.


GRANT USAGE ON SCHEMA "ro_schema" TO GROUP ro_group;

 

Grant Select permission to Read-Only Group to specific Schema

With the above set up, we can now grant SELECT permission on ALL tables for the schema specified above.


GRANT SELECT ON ALL TABLES IN SCHEMA "ro_schema" TO GROUP ro_group;

 

Alter Default Privileges to maintain the permissions on new tables

The above permissions will only apply to existing tables.  So, if we we want to give this user access to tables created later on, we need to alter the default privileges on that schema and grant SELECT permission.


ALTER DEFAULT PRIVILEGES IN SCHEMA "ro_schema" GRANT SELECT ON TABLES TO GROUP ro_group;

 

Revoke CREATE Privilege

Finally, one last step is to REVOKE CREATE privileges for that group


REVOKE CREATE ON SCHEMA "ro_schema" FROM GROUP ro_group;

All queries together


-- Create Read-Only Group

CREATE GROUP ro_group;

-- Create User

CREATE USER ro_user WITH password PASSWORD;

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

 

If you want to create additional users, just run the queries related to USERS and add that user to the read-only group.

 

That should do it.

Leave a Reply

Your email address will not be published. Required fields are marked *