[Pgbouncer-general] Connection Failed with PgBouncer

[Sachin Srivastava]

My server has auth type md5 and i set auth_type to md5 in the pgbouncer.ini.
I have set the .pgpass and /etc/userlist.txt file appropriately.

When i fire this command:
/
bin/psql -U postgres postgres -p 6432
psql: ERROR: password authentication failed for user "postgres"
/
This i get in the logs
/
bash-3.2$ /opt/PgBouncer/bin/pgbouncer -v /opt/PgBouncer/share/pgbouncer.ini
2009-06-15 16:40:26.605 2906 DEBUG postgres: host=127.0.0.1/127.0.0.1
2009-06-15 16:40:26.606 2906 LOG File descriptor limit: 1024 (H:1024),
max_client_conn: 100, max fds possible: 130
2009-06-15 16:40:26.607 2906 LOG listening on *:6432
2009-06-15 16:40:26.607 2906 LOG listening on unix:/tmp/.s.PGSQL.6432
2009-06-15 16:40:53.895 2906 DEBUG C-0x98d49f0:
(nodb)/(nouser)@unix:6432 P: got connection: unix->unix
2009-06-15 16:40:53.895 2906 LOG C-0x98d49f0:
postgres/postgres at unix:6432 login successful: db=postgres user=postgres
2009-06-15 16:40:53.895 2906 DEBUG finish_client_login: no welcome
message, pause
2009-06-15 16:40:53.896 2906 DEBUG C-0x98d49f0:
postgres/postgres at unix:6432 pause_client
2009-06-15 16:40:53.896 2906 LOG S-0x98f1cf0:
postgres/postgres at 127.0.0.1:5432 new connection to server
2009-06-15 16:40:53.897 2906 DEBUG launch_new_connection: already progress
2009-06-15 16:40:53.897 2906 DEBUG S-0x98f1cf0:
postgres/postgres at 127.0.0.1:5432 S: connect ok
2009-06-15 16:40:53.897 2906 DEBUG launch_new_connection: already progress
2009-06-15 16:40:53.897 2906 DEBUG S-0x98f1cf0:
postgres/postgres at 127.0.0.1:5432 calling login_answer
2009-06-15 16:40:53.897 2906 DEBUG S-0x98f1cf0:
postgres/postgres at 127.0.0.1:5432 S: req md5-crypted psw
2009-06-15 16:40:53.897 2906 DEBUG S-0x98f1cf0:
postgres/postgres at 127.0.0.1:5432 P: send md5 password
2009-06-15 16:40:53.898 2906 DEBUG launch_new_connection: already progress
2009-06-15 16:40:53.899 2906 WARNING server login failed: FATAL password
authentication failed for user "postgres"
2009-06-15 16:40:53.899 2906 LOG C-0x98d49f0:
postgres/postgres at unix:6432 closing because: password authentication
failed for user "postgres" (age=0)
2009-06-15 16:40:53.899 2906 LOG C-0x98d49f0:
postgres/postgres at unix:6432 Pooler Error: password authentication failed
for user "postgres"
2009-06-15 16:40:53.900 2906 LOG S-0x98f1cf0:
postgres/postgres at 127.0.0.1:5432 closing because: login failed (age=0)/

Any idea what i am missing?

Thanks for your replies.

[Marko Kreen]

