Having issue with SSL.

Some new information. I was able to connect to postgresql via ssl from a machine in the same subnet as the server. Beyond port 5432 is there any other ports that need to be opened to access postgresql via ssl? There is a firewall setup between the server and the clients that need access but we have only port 5432 opened.
________________________________
From: Randy Needham <randomize17@hotmail.com>
Sent: Wednesday, May 24, 2023 2:02 PM
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Having issue with SSL.

host - Windows Server 2022
postgresql - 14.8
pgAdmin 4 - 7.1
openssl - 3.1.0

So I have generated a key and csr file to be sent to a CA cert issuer InCommon. I generated via openssl with the following command.

openssl.exe req -newkey rsa:2048 -nodes -keyout postgresql.key -out postgresql.csr

Downloaded the PKCS#7, PEM encoded version of the cert to use. The following is the changes I did to postgresql.conf. The x.x.x.x is the actual IP of the Server.

listen_addresses = 'x.x.x.x' 

ssl = on
#ssl_ca_file = ''
ssl_cert_file = './certs/postgresql.cer'
#ssl_crl_file = ''
#ssl_crl_dir = ''
ssl_key_file = './certs/postgresql.key'
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_min_protocol_version = 'TLSv1.2'
#ssl_max_protocol_version = ''
#ssl_dh_params_file = ''
#ssl_passphrase_command = ''
#ssl_passphrase_command_supports_reload = off

Here is the current setup of pg_hba.conf with real IP's being x.x.x.x

# "local" is for Unix domain socket connections only
#local all all scram-sha-256
# IPv4 local connections:
#host all all 127.0.0.1/32 scram-sha-256
#host all all x.x.x.x/32 scram-sha-256
host all all x.x.x.x/32 scram-sha-256
hostssl all all 127.0.0.1/32 scram-sha-256
hostssl all all x.x.x.x/32 scram-sha-256
hostssl all all x.x.x.x/32 scram-sha-256
# IPv6 local connections:
# host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all scram-sha-256
host replication all 127.0.0.1/32 scram-sha-256
# host replication all ::1/128 scram-sha-256

The problem I am running into is my remote client can't connect via SSL to postgrsql. I am able to from the server itself. This is using pgAdmin 4 and making ssl mode as required. Also ran psql.exe on the server to show that SSL was in fact working on the server. "SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)". In the logs it is showing this when I try to connect via my remote client.

[2672]: LOG: could not accept SSL connection: An existing connection was forcibly closed by the remote host.

The error from pgAdmin 4 on the remote client is this.

connection failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. SSL SYSCALL error: Connection reset by peer (0x00002746/100054)

I have been trying to find a solution with no luck. I am hoping that I might be missing something simple and someone will be able to see it.

On 2023-05-24 21:02:04 +0000, Randy Needham wrote:

The problem I am running into is my remote client can't connect via SSL to
postgrsql. I am able to from the server itself. This is using pgAdmin 4 and
making ssl mode as required. Also ran psql.exe on the server to show that SSL
was in fact working on the server. "SSL connection (protocol: TLSv1.3, cipher:
TLS_AES_256_GCM_SHA384, bits: 256, compression: off)". In the logs it is
showing this when I try to connect via my remote client.

[2672] LOG: could not accept SSL connection: An existing connection was
forcibly closed by the remote host.

The error from pgAdmin 4 on the remote client is this.

connection failed: server closed the connection unexpectedly This probably
means the server terminated abnormally before or while processing the request.
SSL SYSCALL error: Connection reset by peer (0x00002746/100054)

So both the server and the client claim that the connection was
terminated by the other side?

I suspect that the server and client cannot agree on a common cypher.
But if both are reasonably up to date that shouldn't happen (it can
happen if the SSL library on your server is much older than that on your
client or vice versa).

Can you use wireshark (or something similar) to record the session and
see where in the protocol they give up?

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

I was able to find out the issue. The firewall between the server and clients refused to allow SSL traffic over port 5432. Once allowing SSL via port 5432 it started working.

________________________________
From: Peter J. Holzer
Sent: Friday, May 26, 2023 3:17 AM
To: pgsql-general@lists.postgresql.org
Subject: Re: Having issue with SSL.

On 2023-05-24 21:02:04 +0000, Randy Needham wrote:

The problem I am running into is my remote client can't connect via SSL to
postgrsql. I am able to from the server itself. This is using pgAdmin 4 and
making ssl mode as required. Also ran psql.exe on the server to show that SSL
was in fact working on the server. "SSL connection (protocol: TLSv1.3, cipher:
TLS_AES_256_GCM_SHA384, bits: 256, compression: off)". In the logs it is
showing this when I try to connect via my remote client.

[2672] LOG: could not accept SSL connection: An existing connection was
forcibly closed by the remote host.

The error from pgAdmin 4 on the remote client is this.

connection failed: server closed the connection unexpectedly This probably
means the server terminated abnormally before or while processing the request.
SSL SYSCALL error: Connection reset by peer (0x00002746/100054)

So both the server and the client claim that the connection was
terminated by the other side?

I suspect that the server and client cannot agree on a common cypher.
But if both are reasonably up to date that shouldn't happen (it can
happen if the SSL library on your server is much older than that on your
client or vice versa).

Can you use wireshark (or something similar) to record the session and
see where in the protocol they give up?

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"