PgBouncer is a lightweight connection pooler for PostgreSQL. PgBouncer 1.7 was announced on the 18th of December 2015. In this blog post we’ll talk about the major new improvements in PgBouncer.

The Most Colorful Features

• PgBouncer 1.7 supports TLS connections and, I think this is the biggest improvement of the new release. They used OpenSSL/LibreSSL libraries as backend implementation of the feature.

Note: TLS (Transport Layer Security) is a protocol that ensures privacy between communicating applications and their users on the Internet. When a server and client communicate, TLS ensures that no third party may eavesdrop or tamper with any message. TLS is the successor to the Secure Sockets Layer (SSL).

• PgBouncer now supports authentication via TLS client certificate.

Note: Traditionally, TLS Client Authentication has been considered the alternative to bearer tokens (passwords and cookies) for web authentication. In TLS Client Authentication, the client (browser) uses a certificate to authenticate itself during the TLS handshake. Once the TLS connection is established (and authenticated), the client and server run HTTP on top of the TLS layer.

Let’s dig into details of TLS settings of PgBouncer. There are 14 config parameters related with TLS setup (client side + server side settings).

For assigning which TLS mode to use for connections from clients, we should set client_tls_sslmode parameter. TLS connections are disabled by default. When enabled, client_tls_key_file and client_tls_cert_file must be also configured to set up key and cert PgBouncer uses to accept client connections.

We can assign a root certificate to validate client certificates by setting client_tls_ca_file parameter, default is unset.

We can specify which TLS protocol versions are allowed by setting client_tls_protocols parameter, default is all.

For more detailed client side settings, you can check client_tls_ciphers, client_tls_ecdhcurve and client_tls_dheparams parameters.

Now, let’s talk about TLS server side config parameters. First, we need to declare TLS mode to use for connections to PostgreSQL servers with server_tls_sslmode parameter. TLS connections are disabled by default. We can assign CA server with server_tls_ca_file parameter. If we would like to assign private key for PgBouncer to authenticate against PostgreSQL server, we can use server_tls_key_file parameter, we can even assign a certificate for private key that PostgreSQL server can validate with server_tls_cert_file parameter. Like we did in client side TLS connection settings, we can declare which TLS protocol versions are allowed with server_tls_protocols parameter.

• After TLS support, other significant new feature is support for “peer” authentication on Unix sockets.

Note: The peer authentication method works by obtaining the client’s operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.

• As a last major feauture of this version I would like to mention support for Host Based Access control file, like pg_hba.conf in Postgres. This allows to configure TLS for network connections and “peer” authentication for local connections.

We can configure how to authenticate users with auth_type parameter of PgBouncer. All of the config parameters are defined in the configuration file pgbouncer.ini. Let’s look into details of auth_type parameter.

auth_type parameter can be assigned one of the 6 values listed below. Let’s see the explanations and the usage of these values.

• hba : If we set auth_type parameter with the value hba, we should set auth_hba_file parameter as well for showing which pg_hba.conf file will be used as a configuration. By doing this we allow actual authentication type to be loaded from auth_hba_file. This means we can use different authentication methods for different access paths. For example, with the version 1.7 connection over Unix socket use peer authentication method, at the same time connection over TCP must use TLS. So far, HBA file format does not support all of the authentication methods of pg_hba.conf. Supported methods are: trust, reject, md5, password, peer and cert.
• cert : Client must connect over TLS connection with valid client certificate. Username is then taken from CommonName field from certificate.
• md5 : Use MD5-based password check. auth_file (the name of the file to load user names and passwords from) may contain both MD5-encrypted or plain-text passwords. This is the default authentication method.
• plain : Clear-text password is sent over wire. Deprecated.
• trust : No authentication is done. Username must still exist in auth_file.
• any : Like the trust method, but the username given is ignored. Requires that all databases are configured to log in as specific user. Additionally, the console database allows any user to log in as admin.

Other Shiny Features

There are more features released in this version. You can visit the PgBouncer changelog page or check the list below for the other improvements:

• Set query_wait_timeout to 120s by default. This parameter defines the maximum time queries are allowed to spend waiting for execution. If the query is not assigned to a server during that time, the client is disconnected. This is used to prevent unresponsive servers from grabbing up connections. It also helps when server is down or database rejects connections for any reason. If this is disabled, clients will be queued infinitely. Current default (0) causes infinite queueing, which is not useful.With the version 1.7 if client has pending query and has not been assigned to server connection, the client connection will be dropped after 120 seconds by default.
• Disable server_reset_query_always by default. Now reset query is used only in pools that are in session mode.
• Increase pkt_buf to 4096 bytes. Improves performance with TLS. The behaviour is probably load-specific, but it should be safe to do as since v1.2 the packet buffers are split from connections and used lazily from pool.
• Support pipelining count expected ReadyForQuery packets. This avoids releasing server too early. Fixes #52.
• Improved sbuf_loopcnt logic – socket is guaranteed to be reprocessed even if there are no event from socket. Required for TLS as it has it’s own buffering.
• Adapt system tests to work with modern BSD and MacOS. (Eric Radman)
• Remove crypt auth. It’s obsolete and not supported by PostgreSQL since 8.4.
• Fix plain “–with-cares” configure option – without argument it was broken.

What is PgBouncer?

PgBouncer is a utility for managing client connections to the PostgreSQL database. In a nutshell, it maintains a connection pool to the PostgreSQL server and reuses those existing connections. While this can be useful for reducing the client connection overhead, it also enables limiting the maximum number of open connections to the database server. It can also be used for traffic shaping like redirecting connections to one or more databases to different database servers. In addition to these, PgBouncer can be used for managing security on user and even on database level.

You can download PgBouncer via their downloads page and start using it now!

For more information about PgBouncer you can check my previous blog post about PgBouncer.

Happy readings!