Starting with PostgreSQL 10, password-based authentication receives a major upgrade with the introduction of SCRAM authentication, a well-defined standard that is a significant improvement over the current system in PostgreSQL. We recommend you upgrade your PostgeSQL instances to use this authentication method.

Complete the following steps to upgrade your existing PostgreSQL instances to use SCRAM authentication:

  1. Determine if you can upgrade to SCRAM authentication.

    There are two key criteria to determine whether you can upgrade your PostgreSQL instance to use SCRAM. If your instance meets both of these criteria, you can start the process of upgrading to SCRAM:

    • You are running PostgreSQL 10 or later.
    • All drivers used to connect to your PostgreSQL instance have SCRAM compatibility. There is also a list of drivers that support SCRAM authentication.

  2. Validate your pg_hba.conf settings.

    The PostgreSQL pg_hba.conf file determines how your clients can connect to PostgreSQL. If it contains the following line, this means that any user trying to connect to your PostgreSQL instance through a local connection must use the md5 authentication method.

    # TYPE	DATABASE		USER		ADDRESS		METHOD
    local	all			all				md5

    Before upgrading to SCRAM, ensure that your password-based authentication methods are set to MD5.

  3. Change the PostgreSQL password encryption method.

    In your postgresql.conf configuration file, there is a password_encryption setting that determines how passwords are hashed. Now, it is set to md5. To start the upgrade process, switch this value to scram-sha-256.

    password_encryption = scram-sha-256

    After that, reload your PostgreSQL instance (restart is not required).

  4. Determine who needs to upgrade.

    Determine which of your users need to upgrade their passwords. As you may not be able to set all of their passwords on your own, you may reach out to these users to have them upgrade their passwords. To determine who needs to upgrade their passwords to SCRAM, as a privileged user (a superuser), you can run this SQL query:

    SELECT
        rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
    FROM pg_authid
    WHERE rolcanlogin;

    This query finds users that have the LOGIN privilege (that is, they can log in to your PostgreSQL instance) and determines whether their password still exists in the MD5 hash. If this query returns FALSE, the user needs to re-hash their password.

    Note: There are some cases where the above query will output a false positive (for example, a plain-text password that starts with SCRAM-SHA-256$).

  5. Upgrade the password.

    Using the psql command-line interface, run this command:

    \password

    Or, if you want to set the password for the specified user in your system:

    \password username

    You will be prompted to enter a new password. This new password will be converted to a SCRAM verifier, and the upgrade for this user will be completed.

  6. Update pg_hba.conf to use only SCRAM authentication.

    Note: If a user account has its password upgraded into a SCRAM verifier, it will use SCRAM authentication even if MD5 is the authentication method used.

    After all the passwords are upgraded, update your pg_hba.conf file and switch all of your entries that use md5 to use scram-sha-256. First, check whether all of your users were upgraded to use SCRAM by running this query:

    SELECT
        rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
    FROM pg_authid
    WHERE rolcanlogin;

    This query must return TRUE for all of your users.

    Then, modify md5 entries in the pg_hba.conf file to use scram-sha-256:

    # TYPE	DATABASE		USER		ADDRESS		METHOD
    local	all			all				scram-sha-256

    After that, reload your PostgreSQL instance (restart is not required).

Now, all of your PostgreSQL user accounts are upgraded to authenticate using SCRAM.