Redshift ALTER DEFAULT PRIVILEGES

“Amazon Redshift runs complex database queries and data analysis jobs that require high memory and computing power. Redshift is designed to utilize a large number of parallel compute nodes which can provide the ability to carry out such complex tasks.

In a Redshift cluster, we can set privileges or permissions for all users, user groups, and database schemas. When a user is created, it gets the default permissions which we can change whenever we want using the Redshift ALTER DEFAULT PRIVILEGES command. In this article, we will discuss how to grant specific permissions on tables and objects to the users.”

View Default Privileges

Redshift allows every Redshift user to view the privileges assigned to them. This information related to user privileges can be found in the table named pg_default_acl. The following SELECT query can be executed in Redshift to get the default privileges for the users.

SELECT defacluser as a user,

defaclnamespace as namespace,

defaclobjtype as object_type,

defaclacl as default_privileges

FROM "pg_catalog"."pg_default_acl";

You can see that at this point, there are no entries in this table for any users.

ALTER DEFAULT Privileges

Now in this section, we will see different examples and use cases for this command to understand it completely. First, let us create a database user which we can use to show how to manage the permissions in Redshift.

CREATE USER demo_user WITH PASSWORD ‘Demo1234’;

Grant INSERT Privileges to Users

Suppose there is a new software engineer joining your development team, you have created his Redshift database user, and now you want to provide him permission to insert data in all the database tables that will be made in the future. The following query will grant the INSERT permission to the user.

ALTER DEFAULT PRIVILEGES IN SCHEMA <Schema Name>

GRANT INSERT ON TABLES TO <User Name>

So this is how you can grant permissions to a single database user to insert data in your Redshift tables. This permission will automatically be assigned to the newly created tables in the future and will not work on existing tables.

Grant DROP Privileges to User Groups

You can also provide privileges to user groups in a similar manner as we have done with a single user. In this section, we are giving permission to a user group to DROP or DELETE tables in a schema.

ALTER DEFAULT PRIVILEGES IN SCHEMA <Schema Name>

GRANT drop ON TABLES TO <Group Name>

So we have shown here how to provide privileges to user groups for the database tables. The permissions will automatically apply to all new tables that will be created in the future for that specific user or user group.

Grant EXECUTE Functions Privilege

Database functions are procedures that take in one or more input parameters and return a single output in the result. Using the ALTER DEFAULT PRIVILEGES command, you can allow your Redshift users to execute functions that will be created in that database or schema. The following ALTER DEFAULT PRIVILEGES query can be used to grant EXECUTE function privileges by default to the users.

ALTER DEFAULT PRIVILEGES GRANT EXECUTE ON FUNCTIONS TO <User Name>

This way, you can easily grant your users permission to execute the functions.

Enable User to GRANT Privileges

In all other cases, you are observing how you can directly give or take the permissions from users and groups, but the ALTER DEFAULT PRIVILEGES command can go one step further by providing a user the ability to further grant or revoke permissions from other users. The thing to remember about this is that it will only work with a single user and not with the user group; also, this is a powerful command, so you should be careful about this.

ALTER DEFAULT PRIVILEGES

GRANT ALL ON TABLES

TO <Demo User> WITH GRANT OPTION

This query is performing two functions here. First, it will grant all the permissions for the Redshift table to the mentioned user, and also, this user gets the ability to further grant this permission to other users.

REVOKE Privileges From the Public

Redshift REVOKE command is used to block the permissions for users and user groups. Here you will get to know how to revoke or take back the given permissions from users in your Redshift cluster. This is important because, for the security and privacy of your data, you must give all users the least privileges, and if a user or group does not need to utilize a certain privilege, you must limit it to keep your Redshift database most secure. For this, you just simply need the following command.

ALTER DEFAULT PRIVILEGES

REVOKE UPDATE ON TABLES

FROM PUBLIC

This query removes the update permission for all the public users and for all the future tables. You can also specify any specific user, table, or schema.

Disable User to GRANT Privileges

Suppose in the past you had multiple team members who could grant privileges to other users in your team. With the passage of time, you just realize that this is not a good option, and you want to take this permission back. The following ALTER DEFAULT PRIVILEGES query can be used to revoke GRANT permissions from the users.

ALTER DEFAULT PRIVILEGES

REVOKE GRANT OPTION FOR EXECUTE ON PROCEDURES

FROM <User Name>

Now the user does not have the privilege to grant execute procedures permission to other users. However, the user itself will maintain its own privileges.

Conclusion

In Amazon Redshift, you can alter the privileges assigned to different users, user groups, and the public using the ALTER DEFAULT PRIVILEGES command. It has multiple options using, which you can use to allow or change permissions related to database tables, functions, or procedures. You can also manage other users and give them rights to further grant the permissions and privileges to other users.