Configure PgBouncer to authenticate against PostgreSQL and rely less on userlist.txt

PgBouncer is a lightweight connection pooler. When a user connects to PgBouncer, the user’s username and password is checked against userlist.txt file. If you haven’t installed PostgreSQL, see Install PostgreSQL 9.6 on Ubuntu 16.04 and reset postgres password

Disadvantage of userlist.txt

The disadvantage is whenever new user is added to PostgreSQL, the user’s username and password has to be added to userlist.txt. Password is the actual plain-text password or plain-text md5 hash.

Authenticate to PostgreSQL instead of looking for credentials in userlist.txt

There is a way to configure PgBouncer so that username and password are authenticated against PostgreSQL without completely relying on userlist.txt. There is still a limited need for userlist.txt. The general idea is to:

  • Create a user called pgbouncer in PostgreSQL
  • Enter pgbouncer user and password in userlist.txt
  • PgBouncer can use this credential to ask PostgreSQL whether the user/password connecting to PgBouncer is actually present in PostgreSQL

Here’s how a user matt will authenticate

  • User matt logs into PgBouncer with his username
  • PgBouncer goes to userlist.txt and collects pgbouncer credentials and uses them to ask PostgreSQL whether matt’s credentials are OK
    • If OK, PgBouncer connects matt to PostgreSQL
    • If not, PgBouncer does NOT connect matt to PostgreSQL

Install PgBouncer

  • Install, if not already installed, PgBouncer on the same server as PostgreSQL by typing sudo apt-get install pgbouncer

Create some users for testing

  • Go to PostgreSQL and create some users
    • Type psql -U postgresl
    • CREATE USER pgbouncer WITH LOGIN;
    • ALTER USER pgbouncer WITH PASSWORD ‘test’;
    • CREATE USER matt WITH LOGIN;
    • ALTER USER matt WITH PASSWORD ‘test’;
    • CREATE USER hannah WITH LOGIN;
    • ALTER USER hannah WITH PASSWORD ‘test’;
  • Type \q to exit PostgreSQL

Create a function in PostgreSQL that will perform user lookup

Log on to PostgreSQL using psql -U postgres and type

Configure PgBouncer to dynamically do user lookup

  • cd /etc/pgbouncer/
  • Type sudo vim userlist.txt
  • Add a line like so

    This credential will be used to call user_lookup function and authenticate user attempting to log on to PgBouncer.
  • Type sudo vim pgbouncer.ini
  • When PgBouncer is asked to connect to PostgreSQL, only connections to postgres database should be entertained. To do so, add a new line under

    like so:

    Notice auth_user=pgbouncer. This addition tells PgBouncer to look up userlist.txt for pgbouncer’s credentials and then authenticate incoming user’s password against PostgreSQL.
  • Allow all systems to connect to PgBouncer. To do so, change the following line

    to
  • Set authentication method and query that pgbouncer user will execute against PostgreSQL
  • Ensure that postgres user is the one that can administer and work with PgBouncer statistics
  • Restart PgBouncer by typing sudo service pgbouncer

Test PgBouncer

  • From a remote system, type psql -h <IP or hostname of PostgreSQL server> -U matt -d postgres -p 6432
  • Type in the password for matt and you should have access through PgBouncer (listening on port 6432)
  • Type psql -h <IP or hostname of PostgreSQL server> -U hannah -d postgres -p 6432
  • Enter the password for hannah and this user should be able to access PostgreSQL through PgBouncer also
  • Typing incorrect password will result in psql: ERROR: Auth failed

Note that matt and hannah are able to connect to PostgreSQL through PgBouncer without the need for userlist.txt to hold their credentials.