Updated May 15, 2023
Introduction to Redshift create user
Redshift create user is to create a new account in Amazon redshift database which can use the database and perform the functions whose privileges are assigned to that user. The new user can be created in Amazon redshift only when you have the privileges of the super user. Therefore, we can create as many users as you want in the AWS Redshift, with different roles and privileges if required. In this article, we will have a look at the syntax of create user and different parameters associated with each of them. Further, we will also have a look at the implementation of create user command along with the help of certain examples.
Syntax –
The syntax of the create user command in AWS Redshift is as shown below –
CREATE USER username [WITH]
PASSWORD {“md5hash code” | “password associated with user” | DISABLE }
[other options as per requirement]
The options used in syntax can be
NOCREATEDB | SYSLOG ACCESS {UNRESTRICTED | RESTRICTED}
| CREATEDB | VALID UNTIL ‘abstime’
| IN GROUP name of group [, ….]
| SESSION TIMEOUT {specification of limit}
| CONNECTION LIMIT {UNLIMITED | limit as per requirement};
Working and parameters explanation
Let us have a detailed discussion on all the parameters used in the above syntax one by one –
User name – The user’s name is the user’s name by which the system and database will be identifying the user with and is used while logging in to the system. Note that this name should not be public and should be a valid name following all the rules as done by the other identifiers in redshift.
WITH – This is the optional parameter which a Redshift data warehouse completely ignores.
PASSWORD {“md5hash code” | “password associated with user” | DISABLE} – this is the password associated with the user that will be used for authorization purposes. The users are allowed to change the passwords on their own provided if the password is not externally disabled. In order to disable the password, we can specify the option of DISABLE in the command. The user can log in to the system through Access Management credentials and temporarily provide the AWS identity. For disabling or enabling passwords of the users, one needs to have the superuser rights. We can make the use of ALTER USER command to change the password. The password of the superuser cannot be disabled. We can either specify the hash string in MD5 encrypted format or simply using a clear string.
Clear String password – There are certain restrictions put while specifying the clear text in the password, which include that it should be 8-to-64 character length and must contain at least one lower case and upper-case letter along with a single occurrence of a number and can include the ASCII characters having its codes between 33 to 126 except double and single quotes. The alternative to a clear-text password is a specification of md5 string encrypted containing the user’s name and password in it.
MD5 password – We can follow the steps of concatenating the password and user name to a single string with the order following the specification of username before the password. For example, if the user name is payal and password is educba, then the string used will be educbapayal. The next step will involve the conversion of the above string to an MD5 hash string containing 32 characters. For example, if we execute the following instruction –
Select md5(‘educba’ || ‘payal’);
The output of the above query execution will give the following encrypted 32-character string shown in the below image –
The next step involves specifying the above-encrypted string prefixed with md5 in the password in the create user command as shown below –
create user payal password 'md5153c434b4b77c89e6b94f12c5393af5b';
Now onwards, we can make the use of the user’s name, payal and password educba to login to amazon redshift.
NOCREATEDB | CREATEDB – This option helps in specifying whether the new user should be allowed to create other databases or not. By default, it is not allowed to create new databases and has the value NOCREATEDB.
NOCREATEUSER | CREATEUSER – This allows the creation of the superuser having all the privileges of the database and containing the access to create new users by using CREATE USER command. By default, it is set to NOCREATEUSER.
SYSLOG ACCESS {UNRESTRICTED | RESTRICTED} – This helps in specifying whether the user has access to the different levels of Redshift system views and tables. In the case of the RESTRICTED value, the user can only observe the row contents of some of the system views and tables which are user-visible. The default value of this option, when not specified, is RESTRICTED. In the case of unrestricted option vale, a user can see all data allowed with restricted mode and additionally the data created by other users but not to the ones to the superuser visible tables. The only privilege to see and access the superuser table is to the superuser. All the users can see the rows of SVV_TRANSACTIONS.
IN GROUP name of the group –
The name of the group where the user will belong and the user may be included in more than one existing group.
CONNECTION LIMIT {UNLIMITED | limit} – The total concurrent number of connections to the database that are allowed for the establishment to the user. There is no such limit for the superuser, and if we specify the UNLIMITED value for this option for the user, then the user will have a maximum number of allowed connections.
Example of Redshift create user
Let us create a user with the name article_writer in the database having its password as educba_payal who has the limit of 10 connections to be opened simultaneously. For this, we can make the use of following query statement, and it is mandatory to have superuser privileges to us to execute the below query –
create user article_writer with password 'educba_payal' createdb connection limit 10;
The execution gives the output –
To confirm the existence, check the pg_user_info catalog table using the query –
select * from pg_user_info;
Output –
Conclusion
We can create a new user in Amazon Redshift by using the CREATE USER command, wherein we can specify different options in the command as per our requirement.
Recommended Articles
This is a guide to Redshift create user. Here we discuss the implementation of create user command along with the help of certain examples. You may also have a look at the following articles to learn more –
