NGINX TLS termination for PostgreSQL

Question

I’ve been trying to use NGINX as a TLS terminator for my PostgreSQL database but without success.

When I try to connect to the database I get the following error:

server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

When I remove the ssl option in listen I can connect without any errors. I’ve tried running another service(Eclipse Mosquitto) with the same NGINX settings, TLS enabled, and it works fine.

I’m using Postico as DB tool.

Here are the NGINX settings I'm using.

# nginx.conf

stream {
    server {
          listen 20000 ssl; # Can’t connect with postgre but with mosquito
          # listen 20000; # Can connect with postgre and mosquitto
          proxy_pass 192.168.1.123:30000;
          include /home/custom/ssl_conf.conf;
    }
}

# ssl_conf.conf

ssl_certificate           /etc/nginx/fullchain.pem;
ssl_certificate_key       /etc/nginx/privkey.pem;
ssl_ciphers 'EECDH+AESGCM:EDH+AESGCM:AES256+EECDH:AES256+EDH';
ssl_dhparam /etc/nginx/ssl/dhparam.pem;
ssl_protocols TLSv1.2;
ssl_prefer_server_ciphers on;

Regin

Have you checked traces on the DB side? Maybe that could help. – Dinsdale

I’ve had a look at the NGINX and DB logs while trying to connect but I get nothing related to my problem. I’m suspicious of my TLS configuration on NGINX, I feel like there might be something I’m missing regarding PG-clients and TLS. As I mentioned before, when I remove the ssl option from the config I can get through with no problem. The certificate I’m using is issued by Let’s Encrypt so it should be trusted, but I still tried including the public .pem file in my PG-client to be sure - got the same error message. – Regin

I'm running into this same issue right now, were you able to make any progress on this rbarlin? – Littell

No, I never figured out what the solution was, I would love to know why. If you do find out please, let me/us know. – Regin

I ran into this issue few years later :( Still no clue, disabling SSL proxies without a problem, just as you described. – Lukewarm

@pikausp, I've added an answer with a possible cause for the problem – Catbird

Answer 1

In short: it's not possible because PostgreSQL has its own handshake that precedes the SSL handshake.

To avoid this you can simply set PostgreSQL to use SSL at its level, and use Nginx's TCP stream as pass-through (the communication is encrypted end-to-end by PostgreSQL).

Source: https://www.postgresql.org/message-id/d05341b9-033f-d5fe-966e-889f5f9218e5%40proxel.se

Sadly that confirms what I feared. Adding SNI to the PostgreSQL protocol wont help with solving your use case because the PostgreSQL protocol has its own handshake which happens before the SSL handshake so the session will not look like SSL to HA Proxy.

Just like HA Proxy does not support STARTTLS for IMAP[1] I do not think that it will ever support SSL for the PostgreSQL protocol, SNI or not.

To solve your use case I recommend using something like stunnel, which does support SNI, to wrap the unencrypted PostgreSQL protocol in SSL.

Setting SSL at Nginx's TCP stream level will trigger the following errors (also reported here: https://www.postgresql.org/message-id/flat/15688-55463748a04474a5%40postgresql.org):

• when trying to connect with psql you get:

psql: error: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

• when looking at the Nginx error log you see:

2021/02/01 19:18:01 [info] 6175#6175: *3 client 127.0.0.1:57496 connected to 127.0.0.1:5433
2021/02/01 19:18:01 [info] 6175#6175: *3 SSL_do_handshake() failed (SSL: error:1408F10B:SSL routines:ssl3_get_record:wrong version number) while SSL handshaking, client: 127.0.0.1, server: 127.0.0.1:5433

Swiercz

Pretty disappointed this is how it works. I came in here expecting to make a TLS proxy for an array of services. Having to handle TLS at the service level is not comfortable. – Nemesis

It is interesting why Postgres (also mysql) does not implement standard TLS and allow external termination. – Cruz

Answer 2

As described in that similar question, the problem is probably that Postgres uses application-level protocol negotation, so a simple proxy will probably not work. See also

https://github.com/envoyproxy/envoy/issues/10942

Catbird