Using PGBouncer With CockroachDB Serverless

Given CockroachDB scales with vCPU, there's a hard limit to how many active connections we can support per vCPU before a serious problem arises. PGBouncer stretches the limits a bit making it a cost-effective option. In serverless architectures, there is no client-side connection pooling, and using middleware like PGBouncer can alleviate the problem of connection storms. Please see my previous articles on the topic for more details.

Previous Articles

1. Using PGBouncer with CockroachDB
2. Using PGBouncer with Cockroach Cloud Free Tier
3. Exploring PGBouncer auth_type(s) with CockroachDB

Motivation

We've covered how to deploy PGBouncer with a self-hosted CockroachDB cluster. Today, I'm going to demonstrate how to run PGBouncer along with the Cockroach Cloud free-forever tier database. The overall concepts are identical, but we will highlight some of the major differences in deploying PGBouncer with a cloud product.

High-Level Steps

• Create a free forever Cockroach Cloud free cluster
• Install and configure PGBouncer
• Verify

Step-By-Step Instructions

Start a Cluster

You can create a cluster using our latest directions. As soon as your cluster is created, you will be prompted with steps to connect. You have to download and install cockroach binary and download a certificate to authenticate with the cluster. Also, make note of the password, as we don't display it again and you will have to change the password from the SQL users screen if you lose it.

Before you proceed with the steps described, take a look at the third tab in the connection modal called Connection parameters. Make note of all of the properties, as we will need to use them in the same form they're displayed; specifically, we need the database name as it is displayed. At the time of writing, the connection string shows options=--cluster%3Dartem-freetier-2924, which is the unique identifier for the multi-tenant cluster we're using. In the display modal for connection parameters, the database name is artem-freetier-2924.defaultdb, which means the same thing, but it's a form PGBouncer can understand. There is no meaningful way to pass the options=--cluster argument to the pgbouncer.ini that I know of. If you do, please respond in the comments, as I'd love to learn.

Install and Configure PGBouncer

I'm using a Mac, and PGBouncer is conveniently available via brew.

brew install pgbouncer

Prepare a userlist.txt File for Authentication Through PGBouncer

It takes the form of "username" and "password" separated by a space on each new line. You will take your CockroachDB username provided earlier along with the password and paste them into the file:

"yourfreetierusername" "yourfreetierpassword"

Prepare a pgbouncer.ini File Using a Template

Fill out the template based on the information you were provided in the connection parameters.

[databases]
* = host = free-tier.gcp-us-central1.cockroachlabs.cloud  dbname = artem-freetier-2924.defaultdb user = artem port = 26257

 [pgbouncer]
 listen_port = 6432
 listen_addr = *
 auth_type = trust
 auth_file = userlist.txt
 logfile = pgbouncer.log
 pidfile = pgbouncer.pid
 admin_users = artem
 ignore_startup_parameters=extra_float_digits,options

Start PGBouncer

-d will run in the background. When we make changes to the pgbouncer.ini file, we can reload the configuration using -R flag.

Initial command:

 pgbouncer -d pgbouncer.ini

Reloading PGBouncer:

 pgbouncer -d -R pgbouncer.ini

Check the Logs

tail -f pgbouncer.log

Connect to the Database via PGBouncer

cockroach sql --host=localhost --port=6432

ERROR: cannot load certificates.
Check your certificate settings, set --certs-dir, or use --insecure for insecure clusters.

problem using security settings: no certificates found; does certs dir exist?
Failed running "sql"

Let's try the suggestion passing --insecure, as we don't have any certificate except for the certificate authority provided to us.

cockroach sql --host=localhost --port=6432 --insecure

# To exit, type: \q.
#
ERROR: password authentication not enabled in insecure mode
Failed running "sql"

Ok, we're getting somewhere. In case it is not obvious, let me explain. We are trying to authenticate using a username and password, but the cluster expects a certificate. We're passing --insecure to invalidate the request for TLS, but the way cockroach binary works is in an insecure mode: we don't even rely on password authentication and we break the connection.

What if we use psql client instead?

psql -h localhost -p 6432 -U artem

psql: error: ERROR:  pgbouncer cannot connect to server

This is still ambiguous. What do the PGBouncer logs say?

2021-08-11 10:21:14.408 EDT [96549] WARNING server login failed: FATAL server requires encryption

The keyword here is server requires encryption. That's the key part, as we are not talking about client. I'm highlighting this point, as it is important when you look at the TLS properties of pgbouncer.ini file, specifically the difference between client_tls_sslmode and server_tls_sslmode.

To prove my point, let's try another attempt to connect to the cluster disabling client_tls_sslmode.

cockroach sql --url "postgresql://localhost:6432/yourfreetierclustername.defaultdb?sslmode=disable&user=yourfreetierusername&password=yourfreetierpassword"

