Client side connection pooling

DISCLAIMER: For the time being, DBOD is not able to support any kind of connection pooling for PostgreSQL.

PgBpouncer is a well known, flexible, reputable connection pooling software for PostgreSQL with a small foot print, which has been around for a long time.

Instance owners can setup PgBouncer on their side ("near" the client/application) to establish a connection pooling layer when accessing the database with a significant number of connections or when the connections are often and suddenly going up and down by a significant number.

The following Helm Chart and Pgbouncer image have been used for setting up pgbouncer in containers by the gitlab team for the gitlab service:

These pointers are shared, completely and utterly without any kind of support from anybody, merely because they have been built in a generic way, hence potentially any user at CERN could make use of them.

Authentication

There are a few different ways to authenticate users in PgBouncer.

One way is with an authentication query returning the password hash. Another using an authentication file containing the list of known roles and their password hash. When both are defined the auth_query is used only for roles not found in the auth_file.

NB: The authentication file can also store passwords in clear text but this should be strongly discouraged and avoided.

In both cases superuser access to pg_shadow table would be required. To serve this data but comply with the least privilege approach, DBOD can:

setup a code owner i.e. define a role with select access to pg_shadow, without login permission, owner of a schema with a wrapper function with definer rights, which is returning the password hash of the role passed as argument in a controlled manner
setup a login role to retrieve the password hash i.e. define a role with login permission, usage on the code owner schema and execute on the wrapper function and no other privileges

With this setup any instance owner can configure the auth_query to invoke the wrapper function instead of accessing pg_shadow directly and use the function in script like the mkauth.py example to retrieve and store the password hash for a list of users in the auth_file:

...
    auth_type: scram-sha-256
    auth_file: /etc/pgbouncer/userlist.txt
    auth_query: select uname, phash from pgbouncer_auth.user_lookup($1)
...

Above: Example of PgBouncer auth configuration

User_lookup wrapper function

The function is filtering out privileged and special users.

Removing login from pgbouncer_auth (code owner) is providing a stronger extra protection.

CREATE OR REPLACE FUNCTION pgbouncer_auth.user_lookup(in i_username text, out uname text, out phash text)
RETURNS record AS $$
BEGIN
    SELECT usename, passwd FROM pg_catalog.pg_shadow
    WHERE usename = i_username
    AND NOT (usesuper OR userepl OR usebypassrls)
    AND usename NOT LIKE 'dod_%'
    INTO uname, phash;
    RETURN;
END;
$$ LANGUAGE plpgsql
   SECURITY DEFINER
   SET search_path = pg_catalog, pg_temp;
-- without the SET clause or with SET pg_catalog 
-- the function could be subverted by creating a temporary table named pg_shadow
`

Request to setup a secure authentication

If you are using client side connection pooling using PgBouncer and would like to secure the authentication using the configuration described above, please open a ticket with the DBOD team.

The configuration will be setup in each database accessible through PgBouncer and should be done also on template1 (or any ad hoc template db) in order to be automatically deployed on newly created databases.

Other settings

Make sure pgbouncer_auth_login is allowed to connect to the instance by asking the owner to configure accordingly pg_hba.conf in the user modifiable section, as in the following example:

...
host    all    pgbouncer_auth_login all    md5
...

NB: if applicable, md5 should be replaced by scram-sha-256