View Issue Details

IDProjectCategoryView StatusLast Update
0000534Pgpool-IIBugpublic2020-02-18 15:10
ReportereldadAssigned Tot-ishii 
PriorityhighSeveritymajorReproducibilityalways
Status resolvedResolutionopen 
PlatformCentOS release 6.10OSCentOS LinuxOS Version6.10
Product Version4.0.5 
Target Version4.0.7Fixed in Version4.0.7 
Summary0000534: unable to connect when enable_pool_hba is off and pg_hba on password
DescriptionHi,

I'm having a strange behavior when trying to connect to the DB with pgpool.
in pgpool pool hba is disabled and pool_passwd =''
in postgres pg_hba I have entries for the master and standby with password METHOD.
.pgpass is configure for both postgres port and pgpool port:
*:5432:*:postgres:*************
*:5400:*:postgres:*************


connecting to postgres directly works well, but with pgpool it gives error

-bash-4.1$ psql -U postgres -p 5432 -h smxashpocpdb01
psql (10.9)
Type "help" for help.

postgres=# \q
-bash-4.1$ psql -U postgres -p 5400 -h smxashpocpdb01
psql: FATAL: clear text password authentication failed
DETAIL: unable to get the password for user: "postgres"

see related logs info in additional information.

I have similar configuration with older postgres (9.6) and pgpool (pg96-3.6.2) that works well.

Please see how can I use it in the new versions - pgpool 4.0.5 and postgres 10.9

Thanks,
Eldad
Steps To Reproducein pgpool :
enable_pool_hba = off
pool_passwd =''

in pg_hba.conf:
# "local" is for Unix domain socket connections only
local all all password
# IPv4 local connections:
host all all 127.0.0.1/32 password
host replication all 192.168.35.3/32 trust
host all all 192.168.35.3/32 password
host replication all 192.168.35.4/32 trust
host all all 192.168.35.4/32 password
Additional Informationpgpool log:
2019-08-06 13:57:45: pid 16823: DEBUG: reading message length
2019-08-06 13:57:45: pid 16823: DETAIL: slot: 1 length: 8
2019-08-06 13:57:45: pid 16823: LOCATION: pool_auth.c:2007
2019-08-06 13:57:45: pid 16823: DEBUG: authentication backend
2019-08-06 13:57:45: pid 16823: DETAIL: auth kind:3
2019-08-06 13:57:45: pid 16823: LOCATION: pool_auth.c:408
2019-08-06 13:57:45: pid 16823: DEBUG: authentication backend
2019-08-06 13:57:45: pid 16823: DETAIL: trying clear text password authentication
2019-08-06 13:57:45: pid 16823: LOCATION: pool_auth.c:437
2019-08-06 13:57:45: pid 16823: WARNING: unable to get password, password file descriptor is NULL
2019-08-06 13:57:45: pid 16823: LOCATION: pool_passwd.c:347
2019-08-06 13:57:45: pid 16823: FATAL: clear text password authentication failed
2019-08-06 13:57:45: pid 16823: DETAIL: unable to get the password for user: "postgres"
2019-08-06 13:57:45: pid 16823: LOCATION: pool_auth.c:1018
2019-08-06 13:57:45: pid 16823: DEBUG: pool_write: to frontend: kind:E po:0
2019-08-06 13:57:45: pid 16823: LOCATION: pool_stream.c:461

postgres log:
postgres 2019-08-06 13:57:45 UTC [16842][postgres][192.168.35.4]: [1-1] DEBUG: postgres child[16842]: starting with (
postgres 2019-08-06 13:57:45 UTC [16842][postgres][192.168.35.4]: [2-1] DEBUG: postgres
postgres 2019-08-06 13:57:45 UTC [16842][postgres][192.168.35.4]: [3-1] DEBUG: )
postgres 2019-08-06 13:57:45 UTC [16842][postgres][192.168.35.4]: [4-1] DEBUG: InitPostgres
postgres 2019-08-06 13:57:45 UTC [16842][postgres][192.168.35.4]: [5-1] DEBUG: my backend ID is 3
postgres 2019-08-06 13:57:45 UTC [16842][postgres][192.168.35.4]: [6-1] DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0
postgres 2019-08-06 13:57:45 UTC [16842][postgres][192.168.35.4]: [7-1] FATAL: expected password response, got message type 88
postgres 2019-08-06 13:57:45 UTC [16842][postgres][192.168.35.4]: [8-1] DEBUG: shmem_exit(1): 1 before_shmem_exit callbacks to make
postgres 2019-08-06 13:57:45 UTC [16842][postgres][192.168.35.4]: [9-1] DEBUG: shmem_exit(1): 6 on_shmem_exit callbacks to make
postgres 2019-08-06 13:57:45 UTC [16842][postgres][192.168.35.4]: [10-1] DEBUG: proc_exit(1): 3 callbacks to make
postgres 2019-08-06 13:57:45 UTC [16842][postgres][192.168.35.4]: [11-1] DEBUG: exit(1)
postgres 2019-08-06 13:57:45 UTC [16842][postgres][192.168.35.4]: [12-1] DEBUG: shmem_exit(-1): 0 before_shmem_exit callbacks to make
postgres 2019-08-06 13:57:45 UTC [16842][postgres][192.168.35.4]: [13-1] DEBUG: shmem_exit(-1): 0 on_shmem_exit callbacks to make
postgres 2019-08-06 13:57:45 UTC [16842][postgres][192.168.35.4]: [14-1] DEBUG: proc_exit(-1): 0 callbacks to make
Tagspgpool-II

