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

[Rajni Baliyan]

On Thu, May 21, 2020 at 10:43 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> >> >> 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.
>
> >>> Can you elaborate what is your security concern?
>
That the password will be in plain text ( not encrypted )  and can be
compromised. Or I am missing something here.

>
> > 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]
>
> >>> I think it works as I expected.
> Great!
>


> > *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
>
> >>>Actually you can use plain text password, md5 password or AES256-CBC
> >>> encrypted password in health_check_password. AES256-CBC is most secure
> >>> but If you prefer md5 password, yes, you need to set md5
> >>> authentication in pg_hba.conf. Plain text and AES256 do not have such
> >>> a restriction.
>
> I tried AES password for healthcheck user and it worked . Thanks

But when I am trying to login via psql (using pgpool user) , it is giving
me belwo error.  I tried using pool_password file ( pgpool:AESxxxxx)  as
well but no luck.
# psql -p 9999-h hostname -U pgpool
psql: ERROR:  unable to read message length
DETAIL:  message length (23) in slot 1 does not match with slot 0(42)
ERROR:  unable to read message length
DETAIL:  message length (23) in slot 1 does not match with slot 0(42)

*Pgpool log:*
2020-05-22 16:24:54: pid 11774: ERROR:  unable to read message length
2020-05-22 16:24:54: pid 11774: DETAIL:  message length (23) in slot 1 does
not match with slot 0(42)
* All users who has md5 password and is has entry in pool_passwd file are
logging successfully( using psql and pgpool) but problem is with user
having SCRAM password.

Questions:
- What am I doing wrong in above step?
- What are the steps , if I need to use combination of md5 and SCRAM
passwords?
- when do I need to use pool_hba?
- Is it true that pool_passwd file works only for md5 passwords?  If yes,
then how users with SCRAM password enabled will be able to connect using
pgpool?



> >>>A password in pool_passwd is used if health_check_password is an empty
> >>>string.
>
> > - 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
>
> >>> "server" means PostgreSQL here.
> Thanks
>
What is the meaning of this message ? server doesn't want to talk SSL



> 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/20200522/e5ead61d/attachment.html>