View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000701Pgpool-IIBugpublic2021-04-16 17:542021-04-17 12:39
Reportershubham.s@helpshift.com Assigned Tot-ishii  
PriorityhighSeveritymajorReproducibilityhave not tried
Status resolvedResolutionopen 
PlatformDebianOSUbuntuOS Version18.04
Product Version4.1.4 
Summary0000701: Password auth not working with md5
DescriptionWe have 2 pgpool nodes in front of postgres master/slave v9.6 setup. Our backends are up when tried with pcp_node_info command on pgpool nodes.

We are getting below error when trying to connect using command psql "password=<plain-text-password OR md5-password> sslmode=require host=<pgpool-IP> port=5434 user=<user> dbname=<db>"

```psql: ERROR: clear text password authentication failed
DETAIL: unable to get the password```
-----------------------
sudo dpkg --list | grep pgpool
ii libpgpool0 4.1.4-2.pgdg18.04+1 amd64 pgpool control protocol library
ii pgpool2 4.1.4-2.pgdg18.04+1 amd64 connection pool server and replication proxy for PostgreSQL
-----------------------

We are also running stunnel on pgpool nodes.
----------------------------
sudo dpkg --list | grep stunnel
ii stunnel4 3:5.44-1ubuntu3 amd64 Universal SSL tunnel for network daemons
----------------------------

Same setup is working fine with PGPOOL v3.6.1.

Attached are the pg_hba.conf file and pool_hba files. Please let us know if we are doing something wrong here.

Auth parameters from pgpool.conf file
-----------------
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60
ssl = off
-----------------
TagsNo tags attached.

Activities

shubham.s@helpshift.com

2021-04-16 17:54

reporter  

pg_hba.conf (993 bytes)    
#
local  all  postgres    trust
# "local" is for Unix domain socket connections only
local  all  all    trust
# IPv4 local connections:
host  all  all  127.0.0.1/32  trust
# IPv6 local connections:
host  all  all  ::1/128  trust

# Passwored hosts
host  all  all  0.0.0.0/0  password

# md5 hosts
host  all  all  0.0.0.0/0  md5

# Trusted hosts

# User custom

# User custom, group based
# e.g. { type: host,  database: replication, user: postgres, groups: ["cl_postgres-master", "cl_postgres-standby"], method: 'trust', comment: 'replication connections allowed from slaves' }
### replication connections allowed from slaves - cl_pg-audit-trail02-master
host  replication  postgres  <pg-master-ip/32>  trust
### replication connections allowed from slaves - cl_pg-audit-trail02-slave
host  replication  postgres  <pg-slave-ip/32>  trust
### all connections allowed from pgpool cluster - cl_pgpool-audit-trail02
host  all  all  <pgpool-1-ip/32>  trust
host  all  all  <pgpool-2-ip/32>  trust
pg_hba.conf (993 bytes)   
pool_hba.conf (150 bytes)    
local  all  all    trust
# IPv4 local connections:
host  all  all  127.0.0.1/32  trust

# Passwored hosts

# md5 hosts
host  all  all  0.0.0.0/0  md5
pool_hba.conf (150 bytes)   

shubham.s@helpshift.com

2021-04-16 21:16

reporter   ~0003803

stunnel.conf (364 bytes)    
setuid = stunnel4
setgid = stunnel4

debug = 4
output = /var/log/stunnel4/stunnel.log
pid = /var/run/stunnel4/stunnel.pid

cert = /etc/stunnel/cert.pem

verify = 3
CApath = /etc/ssl/certs
CAfile = /etc/stunnel/cacert.pem
CRLpath = /etc/ssl/certs

options = NO_SSLv3

[pgpool]
client = no
accept = 0.0.0.0:5430
connect = localhost:5432
protocol = pgsql
verify = 0
stunnel.conf (364 bytes)   

shubham.s@helpshift.com

2021-04-16 21:16

reporter   ~0003804

Also added stunnel.conf file

shubham.s@helpshift.com

2021-04-16 22:30

reporter   ~0003806

Yes we have a setup pool password correctly, all passwords are in md5 format. Below is the entry of the user from which I tried psql command.
------------------
analytics_ro:md5138bbd239314aeac921e07349a3c2763
------------------

Similar password is configured on postgres node as well
-----------------------------
psql (9.6.21)
Type "help" for help.

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

t-ishii

2021-04-16 22:36

developer   ~0003807

Since you have this in pg_hba,conf:
host all all 0.0.0.0/0 password
You need to setup pool_passwd with clear text password, not md5 password.
Are you sure with this?

shubham.s@helpshift.com

2021-04-16 22:46

reporter   ~0003808

Agree with your point but I also have md5 entry in my pg_hba.conf (host all all 0.0.0.0/0 md5) below is the file content so as per my understanding it should also work for md5. Also like I said earlier same setup is working with old pgpool version.

I did try to add plain text password in pool_passwd file and yes that worked for me but my requirement is md5 auth not the plain text one.

--------------------
local all postgres trust
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust

# Password hosts
host all all 0.0.0.0/0 password

# md5 hosts
host all all 0.0.0.0/0 md5
-----------------------

t-ishii

2021-04-16 23:28

developer   ~0003809

> but I also have md5 entry in my pg_hba.conf
But the md5 entry is *below* password entry. pg_hba.conf is parsed from top to bottom. Once matched entry (in this case password) is found, PostgreSQL uses it and ignores rest of the file. "password" auth requires Pgpool-II clear text password provided. That's the reason why you need clear password entry in pool_passwd.

I am not sure older version of Pgpool-II worked though.

shubham.s@helpshift.com

2021-04-16 23:56

reporter   ~0003810

Great, since our postgres databases is not in prod right now I swapped the entries in pg_hba file like below and it started working after that, thanks for your help!!
--------------------
local all postgres trust
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust

# md5 hosts
host all all 0.0.0.0/0 md5

# Password hosts
host all all 0.0.0.0/0 password
-----------------------

t-ishii

2021-04-17 12:39

developer   ~0003811

You are welcome! I will mark this issue as "resolved".

Issue History

Date Modified Username Field Change
2021-04-16 17:54 shubham.s@helpshift.com New Issue
2021-04-16 17:54 shubham.s@helpshift.com File Added: pg_hba.conf
2021-04-16 17:54 shubham.s@helpshift.com File Added: pool_hba.conf
2021-04-16 21:16 shubham.s@helpshift.com Note Added: 0003803
2021-04-16 21:16 shubham.s@helpshift.com File Added: stunnel.conf
2021-04-16 21:16 shubham.s@helpshift.com Note Added: 0003804
2021-04-16 22:30 shubham.s@helpshift.com Note Added: 0003806
2021-04-16 22:36 t-ishii Note Added: 0003807
2021-04-16 22:46 shubham.s@helpshift.com Note Added: 0003808
2021-04-16 23:28 t-ishii Note Added: 0003809
2021-04-16 23:56 shubham.s@helpshift.com Note Added: 0003810
2021-04-17 12:39 t-ishii Note Added: 0003811
2021-04-17 12:39 t-ishii Assigned To => t-ishii
2021-04-17 12:39 t-ishii Status new => resolved
2021-04-17 12:39 t-ishii Description Updated