SCRAM authentication in Azure Database for PostgreSQL - Flexible Server

  • Article

APPLIES TO: Azure Database for PostgreSQL - Flexible Server

Salted Challenge Response Authentication Mechanism (SCRAM) is a password-based mutual authentication protocol. It is a challenge-response scheme that adds several levels of security and prevents password sniffing on untrusted connections. SCRAM supports storing passwords on the server in a cryptographically hashed form which provides advanced security.

Note

To access an Azure Database for PostgreSQL flexible server instance using SCRAM method of authentication, your client libraries need to support SCRAM. Refer to the list of drivers that support SCRAM.

Note

SCRAM authentication imposes additional computational load on your application servers, which need to compute the client proof for each authentication. The performance overhead SCRAM introduces might be mitigated by limiting the number of connections in your application's connection pool (reducing chattiness in your application) or limiting the number of concurrent transactions that your client allows (chunkier transactions). Its recommended to test your workloads before migrating to SCRAM authentication.

Configure SCRAM authentication

  1. Change password_encryption to SCRAM-SHA-256. Currently Azure Database for PostgreSQL flexible server only supports SCRAM using SHA-256. Screenshot of Enable SCRAM password encryption.

  2. Allow SCRAM-SHA-256 as the authentication method. Screenshot of Choose the authentication method.

    Important

    You might choose to enforce SCRAM only authentication by selecting only SCRAM-SHA-256 method. By doing so, users with MD5 authentication can longer connect to the server. Hence, before enforcing SCRAM, it is recommended to have both MD5 and SCRAM-SHA-256 as authentication methods until you update all user passwords to SCRAM-SHA-256. You can verify the authentication type for users using the query mentioned in step #7.

  3. Save the changes. These are dynamic properties and do not require server restart.

  4. From your Azure Database for PostgreSQL flexible server client, connect to the Azure Database for PostgreSQL flexible server instance. For example,

    psql "host=myPGServer.postgres.database.chinacloudapi.cn port=5432 dbname=postgres user=myDemoUser password=MyPassword sslmode=require"

psql (12.3 (Ubuntu 12.3-1.pgdg18.04+1), server 12.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

  5. Verify the password encryption.

    postgres=> show password_encryption;
 password_encryption
---------------------
scram-sha-256
(1 row)

  6. You can then update the password for users.

    postgres=> \password myDemoUser
Enter new password:
Enter it again:
postgres=>

  7. You can verify user authentication types using azure_roles_authtype() function.

    postgres=> SELECT * from azure_roles_authtype();
        rolename          | authtype
---------------------------+-----------
azuresu                   | NOLOGIN
pg_monitor                | NOLOGIN
pg_read_all_settings      | NOLOGIN
pg_read_all_stats         | NOLOGIN
pg_stat_scan_tables       | NOLOGIN
pg_read_server_files      | NOLOGIN
pg_write_server_files     | NOLOGIN
pg_execute_server_program | NOLOGIN
pg_signal_backend         | NOLOGIN
replication               | NOLOGIN
myDemoUser                | SCRAM-256
azure_pg_admin            | NOLOGIN
srtest                    | SCRAM-256
sr_md5                    | MD5
(14 rows)

  8. You can then connect from the client that supports SCRAM authentication to your server.

Note

SCRAM authentication is also supported when connected to the built-in managed PgBouncer. Above tutorial is valid for setting up connectivity using SCRAM authentication via built-in PgBouncer feature.

Related content