Post by Sachin Srivastava
My server has auth type md5 and i set auth_type to md5 in the
pgbouncer.ini.
I have set the .pgpass and /etc/userlist.txt file appropriately.
bin/psql -U postgres postgres -p 6432
psql: ERROR: password authentication failed for user "postgres"
This i get in the logs
bash-3.2$ /opt/PgBouncer/bin/pgbouncer -v
/opt/PgBouncer/share/pgbouncer.ini
2009-06-15 16:40:26.605 2906 DEBUG postgres: host=127.0.0.1/127.0.0.1
2009-06-15 16:40:26.606 2906 LOG File descriptor limit: 1024 (H:1024),
max_client_conn: 100, max fds possible: 130
2009-06-15 16:40:26.607 2906 LOG listening on *:6432
2009-06-15 16:40:26.607 2906 LOG listening on unix:/tmp/.s.PGSQL.6432
P: got connection: unix->unix
2009-06-15 16:40:53.895 2906 LOG C-0x98d49f0: postgres/postgres at unix:6432
login successful: db=postgres user=postgres
2009-06-15 16:40:53.895 2906 DEBUG finish_client_login: no welcome message,
pause
2009-06-15 16:40:53.896 2906 DEBUG C-0x98d49f0: postgres/postgres at unix:6432
pause_client
postgres/postgres at 127.0.0.1:5432 new connection to server
2009-06-15 16:40:53.897 2906 DEBUG launch_new_connection: already progress
postgres/postgres at 127.0.0.1:5432 S: connect ok
2009-06-15 16:40:53.897 2906 DEBUG launch_new_connection: already progress
postgres/postgres at 127.0.0.1:5432 calling login_answer
postgres/postgres at 127.0.0.1:5432 S: req md5-crypted psw
postgres/postgres at 127.0.0.1:5432 P: send md5 password
2009-06-15 16:40:53.898 2906 DEBUG launch_new_connection: already progress
2009-06-15 16:40:53.899 2906 WARNING server login failed: FATAL password
authentication failed for user "postgres"
2009-06-15 16:40:53.899 2906 LOG C-0x98d49f0: postgres/postgres at unix:6432
closing because: password authentication failed for user "postgres" (age=0)
2009-06-15 16:40:53.899 2906 LOG C-0x98d49f0: postgres/postgres at unix:6432
Pooler Error: password authentication failed for user "postgres"
postgres/postgres at 127.0.0.1:5432 closing because: login
failed (age=0)
Any idea what i am missing?
Thanks for your replies.

This probably means that the password in userlist.txt does not
match the password Postgres has.

Also you could have some setting in pg_hba.conf that disables
password login for 'postgres', eg. 'ident'.

[Sachin Srivastava]

Hi Marko,

Thanks for your reply,

Lets password for postgres user is postgres.

So in my userlist.txt file i have:
bash$ cat userlist.txt
"postgres" "postgres"

The file is owned by 'postgres' and has 700 privileges on it.
-rwx------ 1 postgres root 22 2009-06-15 16:40 userlist.txt

There is no setting in pg_hba which restricts password access to user
postgres. As I am able to connect from other clients (psql, libpq, jdbc
etc).

Post by Marko Kreen

Post by Sachin Srivastava
My server has auth type md5 and i set auth_type to md5 in the pgbouncer.ini.
I have set the .pgpass and /etc/userlist.txt file appropriately.
bin/psql -U postgres postgres -p 6432
psql: ERROR: password authentication failed for user "postgres"
This i get in the logs
bash-3.2$ /opt/PgBouncer/bin/pgbouncer -v
/opt/PgBouncer/share/pgbouncer.ini
2009-06-15 16:40:26.605 2906 DEBUG postgres: host=127.0.0.1/127.0.0.1
2009-06-15 16:40:26.606 2906 LOG File descriptor limit: 1024 (H:1024),
max_client_conn: 100, max fds possible: 130
2009-06-15 16:40:26.607 2906 LOG listening on *:6432
2009-06-15 16:40:26.607 2906 LOG listening on unix:/tmp/.s.PGSQL.6432
P: got connection: unix->unix
2009-06-15 16:40:53.895 2906 LOG C-0x98d49f0: postgres/postgres at unix:6432
login successful: db=postgres user=postgres
2009-06-15 16:40:53.895 2906 DEBUG finish_client_login: no welcome message,
pause
2009-06-15 16:40:53.896 2906 DEBUG C-0x98d49f0: postgres/postgres at unix:6432
pause_client
postgres/postgres at 127.0.0.1:5432 new connection to server
2009-06-15 16:40:53.897 2906 DEBUG launch_new_connection: already progress
postgres/postgres at 127.0.0.1:5432 S: connect ok
2009-06-15 16:40:53.897 2906 DEBUG launch_new_connection: already progress
postgres/postgres at 127.0.0.1:5432 calling login_answer
postgres/postgres at 127.0.0.1:5432 S: req md5-crypted psw
postgres/postgres at 127.0.0.1:5432 P: send md5 password
2009-06-15 16:40:53.898 2906 DEBUG launch_new_connection: already progress
2009-06-15 16:40:53.899 2906 WARNING server login failed: FATAL password
authentication failed for user "postgres"
2009-06-15 16:40:53.899 2906 LOG C-0x98d49f0: postgres/postgres at unix:6432
closing because: password authentication failed for user "postgres" (age=0)
2009-06-15 16:40:53.899 2906 LOG C-0x98d49f0: postgres/postgres at unix:6432
Pooler Error: password authentication failed for user "postgres"
postgres/postgres at 127.0.0.1:5432 closing because: login
failed (age=0)
Any idea what i am missing?
Thanks for your replies.

