1

I've install pgbouncer-1.7.2 on the same Linux server as PostgreSQL-9.6 database. When I try to connect to my local database using pgbouncer, database ask to enter password. For example:

psql -p 6432 -U postgres -d mydb10 -h localhost 
**Password for user postgres:**

Login is OK after I enter the password. The same direct request run without password:

psql -p 5432 -U postgres -d mydb10 -h localhost 
psql (9.6.5) 
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,    bits: 256, compression: off) 
Type "help" for help. 

mydb10=# \q

Pgbouncer settings (not all of them):

[databases]
* = host=127.0.0.1 port=5432 auth_user=postgres pool_size=20
[pgbouncer]

listen_addr = *
listen_port = 6432

auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
;; auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

PostgreSQL settings (not all of them):

port = 5432

All METHOD in pg_hba.conf are set to 'trust'. If I set auth_type = trust, connection via pgbouncer do not require password. What is interesting is that the same configuration when pgbouncer installed on separate server, has no such problem.

Pleas, let me know if you have any idea how to fix this.

Anatoli Lyssak
  • 13
  • 3

1 Answers1

1

Seems that auth_type = trust is a way to go if you want connection without password check (as you have configured trust in pg_hba.conf)

Regarding

What is interesting is that the same configuration when pgbouncer installed on separate server, has no such problem.

Maybe /etc/pgbouncer/userlist.txt on that different server differs?

PS also note that from version 1.7 onward, pgbouncer supports auth_type = hba that you might find suitable.

PPS your problem with access through different pgbouncer instances might be because of different .pgpass files, which is used by psql

Pavel T
  • 403
  • 4
  • 12
  • These are some investigation results. pgbouncer require manual password enter if auth_type=md5 auth_type=hba and pg_hba.conf METHOD is md5 pgbouncer does not require password enter if auth_type=trust auth_type=hba and pg_hba.conf METHOD is trust I thought that auth_file role is to supply the required password to avoid manual password enter. It looks like I was wrong. Now I have two questions: How pgbounce use password from auth_file? What is the most secure way to automate password entering? – Anatoli Lyssak Aug 19 '18 at 01:41
  • And the main question: Why pgbounce with auth_type=md5 does not require the password, when pgbouncer run on the server separate from database server. – Anatoli Lyssak Aug 19 '18 at 01:49
  • "auth_file role is to supply the required password to avoid manual password enter" is wrong. The `~/.pgpass` file is for that — https://www.postgresql.org/docs/10/static/libpq-pgpass.html and it might be the sole reason you have different behaviour on different pgbouncers – Pavel T Aug 20 '18 at 08:59
  • As per documentation, pgbouncer do not use .pgpass – Anatoli Lyssak Aug 20 '18 at 15:07
  • Not the pgbouncer. It's pgsql that you're using to connect to PgB is reading this. Did you find differences in .pgpass on these machines? Or did you find any other differences in configurations? – Pavel T Aug 21 '18 at 14:39
  • Hi Pavel. You was right. It is .pgpass problem. Also there were some problems with AWS security group. After fixing these problems I was able to use pgbouncer without entering password. Thank you very much. – Anatoli Lyssak Aug 22 '18 at 16:45
  • Hi @AnatoliLyssak, Please accept my answer above, so I'll get some points! =) – Pavel T Aug 24 '18 at 17:21