PgBouncer Authentication

What Is pgbouncer ?

pgbouncer is a lightweight PostgreSQL connection pooler. Any target application can be connected to pgbouncer as if it were a PostgreSQL server, and pgbouncer will create a connection to the actual server, or it will reuse one of its existing connections.

The aim of pgbouncer is to lower the performance impact of opening new connections to PostgreSQL.

To know more about pgbouncer visit  https://pgbouncer.github.io/usage.html

How pgbouncer work?

As shown in below diagram , this is default authentication method most of us are using.When user try to connect database with specified user & password , PgBouncer will perform a lookup of the provided username/password against userlist.txt to verify that the credentials are correct, and then the username/password are sent to Postgres for an actual database login.

What's wrong with using userlist.txt ?

A general process is 

• You create user/role in database.
• Get md5 password from pg_shadow.
• Add that user and md5 password to userlist.txt file.

But what if - 

• Hundreds of users need to create and add there entry into userlist.txt
• On every password reset you need to change it into userlist.txt
• Hundreds of servers where you have to add this users and entry into userlist.txt

With the help of auth_user and auth_query, you can centralise the password management , with less efforts.

How auth_user & auth_query work ?

When auth_user is provided, PgBouncer will still read in credentials from the connection string, but instead of comparing against userlist.txt, it logs in to Postgres with the specified auth_user and runs auth_query to pull the corresponding md5 password hash for the desired user. The validation is performed at this point, and if correct, the specified user is allowed to log in.

Use of auth_user & auth_query together 

1 ] Create user in database to use as auth_user

NOTE : The auth_user should be an unprivileged user, with no GRANTs to read/write any tables. 

auth_user is used strictly for assisting with PgBouncer authentication.

CREATE USER pgbouncer WITH PASSWORD 'pgbouncer';
CREATE USER dbuser WITH PASSWORD 'dbuser';

2 ] Create function in database to get user/md5 password.

CREATE OR REPLACE FUNCTION user_authentication(uname TEXT) RETURNS TABLE (usename name, passwd text) as
$$
SELECT usename, passwd FROM pg_shadow WHERE usename=$1;
$$
LANGUAGE sql SECURITY DEFINER;

REVOKE EXECUTE ON FUNCTION public.user_authentication(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.user_authentication(text) TO pgbouncer;

NOTE : SECURITY DEFINER clause enables the non-privileged auth_user to view the contents of pg_shadow, otherwise its limited to only admin users.

3 ] Configure pgbouncer.ini

Configure your [databases] section with an alias, like:

[databases]
db_stg= host=127.0.0.1  port=5433  dbname=db_stg auth_user=pgbouncer
db_dev= host=127.0.0.1  port=5434  dbname=db_dev auth_user=pgbouncer
db_prd= host=127.0.0.1  port=5435  dbname=db_prd auth_user=pgbouncer

Then, configure auth_query in the [pgbouncer] section with:

auth_query = SELECT usename, passwd FROM user_authentication($1)

Then, configure auth_type &  auth_file in the [Authentication settings] section with:

auth_type = md5

auth_file = /etc/pgbouncer/userlist.txt

4 ] Configure pg_hba.conf

host    all     pgbouncer    127.0.0.1/32    md5

NOTE : As we are using authentication method as md5 for user pgbouncer , we need to add this user into /etc/pgbouncer/userlist.txt.

"pgbouncer"  "md5be5544d3807b54dd0637f2439ecb03b9"

Reload pgbouncer & pg_hba.conf file.

Lets Try now !

postgres@testsys082:~$ psql -h 127.0.0.1 -p 1111 -U dbuser -d db_stg -W
Password: 
psql (9.3.25)
Type "help" for help.

db_stg=> show port;
 port 
------
 5433
(1 row)

******************************************************** THANK YOU***********************************************************

        

Do You Like This Blog ?