[pgpool-general: 6574] Pgpool-II - PosgreSQL 9.5 MD5 Auth Issues

[.biscuitNinja]

We are attempting to get Pgpool-II (4.0.4-1.pgdg90+1) working with 
PostgreSQL 9.5 (9.5.17-1.pgdg90+1). We are using md5 authentication 
between PgPool-II and PostgreSQL.

We cannot get Pgpool-II to successfully authenticate against PostgreSQL.

We have dry-walked the code here:
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob;f=src/auth/pool_passwd.c;h=c84136bb44ffac81352504c3dd1ff8279cd7f6c8;hb=HEAD

And reviewed the changes applied to support SCRAM.
It appears to us that there is no logic to handle PASSWORD_TYPE_MD5 and 
a password value of 'NULL' is getting returned, resulting in the 
authentication failures.

We do have PostgreSQL 10 on our roadmap, but not for a while yet. 
Implementing pgpool itself is far more pressing in terms of the issues 
we have to resolve.
Is there any suggested way forward in terms of resolving this?

Included below are relevant excerpts from configuration files and log 
files. This is from an installation staged in our lab environment so 
there's no issue caused by disclosure of the md5 hashes in these excerpts.

Thanks
.biscuitNinja




Excerpts from pgpool.conf:

pool_passwd = 'pool_passwd'
sr_check_user = 'pgpool'
sr_check_password = ''
health_check_user = 'pgpool'
health_check_password = ''


pool_passwd:

pgpool:md50c5eb007937f063ddb8d04b7d8b17b1a


Excerpt from postgreSQL HBA:

host    all             pgpool          samenet md5


pg_shadow passwd:

postgres=# select passwd from pg_shadow
postgres-# where usename = 'pgpool'
postgres-# ;
                passwd
-------------------------------------
  md50c5eb007937f063ddb8d04b7d8b17b1a
(1 row)


With debug5 logging, we have the following related messages for PgPool-II:

May 28 12:34:17 dpbdc-pool-1 pgpool[8742]: [175-3] 2019-05-28 12:34:17: 
pid 8742: LOCATION:  wd_lifecheck.c:800
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [610-1] 2019-05-28 12:34:17: 
pid 8736: DEBUG:  could not get the password for user:pgpool
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [610-2] 2019-05-28 12:34:17: 
pid 8736: DETAIL:  username "pgpool" has invalid password type: 2
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [610-3] 2019-05-28 12:34:17: 
pid 8736: LOCATION:  pool_passwd.c:525
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [611-1] 2019-05-28 12:34:17: 
pid 8736: DEBUG:  pool_flush_it: flush size: 39
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [611-2] 2019-05-28 12:34:17: 
pid 8736: LOCATION:  pool_stream.c:633
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [612-1] 2019-05-28 12:34:17: 
pid 8736: DEBUG:  pool_read: read 13 bytes from backend 0
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [612-2] 2019-05-28 12:34:17: 
pid 8736: LOCATION:  pool_stream.c:194
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [613-1] 2019-05-28 12:34:17: 
pid 8736: DEBUG:  authenticate kind = 5
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [613-2] 2019-05-28 12:34:17: 
pid 8736: LOCATION:  pool_auth.c:141
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [614-1] 2019-05-28 12:34:17: 
pid 8736: DEBUG:  pool_write: to backend: 0 kind:p
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [614-2] 2019-05-28 12:34:17: 
pid 8736: LOCATION:  pool_stream.c:450
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [615-1] 2019-05-28 12:34:17: 
pid 8736: DEBUG:  pool_flush_it: flush size: 41
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [615-2] 2019-05-28 12:34:17: 
pid 8736: LOCATION:  pool_stream.c:633
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [616-1] 2019-05-28 12:34:17: 
pid 8736: DEBUG:  pool_read: read 96 bytes from backend 0
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [616-2] 2019-05-28 12:34:17: 
pid 8736: LOCATION:  pool_stream.c:194
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [617-1] 2019-05-28 12:34:17: 
pid 8736: ERROR:  authentication failed
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [617-2] 2019-05-28 12:34:17: 
pid 8736: DETAIL:  password authentication failed for user "pgpool"
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [617-3] 2019-05-28 12:34:17: 
pid 8736: LOCATION:  pool_auth.c:1930
May 28 12:34:17 dpbdc-pool-1 pgpool[8736]: [618-1] 2019-05-28 12:34:17: 
pid 8736: LOG:  find_primary_node: make_persistent_db_connection_noerror 
failed on node 0


Corresponding auth failures in postgresql-9.5-main.log:

2019-05-28 12:35:39.843 BST [16780-1] pgpool:[unknown]@postgres 
192.168.13.31 FATAL:  password authentication failed for user "pgpool"
2019-05-28 12:35:39.843 BST [16780-2] pgpool:[unknown]@postgres 
192.168.13.31 DETAIL:  Connection matched pg_hba.conf line 103: "host   
all             pgpool          samenet md5"
2019-05-28 12:35:40.849 BST [16781-1] pgpool:[unknown]@postgres 
192.168.13.31 FATAL:  password authentication failed for user "pgpool"
2019-05-28 12:35:40.849 BST [16781-2] pgpool:[unknown]@postgres 
192.168.13.31 DETAIL:  Connection matched pg_hba.conf line 103: "host   
all             pgpool          samenet md5"