Requirement:
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.)
apt install -y pgbouncer opensslExpected 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.Open pg_hba.conf, run:
sudo nano /etc/postgresql/17/main/pg_hba.confMake 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-256Explanation:
Open/create pgBouncer auth file, run:
sudo nano /etc/pgbouncer/userlist.txtNote: 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 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))
When a client tries to connect to a PostgreSQL database through pgBouncer, it first connects to the pgBouncer service. pgBouncer then checks its userlist.txt file for the provided username and password. If a match is found, pgBouncer authenticates the client's connection to its own service. After this initial authentication, pgBouncer will then establish a connection to the actual PostgreSQL database using the credentials from its configuration, or in some cases, the same credentials provided by the client, depending on the authentication method configured.
When you create a new database for a new project and want to connect via pgBouncer, be sure to add its owner and password here.
sudo chown postgres:postgres /etc/pgbouncer/userlist.txtNote: 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.txtExplanation:
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.txtThis 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.
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.bakTell PgBouncer where to find the certificate and key files. Open the pgbouncer.ini, run:
sudo nano /etc/pgbouncer/pgbouncer.iniYou 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 = pgbouncerMost of these fields already existed in pgbouncer.ini, make sure you modify them and don't deplicate extra of the same field
The [databases] section of your pgbouncer.ini file can and should have multiple databases listed if you want pgBouncer to manage connection pooling for them. So when you have a new database for a different project, be sure to add it here.
Restart pgBouncer so it loads the new config, run:
sudo systemctl restart pgbouncerIf 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 pgbouncerExpected 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 agoPress 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)Note that if we do this for the second time (adding a second database), this check would give you SSL error. Because by the end of this tutorial, we'll change to mTLS authentication. So you can skip this check.
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 6432Expected 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 updatedExplanation:
MYIP=$(curl -4 ifconfig.me)During setup, uncheck everything except Command Line Tools.
Add the bin folder of the PostgreSQL installation (e.g., C:\Program Files\PostgreSQL\17\bin) to your Windows PATH environment variable.
Reopen PowerShell and test:
psql --versionCheck your ip address (IPv4), run:
curl.exe -s -4 ifconfig.meAllow 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 tcpsudo ufw allow OpenSSHsudo ufw enableCheck if both rules (OpenSSH and ufw) are active, run:
sudo ufw status numberedExpected 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.
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 6432Expected 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:
\dnIf 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 pgbouncerReconnect:
psql -h <DROPLET_IP> -p 6432 -U <DB_USERNAME> -d <DB_NAME>Show default search path:
SHOW search_path;Show all tables:
\dtExpected 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