How to make your Amazon RedShift Query Editor Read Only?

By default, Amazon RedShift Query Editor provides console user full permission on the cluster resources. However, this may not be desirable for some use cases and you may want to restrict that access.

This article provides the steps on how to restrict access to your Amazon RedShift Query Editor console users.

1. RedShift query editor ships with these two policies: AmazonRedshiftQueryEditor and AmazonRedshiftReadOnlyAccess. This is mentioned here: https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor.html
2. Clone AmazonRedshiftQueryEditor policy and remove redshift:GetClusterCredentials action
3. Attach above policies to the IAM entity(user or role) that you will be using with query editor
4. Create a new policy and attach it to your IAM entity to allow permissions to get credentials at the user level as show in the example below:

{

“Version”: “2012–10–17”,

“Statement”: {

“Effect”: “Allow”,

“Action”: “redshift:GetClusterCredentials”,

“Resource”: “arn:aws:redshift:us-west-2:123456789012:dbuser:examplecluster/devuser”

}

}

See this link for more details

- https://docs.aws.amazon.com/redshift/latest/mgmt/generating-iam-credentials-role-permissions.html

5. Create a redshift user : https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_USER.html

e.g. create user devuser with password disable;

6. Grant DML permissions to redshift user — https://docs.aws.amazon.com/redshift/latest/dg/r_GRANT.html

e.g. grant select on table shoes to devuser;

Here are the results:

1. devuser will no longer be a cluster superuser and will be denied permissions when launching query editor:

2. Change database user to devuser and click create a temporary password link

3. Query your database table

4. Try inserting to your database table.

And insert fails as expected!