REDSHIFT Add User to a Group

The concept of users and groups in Redshift is very important to understand the permission management of users in Redshift. A group is created in Amazon Redshift and a specific permission policy is attached to that group. After assigning the permission to the group, users are added to the group to assign the same permission to multiple users.

Managing the permissions for every user individually becomes much more difficult when the number of users grows. To assign the same permissions to multiple users, we create a group, assign the permissions to the group, and then add the users to the group to assign the same permissions to multiple users.

To add the user to a group, the ALTER GROUP command is used in Amazon Redshift. In this blog, we will discuss how we can manage user permission by adding them into the group using the ALTER GROUP command.

Syntax of ALTER GROUP command

The syntax of the ALTER GROUP command to add or remove users from the group is as below.

ALTER GROUP group_name
{
ADD USER user_name [, … ] |
DROP USER user_name [, … ] |
RENAME TO new_name
}

As the syntax of the command shows, we can perform the following operations using the ALTER GROUP command.

Add a user to a group
Remove a user from a group
Rename the group

Now we will discuss the parameters used with the ALTER GROUP command in Redshift. The following are the parameters that can be used with the ALTER GROUP command to perform different actions on the group.

group_name
ADD USER
DROP USER
RENAME TO

The group_name parameter specifies the group in which you want to add or remove the users.

The ADD USER parameter is used when you want to add a user into the group using the ALTER GROUP command. The ADD USER parameter takes the user_name which will be added to the group.

Just like the ADD USER parameter, the DROP USER parameter is used when you want to drop a user from the group. It takes the user_name as an argument, which will be dropped from the group.

As the name suggests, the RENAME TO option is used to change the name of the group in Amazon Redshift. It takes the new group name as an argument and changes the name of the group.

Examples of ALTER GROUP command

In this section, we will see multiple examples of using the ALTER GROUP command to add or remove users from a group.

Creating a group in Redshift

The first step to adding a user to a group in Amazon Redshift is to create a group. The CREATE GROUP command can be used to create a brand new group in Amazon Redshift.

CREATE GROUP admin_group;

The above query, when executed in Amazon Redshift will create a group named admin_group.

You can check whether the group is created or not by listing all the groups in the cluster. The following query will list all the groups in the Redshift cluster when executed.

SELECT * FROM pg_groups;

The newly created group will be added to the list of all the available groups in Amazon Redshift.

While creating a group, you can also add an existing user into the group using the WITH USER statement with the CREATE GROUP command. Use the following query to add an existing user to the group while creating a new group.

CREATE GROUP admin_group WITH USER user_1;

Or use the following query to add multiple existing users into the newly created group while creating the group.

CREATE GROUP admin_group WITH USER user_1, user_2;

Creating a user in Redshift

After creating the group, now it turns to create a new user into the Amazon Redshift cluster. The following query, when executed, will create a user in the Redshift cluster with the specified password.

CREATE USER user_1 WITH PASSWORD ‘pass123’;

After creating the user, you can check whether the user is created or not in the Redshift cluster. Execute the following query to list all the available users in Redshift.

SELECT * FROM pg_user;

The newly created user will be in the list of all the available users in Redshift. Another way to add a new user to an existing group is by using the IN GROUP statement while creating a new user. Use the following query to add the user to an existing group.

CREATE USER user_1 WITH PASSWORD ‘pass123’ IN GROUP group_name;

The above query will create the user_1 user and add to the existing group group_name.

Adding user to the group

So far, we have created a new user and a new group. Now, it is time to add the user into a group using the ALTER GROUP command. Although we have seen how a user can be added to a group while creating a new user and how multiple users can be added to the group while creating a new group, in this section, we will see how an existing user can be added to an existing group.

To add an existing user to an existing group, run the following query in Redshift.

ALTER GROUP group_name ADD USER user 1;

The above query will add user_1 into an existing group group_name in the Redshift.

Similarly, a user can be removed from a group in Redshift using the ALTER GROUP command. The following query can be executed to remove a user user_1 from the group group_1 in the Redshift cluster.

ALTER GROUP group_1 DROP USER user_1;

Conclusion

Managing users into groups in Redshift is the best way to assign limited permissions to the users. Assigning the permissions to the individual user is a much more tedious task if all the users have the same permissions. This tedious task can be simplified by first creating the group then adding users into the group, and then assigning the permissions to the group. The permissions assigned to the group will be passed to all the users in the group. This blog describes how we can create groups and then add and drop users from groups in the Redshift cluster using the ALTER GROUP command.