[pgpool-general: 8909] Re: pgpool forwarding database users/passwords

[Bo Peng]

Hi,

> How could I know which IP is connecting to DB in order to properly
> configure hba file?

If you could enable "log_connections = on" in pgpool.conf,
the client IP address will be output in log file.

For example, in this case, the client IP address is "192.168.56.101".
------------------
2023-08-04 16:57:31.850: child pid 32148: LOG:  new connection received
2023-08-04 16:57:31.850: child pid 32148: DETAIL:  connecting host=192.168.56.101 port=44260
------------------

On Fri, 4 Aug 2023 09:43:01 +0200
Tan Mientras <tanimientras at gmail.com> wrote:

> Indeed, setting:
>      "host  all  all   all password"
> finally run and a database connection was succesfuly stablished from drupal
> 
> Apart from configuring hostssl, I still haven't found why client's ip
> aren't those specified...
> 
> How could I know which IP is connecting to DB in order to properly
> configure hba file?
> 
> Thanks a lot.
> 
> 
> On Thu, Aug 3, 2023 at 9:27 AM Bo Peng <pengbo at sraoss.co.jp> wrote:
> 
> > Hi,
> >
> > Thank you for your reply.
> >
> > > $ cat /opt/bitnami/pgpool/conf/pool_hba.conf
> > > host all all 192.162.122.0/24 password
> > > local    all             all                            trust
> > > host     all             replica       all         trust
> > > host     all             postgres       all         scram-sha-256
> > > host     all             wide               all         trust
> > > host     all             pop_user           all         trust
> > > host     all             all                all         scram-sha-256
> >
> > I think one possible reason is that the client IP address doesn't match
> > 192.162.122.0/24.
> >
> > If the client doesn't match any entry, pgpool will return an error like:
> >
> >   DETAIL:  no pool_hba.conf entry for host "...", user "postgres"...,
> > database "..."
> >
> > However, as you mentioned the connection failed with an error:
> >
> >   DETAIL:  pool_passwd file does not contain an entry for ...
> >
> >
> > I guess your application matched the last entry "host  all  all   all
> > scram-sha-256".
> > In this case, pgpool will try to read password from pool_passwd file.
> >
> > If it is possible, could you try to change the setting to "host all all
> > all password"?
> > In this case, if your application can successfully connect to pgpool-II,
> > it can be determined that your application does not match 192.162.122.0/24
> > .
> >
> > On Wed, 2 Aug 2023 13:30:08 +0200
> > Tan Mientras <tanimientras at gmail.com> wrote:
> >
> > > pgadmin also fails to connect to other database than postgres/postgres
> > (?)
> > >
> > > On Wed, Aug 2, 2023 at 12:43 PM Tan Mientras <tanimientras at gmail.com>
> > wrote:
> > >
> > > > $ cat /opt/bitnami/pgpool/conf/pool_hba.conf
> > > > host all all 192.162.122.0/24 password
> > > > local    all             all                            trust
> > > > host     all             replica       all         trust
> > > > host     all             postgres       all         scram-sha-256
> > > > host     all             wide               all         trust
> > > > host     all             pop_user           all         trust
> > > > host     all             all                all         scram-sha-256
> > > >
> > > >
> > > > On Wed, Aug 2, 2023 at 12:40 PM Tan Mientras <tanimientras at gmail.com>
> > > > wrote:
> > > >
> > > >>
> > > >> Did you get the error when your application connects to pgpool or
> > > >>> when you connect to pgpool using psql?
> > > >>>
> > > >> when the application (drupal site/php) connects to db.
> > > >>
> > > >>
> > > >>
> > > >>> As you mentioned you are using bitnami docker image.
> > > >>> Sorry, I am not familiar with bitnami docker image.
> > > >>> Please make sure the setting was updated.
> > > >>>
> > > >>
> > > >>
> > > >> $ grep enable_pool_hba /opt/bitnami/pgpool/conf/pgpool.conf
> > > >> enable_pool_hba = 'on'
> > > >>
> > > >>
> >
> >
> > --
> > Bo Peng <pengbo at sraoss.co.jp>
> > SRA OSS LLC
> > TEL: 03-5979-2701 FAX: 03-5979-2702
> > URL: https://www.sraoss.co.jp/
> >


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/