ERROR: SSL authentication error while connecting.

pq: pgbouncer cannot connect to server
Failed running "sql"

The logs say:

2021-08-11 10:27:59.045 EDT [96549] WARNING server login failed: FATAL server requires encryption
2021-08-11 10:27:59.045 EDT [96549] LOG S-0x7ff811808210: artem-freetier-2924.defaultdb/artem@35.184.49.18:26257 closing because: login failed (age=0s)

I did try to invalidate the sslmode=disable, but that's not what the cluster is expecting. Also, recall that we were provided a root.crt, and up to this point we have not used it. We have to make some additional changes to the pgbouncer.ini file.

[databases]
* = host = free-tier.gcp-us-central1.cockroachlabs.cloud  dbname = artem-freetier-2924.defaultdb user = artem port = 26257

 [pgbouncer]
 listen_port = 6432
 listen_addr = *
 auth_type = trust
 auth_file = userlist.txt
 logfile = pgbouncer.log
 pidfile = pgbouncer.pid
 admin_users = artem
 ignore_startup_parameters=extra_float_digits,options

# free tier provided cert
server_tls_sslmode = verify-full
server_tls_ca_file = /Users/artem/.postgresql/root.crt

Again, we're focusing on the server part here, so let's add server_tls_sslmode and server_tls_ca_file properties which we were provided by Cockroach Cloud.

server_tls_sslmode = verify-full
server_tls_ca_file = /Users/artem/.postgresql/root.crt

The "sslmode" is straight out of the connection string and root.crt is the cert you downloaded originally.

Let's restart PGBouncer and see what changes. By the way, we have to restart PGBouncer when we're making changes with TLS up to version 1.15.0 of PGBouncer. Given that PGBouncer 1.16.0 was just released, the key feature is hot reloading TLS settings without restarting. I'm patiently waiting for the new version to be available in brew!

Let's try to connect again:

cockroach sql --url "postgresql://localhost:6432/yourfreetierclustername.defaultdb?sslmode=disable&user=yourfreetierusername&password=yourfreetierpassword"

#
# Enter \? for a brief introduction.
#
artem@localhost:6432/defaultdb>

Success! Let's try the other methods to connect:

psql -h localhost -p 6432 -U artem -d artem-freetier-2924.defaultdb

psql (13.3, server 13.0.0)
Type "help" for help.

artem-freetier-2924.defaultdb=>

Again, success!

cockroach sql --host=localhost --port=6432 --insecure

#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
ERROR: password authentication not enabled in insecure mode
Failed running "sql"

Bummer!

The good news is we can connect to the Cockroach cluster using PGBouncer using cockroach binary, --url, and via psql. We could stop here if we wanted. This state will do the trick and you can test scaling active connections with PGBouncer, but this is not representative of real-world use cases. There's also the issue of authenticating to PGBouncer using trust method, which is weak.

Considering PGBouncer is running on my laptop where my client resides, this is a risk worth taking for the benefit of convenience. However, say PGBouncer was not on the same client, and you had multiple clusters and users you need to manage. It is highly advisable to set up TLS between the client and PGBouncer. Reasons include:

1. Man in the middle attacks are preventable.
2. Configuration errors where clients are directed to the wrong PGBouncer instances, etc.
3. Users with varying privileges are not intermixed.
4. Specifically in our case, as the free tier today comes with password authentication, we no longer need to pass passwords in clear text to the console.

Obviously, if you have a mature infrastructure where the lift of additional TLS complexity is solved by service mesh architecture or something else entirely, you can ignore the next steps.

Setup TLS Between Client and PGBouncer

Let's make this a little bit more interesting by enabling TLS between client and PGBouncer, as the connection between PGBouncer and the cluster is already encrypted after having done server_tls_ssmode and server_tls_ca_file.

We're moving away from trust in favor of cert authentication. We have to create the certs to make it work, and you can follow these directions to do so.

Step 1:  Create the CA key and certificate pair.

The ca.cnf file is used as-is from the docs, but you can change it to fit your environment if necessary.

mkdir certs my-safe-directory
openssl genrsa -out my-safe-directory/ca.key 2048
chmod 400 my-safe-directory/ca.key
openssl req -new -x509 -config ca.cnf -key my-safe-directory/ca.key -out certs/ca.crt -days 365 -batch
rm -f index.txt serial.txt
touch index.txt
echo '01' > serial.txt

Step 2:  Create the certificate and key pairs for nodes.

My node.cnf file looks like so:

# OpenSSL node configuration file
[ req ]
prompt=no
distinguished_name = distinguished_name
req_extensions = extensions

[ distinguished_name ]
organizationName = Example Inc

