[pgpool-hackers: 3334] Re: [pgpool-general: 6575] Re: Pgpool-II - PosgreSQL 9.5 MD5 Auth Issues
Tatsuo Ishii
ishii at sraoss.co.jp
Thu May 30 11:29:11 JST 2019
Hi Usama,
After answering this:
> 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.
I start to thik that MD5 format password stored in pool_password
cannot be used for sr_check_password is empty case is not essetial
restriction but just a coding restriction.
1. worker process needs to connect to backend.
2. it finds an entry for sr_check_user in pool_passwd.
3. the password is actually md5(password+username). let me denote it as "S".
4. worker process sends a start up packet to backend.
5. backend reply back with salt "s0".
6. worker process calculate md5(S+s0) and sent it to backend.
7. md5 auth succeeds.
Am I missing something?
>> 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
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
More information about the pgpool-hackers
mailing list