5. Integrating pgBouncer

Requirement:

• This tutorial requires Payload Account Functionality

1. Install pgBouncer on Droplet

First, SSH into your Droplet:

ssh -i ~/.ssh/id_ed25519_do root@<your_droplet_ip>

If you see System restart required, run:

reboot

(Wait 10 seconds and reconnect to Droplet again.)

Install bgBouncer and openssl:

apt install -y pgbouncer openssl

Expected outcome:

Running kernel seems to be up-to-date.
No services need to be restarted.
No containers need to be restarted.
No user sessions are running outdated binaries.
No VM guests are running outdated hypervisor (qemu) binaries on this host.

2. Review pg_hba.conf

Open pg_hba.conf, run:

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

Make sure you have the following lines:

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

Explanation:

• local all all peer means any OS user who is also a Postgres role with the same name can connect without a password via the local Unix socket. Lets you run sudo -u postgres psql for admin tasks without typing a password.
• host all all 127.0.0.1/32 scram-sha-256 means: any TCP connection from 127.0.0.1 (IPv4 localhost) must use password authentication with SCRAM-SHA-256.
• host all all ::1/128 scram-sha-256 means: Same as above but for IPv6 localhost.

3. Create pgBouncer auth file

Open/create pgBouncer auth file, run:

sudo nano /etc/pgbouncer/userlist.txt

Note: You won't see anything at all in this txt file if you open it for the first time.

Give pgBouncer a list of users:

Give pgBouncer a list of users and passwords so it can use this list to authenticate the connection from a client.

While in userlist.txt, paste the following code to the end of the file:

"test_payload_auth_admin_v3" "your_user_password"

Add the following code (right after the above code) to create pgBouncer admin user:

"pgbouncer" "this_user_password"

Note: pgbouncer is a special reserved username that gives the user administrative privileges within pgBouncer's console.

Exit & Save (ctrl + X, yes, and confirm (enter))

Lock down the permission on the userlist.txt, run:

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

Note: This command changes the ownership of the file to the postgres user and group. The reason for setting the ownership to postgres is that the pgBouncer service itself runs as the postgres user on most standard Linux systems. Even though pgbouncer is a separate piece of software, its process is typically started and managed by the postgres user to ensure it has the necessary permissions to communicate with the underlying PostgreSQL database files and to manage its own configuration. As long as it doesn't show error, you're good to continue.

Set permission, run:

sudo chmod 600 /etc/pgbouncer/userlist.txt

Explanation:

• This command sets the permissions on the file. The 600 permission code breaks down as follows:
• First digit represents the owner and number 6 means can read and write
• Second digit represents the group and 0 means no access
• Third digit represents others and 0 means no access

In short, for security files like userlist.txt, 600 is used so only the owner can touch it.

Verify permission, run:

ls -l /etc/pgbouncer/userlist.txt

This command lists the file's permissions and ownership, allowing you to verify that your changes were applied correctly. The output should look similar to rw------- 1 pgbouncer pgbouncer. -rw------- means only the owner can read/write, no one else can access. This means your userlist.txt is now secure and usable.

4. Configure pgbouncer.ini

First let's create a backup for pgbouncer.ini in case we make a mistake and pgBouncer fails to start, run:

sudo cp /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.bak

Tell PgBouncer where to find the certificate and key files. Open the pgbouncer.ini, run:

sudo nano /etc/pgbouncer/pgbouncer.ini

You need to add or modify several key settings to configure PgBouncer to work with your PostgreSQL database and enable secure TLS connections. Reference:

[databases]
<database_name> = host=127.0.0.1 port=5432 dbname=<database_name> user=<database_user/owner_name>
 
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
 
; Pool settings optimized for serverless (Vercel)
pool_mode = transaction
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 10
max_client_conn = 500
server_reset_query = DISCARD ALL
 
; Authentication
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
 
; TLS settings (Vercel -> pgBouncer)
client_tls_sslmode   = require
client_tls_cert_file = /etc/pgbouncer/pgbouncer.crt
client_tls_key_file  = /etc/pgbouncer/pgbouncer.key
 
; QoL and management
ignore_startup_parameters = application_name,extra_float_digits
admin_users = pgbouncer
stats_users = pgbouncer

Restart pgBouncer so it loads the new config, run:

sudo systemctl restart pgbouncer

If this is not your first time, skip the rest and continue to Issue the client certificate (for Vercel)

Check pgBouncer status to make sure it's running without error, run:

sudo systemctl status pgbouncer

Expected outcome:

● pgbouncer.service - connection pooler for PostgreSQL
     Loaded: loaded (/usr/lib/systemd/system/pgbouncer.service; enabled; preset: enabled)
     Active: active (running) since Sun 2025-08-10 16:09:04 UTC; 42s ago

Press q to exist the status viewing

Try a local connection through pgBouncer (no firewall needed), run:

psql "host=127.0.0.1 port=6432 dbname=<database_name> user=<database_owner_name> sslmode=require" -c "select current_user, current_database();"

(Enter db owner password when prompted)