Activities

t-ishii

2019-08-07 15:57

developer   ~0002755

I think you need to set:

allow_clear_text_frontend_auth = on

in your pgpool.conf.

eldad

2019-08-08 20:15

reporter   ~0002756

Hi,

I already set allow_clear_text_frontend_auth = on before reporting the problem, it didn't help.
Is there any additional information you need ?

Regards,
Eldad

t-ishii

2019-08-09 10:34

developer   ~0002759

Last edited: 2019-08-09 11:24

View 2 revisions

I suspect that you actually store passwords in PostgreSQL as md5 hash (as long as you don't change password_encryption parameter of PostgreSQL) despite that you set auth method as "password", which makes Pgpool-II confused since it thinks that PostgreSQL sends password in clear text form.

You can workaround this by setting "md5" instead of "password" in pg_hba.conf.

eldad

2019-08-11 16:18

reporter   ~0002768

Hi,

I tried using md5 got this error:
-bash-4.1$ psql -U postgres -p 5400 -h smxashpocpdb01
psql: ERROR: unable to read message length
DETAIL: message length (8) in slot 1 does not match with slot 0(12)

connecting directly to postgres with port 5432 works well in both cases.
-bash-4.1$ psql -U postgres -p 5432 -h smxashpocpdb01
psql (10.9)
Type "help" for help.

postgres=#


I'm attaching pgpool and postgres conf files
regards,
Eldad

postgresql.conf (23,028 bytes)
pgpool.conf (40,647 bytes)

eldad

2019-08-11 16:32

reporter   ~0002769

Issue resolved after setting md5 on slave pg_hba.conf also and allow_clear_text_frontend_auth = on

seems like there is no way to work with "password" in pg_hba.conf, maybe you should check it or update the documentation accordingly.

Thanks for the help,
Eldad

t-ishii

2019-08-11 17:40

developer   ~0002770

I think you should have been able to use "password" in pg_hba.conf and allow_clear_text_frontend_auth = on. I suspect it's a new issue with Pgpool-II. I believe one of our developers Usama is working on it.

t-ishii

2019-08-19 08:48

developer   ~0002791

Last edited: 2019-08-19 08:49

View 2 revisions

Usama has pushed the fix for this. Will appear in the next minor release.
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=94428eaeff8d5985940d422bd00442dbd00e437c

t-ishii

2019-09-05 15:25

developer   ~0002820

The minor release has been already out. I am going to mark this issue as resolved if there's no objection.

Issue History

Date Modified Username Field Change
2019-08-06 23:25 eldad New Issue
2019-08-06 23:25 eldad Tag Attached: pgpool-II
2019-08-07 15:55 t-ishii Assigned To => t-ishii
2019-08-07 15:55 t-ishii Status new => assigned
2019-08-07 15:57 t-ishii Note Added: 0002755
2019-08-07 15:57 t-ishii Status assigned => feedback
2019-08-08 20:15 eldad Note Added: 0002756
2019-08-08 20:15 eldad Status feedback => assigned
2019-08-09 10:34 t-ishii Note Added: 0002759
2019-08-09 10:34 t-ishii Status assigned => feedback
2019-08-09 11:24 t-ishii Note Edited: 0002759 View Revisions
2019-08-11 16:18 eldad File Added: pgpool.conf
2019-08-11 16:18 eldad File Added: postgresql.conf
2019-08-11 16:18 eldad Note Added: 0002768
2019-08-11 16:18 eldad Status feedback => assigned
2019-08-11 16:32 eldad Note Added: 0002769
2019-08-11 17:40 t-ishii Note Added: 0002770
2019-08-19 08:48 t-ishii Note Added: 0002791
2019-08-19 08:49 t-ishii Note Edited: 0002791 View Revisions
2019-08-19 08:50 t-ishii Status assigned => feedback
2019-08-19 08:50 t-ishii Target Version => 4.0.7
2019-09-05 15:25 t-ishii Note Added: 0002820
2019-10-31 18:39 administrator Fixed in Version => 4.0.7
2020-02-18 15:10 t-ishii Status feedback => resolved