CREATE USER - Amazon Redshift

CREATE USER

Creates a new database user. Database users can retrieve data, run commands, and perform other actions in a database, depending on their privileges and roles. You must be a database superuser to run this command.

Required privileges

Following are required privileges for CREATE USER:

  • Superuser

  • Users with the CREATE USER privilege

Syntax

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

Parameters

name

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

WITH

Optional keyword. WITH is ignored by Amazon Redshift

PASSWORD { 'password' | 'md5hash' | 'sha256hash' | 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, as an MD5 hash string, or as a SHA256 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 initial 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.

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 -------------------------------- 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 sign-in credentials.

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

Another secure alternative is to specify an SHA-256 hash of a password string; or you can provide your own valid SHA-256 digest and 256-bit salt that was used to create the digest.

  • Digest – The output of a hashing function.

  • Salt – Randomly generated data that is combined with the password to help reduce patterns in the hashing function output.

'sha256|Mypassword'
'sha256|digest|256-bit-salt'

In the following example, Amazon Redshift generates and manages the salt.

CREATE USER admin PASSWORD 'sha256|Mypassword1';

In the following example, a valid SHA-256 digest and 256-bit salt that was used to create the digest are supplied.

To specify a password and hash it with your own salt, follow these steps:

  1. Create a 256-bit salt. You can obtain a salt by using any hexadecimal string generator to generate a string 64 characters long. For this example, the salt is c721bff5d9042cf541ff7b9d48fa8a6e545c19a763e3710151f9513038b0f6c6.

  2. Use the FROM_HEX function to convert your salt to binary. This is because the SHA2 function requires the binary representation of the salt. See the following statement.

    SELECT FROM_HEX('c721bff5d9042cf541ff7b9d48fa8a6e545c19a763e3710151f9513038b0f6c6');
  3. Use the CONCAT function to append your salt to your password. For this example, the password is Mypassword1. See the following statement.

    SELECT CONCAT('Mypassword1',FROM_HEX('c721bff5d9042cf541ff7b9d48fa8a6e545c19a763e3710151f9513038b0f6c6'));
  4. Use the SHA2 function to create a digest from your password and salt combination. See the following statement.

    SELECT SHA2(CONCAT('Mypassword1',FROM_HEX('c721bff5d9042cf541ff7b9d48fa8a6e545c19a763e3710151f9513038b0f6c6')), 0);
  5. Using the digest and salt from the previous steps, create the user. See the following statement.

    CREATE USER admin PASSWORD 'sha256|821708135fcc42eb3afda85286dee0ed15c2c461d000291609f77eb113073ec2|c721bff5d9042cf541ff7b9d48fa8a6e545c19a763e3710151f9513038b0f6c6';
  6. Log on to the database using the sign-in credentials.

    For this example, log on as admin with password Mypassword1.

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.

CREATEDB | NOCREATEDB

The CREATEDB option allows the new user 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 superuser.

SYSLOG ACCESS { RESTRICTED | UNRESTRICTED }

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

Regular users who have the SYSLOG ACCESS RESTRICTED permission can see only the rows generated by that user in user-visible system tables and views. The default is RESTRICTED.

Regular users who have the SYSLOG ACCESS UNRESTRICTED permission 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.

Note

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 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.

VALID UNTIL abstime

The VALID UNTIL option sets an absolute time after which the user's 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 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.

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.

SESSION TIMEOUT limit

The maximum time in seconds that a session remains inactive or idle. The range is 60 seconds (one minute) to 1,728,000 seconds (20 days). If no session timeout is set for the user, the cluster setting applies. For more information, see Quotas and limits in Amazon Redshift in the Amazon Redshift Management Guide.

When you set the session timeout, it's applied to new sessions only.

To view information about active user sessions, including the start time, user name, and session timeout, query the STV_SESSIONS system view. To view information about user-session history, query the STL_SESSIONS view. To retrieve information about database users, including session-timeout values, query the SVL_USER_INFO view.

EXTERNALID external_id

The identifier for the user, which is associated with an identity provider. The user must have their password disabled. For more information, see Native identity provider (IdP) federation for Amazon Redshift.

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 case of a username enclosed in double quotation marks is always preserved regardless of the setting of the enable_case_sensitive_identifier configuration option. For more information, see enable_case_sensitive_identifier.

Examples

The following command creates a user 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 a user with the md5 password.

create user slashpass password 'md50c983d1a624280812631c5389e60d48c';

The following example creates a user named dbuser with an idle-session timeout set to 120 seconds.

CREATE USER dbuser password 'abcD1234' SESSION TIMEOUT 120;

The following example creates a user named bob. The namespace is myco_aad. This is only a sample. To run the command successfully, you must have a registered identity provider. For more information, see Native identity provider (IdP) federation for Amazon Redshift.

CREATE USER myco_aad:bob EXTERNALID "ABC123" PASSWORD DISABLE;