CODEWITHSUNDEEP
databasepostgresqlportconnection-poolingpgbouncer
L Marfell
L Marfell

Reputation: 359

Server cannot connect to pgbouncer (PostgreSQL)

I am trying to set up connectional pooling using pgbouncer 1.7.2 for PostgreSQL 9.6 on Windows 10.

Trying to connect to pgbouncer in the cmd

    psql -p 6432 -h 127.0.0.1 postgres pgbouncer

gives the error:

    psql: server closed the connection unexpectedly Server terminated abnormally before or while processing

The port 6432 is listening and looking in the pg_log there are no errors.

Here is my pgbouncer.ini config file:

    [databases]

    mydb = host=127.0.0.1 port=5432 dbname=mydb

    [pgbouncer]

    logfile = pgbouncer.log
    pidfile = pgbouncer.pid

    listen_addr = *
    listen_port = 6432

    auth_type = md5
    auth_file = C:/Program Files/pgbouncer-1.7.2-win32/etc/pgbouncer/userlist.txt

    admin_users = postgres, lisam

    stats_users = stats, root

Where users.txt contains "postgres" "some_password"

In the pgbouncer guide it says:

Since PostgreSQL 9.0, the text files are not used anymore. Thus the auth file needs to be generated. See ./etc/mkauth.py for sample script to generate auth file from pg_shadow table. PostgreSQL MD5-hidden password format: "md5" + md5(password + username)

Does anyone know how to manually generate the auth file?

Any help would be much appreciated.

Thanks,

Lisa

Upvotes: 2

Views: 2483

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51406

you can use postgres md5 function, eg:

postgres=# select usename,concat('md5',md5('postregs user password'||usename)) from pg_user where usename='postgres';
 usename  |               concat
----------+-------------------------------------
 postgres | md584f1938f5f80a2f6ba95cea7875ad602
(1 row)

that would mean you shoud add 

"postgres" "md584f1938f5f80a2f6ba95cea7875ad602"

to you C:/Program Files/pgbouncer-1.7.2-win32/etc/pgbouncer/userlist.txt

Upvotes: 4

Related Questions