How to Configure Pgbouncer Connection Pooling with Transaction Mode and Scram-sha-256 Authentication for PostgreSQL on Ubuntu Server

ByAkensai

Learning how to configure Pgbouncer connection pooling with transaction mode and scram-sha-256 authentication for PostgreSQL on Ubuntu Server is one of the best ways to improve database performance at scale. Without connection pooling, every application request opens a new database connection. PostgreSQL handles each connection as a separate process. This gets expensive fast. PgBouncer sits between your application and PostgreSQL. It reuses existing connections instead of creating new ones. Transaction mode is the most efficient pooling mode. It releases a connection back to the pool after each transaction completes. Combined with scram-sha-256 authentication, you get a secure, high-performance setup. This tutorial walks you through the complete configuration. You’ll install PgBouncer, set up the authentication files, configure transaction pooling, and test the connection. By the end, you’ll have a production-ready setup running on Ubuntu Server.

Prerequisites and Requirements for This PgBouncer Setup

Before starting this tutorial on configuring PgBouncer connection pooling with transaction mode and scram-sha-256 authentication, make sure you have the following in place.

Required software and access:

  • Ubuntu Server 20.04 or 22.04 (fresh or existing install)
  • PostgreSQL 14 or later installed and running
  • A PostgreSQL superuser account (typically postgres)
  • Root or sudo access on the server

Assumed knowledge:

  • Basic Linux command line usage
  • Familiarity with PostgreSQL basics
  • Understanding of what a database connection is

Estimated time: 30–45 minutes

You should also confirm that PostgreSQL is configured to use scram-sha-256 as its authentication method. Check your pg_hba.conf file before proceeding. If your PostgreSQL cluster still uses md5, you’ll need to switch it to scram-sha-256 first. The official PostgreSQL documentation on pg_hba.conf covers this in detail.

How to Configure Pgbouncer Connection Pooling with Transaction Mode and Scram-sha-256 Authentication

For a related walkthrough, see: Setup Pivpn Server on Ubuntu and Connect on Windows

Follow these steps carefully. Each step builds on the previous one.

Step 1: Install PgBouncer

Update your package list first, then install PgBouncer.

sudo apt update
sudo apt install pgbouncer -y

Verify the installation completed successfully.

pgbouncer --version

You should see output showing the installed version number.

Step 2: Configure PostgreSQL to Use Scram-sha-256

Open the PostgreSQL configuration file to confirm or set the password encryption method.

sudo nano /etc/postgresql/14/main/postgresql.conf

Find and set this line:

password_encryption = scram-sha-256

Now open pg_hba.conf and update the authentication method.

sudo nano /etc/postgresql/14/main/pg_hba.conf

Change md5 to scram-sha-256 for local and host connections:

local   all             all                                     scram-sha-256
host    all             all             127.0.0.1/32            scram-sha-256

Restart PostgreSQL to apply the changes.

sudo systemctl restart postgresql

Step 3: Create a Database User and Set the Password

Log in to PostgreSQL as the postgres user.

sudo -u postgres psql

Create a new user with a password. This automatically uses scram-sha-256 if you set it in Step 2.

CREATE USER appuser WITH PASSWORD 'StrongPassword123';
GRANT CONNECT ON DATABASE myapp TO appuser;

Exit psql with q.

Step 4: Get the Scram-sha-256 Password Hash

PgBouncer needs the hashed password, not the plaintext one. Retrieve it from the PostgreSQL system catalog.

sudo -u postgres psql -c "SELECT usename, passwd FROM pg_shadow WHERE usename='appuser';"

Copy the full hash string. It starts with SCRAM-SHA-256$. You’ll need it in the next step.

Step 5: Configure the PgBouncer userlist.txt File

PgBouncer stores credentials in a file called userlist.txt. Open it for editing.

sudo nano /etc/pgbouncer/userlist.txt

Add your user and the full scram-sha-256 hash you copied earlier. The format is:

"appuser" "SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>"

Paste the actual hash value from pg_shadow in place of the placeholder above. Save and close the file.

Step 6: Configure pgbouncer.ini for Transaction Mode

Open the main PgBouncer configuration file.

sudo nano /etc/pgbouncer/pgbouncer.ini

Replace the contents with a clean configuration like this:

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 200
default_pool_size = 25
reserve_pool_size = 5
log_connections = 1
log_disconnections = 1
admin_users = postgres

The key setting here is pool_mode = transaction. This tells PgBouncer to return connections to the pool after each transaction. It’s the most efficient mode for most web applications.

Step 7: Start and Enable PgBouncer

Start the PgBouncer service and enable it to run on boot.

sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer
sudo systemctl status pgbouncer

The status output should show active (running).

Step 8: Test the Connection Through PgBouncer

Connect to your database through PgBouncer on port 6432 instead of the default PostgreSQL port 5432.

psql -h 127.0.0.1 -p 6432 -U appuser -d myapp

Enter the password when prompted. If you connect successfully, your setup is working. You can also check active connections in the PgBouncer admin console.

psql -h 127.0.0.1 -p 6432 -U postgres pgbouncer
SHOW POOLS;

Troubleshooting Common Pgbouncer and Scram-sha-256 Issues

Even with careful setup, you may hit a few snags. Here are the most common problems and how to fix them.

Error: “unsupported frontend protocol”
This usually means your client doesn’t support scram-sha-256. Update your PostgreSQL client library or driver to a recent version.

Error: “password authentication failed”
Double-check the hash in userlist.txt. It must be the exact string from pg_shadow. A single missing character breaks authentication. Also confirm auth_type = scram-sha-256 is set in pgbouncer.ini.

PgBouncer won’t start
Check the logs for details.

sudo journalctl -u pgbouncer -n 50

Common causes include a malformed pgbouncer.ini file or incorrect file permissions on userlist.txt.

Prepared statements fail in transaction mode
Transaction mode doesn’t support session-level prepared statements. This is a known limitation. You need to disable prepared statements in your application or ORM. For example, in a Node.js app using pg, set statement_timeout and avoid named prepared statements. The PgBouncer FAQ covers this limitation and workarounds in detail.

Fix file permissions if PgBouncer can’t read userlist.txt

sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 640 /etc/pgbouncer/userlist.txt

Conclusion

You now have a working PgBouncer setup running in transaction mode with scram-sha-256 authentication on Ubuntu Server. This configuration significantly reduces the number of direct connections to PostgreSQL. Your application connects to PgBouncer on port 6432. PgBouncer manages the actual database connections efficiently. The result

Similar Posts