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