12

pgbouncer version 1.7.2

psql (9.5.6)

I try use auth_hba_file (/var/lib/pgsql/9.5/data/pg_hba.conf) in PgBouncer.

Config pgbouncer.ini

postgres = host=localhost port=5432 dbname=postgres user=postgres
test = host=localhost port=5432 dbname=test user=test
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
auth_type = hba
auth_hba_file = /var/lib/pgsql/9.5/data/pg_hba.conf
admin_users = postgres
stats_users = stats, postgres
pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 100
default_pool_size = 20

cat pg_hba.conf | grep -v "#" | grep -v "^$"

local   all             all                              trust
host    all             all             127.0.0.1/32     trust
host    all             all             ::1/128          trust
host    test            test            10.255.4.0/24    md5

psql -h 10.233.4.16 -p 5432 -U test

Password for user test:
psql (9.5.6)
Type "help" for help.

test=> \q

psql -h 10.233.4.16 -p 6432 -U test

psql: ERROR:  No such user: test

tail -fn10 /var/log/pgbouncer/pgbouncer.log

LOG C-0x78f7e0: (nodb)/(nouser)@10.255.4.245:8963 closing because: No such user: test (age=0)
WARNING C-0x78f7e0: (nodb)/(nouser)@10.255.4.245:8963 Pooler Error: No such user: test
LOG C-0x78f7e0: (nodb)/(nouser)@10.255.4.245:8963 login failed: db=test user=test

But i cannot connect to postgresql (using PgBouncer) using pg_hba.conf

Can somebody help? May you have example for use auth_hba_file. Thanks

I changed config:

[root@dev-metrics2 pgbouncer]# cat pgbouncer.ini | grep -v ";" | grep -v "^$" | grep -v "#"

[databases]
postgres = host=localhost port=5432 dbname=postgres user=postgres
test = host=localhost port=5432 dbname=test auth_user=test
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
admin_users = postgres
stats_users = stats, postgres
pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 100
default_pool_size = 20

Drop and Create user and DB

[local]:5432 postgres@postgres # DROP DATABASE test;
DROP DATABASE
[local]:5432 postgres@postgres # DROP USER test ;
DROP ROLE
[local]:5432 postgres@postgres # CREATE USER test with password 'test';
CREATE ROLE
[local]:5432 postgres@postgres # CREATE DATABASE test with owner test;
CREATE DATABASE

PGPASSWORD=test psql -h 10.233.4.16 -p 6432 -U test

Password for user test:
psql: ERROR:  Auth failed

tail -fn1 /var/log/pgbouncer/pgbouncer.log

LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us
LOG C-0x17b57a0: test/test@10.255.4.245:3069 login attempt: db=test user=test tls=no
LOG C-0x17b57a0: test/test@10.255.4.245:3069 closing because: client unexpected eof (age=0)
LOG C-0x17b57a0: test/test@10.255.4.245:3070 login attempt: db=test user=test tls=no
LOG C-0x17b57a0: test/test@10.255.4.245:3070 closing because: Auth failed (age=0)
WARNING C-0x17b57a0: test/test@10.255.4.245:3070 Pooler Error: Auth failed

Work config:

cat pgbouncer.ini | grep -v ";" | grep -v "^$" | grep -v "#"

[databases]
*= port=5432 auth_user=postgres
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
admin_users = postgres
stats_users = stats, postgres
pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 100
default_pool_size = 20
Anton Patsev
  • 605
  • 2
  • 13
  • 27
  • using database hba for pgbouncer is perfectly fine. show us your error (from `/var/log/pgbouncer/pgbouncer.log`) – Vao Tsun Apr 12 '17 at 14:01
  • 2
    m! much better. please check if user test is in file, specified at `auth_file`, and if not (must be the case) add it, smth like `"test" "md564b76e462e88c4fa6898960d067845b8"` - you can find the hash from `select passwd from pg_shadow where usename = 'test';` – Vao Tsun Apr 13 '17 at 07:03
  • I update post. I used auth_query, but cannot connect to PostgreSQL :( – Anton Patsev Apr 13 '17 at 08:38
  • restarted pgbouncer? show the log - when you can't connect you get the message and log lines generated - they are essential to understand what's wrong – Vao Tsun Apr 13 '17 at 08:40
  • I changed config and he is work. :) – Anton Patsev Apr 13 '17 at 09:08
  • what was the issue? – Vao Tsun Apr 13 '17 at 09:15
  • issue - i dont know how setting auth_hba_file using mkauth.py. But i used auth_query and i think need close this question :) – Anton Patsev Apr 13 '17 at 11:32

1 Answers1

0

Try put space

*= port=5432 auth_user=postgres  # old string
* = port=5432 auth_user=postgres # new string

work for me

Pirr
  • 113
  • 1
  • 1
  • 6