CREATE USER - Amazon Redshift


Creates a new database user account. You must be a database superuser to execute this command.


CREATE USER name [ WITH ] PASSWORD { 'password' | 'md5hash' | DISABLE } [ option [ ... ] ] where option can be: CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | SYSLOG ACCESS { RESTRICTED | UNRESTRICTED } | IN GROUP groupname [, ... ] | VALID UNTIL 'abstime' | CONNECTION LIMIT { limit | UNLIMITED }



The name of the user account to create. The user name can't be PUBLIC. For more information about valid names, see Names and identifiers.


Optional keyword. WITH is ignored by Amazon Redshift

PASSWORD { 'password' | 'md5hash' | DISABLE }

Sets the user's password.

By default, users can change their own passwords, unless the password is disabled. To disable a user's password, specify DISABLE. When a user's password is disabled, the password is deleted from the system and the user can log on only using temporary AWS Identity and Access Management (IAM) user credentials. For more information, see Using IAM Authentication to Generate Database User Credentials. Only a superuser can enable or disable passwords. You can't disable a superuser's password. To enable a password, run ALTER USER and specify a password.

You can specify the password in clear text or as an MD5 hash string.


When you launch a new cluster using the AWS Management Console, AWS CLI, or Amazon Redshift API, you must supply a clear text password for the master database user. You can change the password later by using ALTER USER.

For clear text, the password must meet the following constraints:

  • It must be 8 to 64 characters in length.

  • It must contain at least one uppercase letter, one lowercase letter, and one number.

  • It can use any ASCII characters with ASCII codes 33–126, except ' (single quotation mark), " (double quotation mark), \, /, or @.

As a more secure alternative to passing the CREATE USER password parameter as clear text, you can specify an MD5 hash of a string that includes the password and user name.


When you specify an MD5 hash string, the CREATE USER command checks for a valid MD5 hash string, but it doesn't validate the password portion of the string. It is possible in this case to create a password, such as an empty string, that you can't use to log on to the database.

To specify an MD5 password, follow these steps:

  1. Concatenate the password and user name.

    For example, for password ez and user user1, the concatenated string is ezuser1.

  2. Convert the concatenated string into a 32-character MD5 hash string. You can use any MD5 utility to create the hash string. The following example uses the Amazon Redshift MD5 function and the concatenation operator ( || ) to return a 32-character MD5-hash string.

    select md5('ez' || 'user1'); md5 -------------------------------- 153c434b4b77c89e6b94f12c5393af5b
  3. Concatenate 'md5' in front of the MD5 hash string and provide the concatenated string as the md5hash argument.

    create user user1 password 'md5153c434b4b77c89e6b94f12c5393af5b';
  4. Log on to the database using the user name and password.

    For this example, log on as user1 with password ez.


The CREATEDB option allows the new user account to create databases. The default is NOCREATEDB.


The CREATEUSER option creates a superuser with all database privileges, including CREATE USER. The default is NOCREATEUSER. For more information, see superuser.


A clause that specifies the level of access the user has to the Amazon Redshift system tables and views.

If RESTRICTED is specified, the user can see only the rows generated by that user in user-visible system tables and views. The default is RESTRICTED.

If UNRESTRICTED is specified, the user can see all rows in user-visible system tables and views, including rows generated by another user. UNRESTRICTED doesn't give a regular user access to superuser-visible tables. Only superusers can see superuser-visible tables.


Giving a user unrestricted access to system tables gives the user visibility to data generated by other users. For example, STL_QUERY and STL_QUERYTEXT contain the full text of INSERT, UPDATE, and DELETE statements, which might contain sensitive user-generated data.

All rows in STV_RECENTS and SVV_TRANSACTIONS are visible to all users.

For more information, see Visibility of data in system tables and views.

IN GROUP groupname

Specifies the name of an existing group that the user belongs to. Multiple group names may be listed.


The VALID UNTIL option sets an absolute time after which the user account password is no longer valid. By default the password has no time limit.


The maximum number of database connections the user is permitted to have open concurrently. The limit isn't enforced for superusers. Use the UNLIMITED keyword to permit the maximum number of concurrent connections. A limit on the number of connections for each database might also apply. For more information, see CREATE DATABASE. The default is UNLIMITED. To view current connections, query the STV_SESSIONS system view.


If both user and database connection limits apply, an unused connection slot must be available that is within both limits when a user attempts to connect.

Usage notes

By default, all users have CREATE and USAGE privileges on the PUBLIC schema. To disallow users from creating objects in the PUBLIC schema of a database, use the REVOKE command to remove that privilege.

When using IAM authentication to create database user credentials, you might want to create a superuser that is able to log on only using temporary credentials. You can't disable a superuser's password, but you can create an unknown password using a randomly generated MD5 hash string.

create user iam_superuser password 'md5A1234567890123456780123456789012' createuser;


The following command creates a user account named dbuser, with the password "abcD1234", database creation privileges, and a connection limit of 30.

create user dbuser with password 'abcD1234' createdb connection limit 30;

Query the PG_USER_INFO catalog table to view details about a database user.

select * from pg_user_info; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig | useconnlimit -----------+----------+-------------+----------+-----------+----------+----------+-----------+------------- rdsdb | 1 | true | true | true | ******** | infinity | | adminuser | 100 | true | true | false | ******** | | | UNLIMITED dbuser | 102 | true | false | false | ******** | | | 30

In the following example, the account password is valid until June 10, 2017.

create user dbuser with password 'abcD1234' valid until '2017-06-10';

The following example creates a user with a case-sensitive password that contains special characters.

create user newman with password '@AbC4321!';

To use a backslash ('\') in your MD5 password, escape the backslash with a backslash in your source string. The following example creates a user named slashpass with a single backslash ( '\') as the password.

select md5('\\'||'slashpass'); md5 -------------------------------- 0c983d1a624280812631c5389e60d48c create user slashpass password 'md50c983d1a624280812631c5389e60d48c';