[pgpool-general: 8621] Re: FW: PGPOOL SSL setup Issues

Bo Peng pengbo at sraoss.co.jp
Wed Mar 1 14:16:04 JST 2023 

Hello,

It works, if you set:

    hostssl all         all         0.0.0.0/0             trust


It fails, if you set:

    host all         all         0.0.0.0/0             trust

Is it correct?

In your log, it seems only node1 fails.
If the setting of ssl connection is the cause, pgpool should have failed to connect both node0 and node1.
Can you check the log of PostgreSQL node1 if any error occurs?
When ssl is enabed, can you use psql to connect to PostgreSQL using "pgpool" user on the server where pgpool is runnung?

    [pgpool server]$ psql -h 192.168.0.38 -p 5432 -U pgpool postgres


If you turn off ssl, can pgpool connect to both PostgreSQL nodes, and both node0 and node1 are running?
Could you run "show pool_nodes" command?

On Wed, 1 Mar 2023 04:38:22 +0000
"Abhishek 1. Garg (Nokia)" <abhishek.1.garg at nokia.com> wrote:

> Yes,
> It works if ssl turned off in pg_hba.conf and pool_hba.conf
> 
> -----Original Message-----
> From: Bo Peng <pengbo at sraoss.co.jp> 
> Sent: Wednesday, March 1, 2023 10:05 AM
> To: Abhishek 1. Garg (Nokia) <abhishek.1.garg at nokia.com>
> Cc: pgpool-general at pgpool.net; Srikant Sharma (Nokia) <srikant.sharma at nokia.com>
> Subject: Re: FW: [pgpool-general: 8603] PGPOOL SSL setup Issues
> 
> 
> CAUTION: This is an external email. Please be very careful when clicking links or opening attachments. See http://nok.it/ext for additional information.
> 
> 
> 
> hello,
> 
> > As mentioned in earlier email, with ssl turned off, all the connectivity works fine.
> >
> > P.S. -     This setup works fine when pool_hba.conf is updated with below entries, result shown below
> > hostssl all         all         0.0.0.0/0             trust
> 
> Have you disable ssl on both side of PostgreSQL <-> Pgpool-II and Pgpool-II <-> client.
> 
> You need to turn of ssl in pg_hba.conf and pool_hba.conf.
> 
> On Wed, 1 Mar 2023 04:24:27 +0000
> "Abhishek 1. Garg (Nokia)" <abhishek.1.garg at nokia.com> wrote:
> 
> > Hello,
> > As mentioned in earlier email, with ssl turned off, all the connectivity works fine.
> >
> > -----Original Message-----
> > From: Bo Peng <pengbo at sraoss.co.jp>
> > Sent: Wednesday, March 1, 2023 7:14 AM
> > To: Abhishek 1. Garg (Nokia) <abhishek.1.garg at nokia.com>
> > Cc: pgpool-general at pgpool.net; Srikant Sharma (Nokia) 
> > <srikant.sharma at nokia.com>
> > Subject: Re: FW: [pgpool-general: 8603] PGPOOL SSL setup Issues
> >
> >
> > CAUTION: This is an external email. Please be very careful when clicking links or opening attachments. See http://nok.it/ext for additional information.
> >
> >
> >
> > Hello,
> >
> > To figure out the cause if it is a SSL connection issue or network issue, could you check if the same error occurs if you turn off ssl connection in PostgreSQL.
> >
> > > Error:
> > > 2023-02-15 13:51:38.201: main pid 46946: LOG:  find_primary_node:
> > > make_persistent_db_connection_noerror failed on node 1
> >
> > Normally pgpool connects to all the backend nodes to find the primary node.
> > But in your log, it failes only when connecting to node1.
> >
> > Any configuration difference between node0 and node1?
> >
> >
> > On Wed, 22 Feb 2023 08:06:12 +0000
> > "Abhishek 1. Garg (Nokia)" <abhishek.1.garg at nokia.com> wrote:
> >
> > > Unable to share certificates, so sending just .conf files.
> > >
> > > -----Original Message-----
> > > From: Abhishek 1. Garg (Nokia)
> > > Sent: Wednesday, February 22, 2023 1:34 PM
> > > To: Bo Peng <pengbo at sraoss.co.jp>
> > > Cc: pgpool-general at pgpool.net; Srikant Sharma (Nokia) 
> > > <srikant.sharma at nokia.com>
> > > Subject: RE: [pgpool-general: 8603] PGPOOL SSL setup Issues
> > >
> > > Hello,
> > >
> > > Please refer attached files. Our Postgres DB is ssl enabled so attaching certificates as well.
> > > And we want to achieve PGPOOL with SSL connectivity to Postgres with SSL, using same certificates as attached.
> > >
> > > Regards
> > >
> > > -----Original Message-----
> > > From: Bo Peng <pengbo at sraoss.co.jp>
> > > Sent: Wednesday, February 22, 2023 7:47 AM
> > > To: Abhishek 1. Garg (Nokia) <abhishek.1.garg at nokia.com>
> > > Cc: pgpool-general at pgpool.net; Srikant Sharma (Nokia) 
> > > <srikant.sharma at nokia.com>
> > > Subject: Re: [pgpool-general: 8603] PGPOOL SSL setup Issues
> > >
> > > [You don't often get email from pengbo at sraoss.co.jp. Learn why this 
> > > is important at https://aka.ms/LearnAboutSenderIdentification ]
> > >
> > > CAUTION: This is an external email. Please be very careful when clicking links or opening attachments. See http://nok.it/ext for additional information.
> > >
> > >
> > >
> > > Hello,
> > >
> > > Thank you for sharing the configurations.
> > >
> > > > > 2023-02-15 13:51:38.201: main pid 46946: LOG:  find_primary_node:
> > > > > make_persistent_db_connection_noerror failed on node 1
> > >
> > > I think the pool_hba.conf doesn't cause the error above because it is the error between pgpool and postgresql.
> > >
> > > Cloud you share your postgresql.conf and pg_hba.conf?
> > >
> > > On Mon, 20 Feb 2023 04:19:53 +0000
> > > "Abhishek 1. Garg (Nokia)" <abhishek.1.garg at nokia.com> wrote:
> > >
> > > > Below are the details
> > > >
> > > >
> > > >
> > > > root at sr-vm-abh-1:/etc/pgpool2# cat pool_passwd
> > > >
> > > > pgpool:TEXTpgpool
> > > >
> > > > postgres:TEXTpostgres
> > > >
> > > > root at sr-vm-abh-1:/etc/pgpool2# pwd
> > > >
> > > > /etc/pgpool2
> > > >
> > > > root at sr-vm-abh-1:/etc/pgpool2#
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: Bo Peng <pengbo at sraoss.co.jp>
> > > > Sent: Friday, February 17, 2023 5:53 PM
> > > > To: Abhishek 1. Garg (Nokia) <abhishek.1.garg at nokia.com>
> > > > Cc: pgpool-general at pgpool.net; Srikant Sharma (Nokia) 
> > > > <srikant.sharma at nokia.com>
> > > > Subject: Re: [pgpool-general: 8603] PGPOOL SSL setup Issues
> > > >
> > > >
> > > >
> > > > [You don't often get email from
> > > > pengbo at sraoss.co.jp<mailto:pengbo at sraoss.co.jp>. Learn why this is 
> > > > important at https://aka.ms/LearnAboutSenderIdentification ]
> > > >
> > > >
> > > >
> > > > Hi,
> > > >
> > > >
> > > >
> > > > > Error:
> > > >
> > > > > 2023-02-15 13:51:38.201: main pid 46946: LOG:  find_primary_node:
> > > > > make_persistent_db_connection_noerror failed on node 1
> > > >
> > > >
> > > >
> > > > Pgpool-II uses the user specified in "sr_check_user" to fine the primary node.
> > > >
> > > > Because sr_check_password is empty, Pgpool-II will read the password from pool_passwd file.
> > > >
> > > > Please check if you have set the password correctly in pool_passwd file?
> > > >
> > > >
> > > >
> > > > -------------------------
> > > >
> > > > sr_check_user = 'pgpool'
> > > >
> > > > sr_check_password = ''
> > > >
> > > > -------------------------
> > > >
> > > >
> > > >
> > > > On Wed, 15 Feb 2023 09:12:52 +0000
> > > >
> > > > "Abhishek 1. Garg (Nokia)" <abhishek.1.garg at nokia.com<mailto:abhishek.1.garg at nokia.com>> wrote:
> > > >
> > > >
> > > >
> > > > > Hi Team,
> > > >
> > > > >
> > > >
> > > > > We are facing challenges in our Sandbox environment where we are trying to configure 2 PGPOOL nodes with 2 PostgreSQL servers(with Standby Replication).
> > > >
> > > > > Refer attached conf file and logs.
> > > >
> > > > >
> > > >
> > > > > Error:
> > > >
> > > > > 2023-02-15 13:51:38.201: main pid 46946: LOG:  find_primary_node:
> > > > > make_persistent_db_connection_noerror failed on node 1
> > > >
> > > > >
> > > >
> > > > > Can you please suggest?
> > > >
> > > > >
> > > >
> > > > > P.S. -     This setup works fine when pool_hba.conf is updated with below entries, result shown below
> > > >
> > > > > hostssl all         all         0.0.0.0/0             trust
> > > >
> > > > >
> > > >
> > > > > [cid:image001.png at 01D9414B.C5876E00]
> > > >
> > > > >
> > > >
> > > > > Regards
> > > >
> > > > > Abhishek Garg
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > --
> > > >
> > > > Bo Peng <pengbo at sraoss.co.jp<mailto:pengbo at sraoss.co.jp>>
> > > >
> > > > SRA OSS LLC
> > > >
> > > > https://www.sraoss.co.jp/
> > >
> > >
> > > --
> > > Bo Peng <pengbo at sraoss.co.jp>
> > > SRA OSS LLC
> > > https://www.sraoss.co.jp/
> >
> >
> > --
> > Bo Peng <pengbo at sraoss.co.jp>
> > SRA OSS LLC
> > https://www.sraoss.co.jp/
> 
> 
> --
> Bo Peng <pengbo at sraoss.co.jp>
> SRA OSS LLC
> https://www.sraoss.co.jp/


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
https://www.sraoss.co.jp/

More information about the pgpool-general mailing list