[ extensions ]
subjectAltName = critical,DNS:localhost,DNS:node,IP:0.0.0.0

We are going to connect to PGBouncer using localhost, but we also require node, so I'm including both in the SAN field.

openssl genrsa -out certs/node.key 2048
chmod 400 certs/node.key
openssl req -new -config node.cnf -key certs/node.key -out node.csr -batch
openssl ca -config ca.cnf -keyfile my-safe-directory/ca.key -cert certs/ca.crt -policy signing_policy -extensions signing_node_req -out certs/node.crt -outdir certs/ -in node.csr -batch
openssl x509 -in certs/node.crt -text | grep "X509v3 Subject Alternative Name" -A 1

            X509v3 Subject Alternative Name: critical
                DNS:localhost, DNS:node, IP Address:0.0.0.0

Step 3:  Create the certificate and key pair for the first user.

My client.cnf file:

[ req ]
prompt=no
distinguished_name = distinguished_name
req_extensions = extensions

[ distinguished_name ]
organizationName = Cockroach
commonName = artem

[ extensions ]
subjectAltName = DNS:root

openssl genrsa -out certs/client.artem.key 2048
chmod 400 certs/client.artem.key
openssl req -new -config client.cnf -key certs/client.artem.key -out client.artem.csr -batch
openssl ca -config ca.cnf -keyfile my-safe-directory/ca.key -cert certs/ca.crt -policy signing_policy -extensions signing_client_req -out certs/client.artem.crt -outdir certs/ -in client.artem.csr -batch
openssl x509 -in certs/client.artem.crt -text | grep "CN ="

        Issuer: O = Cockroach, CN = Cockroach CA
        Subject: O = Cockroach, CN = artem

Now, one quirk to note before we attempt to connect is the placement of node.crt and client.username.crt certificates in the same directory. I recommend creating a separate node-certs directory, and moving node cert as well as the associated key there. Also, copy ca.crt to the same directory for good measure.

pgbouncer_freetier mkdir node-certs
mv certs/node.* node-certs
cp certs/ca.crt node-certs

We are going to add the following additional properties to the pgbouncer.ini file.

auth_type = cert
client_tls_sslmode = verify-full
# Path to file that contains trusted CA certs
client_tls_key_file = ./certs/node.key
client_tls_cert_file = ./certs/node.crt
client_tls_ca_file = ./certs/ca.crt

My final pgbouncer.ini file looks like so:

[databases]
* = host = free-tier.gcp-us-central1.cockroachlabs.cloud  dbname = artem-freetier-2924.defaultdb user = artem port = 26257

[pgbouncer]
listen_port = 6432
 listen_addr = *
auth_type = cert
auth_file = userlist.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = artem
ignore_startup_parameters=extra_float_digits,options

client_tls_sslmode = verify-full
# Path to file that contains trusted CA certs
client_tls_key_file = ./node-certs/node.key
client_tls_cert_file = ./node-certs/node.crt
client_tls_ca_file = ./node-certs/ca.crt

server_tls_sslmode = verify-full
server_tls_ca_file = /Users/artem/.postgresql/root.crt

Restart PGBouncer.

Verify

cockroach sql --url "postgresql://localhost:6432/artem-freetier-2924.defaultdb?sslcert=./certs%2F/client.artem.crt&sslkey=./certs%2F/client.artem.key&sslmode=verify-full&sslrootcert=./certs%2Fca.crt"

#
# Enter \? for a brief introduction.
#
artem@localhost:6432/defaultdb> 

From the logs:

2021-08-11 13:08:00.794 EDT [99846] LOG C-0x7f9527008610: artem-freetier-2924.defaultdb/artem@[::1]:51138 login attempt: db=artem-freetier-2924.defaultdb user=artem tls=TLSv1.3/TLS_AES_256_GCM_SHA384

Notice, we're using localhost, port 6432, sslmode=verify-full, and ca as well as client certs we just created. This means PGBouncer and client will check the authenticity of the hostname as well as the authenticity of the client accessing PGBouncer.

For completeness, here's the psql version:

psql "host=localhost dbname=artem-freetier-2924.defaultdb port=6432 user=artem sslmode=verify-full sslcert=./certs/client.artem.crt sslkey=./certs/client.artem.key sslrootcert=./certs/ca.crt"

2021-08-11 13:15:01.161 EDT [99846] LOG C-0x7f9527008610: artem-freetier-2924.defaultdb/artem@[::1]:51253 login attempt: db=artem-freetier-2924.defaultdb user=artem tls=TLSv1.3/TLS_AES_256_GCM_SHA384

Wrap-Up

At this point, we have a working CockroachDB free tier cluster connected to PGBouncer and secured with TLS end to end. Happy pooling!