Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Understanding PostgreSQL roles and permissions

When you create an Aurora PostgreSQL DB cluster using the Amazon Web Services Management Console, an administrator account is created at the same time. By default, its name is postgres, as shown in the following screenshot:

You can choose another name rather than accept the default (postgres). If you do, the name you choose must start with a letter and be between 1 and 16 alphanumeric characters. For simplicity's sake, we refer to this main user account by its default value (postgres) throughout this guide.

If you use the create-db-cluster Amazon CLI rather than the Amazon Web Services Management Console, you create the user name by passing it with the master-username parameter. For more information, see Step 2: Create an Aurora PostgreSQL DB cluster.

Whether you use the Amazon Web Services Management Console, the Amazon CLI, or the Amazon RDS API, and whether you use the default postgres name or choose a different name, this first database user account is a member of the rds_superuser group and has rds_superuser privileges.

Understanding the rds_superuser role

In PostgreSQL, a role can define a user, a group, or a set of specific permissions granted to a group or user for various objects in the database. PostgreSQL commands to CREATE USER and CREATE GROUP have been replaced by the more general, CREATE ROLE with specific properties to distinguish database users. A database user can be thought of as a role with the LOGIN privilege.

The postgres user is the most highly privileged database user on your Aurora PostgreSQL DB cluster. It has the characteristics defined by the following CREATE ROLE statement.

CREATE ROLE postgres WITH LOGIN NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION VALID UNTIL 'infinity'

The properties NOSUPERUSER, NOREPLICATION, INHERIT, and VALID UNTIL 'infinity' are the default options for CREATE ROLE, unless otherwise specified.

By default, postgres has privileges granted to the rds_superuser role, and permissions to create roles and databases. The rds_superuser role allows the postgres user to do the following:

In the CREATE ROLE postgres... statement, you can see that the postgres user role specifically disallows PostgreSQL superuser permissions. Aurora PostgreSQL is a managed service, so you can't access the host OS, and you can't connect using the PostgreSQL superuser account. Many of the tasks that require superuser access on a stand-alone PostgreSQL are managed automatically by Aurora.

For more information about granting privileges, see GRANT in the PostgreSQL documentation.

The rds_superuser role is one of several predefined roles in an Aurora PostgreSQL DB cluster.

In the following list, you find some of the other predefined roles that are created automatically for a new Aurora PostgreSQL DB cluster. Predefined roles and their privileges can't be changed. You can't drop, rename, or modify privileges for these predefined roles. Attempting to do so results in an error.

To see all predefined roles, you can connect to the primary instance of your Aurora PostgreSQL DB cluster and use the psql \du metacommand. The output looks as follows:

List of roles
  Role name   |      Attributes                   |      Member of      
--------------+-----------------------------------+------------------------------------
postgres      | Create role, Create DB           +| {rds_superuser}
              | Password valid until infinity     |
rds_superuser | Cannot login                      | {pg_monitor,pg_signal_backend,
              |                                  +|   rds_replication,rds_password}
...

In the output, you can see that rds_superuser isn't a database user role (it can't login), but it has the privileges of many other roles. You can also see that database user postgres is a member of the rds_superuser role. As mentioned previously, postgres is the default value in the Amazon RDS console's Create database page. If you chose another name, that name is shown in the list of roles instead.

Controlling user access to the PostgreSQL database

New databases in PostgreSQL are always created with a default set of privileges in the database's public schema that allow all database users and roles to create objects. These privileges allow database users to connect to the database, for example, and create temporary tables while connected.

To better control user access to the databases instances that you create on your Aurora PostgreSQL DB cluster primary node , we recommend that you revoke these default public privileges. After doing so, you then grant specific privileges for database users on a more granular basis, as shown in the following procedure.

At this point, lab_user1 and lab_user2 can connect to the lab_db database. This example doesn't follow best practices for enterprise usage, which might include creating multiple database instances, different schemas, and granting limited permissions. For more complete information and additional scenarios, see Managing PostgreSQL Users and Roles.

For more information about privileges in PostgreSQL databases, see the GRANT command in the PostgreSQL documentation.

Delegating and controlling user password management

As a DBA, you might want to delegate the management of user passwords. Or, you might want to prevent database users from changing their passwords or reconfiguring password constraints, such as password lifetime. To ensure that only the database users that you choose can change password settings, you can turn on the restricted password management feature. When you activate this feature, only those database users that have been granted the rds_password role can manage passwords.

By default, this feature is off, as shown in the following:

postgres=> SHOW rds.restrict_password_commands;
  rds.restrict_password_commands
--------------------------------
 off