This probably means that the password in userlist.txt does not
match the password Postgres has.
Also you could have some setting in pg_hba.conf that disables
password login for 'postgres', eg. 'ident'.

[Marko Kreen]

Post by Sachin Srivastava
Lets password for postgres user is postgres.
bash$ cat userlist.txt
"postgres" "postgres"
The file is owned by 'postgres' and has 700 privileges on it.
-rwx------ 1 postgres root 22 2009-06-15 16:40 userlist.txt
There is no setting in pg_hba which restricts password access to user
postgres. As I am able to connect from other clients (psql, libpq, jdbc
etc).

Ok. Please try following things:

1. In [databases] section use locahost instead unix socket - add
host=127.0.0.1 to database line.

2. Instead userlist.txt, use PGDATA/global/pg_auth directly.
This works only if they are running as same user.

If neither change has no effect, then it seems you have uncovered a bug
in pgbouncer. Please send me details about your arch, OS, kernel,
pgbouncer, postgres, compiler versions.

[Sachin Srivastava]

Post by Marko Kreen

Post by Sachin Srivastava
Lets password for postgres user is postgres.
bash$ cat userlist.txt
"postgres" "postgres"
The file is owned by 'postgres' and has 700 privileges on it.
-rwx------ 1 postgres root 22 2009-06-15 16:40 userlist.txt
There is no setting in pg_hba which restricts password access to user
postgres. As I am able to connect from other clients (psql, libpq, jdbc
etc).

1. In [databases] section use locahost instead unix socket - add
host=127.0.0.1 to database line.

This is what i already have in the ini:

[databases]

postgres = host=127.0.0.1 port=5432 user=postgres

Post by Marko Kreen
2. Instead userlist.txt, use PGDATA/global/pg_auth directly.
This works only if they are running as same user.

Done that also, but to no avail.

Post by Marko Kreen
If neither change has no effect, then it seems you have uncovered a bug
in pgbouncer. Please send me details about your arch, OS, kernel,
pgbouncer, postgres, compiler versions.

arch:= i386
OS:= Fedora 10 32 bit
Kernel :=Linux 2.6.27.24-170.2.68.fc10.i686
postgres := 8.3.7 (also on 8.4 rc1)
pgbouncer := 1.3
gcc = 4.3.2

[Marko Kreen]

Post by Marko Kreen
[databases]
postgres = host=127.0.0.1 port=5432 user=postgres

Ok, here's the problem - if you are overriding the username you
are supposed to give password too, it does not use userlist.txt one
in that case.

[Sachin Srivastava]

Hi Marko,

Post by Marko Kreen

Post by Marko Kreen
[databases]
postgres = host=127.0.0.1 port=5432 user=postgres

Ok, here's the problem - if you are overriding the username you
are supposed to give password too, it does not use userlist.txt one
in that case.

Thanks a lot for your reply, its working now.