Added pgbouncer authentication via auth_user and auth_query

[chuegel]

Hi,

following this discussion, the PR adds the possibility to use only one user to query the database and perform the authentication via pgbouncer auth_user and auth_query. This will create dynamic pools for each dbname+user combination.

I've tested it with a 3 node Ubuntu 22.04 LTS cluster but I guess it will need additional distributions to run test against.

# connect to master (R/W)
psql -h 192.168.100.101 -p 6432 -U postgres
Password for user postgres:
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.100.101" at port "6432".

## pgbouncer log 
2023-07-06 16:52:51.327 UTC [13299] LOG C-0x5627b3c13e50: (nodb)/(nouser)@192.168.100.102:25638 registered new auto-database: db=postgres

# connect to replica (R/O)

psql -h 192.168.100.102 -p 6432 -U postgres
Password for user postgres:
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
Type "help" for help.

postgres=#  \conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.100.102" at port "6432".

## pgbouncer log
2023-07-06 16:56:33.786 UTC [13757] LOG C-0x55c17144ce50: (nodb)/(nouser)@192.168.100.103:63856 registered new auto-database: db=postgres

# create DB and user

 psql -h 192.168.100.101 -p 6432 -U postgres
Password for user postgres:
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
Type "help" for help.

postgres=# create database testdb;
CREATE DATABASE
postgres=# create user testuser with encrypted password 'strong-password-here';
CREATE ROLE
postgres=# grant all privileges on database testdb to testuser;
GRANT

# connect with the user

psql -h 192.168.100.101 -p 6432 -U testuser -d testdb
Password for user testuser:
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
Type "help" for help.

testdb=> \conninfo
You are connected to database "testdb" as user "testuser" on host "192.168.100.101" at port "6432".

# pgbouncer log

2023-07-06 17:01:39.643 UTC [13299] LOG C-0x5627b3c13e50: (nodb)/(nouser)@192.168.100.103:41824 registered new auto-database: db=testdb

CAVEAT: this only works if the password for the auth_user is set in plaintext in userlist.txt because there is no actual client doing SCRAM authentication against the database.

[vitabaks]

Related Issue https://github.com/vitabaks/postgresql_cluster/issues/334

[vitabaks]

@chuegel Thank you for your PR

I will look carefully at the code tomorrow.

In the meantime, I would like to invite you to read the article https://www.enterprisedb.com/postgres-tutorials/pgbouncer-authquery-and-authuser-pro-tips

perhaps after reading the article you will make a few more commits ;)

[chuegel]

@vitabaks thanks, I will look into it. The PR can be improved for sure.

[vitabaks]

@chuegel Let me know if you need my help in finalizing this PR.

[vitabaks]

Thank you @chuegel good job!