(1 row)

To turn on this feature, you use a custom parameter group and change the setting for rds.restrict_password_commands to 1. Be sure to reboot your Aurora PostgreSQL's primary DB instance so that the setting takes effect.

With this feature active, rds_password privileges are needed for the following SQL commands:

CREATE ROLE myrole WITH PASSWORD 'mypassword';
CREATE ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2023-01-01';
ALTER ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2023-01-01';
ALTER ROLE myrole WITH PASSWORD 'mypassword';
ALTER ROLE myrole VALID UNTIL '2023-01-01';
ALTER ROLE myrole RENAME TO myrole2;

Renaming a role (ALTER ROLE myrole RENAME TO newname) is also restricted if the password uses the MD5 hashing algorithm.

With this feature active, attempting any of these SQL commands without the rds_password role permissions generates the following error:

ERROR: must be a member of rds_password to alter passwords

We recommend that you grant the rds_password to only a few roles that you use solely for password management. If you grant rds_password privileges to database users that don't have rds_superuser privileges, you need to also grant them the CREATEROLE attribute.

Make sure that you verify password requirements such as expiration and needed complexity on the client side. If you use your own client-side utility for password related changes, the utility needs to be a member of rds_password and have CREATE ROLE privileges.

Using SCRAM for PostgreSQL password encryption

The Salted Challenge Response Authentication Mechanism (SCRAM) is an alternative to PostgreSQL's default message digest (MD5) algorithm for encrypting passwords. The SCRAM authentication mechanism is considered more secure than MD5. To learn more about these two different approaches to securing passwords, see Password Authentication in the PostgreSQL documentation.

We recommend that you use SCRAM rather than MD5 as the password encryption scheme for your Aurora PostgreSQL DB cluster. As of the Aurora PostgreSQL 14 release, SCRAM is supported in all available Aurora PostgreSQL versions, including versions 10, 11, 12, 13, and 14. It's a cryptographic challenge-response mechanism that uses the scram-sha-256 algorithm for password authentication and encryption.

You might need to update libraries for your client applications to support SCRAM. For example, JDBC versions before 42.2.0 don't support SCRAM. For more information, see PostgreSQL JDBC Driver in the PostgreSQL JDBC Driver documentation. For a list of other PostgreSQL drivers and SCRAM support, see List of drivers in the PostgreSQL documentation.

Setting up Aurora PostgreSQL DB cluster to require SCRAM

For Aurora PostgreSQL 14.3 and higher versions, you can require the Aurora PostgreSQL DB cluster to accept only passwords that use the scram-sha-256 algorithm.

Before making changes to your system, be sure you understand the complete process, as follows:

Getting ready to require SCRAM for your Aurora PostgreSQL DB cluster

Before making any changes to your Aurora PostgreSQL DB cluster, check all existing database user accounts. Also, check the type of encryption used for passwords. You can do these tasks by using the rds_tools extension. This extension is supported on Aurora PostgreSQL 13.1 and higher releases.

Creating a custom DB cluster parameter group

For an overview of parameter groups for Aurora, see Creating a DB cluster parameter group.

The password encryption type used for passwords is set in one parameter, password_encryption. The encryption that the Aurora PostgreSQL DB cluster allows is set in another parameter, rds.accepted_password_auth_method. Changing either of these from the default values requires that you create a custom DB cluster parameter group and apply it to your cluster.

You can also use the Amazon Web Services Management Console or the RDS API to create a custom DB cluster parameter group . For more information, see Creating a DB cluster parameter group.

You can now associate the custom parameter group with your DB instance.

Configuring password encryption to use SCRAM

The password encryption mechanism used by an Aurora PostgreSQL DB cluster is set in the DB cluster parameter group in the password_encryption parameter. Allowed values are unset, md5, or scram-sha-256. The default value depends on the Aurora PostgreSQL version, as follows:

With a custom DB cluster parameter group attached to your Aurora PostgreSQL DB cluster, you can modify values for the password encryption parameter.

Migrating passwords for user roles to SCRAM

You can migrate passwords for user roles to SCRAM as described following.

Changing parameter to require SCRAM

This is the final step in the process. After you make the change in the following procedure, any user accounts (roles) that still use md5 encryption for passwords can't log in to the Aurora PostgreSQL DB cluster.

The rds.accepted_password_auth_method specifies the encryption method that the Aurora PostgreSQL DB cluster accepts for a user password during the login process. The default value is md5+scram, meaning that either method is accepted. In the following image, you can find the default setting for this parameter.

The allowed values for this parameter are md5+scram or scram alone. Changing this parameter value to scram makes this a requirement.