Expected outcome:

Expected outcome:
        current_user        |   current_database
----------------------------+----------------------
        <db_owner_name>     |   <db_name>
(1 row)

• From the result, this has proven pgBouncer works locally on the droplet (psql from 127.0.0.1:6432).
• But your laptop still can’t reach it, because UFW blocks all traffic to port 6432 from the outside. So let's make it works from the laptop.

5. Simulate remote connection

Set firewall rule to allow your current public IP to reach pgBouncer (port 6432), run:

(We will set mTLS for security later)

MYIP=$(curl -4 ifconfig.me)
echo "Your IP is: $MYIP"
sudo ufw allow from $MYIP/32 to any port 6432 proto tcp
sudo ufw status | grep 6432

Expected outcome:

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    15  100    15    0     0     69      0 --:--:-- --:--:-- --:--:--    69
Your IP is: <your_droplet_ip>
Rules updated

Explanation:

• Runs curl to query ifconfig.me, which returns your current public IPv4 address.
• Stores it in the shell variable MYIP.
• The -4 ensures it uses IPv4 (avoids IPv6 complications).

• Prints the IP we just detected, so you can confirm it’s correct before making firewall changes.

• Updates the firewall (UFW) to allow incoming TCP connections on port 6432 (pgBouncer)
• Only from the specific IP $MYIP.
• /32 means “just this one IP,” so it’s tightly restricted.

• Checks the firewall status list and filters for lines containing 6432 so you see the rule you just added.

6. Prepare remote connection

Install psql command-line client (if not already)

• Download the latest version for your operating system from PSQL Command Client

During setup, uncheck everything except Command Line Tools.

Add to Windows PATH environment variable

Add the bin folder of the PostgreSQL installation (e.g., C:\Program Files\PostgreSQL\17\bin) to your Windows PATH environment variable.

• Windows -> search "Edit System Environment Variables"
• Click "Environment Variables" button
• In User variables for your PC username, select Path.
• Click Edit → New.
• Paste: C:\Program Files\PostgreSQL\17\bin
• Click OK on all windows.

Reopen PowerShell and test:

psql --version

Check your ip address (IPv4), run:

curl.exe -s -4 ifconfig.me

Allow your PC ip: Tell Droplet (Ubuntu firewall) to allow it. SSH into your Droplet and run:

sudo ufw allow from <your_pc_ip_address>/32 to any port 6432 proto tcp

sudo ufw allow OpenSSH

sudo ufw enable

• Type y + enter to confirm

Check if both rules (OpenSSH and ufw) are active, run:

sudo ufw status numbered

Expected outcome:

     To                         Action      From
     --                         ------      ----
[ 1] 6432/tcp                   ALLOW IN    <your_droplet_ip>
[ 2] 6432/tcp                   ALLOW IN    <your_pc_ip_address>
[ 3] OpenSSH                    ALLOW IN    Anywhere
[ 4] OpenSSH (v6)               ALLOW IN    Anywhere (v6)

Now we've confirmed that UFW is allowing traffic on port 6432, but that doesn't guarantee a remote connection will work.

7. Test remote connection

To actually test if remote access works, you need to try connecting from outside the Droplet (from your own machine) to that port.

First, check if pgBouncer is listening to public interface, run:

sudo ss -ltnp | grep 6432

Expected outcome:

LISTEN 0      128          0.0.0.0:6432      0.0.0.0:*    users:(("pgbouncer",____))

Test connection from your pc, run:

psql -h 127.0.0.1 -p 6432 -U <DB_USERNAME> -d <DB_NAME>

Note: It will ask you to enter the database user password.

Expected outcome:

psql (17.5 (Ubuntu 17.5-0ubuntu0.25.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: none)

Now you're in as the database user. Try to see the schemas in this database, run:

\dn

If you don't use public schema as default, you can set the search path of the database as follows:

ALTER ROLE test_payload_auth_admin_v3 IN DATABASE test_payload_auth_v3
  SET search_path = payload, public;

Exit the session with \q then restart pgBouncer:

sudo systemctl restart pgbouncer

Reconnect:

psql -h <DROPLET_IP> -p 6432 -U <DB_USERNAME> -d <DB_NAME>

Show default search path:

SHOW search_path;

Show all tables:

\dt

Expected outcome:

                              List of relations
 Schema  |             Name              | Type  |           Owner
---------+-------------------------------+-------+----------------------------
 payload | payload_locked_documents      | table | test_payload_auth_admin_v3
 payload | payload_locked_documents_rels | table | test_payload_auth_admin_v3
 payload | payload_migrations            | table | test_payload_auth_admin_v3
 payload | payload_preferences           | table | test_payload_auth_admin_v3
 payload | payload_preferences_rels      | table | test_payload_auth_admin_v3
 payload | profiles                      | table | test_payload_auth_admin_v3
 payload | users                         | table | test_payload_auth_admin_v3
 payload | users_sessions                | table | test_payload_auth_admin_v3
(8 rows)

You have successfully integrated pgBouncer

Continue to the next part: TLS Certificate