Re: Installed. Now what?

Dne 20.11.2011 04:15, Phoenix Kiula napsal(a):
> I just did some testing.
>
> If the password is wrong, then it shows me the "authentication failed"
> message right in the terminal window, immediately.
>
> If the password is correct (plain text or md5 of that plain text --
> both have similar requests), it shows me the second error "no working
> connection" below.

Because it's failing at different times.

The first command fails because the pgbouncer verifies the password
against the auth_file, finds out it's incorrect and kicks you out.

The second command actually connects to pgbouncer (the password is
correct), attempts to open the connection to the database using the
connection string - AFAIK it's

MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8
port=5432

and fails because there's no password or incorrect password.

You've used the same username and password both for the connection
pooler and for database, so it's rather confusing.

> [host] > psql -h 127.0.0.1 MYDB -E "MYDB_MYDB" -p 6543psql: ERROR:
> password authentication failed for user "MYDB_MYDB"[coco] ~ > [coco] ~
>> pico /var/lib/pgsql/pgbouncer.txt
>
> [host] ~ > psql -h 127.0.0.1 MYDB -E "MYDB_MYDB" -p 6543
> psql: ERROR: no working server connection
>
>
> But in the second case, the error in the pgbouncer log is the same --
> authentication is failing.

No it's not. When the authentication fails when connecting to pgbouncer,
the message is

Pooler Error: Auth failed

but when the database authentication fails, the message is

Pooler Error: password authentication failed for user "..."

In the first case you have to check the auth_file, in the second you
need to check the connection string in pgbouncer.ini.

> Why this inconsistent and utterly inane behavior from pgbouncer? Why
> can't we see transparently what the error is?

It's saying you exactly what's going on. You're confused because the
connection pooling is new to you and because you've decided to use the
same credentials both for DB and pgbouncer.

> Nowhere in the docs does it clearly specify with an example how the
> auth_file format should be.

Not sure which docs are you talking about, but the "quick start" in
doc/usage.txt shows an example of the file, and doc/config.txt (and the
man pages) state that the format is the same as pg_auth/pg_pwd.

Anyway it's quite trivial - two strings, first one is username, second
one is the password. It's either plain or hashed (depending on the
auth_type).

Tomas