Redshift automatically update permissions on new table creation

Question

Hi

I recently started working with Redshift and have an issue with permission management.

Assume multiple users need to access a single schema in the Redshift database. However, only a single user must have CREATE permission to create new tables with all other users only having SELECT permissions.

I have provisioned two roles user_write and user_read and assigned CREATE and SELECT permissions, respectively, and assigned the relevant users.

Can I know how to achieve the following

1. Automatically allow users assigned to the user_read role to be able to view and read any new table created by the user_write role user. Any new table that is created by the user assigned to user_write role must be visible to anyone assigned to user_read role.
2. Can we provide GRANT OPTION to a role so that the users assigned to that role can manage the permissions to the objects they create? Or a workaround to achieve the same.

If possible I would like to avoid granting permissions directly to the user. This is simply to reduce the management overhead and easily onboard and offboard users without having to individually manage each user.

Thank you, any help in this matter is greatly appreciated.

Answer

To provide access on newly created tables in the schema to your role, you can use [ALTER DEFAULT PRIVILEGES](!https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_DEFAULT_PRIVILEGES.html)

Each "user_write" role user should execute the ALTER DEFAULT PRIVILEGE statement once before creating new tables. By executing this statement, the user is defining the privileges that should be automaticllay applied for the objects they are creating. Syntax for "Alter default privilege" is:

```
ALTER DEFAULT PRIVILEGES
    [ FOR USER target_user [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    grant_or_revoke_clause
```

For example: consider user1 is assigned the role user_write. Before creating tables, user1 should execute the below ALTER DEFAULT PRIVILEGE statement

ALTER DEFAULT PRIVILEGES
   IN SCHEMA schema1
   GRANT SELECT ON TABLES to role user_read;

By executing this, user1 is indicating that for all the tables they create, a SELECT is automatically granted to user_read role. From that point, any table that user1 creates will have an automatic SELECT grant to user_read role.

If there is another user user2 assigned to role user_write, they need to follow the same mentioned above

Redshift automatically update permissions on new table creation

Relevant content