pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedHaving issue with SSL.
4+ messages / 2 participants
[nested] [flat]
* Having issue with SSL.
@ 2023-05-24 21:02 Randy Needham <randomize17@hotmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Randy Needham @ 2023-05-24 21:02 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Having issue with SSL.
@ 2023-05-25 20:01 Randy Needham <randomize17@hotmail.com>
parent: Randy Needham <randomize17@hotmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Randy Needham @ 2023-05-25 20:01 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Having issue with SSL.
@ 2023-05-26 10:17 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: Randy Needham <randomize17@hotmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Peter J. Holzer @ 2023-05-26 10:17 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Having issue with SSL.
@ 2023-05-30 23:17 Randy Needham <randomize17@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Randy Needham @ 2023-05-30 23:17 UTC (permalink / raw)
To: Peter J. Holzer <hjp-pgsql@hjp.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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!"
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-05-30 23:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-24 21:02 Having issue with SSL. Randy Needham <randomize17@hotmail.com>
2023-05-25 20:01 ` Randy Needham <randomize17@hotmail.com>
2023-05-26 10:17 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-05-30 23:17 Re: Having issue with SSL. Randy Needham <randomize17@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox