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

Tatsuo Ishii ishii at sraoss.co.jp
Thu May 30 01:59:18 JST 2019


> 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.

I am not sure what you mean "there is no logic to handle
PASSWORD_TYPE_MD5" here, but I would like to propose two points to fix
the problem.

1) you need to trun on enable_pool_hba.

2) you need to store the password for "pgpool" user as either "plain
text" or "AES256 encrypted" form (see
http://www.pgpool.net/docs/latest/en/html/auth-aes-encrypted-password.html
for more detail) into pool_password. "md5" form does not work in this
case. This is because you set sr_check_password to '' and expect
Pgpool-II to extract password from pool_password. If the password is
in md5, Pgpool-II cannot "unhash" the md5 passord to extract the raw
password string.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> 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"
> 
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general


More information about the pgpool-general mailing list