Users - Amazon Redshift


You can create and manage database users using the Amazon Redshift SQL commands CREATE USER and ALTER USER. Or you can configure your SQL client with custom Amazon Redshift JDBC or ODBC drivers. These manage the process of creating database users and temporary passwords as part of the database logon process.

The drivers authenticate database users based on AWS Identity and Access Management (IAM) authentication. If you already manage user identities outside of AWS, you can use a SAML 2.0-compliant identity provider (IdP) to manage access to Amazon Redshift resources. You use an IAM role to configure your IdP and AWS to permit your federated users to generate temporary database credentials and log on to Amazon Redshift databases. For more information, see Using IAM authentication to generate database user credentials.

Amazon Redshift users can only be created and dropped by a database superuser. Users are authenticated when they log on to Amazon Redshift. They can own databases and database objects (for example, tables). They can also grant permissions on those objects to users, groups, and schemas to control who has access to which object. Users with CREATE DATABASE rights can create databases and grant permissions to those databases. Superusers have database ownership permissions for all databases.

Creating, altering, and deleting users

Database users are global across a data warehouse cluster (and not for each individual database).

  • To create a user, use the CREATE USER command.

  • To create a superuser, use the CREATE USER command with the CREATEUSER option.

  • To remove an existing user, use the DROP USER command.

  • To change a user, for example changing a password, use the ALTER USER command.

  • To view a list of users, query the PG_USER catalog table.

    select * from pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig ------------+----------+-------------+----------+-----------+----------+----------+----------- rdsdb | 1 | t | t | t | ******** | | masteruser | 100 | t | t | f | ******** | | dwuser | 101 | f | f | f | ******** | | simpleuser | 102 | f | f | f | ******** | | poweruser | 103 | f | t | f | ******** | | dbuser | 104 | t | f | f | ******** | | (6 rows)