Keep pgBouncer public, but require a client certificate to even complete TLS.
Only holders of your client cert & key (your Vercel) can connect; everyone else gets dropped at handshake.
1. Setup Root CA
SSH into your droplet and make a new directory called mtls, run:
sudo mkdir -p /etc/pgbouncer/mtls
Give (or change) owner to the root postgres user, run:
You successfully created a self-signed Certificate Authority (CA) on your droplet.
This CA is a set of files (ca.key and ca.crt) that will serve as the root of trust for your application.
This is the first and most critical step in setting up Mutual TLS (mTLS) to secure your database connection
from Vercel without opening the port to the public.
2. Issue the client certificate (for Vercel)
Generate client private key, run:
For a new project, change the "vercel-client.key" part to a different name.
You've created a unique private key (vercel-client.key) and a Certificate Signing Request (CSR) for your Vercel app.
You've used your "tiny CA's" private key to sign the CSR, which resulted in the final client certificate (vercel-client.crt).
The Certificate request self-signature ok message and Subject and Issuer lines in your terminal output confirm that the certificate was created successfully and was signed by your CA.
You've set the correct permissions for the client key and certificate to ensure they are secure.
The final egrep command shows that the certificate is correctly configured for TLS Web Client Authentication, which is the crucial setting that tells pgBouncer this certificate is for a client connecting to a server.
You've successfully created all the necessary cryptographic files for your Vercel application to connect securely to your database using Mutual TLS (mTLS).
4. Copy the client cert & key to your Vercel and use them
Run and copy the cert result:
base64 -w0 /etc/pgbouncer/mtls/vercel-client.crt
Run and copy the key result:
base64 -w0 /etc/pgbouncer/mtls/vercel-client.key
Go to Vercel and add 2 environment variables called PGCLIENT_KEY_B64 and
PGCLIENT_CERT_B64 and paste their corresponding content.
Do not add these variables to env local development. It contains sensitive information
Do not include the "==" part from the cert nor the "root@..." part from the cert and key. Even though Vercel is smart enough to exclude it but it's better not to include
In .env.local, (local dev) change DATABASE_URI_VERCEL by removing the sslmode=verify-full part.
If you get CONNECTION ESTABLISHED and Verification: OK somehwere in the result,
it means the mTLS mechanism is working and the rest is up to your Vercel and Next.js setup.
You are ready to deploy via Vercel.
Just don't forget to give Vercel PGCLIENT_KEY_B64 and PGCLIENT_CERT_B64
Debug
Make sure all environment variables used in payload.config.ts match .env.local
Make sure to give Vercel PGCLIENT_KEY_B64 and PGCLIENT_CERT_B64
Make sure there are no duplicates in pgbouncer.ini
Double check in:
sudo nano /etc/pgbouncer/pgbouncer.ini
Make sure the userlist.txt has your new user (the owner of the database), run:
sudo nano /etc/pgbouncer/userlist.txt
And add:
"database_owner" "owner_password"
Makesure pgbouncer.ini has your new database, open: