CODEWITHSUNDEEP
ubuntunginxproxy
Alessandro Bassi
Alessandro Bassi

Reputation: 427

Timeout issues using NGINX as a Postgres Proxy

I'm setting up a proxy server to intercept and forward all traffic to my Postgres database. I am using NGINX 1.19.2 to accomplish this.

The problem I have is that any queries running more than 10 minute time out. All shorter queries seem to run just fine.

Here is my entire nginx.conf:

worker_processes auto;
pid /run/nginx.pid;

include /etc/nginx/modules-enabled/*.conf;

events {
  worker_connections 768;
}

stream {
  server {
    # Allow up to 30s to establish a connection with the proxied database
    proxy_connect_timeout 30s;

    proxy_socket_keepalive on;

    listen 5432;
    proxy_pass my-server.rds.amazonaws.com:5432;
  }
}

I have tried several of the NGINX proxy-related configuration options (eg. proxy_read_timeout), however, those don't work inside of a stream { } context and need to be in an http { } context. For the purpose of connecting to Postgres, I cannot use http.

Upvotes: 3

Views: 4088

Answers (2)

galeaspablo
galeaspablo

Reputation: 885

Nginx only allows you to do a layer 4 passhthrough, which means all your clients will need to trust the TLS certificate of the RDS database. Furthermore, the nginx proxy will have a different hostname to your RDS database, so certificate validation will fail. So if you care about connecting securely, you should have TLS termination in NGINX. The problem is Nginx cannot do the handshake for Postgres.

So what should you do? You asked for a better alternative, so here's a gift from us :) A proxy specifically built to address this, which is now open source, and available as a Docker image.

Yeah Postgres has its own wrapper around TLS. We had to solve this exact issue, and we open sourced the solution, because nothing else could do it (eg nginx)

https://hub.docker.com/r/ambarltd/pgt-proxy

Upvotes: 0

Steve Robbins
Steve Robbins

Reputation: 13812

Do you see any errors in /var/log/nginx/error.log that relate to these requests?

If you see Connection reset by peer that would mean your upstream (Postgres) is closing the connection, not Nginx.

Also, are you sure it's not your downstream? Do you connect directly to this server or is there a Load Balancer -> Nginx -> Postgres? The Load Balancer could be terminating the connection.

I would try setting all the timeout settings absurdly high, they would go inside the server {} context

proxy_cache_lock_timeout
proxy_connect_timeout
proxy_next_upstream_timeout
proxy_read_timeout
proxy_send_timeout

client_body_timeout
client_header_timeout
keepalive_timeout
lingering_timeout
resolver_timeout
send_timeout

# Not sure if you're using fastcgi
fastcgi_cache_lock_timeout
fastcgi_connect_timeout
fastcgi_next_upstream_timeout
fastcgi_read_timeout
fastcgi_send_timeout

See docs for explanation of each:

For things like VACUUM the connection might terminate but the process may still be running. You could check running processes in Postgres to confirm when it's completed.

Upvotes: 1

Related Questions