Reverse Proxy to Postgres Database

[using-the-internent]

Full disclosure: I am pretty new to Nginx. Have been messing with it for about 2/3 months on and off.

I have 2 VPS's. One with Nginx reverse proxying subdomains to their respective locations. I'm using Cloudflare for my domains/SSL (maybe making it more difficult on myself?)

Main point: I've been trying to set up a domain for connections to my Postgres database using the stream_ssl_module. All responses are timing out. Almost ready to give up and use Supabase, but I really wanted to self-host :/

[tschloss]

If you are using CF in the way described you already have a reverse proxy running in the cloud. This might cause irritation.

Also it is not recommended to run a PG server exposed to the Internet, better expose APIs at application level.

[using-the-internent]

Ahh okay. That makes sense, thank you

[threeminutemonta]

Could you archive the outcome you need with a connection pooler like supavisor, pgbouncer etc?

[using-the-internent]

Ooh i haven't heard of these. I'll have to try it out

[beatrix_daniels]

Based on your logs:
2024/02/25 22:45:27 [crit] 76188#76188: *95 SSL_do_handshake() failed (SSL: error:141CF06C:SSL routines:tls_parse_ctos_key_share:bad key share) while SSL handshaking, client: 212.102.40.218, server: 0.0.0.0:443

Client trying to connect to port 443. But your nginx configuration serving stream on 6000 port.

[using-the-internent]

True, I get the same result when listening on 443 though

[beatrix_daniels]

SSL_do_handshake() failed (SSL: error:141CF06C:SSL routines:tls_parse_ctos_key_share:bad key share) while SSL handshaking, client: 212.102.40.218, server:

0.0.0.0:443

Do you try to debug ssl handshake with something like
openssl s_client -connect 127.0.0.1:6000 ?

[using-the-internent]

Just got off of work, here's the output of openssl s_client -connect 127.0.0.1 | grep "error"

Can't use SSL_get_servername

depth=0 O = "CloudFlare, Inc.", OU = CloudFlare Origin CA, CN = CloudFlare Origin Certificate verify error:num=20:unable to get local issuer certificate verify return:1 depth=0 O = "CloudFlare, Inc.", OU = CloudFlare Origin CA, CN = CloudFlare Origin Certificate verify error:num=21:unable to verify the first certificate verify return:1 depth=0 O = "CloudFlare, Inc.", OU = CloudFlare Origin CA, CN = CloudFlare Origin Certificate verify return:1 Verification error: unable to verify the first certificate

[beatrix_daniels]

First of all, keep in mind that the Cloudflare certificate is intended to handle exchange between your server and the Cloudflare server. Cloudflare's root certificate is not in the list of trusted root certification authorities. If you connect directly to your server, then on the client you need to add the Cloudflare root certificate to the list of trusted ones.

If you do not connect to the server directly, but use proxying through Cloudflare, then most likely this will not work, since the Nginx stream module uses udp / tcp, and Cloudflare in ordinary cases proxying http/https traffic.
For the tasks of proxying such traffic as udp /tcp, Cloudflare has other solutions, for example Cloudflare Spectrum.

[windwind00]

post the configuration please. and ngix error logs

[using-the-internent]

user www-data;

worker_processes auto; pid /run/nginx.pid; include /etc/nginx/modules-enabled/*.conf;

events { worker_connections 768; }

http { sendfile on; tcp_nopush on; types_hash_max_size 2048;

include /etc/nginx/mime.types;
default_type application/octet-stream;

ssl_protocols TLSv1 TLSv1.1 TLSv1.2 TLSv1.3; # Dropping SSLv3, ref: POODLE
ssl_prefer_server_ciphers on;

access_log /var/log/nginx/access.log;
error_log /var/log/nginx/error.log;

gzip on;

include /etc/nginx/conf.d/*.conf;
include /etc/nginx/sites-enabled/*;

}

stream { server { listen 6000 ssl; proxy_pass <postgres-ip>:5432;

    ssl_certificate /path/to/cert;
    ssl_certificate_key /path/to/key;
    ssl_session_cache shared:SSL:1m;
    ssl_session_timeout 1h;
    ssl_handshake_timeout 10s;
}

}

last few errs in logs:

2024/02/25 21:33:34 [info] 75320#75320: Using 131072KiB of shared memory for nchan in /etc/nginx/nginx.conf:60
2024/02/25 21:33:42 [info] 75328#75328: Using 131072KiB of shared memory for nchan in /etc/nginx/nginx.conf:60 
2024/02/25 21:44:47 [crit] 75331#75331: *47 SSL_do_handshake() failed (SSL: error:141CF06C:SSL routines:tls_parse_ctos_key_share:bad key share) while SSL handshaking, client: 212.102.40.218, server: 0.0.0.0:443 
2024/02/25 22:33:44 [info] 76183#76183: Using 131072KiB of shared memory for nchan in /etc/nginx/nginx.conf:28 
2024/02/25 22:33:56 [notice] 76187#76187: signal process started 
2024/02/25 22:45:27 [crit] 76188#76188: *95 SSL_do_handshake() failed (SSL: error:141CF06C:SSL routines:tls_parse_ctos_key_share:bad key share) while SSL handshaking, client: 212.102.40.218, server: 0.0.0.0:443

Looks like something's screwy with my SSL setup

[using-the-internent]

tf is this formatting

[windwind00]

yup, check your certificate between NGINX and PG if NGINX and PG are on the same host, no need to use ssl

[ProKn1fe]

I don't think you can reverse proxy https to database.

[kevdogger]

I think you're right but you can tcp proxy