Skip to main content

Migrate Aiven for PostgreSQL® passwords from MD5 to SCRAM

Find out which Aiven for PostgreSQL® database users still use the deprecated MD5 password hashing and migrate them to scram-sha-256.

MD5 password hashing is deprecated in PostgreSQL and will be removed in a future release. scram-sha-256 is the recommended replacement. It resists offline attacks better and stops a stored hash from being replayed as a password.

MD5 authentication keeps working on every PostgreSQL version you can run on Aiven, so nothing breaks today. On PostgreSQL 18, MD5 is marked as deprecated, and CREATE ROLE and ALTER ROLE log a WARNING when they set an MD5 password.

PostgreSQL has not announced which release removes MD5 support. Migrate while it is still a warning rather than waiting for it to become an error.

How Aiven for PostgreSQL hashes passwords​

The pg.password_encryption service configuration option sets the algorithm used to hash passwords. It accepts md5 or scram-sha-256.

  • New services use scram-sha-256 by default. Services in accounts that still allow legacy pinned PgBouncer pools default to md5.
  • Changing pg.password_encryption applies to passwords that are set after the change. Existing database users keep their current hash until their password is set again.
  • Aiven's internal system roles always use scram-sha-256, whatever this option is set to. This does not cover avnadmin or any other service user you connect with, so check and migrate those yourself.

Check which users still use MD5​

Every Aiven for PostgreSQL service user reports its current hashing algorithm in the password_encryption_type field. The value is scram-sha-256, md5, or unknown. unknown means the stored hash is missing or in an unrecognized format, for example when the user has no password.

The default avn service user-list output doesn't include this field, so request it explicitly:

avn service user-list --project PROJECT_NAME SERVICE_NAME \
  --format '{username} {password_encryption_type}'

Example output:

avnadmin scram-sha-256
pool_usr md5
app_user md5

Any user reported as md5 still needs migrating.

Migrate to scram-sha-256​

Check your PgBouncer connection pools first​

A pool created with a specific username is pinned to that user: PgBouncer connects to PostgreSQL as the pool user regardless of the user your application authenticates with. Because the PostgreSQL client starts a challenge-response exchange that PgBouncer only proxies, connecting as another role through a pinned pool fails with a permission denied error once scram-sha-256 is enforced.

While your account still allows pinned pools, setting pg.password_encryption to scram-sha-256 is rejected with a 403 response and the error Setting password_encryption to scram-sha-256 is not allowed for this project. Move your pools off the pinned behavior first, then contact Aiven support to have the restriction lifted for your account.

  1. Check which connection pools have specific usernames by running the avn service connection-pool-list command:

    avn service connection-pool-list --project PROJECT_NAME SERVICE_NAME

    Example output:

    POOL_NAME        DATABASE      USERNAME  POOL_MODE    POOL_SIZE
    ===============  ============  ========  ===========  =========
    my_pool          defaultdb     pool_usr  session      20
    general_pool     defaultdb               transaction  15

  2. Review the USERNAME column to identify potential issues:

    • Pools with usernames (my_pool with pool_usr) can hit authentication issues with scram-sha-256.
    • Pools without usernames (general_pool) are compatible with scram-sha-256.

  3. For pools with specific usernames, check your application's connection string postgresql://pool_usr:password@service-host:port/my_pool to verify the username matches exactly:

    • Connection string username: pool_usr
    • Pool configuration username: pool_usr

  4. If the usernames don't match, connect your application to a pool with a matching username or migrate the pool using one of the following methods:

    • Remove the username from the pool:

      avn service connection-pool-update \
        --project PROJECT_NAME SERVICE_NAME my_pool \
        --username=""

    • Re-hash the pool user's password.

    • Update your application to use a different compatible pool without specific username requirements:

      postgresql://any_user:password@service-host:port/general_pool

Set the password encryption option​

Set the password encryption value in your service configuration:

{
  "pg": {
    "password_encryption": "scram-sha-256"
  }
}

New passwords are hashed with scram-sha-256 from this point on. Existing MD5 hashes keep working, so your current connections are not interrupted.

important

This step alone does not migrate anyone. Users that already have an MD5 hash keep it until you re-hash their passwords.

Re-hash existing user passwords​

Set the password again for every user still reported as md5 so that PostgreSQL stores a scram-sha-256 hash:

ALTER ROLE ROLE_NAME PASSWORD 'ROLE_PASSWORD';

You can reuse the same password. The hash is recalculated with the algorithm that pg.password_encryption is currently set to, so run it only after you set the option to scram-sha-256. On PostgreSQL 18 and later, ALTER ROLE returns a deprecation WARNING if the password is still stored as an MD5 hash, which tells you the option is not in effect yet.

Re-run the check for MD5 users to confirm that no users are left on MD5.

Troubleshoot connection issues​

If you experience authentication failures after migrating:

  • Check client library support: Make sure your PostgreSQL client and driver support scram-sha-256.
  • Review connection logs: Look for authentication method mismatches and permission denied errors, which point to a pinned connection pool.

Related pages