Skip to main content

Verify the Aiven for PostgreSQL® password encryption method

Verify that your Aiven for PostgreSQL® connections use scram-sha-256 password encryption.

Aiven for PostgreSQL defaults to scram-sha-256 password encryption for enhanced security, replacing the MD5 method. This default might need enforcing in specific configurations or setups. Check if your action is needed and, if so, update your configuration to enable scram-sha-256.

important

PostgreSQL 19 will no longer support the MD5 password encryption, making the scram-sha-256 password encryption mandatory.

Check if your action is needed​

  • No action is needed if in your Aiven for PostgreSQL services:

    • There are no PgBouncer connection pools tied to specific database users.
    • All database users are managed by Aiven.

  • Your action is required if in your Aiven for PostgreSQL services:

    • PgBouncer connection pools are tied to specific database users.
    • There are database users not managed by Aiven.

If your action is required, review the scram-sha-256 compatibility guidelines, and follow up, depending on your configuration requirements.

Ensure scram-sha-256 compatibility​

Ensure app connections to PgBouncer connection pools​

When connection pools are configured with specific user names, attempts to connect using another role after scram-sha-256 is enforced fails with a permission denied error. This is due to the challenge-response authentication flow initiated by the PostgreSQL client and proxied by PgBouncer to PostgreSQL.

  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) may experience 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, choose your migration strategy:

    • 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

Update service's user_config​

Update the password encryption value in your service's user_config:

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

This enables hashing and authenticating new managed users' passwords using scram-sha-256.

important

While this maintains the MD5 compatibility, re-hash the passwords at your earlier convenience.

Re-hash database user passwords​

Re-hash existing passwords supported by MD5 to use the scram-sha-256 encryption:

ALTER ROLE ROLE_NAME PASSWORD 'ROLE_PASSWORD';

Troubleshoot connection issues​

If you experience authentication failures:

  • Check client library support: Ensure your PostgreSQL client supports scram-sha-256.
  • Review connection logs: Look for authentication method mismatches.