Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

CREATE USER

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

Syntax

CREATE USER name
[ [ WITH] option [ ... ] ]

where option can be:

CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| IN GROUP groupname [, ... ]
| PASSWORD { 'password' | 'md5hash' }
| VALID UNTIL 'abstime'
| CONNECTION LIMIT { limit | UNLIMITED }

Parameters

name

The name of the user account to create. For more information about valid names, see Names and Identifiers.

CREATEDB | NOCREATEDB

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

CREATEUSER | NOCREATEUSER

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

IN GROUP groupname

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

PASSWORD { 'password' | 'md5hash' }

Sets the user's password.

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

Note

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 printable ASCII characters (ASCII code 33 to 126) except ' (single quote), " (double quote), \, /, @, or space.

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.

Note

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                             
    --------------------------------
    37af65b44378ac7a5a1fb187a1969c71
  3. Concatenate 'md5' in front of the MD5 hash string and provide the concatenated string as the md5hash argument.

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

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

VALID UNTIL abstime

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.

CONNECTION LIMIT { limit | UNLIMITED }

The maximum number of database connections the user is permitted to have open concurrently. The limit is not enforced for super users. Use the UNLIMITED keyword to permit the maximum number of concurrent connections. The limit of concurrent connections for each cluster is 500. 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.

Note

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.

Examples

The following command creates a user account named danny, with the password "abcD1234", database connection 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   
 danny     |      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';