How to Run pgbench Through DataSunrise Proxy on PostgreSQL 14 with SCRAM Authentication

Introduction

Welcome to our latest blog post where we dive into the specifics of running pgbench through a DataSunrise proxy for PostgreSQL databases, particularly for version 14 and later. These versions of PostgreSQL come with an activated feature known as SCRAM authentication, enhancing security through better password handling mechanisms. However, this feature introduces complexities when running tools like pgbench through a proxy. Let’s unpack this issue and provide a solution.

Understanding SCRAM Authentication

SCRAM (Salted Challenge Response Authentication Mechanism) is a security protocol that offers robust password protection for database connections. It prevents password sniffing on untrusted networks and allows passwords to be stored in a secure, hashed format on the server. A key part of this protocol involves using a digital certificate for encrypting passwords during the authentication process.

The Challenge with Proxies

When you connect to PostgreSQL through a proxy like DataSunrise, the certificates used by the proxy and the database server may differ. Due to this, the encrypted password sent from the client, which the database should decrypt using SCRAM, cannot be processed if it goes through the proxy because of the certificate mismatch.

Solution for Clients

Clients wishing to use tools like pgbench through DataSunrise proxy will need to disable the SCRAM authentication protocol on their application or drivers. Disabling SCRAM allows the connection to revert to a less strict authentication method which doesn’t rely on matching certificates for password encryption.

How to Disable SCRAM for pgbench

To disable SCRAM authentication when using pgbench, you can set an environment variable that instructs the PostgreSQL client to ignore channel binding (a component of SCRAM). Here’s how you can do it:

1. Open your terminal.

2. Before running pgbench, export the following environment variable:

   export PGCHANNELBINDING=disable

3. Now, run pgbench as you normally would.

Conclusion

By setting the PGCHANNELBINDING environment variable to ‘disable’, you can seamlessly run pgbench through DataSunrise proxy even on the latest PostgreSQL versions that utilize SCRAM authentication. This workaround ensures your performance testing doesn’t hit a roadblock due to enhanced security features.

Remember that while this method allows you to proceed with your performance testing, it should be used with caution and preferably in a secure, trusted network environment, as it bypasses an advanced security feature of PostgreSQL.