[pgpool-general: 7028] Re: SSL authentication in Pgpool

[Rajni Baliyan]

On Mon, May 18, 2020 at 5:25 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> > Hello folks,
> >
> > I need your expert advice on using SSL authentication in pgpool.
> >
> > Environment:
> > - OS - RHEL 7.6
> > - PostgreSQL- 11.6 ( Master and Replica on different servers)
> > - pgpool - 4.0.2 - active on master node ( sorry but I need this version)
> >
> > My requirement is to have secure communication between client<=> pgpool
> and
> > pgoll <=> postgres.
> > Maintaining pool_passwd file is not possible ( no control over user and
> > password)  in my use case.
>
> >> You can use allow_clear_text_frontend_auth to not store passwords in
> pool_passwd file.
>
> >>
> https://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html#GUC-ALLOW-CLEAR-TEXT-FRONTEND-AUTH



> Thanks I will explore this option.
>


> > Another option is Certificate Authentication (SSL) between both client<=>
> > pgpool and pgpool<=> postgres ( using same server cert).
>
> Unfortunately Certificate Authentication is not supported between
> pgpool <=> postgres.
>
> > In order to achieve above , I performed below steps-
> > *- generated self signed certificate *
> > *- updated pgpool.conf*
> > *- updated postgresql.conf , and  pg_hba.conf *
> > *-  restarted whole setup.*
> >
> > I can successfully login to  postgresql using cert( i.e. user can log in
> > using client cert) but SSL between pgpool<=> postgres is not working.
> >
> > pgpool log:
>
> [snip]
>
> > As I did not find any related document and the document that I found
> > <
> https://www.highgo.ca/2020/02/25/setting-up-ssl-certificate-authentication-with-pgpool-ii/
> >is
> > not working as expected. There is another contradictory information in
> > pgpool doc  source-6.2.4
> > <https://www.pgpool.net/docs/40/en/html/auth-methods.html>  , which says
> > certificate authentication between pgppol <=> postgres is not possible.
>
> Yes, it's not possible.
>
> > Am I doing something wrong? or this is not at all a possible use case.
>
> >> I recommend you following:
>
> >> 0. enable allow_clear_text_frontend_auth.
>
I wounder whether this is secure option to choose?

> >> 1. client<=>> pgpool
>
> >> Use SSL connection and clear text password authenticatoion. You don't
> need to set up pool_passwd. Password will be provided by client.
>
> >> 2. pgppol <=> postgres
>
> >>Use SSL connection and md5 or SCRAM authenticatoion. The password used
> >>for the authentication is provided by client if
> >>allow_clear_text_frontend_auth is enabled.
>
> I assume I can't avoid pool_passwd if I don't want to compromise on
security.   I have tested below- could you please validate?
1- Create server and client certs
2- Update pgpool.conf ( using master slave mode)
       ssl=on
    ssl_key = '/database11/ssl/erver.key'
    ssl_cert = '/database11/ssl/server.crt
    ssl_ca_cert = '/database11/ssl/root.crt'
    pool_passwd = 'pool_passwd'

  3- Updated postgresql.conf
       ssl=on
    ssl_key = '/database11/ssl/erver.key'
    ssl_cert = '/database11/ssl/server.crt
    ssl_ca_cert = '/database11/ssl/root.crt'

    4- Updated pg_hba.conf
    host postgres postgres md5
    host postgres pgpool IP trust  ## pgpool- health check user

5- Restart whole setup.
6- Test

   1. connect via psql using pgpool port --> Successful and using SSL
   connection

# psql -U postgres -d postgres -p 8888
Password for user postgres:
psql (11.6)
SSL connection (protocol: TLSv1.2, cipher: AES256-GCM-SHA384, bits: 256,
compression: off)
Type "help" for help.
postgres=> \c
SSL connection (protocol: TLSv1.2, cipher: AES256-GCM-SHA384, bits: 256,
compression: off)
You are now connected to database "postgres" as user "postgres".
postgres=>

*Postgres log:*
 LOG:  connection authorized: user=postgres database=postgres SSL enabled
(protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256,
compression=off)

             2. Connect using PGAdmin
client<==> pgpool : client certificate + md5 password
pgpool<==>postgres : SSL + md5 password

*pgpool logs: *
[image: image.png]

*Some further questions: *
-  Should healthcheck user ( pgpool ) be authenticating as either plain
password in pgpool.conf  or trust in pg_hba.conf?
  I assume if pool_passwd is enabled , then pgpool should use md5 password
for backend authentication or it needs to be trust

- Some Detail msg in pgpool log that I an mot sure of  ?   what is server
here ( pgpool or postgres) - server doesn't want to talk SSL
2020-05-21 19:16:20: pid 6664: DEBUG:  authenticate backend: key data
received
2020-05-21 19:16:20: pid 6664: DEBUG:  authenticate backend: transaction
state: I
2020-05-21 19:16:20: pid 6664: DEBUG:  attempting to negotiate a secure
connection
2020-05-21 19:16:20: pid 6664: DETAIL:  sending client->server SSL request
2020-05-21 19:16:20: pid 6664: DEBUG:  attempting to negotiate a secure
connection
2020-05-21 19:16:20: pid 6664: DETAIL:  client->server SSL response: N
2020-05-21 19:16:20: pid 6664: DEBUG:  attempting to negotiate a secure
connection
2020-05-21 19:16:20: pid 6664: DETAIL:  *server doesn't want to talk SSL*
2020-05-21 19:16:20: pid 6664: DEBUG:  authenticate kind = 0

Thanks in advance
Regards,
Raj


Best regards,

> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20200521/a92b076e/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 115272 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20200521/a92b076e/attachment-0001.png>