Redshift Create User Command: Syntax, Parameters, and 5 Easy Examples

Amazon Redshift is one of the trusted and most popular Data Warehouse solutions currently available in the market. It works flawlessly with SQL commands and provides a wide range of functionality ranging from storing the data to analyzing it.

Amazon Redshift Users are created by users with privileges known as Superusers and they use the command known as Redshift CREATE USER command.

This article gives a comprehensive guide on the Redshift CREATE USER command, showing its syntax, parameters, and use cases.

What is Amazon Redshift?

Redshift is a columnar database that provides OLAP (Online Analytical Processing). It is designed on PostgreSQL version 8.0.2. This means allows the users to use regular SQL queries with Redshift. It utilizes the concept of massively parallel processing that allows for Fast queries. This technology was developed by ParAccel. The MPP technology uses multiple computer processors that work in parallel to provide the necessary calculations. Even the processors located in multiple servers are used collectively to process heavy tasks. The main benefit of using AWS Redshift is the cost-benefit to your business. The cost is one-fifth (about one-twentieth) of competitors such as Teradata and Oracle. 

Learn more about Amazon redshift.

Redshift CREATE USER command

Redshift Create user command is used to create a new user account for the database. Only a superuser has the privilege to run this command and create a new user.

Syntax

CREATE USER name [ WITH ] 
PASSWORD { 'plain text' | 'md5hash' | 'sha256hash' | DISABLE }
[ option [ ... ] ]

Basic Parameters

NAME: this parameter defines the name of the user that is created using the Redshift Create user command for the database.

PASSWORD: set the user Password in the Redshift Create User Command and store in secure password manager. the default settings ensure the user can change their password, but it can be disabled.

select md5('ez' || 'user1');
md5                             
--------------------------------
153c434b4b77c89e6b94f12c5393af5b

• continue…
  • By adding ‘md5’ and concatenating it to the front of MD5 sting in the previous step and passing the Concatenated string as md5hash argument

create user user1 password 'md5153c434b4b77c89e6b94f12c5393af5b';

• continue…
  • Log in to the Amazon account using the generated hash password.

• SHA-256 hash is also a secure option that can be used instead of a plain text password. SHA-256 consists of two parts, SALT and DIGEST.
  • valid SHA-256 digest and 256-bit salt that was used to create the digest will be used to create a valid SHA-256 Hash Password.
    • The Digest is the output of a hashing function.
    • Salt is Randomly generated data that is combined with the password to help reduce patterns in the hashing function output.
• the syntax for SHA-256:

'sha256|Mypassword'

or

'sha256|digest|256-bit-salt'

• examples for SHA-256 passwords
  • In this example, the amazon redshift self manages SALT and DIGEST aspects.

CREATE USER admin PASSWORD 'sha256|Mypassword1';

• example
  • In this example, valid SALT and DIGEST were used to generate password

CREATE USER admin PASSWORD 'sha256|fe95f2bc7c4a111b6f0f7d0b60bfedd1935fb295f8dce1d62708ab8d2f564baf|c721bff5d9042cf541ff7b9d48fa8a6e545c19a763e3710151f9513038b0f6c6';

NOTE: If you set a password in plain text without specifying the hashing function, then an MD5 digest is generated using the username as the salt.

Integrate MongoDB to Redshift

Get a DemoTry it

Integrate MS SQL Server to Redshift

Get a DemoTry it

Integrate HubSpot to Redshift

Get a DemoTry it

Options

there are multiple parameters or options that can be used in Redshift CREATE USER.

Examples

Redshift CREATE USER Creates a user with the name hevo, with the password “abcD1234”, database creation privileges, and a connection limit of 30.

CREATE USER hevo with password 'abcD1234' createdb connection limit 30;

when the PG_USER_INFO catalog table is queried, it views the details of all the database users.

select * from pg_user_info;
usename   | usesysid | usecreatedb | usesuper | usecatupd | passwd   | 
----------+----------+-------------+----------+-----------+----------+
 rdsdb    |        1 | true        | true     | true      | ******** |          
 adminuser|      100 | true        | true     | false     | ******** |          
 hevo     |      102 | true        | false    | false     | ******** |          


valuntil | useconfig | useconnlimit
---------+-----------+-------------
infinity |           |             
         |           | UNLIMITED   
         |           | 30                

the account password is valid until DEC 10, 2022.

CREATE USER hevo with password 'abcD1234' valid until '2022-12-10';

Redshift CREATE USER creates a user with username hevo with a case-sensitive password that contains special characters.

CREATE USER hevo with password '@AbC4321!';

To use a backslash (”) in your MD5 password in the Redshift CREATE USER command, escape the backslash with a backslash in your source string. The following example creates a user named hevo with a single backslash ( ”) as the password.

select md5(''||'slashpass');
md5                             
--------------------------------
0c983d1a624280812631c5389e60d48c
 
CREATE USER hevo password 'md50c983d1a624280812631c5389e60d48c';

Redshift CREATE USER command creates a user named hevo with session timeout set to 120 seconds.

CREATE USER hevo password 'abcD1234' SESSION TIMEOUT 120;

Learn More About:

• Amazon Redshift CREATE Schema Command
• Redshift Permissions

Load your Data from any Source to Redshift in minutes

Start For Free

No credit card required

Conclusion

Redshift CREATE USER command offers a wide range of options that can allow providing privileges to a new user that has been added to the database.

Redshift is a trusted data warehouse that a lot of companies use and store data as it provides many benefits but transferring data into it is a hectic task. The Automated data pipeline helps in solving this issue and this is where Hevo comes into the picture. Hevo Data is a No-code Data Pipeline and has awesome 100+ pre-built Integrations that you can choose from.

Hevo can help you Integrate your data from numerous sources and load them into a destination to Analyze real-time data with a BI tool such as Tableau. It will make your life easier and data migration hassle-free. It is user-friendly, reliable, and secure.

FAQ on Redshift Create User Command

Arsalan Mohammed Research Analyst, Hevo Data

Arsalan is a research analyst at Hevo and a data science enthusiast with over two years of experience in the field. He completed his B.tech in computer science with a specialization in Artificial Intelligence and finds joy in sharing the knowledge acquired with data practitioners. His interest in data analysis and architecture drives him to write nearly a hundred articles on various topics related to the data industry.