pgpool-II 3.7.5 with ssl

Vikas Sharma <shavikas@gmail.com> writes:

We use postgresql 9.6 and pgpool 3.7.5 and we are now asked to enable ssl
for 'in transit'. I have setup the ssl server side on the
postgresql dbs ( master and slave) and can see in pg_stat_ssl that the
master slave communication and connections from the application are showing
ssl = 't'

I have set the parameters in pgpool.conf as well but not sure if the pgpool
is working with ssl enabled. because when I try to connect
with psql using pgpool I get below:

[postgres@pgool-server ~]$ psql 'host=localhost port=5432 dbname=postgres
user=user1 sslmode=require'
psql: server does not support SSL, but SSL was required

Hm, is pgpool maybe using Unix-socket connections to the database?
I'm not sure why pgpool would be trying to pass SSL-ness of the
connection through to the server in that case, though. Seems like
something you should discuss with the pgpool people.

regards, tom lane

Vikas Sharma <shavikas@gmail.com> writes:

We use postgresql 9.6 and pgpool 3.7.5 and we are now asked to enable ssl
for 'in transit'. I have setup the ssl server side on the
postgresql dbs ( master and slave) and can see in pg_stat_ssl that the
master slave communication and connections from the application are showing
ssl = 't'

I have set the parameters in pgpool.conf as well but not sure if the pgpool
is working with ssl enabled. because when I try to connect
with psql using pgpool I get below:

[postgres@pgool-server ~]$ psql 'host=localhost port=5432 dbname=postgres
user=user1 sslmode=require'
psql: server does not support SSL, but SSL was required

Hm, is pgpool maybe using Unix-socket connections to the database?
I'm not sure why pgpool would be trying to pass SSL-ness of the
connection through to the server in that case, though.

Pgpool-II handles connection between client and Pgpool-II, and between
Pgpool-II and PostgreSQL separately. i.e. it is possible to establish
SSL connection between client and Pgpool-II while the connection
between Pgpool-II and PostgreSQL is established without SSL depending
the configuration of Pgpool-II and PostgreSQL (for example, if
Pgpool-II is configured to connect to PostgreSQL using Unix-socket,
SSL will be disabled between Pgpool-II and PostgreSQL as you said).

I think the error suggests that there's something wrong with Pgpool-II
SSL configuration. For example, if the pass to ssl key is wrong, you
see something like below in the pgpool log while pgpool is starting
up:

2019-12-11 08:53:23: pid 8506: WARNING: could not access private key file "/usr/local/etc/server.keyk": No such file or directory

Seems like
something you should discuss with the pgpool people.

True. The issue is almost nothing to do with PostgreSQL. I recommend
to discuss in the pgpool mailing list:

https://www.pgpool.net/mailman/listinfo/pgpool-general

BTW, pgpool 3.7.5 is pretty old (released in 2018). The latest one in
3.7.x series is 3.7.12.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp