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

CREATE OR REPLACE FUNCTION public.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 INTO uname, phash;
    RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

REVOKE ALL ON FUNCTION public.user_lookup(text) FROM public, pgbouncer;

GRANT EXECUTE ON FUNCTION public.user_lookup(text) TO pgbouncer;

Configure PgBouncer to dynamically do user lookup

cd /etc/pgbouncer/
Type sudo vim userlist.txt
Add a line like so
```
"pgbouncer" "test"
```
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
```
;bardb = host=localhost dbname=bazdb
```
like so:
```
postgres = host=localhost dbname=postgres auth_user=pgbouncer
```
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
```
listen_addr = 127.0.0.1
```
to
```
listen_addr = *
```
Set authentication method and query that pgbouncer user will execute against PostgreSQL
```
auth_type = trust
auth_query = SELECT uname, phash from user_lookup($1)
```
Ensure that postgres user is the one that can administer and work with PgBouncer statistics
```
admin_users = postgres
stats_users = postgres
```
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.