How to downgrade user to read only?

Question

There are some users in the Redshift data warehouse who have read and edit permissions. What query should I run to remove their edit permissions so that they can only do select queries?

Answer 1

The SQL command you are looking for is REVOKE - https://docs.aws.amazon.com/redshift/latest/dg/r_REVOKE.html

Now to get the exact command(s) you want to run a few more pieces of information will be needed. But first a quick overview of Redshift permissions. In many database like Redshift users don't (in general) have permissions, the objects have access information about which users/groups can perform which actions. So there is no 'make this user be read-only always' command. REVOKE can act on databases, schemas, tables, and views but these actions only apply to currently existing objects. Objects created in the future will have the access rights assigned by the creating users default ACL.

Now to the questions - are all these users part of a group and the only members of this group? If so you will likely want to apply the REVOKE to the group. Is this restriction for existing tables or do you want them to not be able to create new table (even temp table) in all schemas and database? This will impact what object types you want to revoke rights. Have you or your DBAs changed the default ACLs on the database? These may need to be updated to prevent write accesses being given on future objects in the database.