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

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

How to Migrate DataSunrise CloudFormation Template from Launch Configuration (LC) to Launch Template (LT) Resource in Auto Scaling Group How to Send DataSunrise Events to a Microsoft Teams Channel via Incoming Webhook using Subscribers How To Offload The Audit Database Data To AWS S3 And Read It Using AWS Athena Service Convert Trial or BYOL Configuration of DataSunrise to Hourly Billing PostgreSQL (RDS) vs Aurora PostgreSQL How to Troubleshoot “Connection Was Terminated” or “Connection Terminated Unexpectedly” Errors in Applications Using DataSunrise Proxies DataSunrise’s Performance Under High Traffic Conditions DataSunrise’s Approach to Configuring Penalties for SQL Injection Detection How to Block Specific Hosts in DataSunrise for Enhanced Database Security Troubleshooting AWS Metering and Hourly Billing Issues in DataSunrise on AWS Marketplace How To Perform Cloud Formation Modification Dynamic Data Masking with DataSunrise: Masking with Lua scripts How to Choose the Database for Audit Storage: A Performance Analysis How to Run pgbench Through DataSunrise Proxy on PostgreSQL 14 with SCRAM Authentication DataSunrise SSO Authentication Based on SAML (Okta) DataSunrise SSO Authentication Based on OpenID (Okta) Comprehensive Guide On How to Search for Sensitive Data in Images Hosted on AWS S3 How to Deploy DataSunrise with Terraform Template on Azure Integrate DataSunrise with SQL Server Always On Cluster How to Deploy DataSunrise in Microsoft Azure Using Azure Resource Manager How to Perform DataSunrise Static Data Masking for MongoDB How to Configure DB Audit Trailing for MS Azure MySQL Configure DB Audit Trailing for MS Azure PostgreSQL How to Configure DataSunrise to Mask Data for Amazon Athena How to Upgrade RHEL OS version of existing DataSunrise servers How to Integrate DataSunrise with AWS Database Activity Streams for Getting Auditing Results for AWS Aurora PostgreSQL Set Up SSL Certificates for DataSunrise Database Proxy Reports in DataSunrise: Crucial System for Enhanced Database Security How to Hide Schemas From Users in Redshift AWS RDS PostgreSQL Audit Logs in DataSunrise Audit Administrative Actions in Your Oracle RDS and EC2 How to Check if DataSunrise Receives Traffic Remove a Procedure or a Function From a Database

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:

Open your terminal.
Before running pgbench, export the following environment variable:
```
export PGCHANNELBINDING=disable
```
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.