SCRAM Authentication in RDS for PostgreSQL 13

February 2023: This post was updated for accuracy. 

The Salted Challenge Response Authentication Mechanism (SCRAM) greatly improves the security of password-based user authentication by adding several key security features that prevent rainbow-table attacks, man-in-the-middle attacks, and stored password attacks, while also adding support for multiple hashing algorithms and passwords that contain non-ASCII characters.

PostgreSQL 10 added support for SCRAM authentication, and AWS customers have been able to use SCRAM authentication since Amazon Relational Database Service (Amazon RDS) for PostgreSQL 10 was launched. However, the use of SCRAM authentication was optional and could not be required for all users at a server level. With the launch of Amazon RDS for PostgreSQL 13, you now control whether or not SCRAM authentication is required for all database users.

In this post we explain how to require SCRAM authentication on your RDS for PostgreSQL database instances. We use the AWS Command Line Interface (AWS CLI), which allows you to interact with the AWS control plane from a Linux, UNIX, or Macintosh shell, or the AWS CloudShell. You can also implement this solution on the AWS Management Console, if you prefer.

Upgrading your client libraries

All users and applications that access the database need to use client libraries that support SCRAM. The PostgreSQL Wiki has a list of client library versions and which ones support SCRAM. Applications and users who use client libraries that don’t support SCRAM can’t connect to the database once you require SCRAM authentication.

Creating a database parameter group

Database parameter groups are collections of database server settings that control how your RDS instance behaves. Each parameter group has a number of database settings, some of which are changeable and some of which are not. Database parameter groups allow you to have a single standard configuration for all your databases so that they behave in a uniform way. By default, RDS instances use a default parameter group, whose settings can’t be modified. Therefore, you can’t use the default parameter group for this procedure.

Although sharing parameter groups across multiple database instances is helpful, there is also one drawback: any change made to the parameter group is applied to all database instances that use that parameter group. If you modify your shared parameter group to require SCRAM authentication, then SCRAM authentication is required on all database instances that use that parameter group. Therefore, you must create a separate parameter group for use during this procedure.

PostgreSQL 10 introduced a parameter called password_encryption, which tells the PostgreSQL database which password encryption mechanism to use by default. The default is md5, but you can also choose scram-sha-256 to tell PostgreSQL that you want to use the newer SCRAM password hashing algorithm by default.

To create a database parameter group and configure it to default to SCRAM password encryption, use the following commands:

aws rds create-db-parameter-group \
  --db-parameter-group-name 'scram-passwords' \
  --db-parameter-group-family postgres13 \
  --description 'Implements SCRAM passwords'
  
aws rds modify-db-parameter-group \
  --db-parameter-group-name 'scram-passwords' \
  --parameters 'ParameterName=password_encryption,ParameterValue=scram-sha-256,ApplyMethod=immediate'

Configuring the server password encryption setting

Let’s assume that your database instance identifier is PG13DB. To attach the new parameter group to your database instance, use the following command:

aws rds modify-db-instance \
  --db-instance-identifier 'PG13DB' \
  --db-parameter-group-name 'scram-passwords'

aws rds wait db-instance-available \
  --db-instance-identifier 'PG13DB'

Changing to the new parameter group doesn’t re-encrypt any existing passwords, nor does it require that users authenticate using SCRAM. Instead, it instructs the server to use the SCRAM method when users change their passwords from this point on. You can run a server with a mix of MD5 and SCRAM passwords, and changing this parameter doesn’t affect any users who currently have MD5 passwords.

Switching a database instance to a different parameter group requires a reboot. If you’re working with a production database, I recommend waiting until the next database maintenance window for the instance to be rebooted and for these changes to be applied. If you’re working with a development or test database, you can reboot the instance immediately using the following command:

aws rds reboot-db-instance \
  --db-instance-identifier 'PG13DB'

Checking for users with non-SCRAM passwords

Before you begin enforcing SCRAM authentication, you should determine which users (if any) currently have MD5 passwords by running the following commands in a PostgreSQL session:

=> CREATE EXTENSION rds_tools;

=> select * from rds_tools.role_password_encryption_type();
Role Name | Encryption Method
—————————————————
mydbuser1 | md5
mydbuser2 |
mydbuser3 | scram
mydbuser4 |

Users that show md5 in the Encryption Method column can’t authenticate after you modify the following parameter. Therefore, it’s important to update the password for each user that has an MD5 password before proceeding to the next step. You can change an individual user’s password using the following SQL command while connected to the server using the psql tool:

\password mydbuser1

This method is preferred to using ALTER ROLE, because ALTER ROLE statements might get logged or transmitted in plaintext, potentially exposing the user’s new plaintext password to anyone with access to those logs or network paths. According to the PostgreSQL documentation:

Caution must be exercised when specifying an unencrypted password with this command. The password will be transmitted to the server in cleartext, and it might also be logged in the client’s command history or the server log. psql contains a command \password that can be used to change a role’s password without exposing the cleartext password.

Modifying the database parameter group to require SCRAM

After all your users and applications are upgraded to use client libraries that support SCRAM, and all your passwords are updated to the SCRAM format, you can configure RDS for PostgreSQL to require SCRAM authentication on your database. The rds.accepted_password_auth_method parameter tells Amazon RDS for PostgreSQL to allow both MD5 and SCRAM passwords, or to only allow SCRAM passwords. The default setting of md5+scram lets users with either MD5 or SCRAM passwords authenticate. Setting this parameter to scram forces the PostgreSQL server to only permit SCRAM passwords. You can make this change by updating the parameter group using this command:

aws rds modify-db-parameter-group \
  --db-parameter-group-name 'scram-passwords' \
  --parameters 'ParameterName=rds.accepted_password_auth_method,ParameterValue=scram,ApplyMethod=immediate'

Because you’re modifying a dynamic parameter on a parameter group that’s already attached to the database instance, rather than attaching a different parameter group to the database instance, you don’t have to reboot for this change to take effect. After you update this parameter, new connections to the Amazon RDS for PostgreSQL server are required to use SCRAM authentication. New connections that attempt to use MD5-based authentication fail, even if the database user still has an MD5 password. Connections that are currently in flight aren’t affected by this change.

Summary

In this post, you learned how to improve the security posture of your Amazon RDS for PostgreSQL 13 server by requiring that all database users use SCRAM-based password hashing and authentication. When you implement this change on your RDS for PostgreSQL 13 servers, we would love to hear about how the transition went in the comments section.

About the Authors

Tim Gustafson is a Senior Database Specialist Solutions Architect working primarily with open-source database engines.

Arun Bhati is Software Development Engineer for Amazon RDS. Prior to joining AWS, he worked on building document generation platform for Amazon retail. Outside of work, he likes chess, outdoors and spend time with family and friends in his free time.