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;

Answer 1

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

Answer 2

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

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