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

[Tatsuo Ishii]

>> >> 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?

> 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.

> *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.

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.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp