To create a new user account for your Redshift cluster, the CREATE USER command is used. In order to use this command, you must be a superuser or you must have proper privileges to create the user. The purpose of creating multiple users in the Redshift cluster is to assign the limited permissions to each user depending upon the functionalities in which the user will perform.
In this blog, we will discuss about the CREATE USER function to create a user on the Redshift cluster on AWS.
CREATE USER Syntax
The following is the syntax to create a user in the Redshift cluster:
1 2 3 | CREATE USER <user name> [ WITH ] PASSWORD { '<user password>' | 'md5hash' | 'sha256hash' | DISABLE } [ option [ ... ] ] |
The CREATE USER function takes the following parameters:
- Name
- Password
- Plain text
- MD5 hashed
- SHA256 hashed
- Options
Name
This parameter is the name of a new user account which will be created on Redshift.
Password
In this parameter, you can set the password for the new user. You can disable the access for the user to change the password. But by default, new users can change their password. When you disable the user password, the user can only log on by using the AWS IAM (Identity and Access Management) credentials. New users cannot disable or change the superuser password. Only the superuser has this privilege.
We can set the passwords in three different ways, i.e, plain text, MD5 hash string and SHA256 hash string.
Plain Text
For the plain text, the password must fulfill the following conditions:
- It must contain a minimum of 8 characters and maximum of 64 characters
- It must contain both lower and upper case letters
- It must contain at least one numeric number
- It can also use the ASCII characters with codes ranging from 33 to 126 except the single quotation mark (‘), double quotation mark (“), forward slash (/), back slash (\), or at the rate sign (@).
MD5 Hash String
The more secure way to set the password is the MD5 hash string as compared to the plain text password.
For MD5 hash string, you need to follow these steps:
- The first step is to concatenate the username and password, which means joining the password and the username. For example, the username is admin and the password is 123, then the concatenate string is 123admin.
- Convert the concatenate string into a 32-character MD5 hash string. There are many ways to convert it. We use the AWS Redshift concatenate function(||) to return the 32-character MD5 hash string.
You can generate the MD5 hash of a string by executing the following query in the Redshift cluster:
1 | select md5('123' || 'admin'); |
Output: d829b843a6550a947e82f2f38ed6b7a7
You need to concatenate the md5 keyword with the 32-character MD5 hash string and apply this string to the MD5 hash argument.
In order to create a user with the MD5 hash password, you need to concatenate the md5 keyword before the MD5 hash password.
1 | create user admin password 'md5D829b843a6550a947e82f2f38ed6b7a7'; |
Now, this username and password can be used to log in to the Redshift cluster.
SHA-256 Hash
This is another secure way to set the password. Following are the two parts of the SHA256 hash:
Digest: The output of the hashing function in SHA-256.
Salt: It is the data generated randomly to combine with the password to help securely encrypt the password.
Following is the query to create a new user in Redshift with SHA256 hashed password. In this query, AWS Redshift automatically generates and manages the salt.
1 | CREATE USER admin PASSWORD 'sha256|Mypassword1'; |
Options
There are multiple options available that can be used while creating the user for the Redshift cluster. These options can define multiple parameters for the user. Following are some options that can be used while creating a new user:
- CREATEDB | NOCREATEDB
- CREATEUSER | NOCREATEUSER
- SYSLOG ACCESS { RESTRICTED | UNRESTRICTED }
- IN GROUP groupname
- VALID UNTIL ABSTIME
- CONNECTION LIMIT (LIMIT | UNLIMITED)
- SESSION TIMEOUT LIMIT
- EXTERNALID
Now, we will discuss all these options one by one.
CREATEDB | NOCREATEDB
CREATEDB option equips the new user to create a new database. By default, it is set to NOCREATEDB.
CREATEUSER | NOCREATEUSER:
CREATEUSER option provides the new user a full access to create a new user in the Redshift cluster. By default, this option is set to NOCREATEUSER and the newly created user cannot create another user in the Redshift cluster with the default value for this option.
SYSLOG ACCESS { RESTRICTED | UNRESTRICTED }
It defines the level of access that a new user has on the Redshift tables. If it is defined as restricted. The new user’s access is restricted to the rows generated by itself in the user-visible system tables and views. By default, it is set to restricted.
If it is defined as unrestricted, the new user can view all the rows even if it is included by the other users. But, it does not give the access of the superuser visible tables.
IN GROUP Groupname
This option defines the name of the group that the new user will belong to. For this option, multiple group names may also be listed.
VALID UNTIL ABSTIME
This option defines the absolute time after which a new user account password will no longer be valid. By default, the new user password has no time limit and is valid forever.
CONNECTION LIMIT (LIMIT | UNLIMITED)
This option defines the maximum number of database connections that can be opened simultaneously by the user. By default, it is set to unlimited.
SESSION TIMEOUT LIMIT
This Option defines the maximum time in seconds that the session can remain inactive. The session timeout range for a Redshift user is from one minute to 20 days. By default, it is decided by the cluster.
EXTERNALID
The EXTERNALID option specifies the identifier for a new user that is linked with an external identity provider. The user is authorized by an external identity provider instead of password, so the password must be disabled if this option is specified.
CREATE USER Examples
In this section, let’s take the multiple examples to fully understand the concept of creating a new user account in a Redshift cluster.
Setting the Connection Limit on New User
In order to create a user with the name engineer and password Admin123 and having a maximum connection limit of 20, use the following query:
1 | create user engineer with password 'Admin123' createdb connection limit 20; |
Now, execute the following query to view the details about all the database users:
1 | select * from pg_user_info; |
Scroll right to the menu bar to view the column of useconnlimit. For the user engineer, the connection limit is 20.
Setting the Password Validation on New User
In this example, we will create a user account with the name developer and password Admin1234. We will also pass an option to set the password validation.
1 | create user developer with password 'Admin1234' valid until '2022-06-10'; |
Now, execute the following query to list all the users in the Redshift cluster.
1 | select * from pg_user_info; |
As you can see in the following screenshot, that password validation for the user is set to 2022-03-10.
Setting the User Password Including Special Characters
In this example, we will create a user with the case sensitive characters and special characters in the password.
1 | CREATE USER awsadmin with password '&Admin1234!'; |
Setting the Session Timeout for New User
In this example, we will create a user with the name newman and password abcD1234. And set the session timeout to 150 seconds.
1 | CREATE USER newman with password 'abcD1234' SESSION TIMEOUT 150; |
Setting the Namespace for New User
In this example, we will create a user with the name john and the namespace aws_user using the option EXTERNALID.
1 | CREATE USER aws_user:john EXTERNALID "ADMIN123" PASSWORD DISABLE; |
Conclusion
In this article, we discussed how we can use the CREATE USER command in the Redshift to create a user. We can use the multiple options while creating the new user to set the certain limits on the user. AWS Redshift creates a user command that is very useful and provides a wide range of options to create new users with the different levels of privilege for the database.
