TCP proxy to postgres database as an upstream server in nginx

Question

Question: Is it possible to set Nginx as a reverse proxy for a database? These are the flags I have at the moment and I believed that having the --with-stream module was sufficient to use TCP streams to the database. Is this a PLUS feature?

Nginx configuration options:

--prefix=/etc/nginx --sbin-path=/usr/sbin/nginx --modules-path=%{_libdir}/nginx/modules --conf-path=/etc/nginx/nginx.conf --error-log-path=/var/log/nginx/error.log --http-log-path=/var/log/nginx/access.log --pid-path=/var/run/nginx.pid --lock-path=/var/run/nginx.lock --http-client-body-temp-path=/var/cache/nginx/client_temp --http-proxy-temp-path=/var/cache/nginx/proxy_temp --http-fastcgi-temp-path=/var/cache/nginx/fastcgi_temp --http-uwsgi-temp-path=/var/cache/nginx/uwsgi_temp --http-scgi-temp-path=/var/cache/nginx/scgi_temp --user=nginx --group=nginx --with-http_ssl_module --with-http_realip_module --with-http_addition_module --with-http_sub_module --with-http_dav_module --with-http_flv_module --with-http_mp4_module --with-http_gunzip_module --with-http_gzip_static_module --with-http_random_index_module --with-http_secure_link_module --with-http_stub_status_module --with-http_auth_request_module --with-threads --with-stream --with-stream_ssl_module --with-http_slice_module --with-mail --with-mail_ssl_module --with-file-aio --with-http_v2_module --with-cc-opt='-g -O2 -fstack-protector-strong -Wformat -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2' --with-ld-opt='-Wl,-z,relro -Wl,--as-needed' --with-ipv6

Nginx config

stream {

    include conf.d/streams/*.conf;
}

contents of conf.d/streams/upstream.conf

upstream database_server {
    least_conn;
    keepalive 512;
    server 192.168.99.103:32778 max_fails=5 fail_timeout=30s weight=1;

}

Error message from Nginx

2016/02/22 03:54:13 [emerg] 242#242: invalid host in upstream "http://database_server" in /etc/nginx/conf.d/streams/database_server.conf:9

Nathaniel Waisbrot · Accepted Answer · 2021-04-11 02:37:15Z

19

Here's an nginx configuration that worked for me (I'm running inside Docker, so some of these options are to help with that):

worker_processes auto;

daemon off;

error_log stderr info;

events {
    worker_connections 1024;
}

stream {
    upstream postgres {
        server my_postgres:5432;
    }

    server {
        listen 5432 so_keepalive=on;
        proxy_pass postgres;
    }
}

The key for me was the line listen 5432 so_keepalive=on;, which turns on TCP keepalive. Without that, I could connect but my connection would get reset after a few seconds.

edited Apr 11, 2021 at 2:37

answered Jul 8, 2020 at 3:04

Nathaniel Waisbrot

24.7k7 gold badges77 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Pablo Alexis Domínguez Grau Over a year ago

I suppose that my_postgres:5432 stands for the docker container with the name of my_postgres, correct? I'm also trying this out for my docker stuff.

Nathaniel Waisbrot Over a year ago

Here, my_postgres is a name that resolves to a server listening on port 5432. If you had a docker-compose configuration, you might name your Postgres container my_postgres and then Docker would set up name resolution for you. But you could be outside of Docker and just using DNS.

Biel Simon Over a year ago

For me the connection times out when trying to connect to postgres from a client using this configuration.

Nathaniel Waisbrot Over a year ago

@BielSimon if you time out while connecting then your Nginx is unable to reach your Postgres. Probably one or both are misconfigured (maybe Postgres is not listening on the right interface, maybe Nginx is using the wrong port, etc).

sutan Over a year ago

To those new with nginx, this code block is for nginx.conf that is usually located in /etc/nginx/. Normally there is a http{ ... block but @NathanielWaisbrot adjusted it because it's for docker.

Devonte · Accepted Answer · 2016-02-22 04:21:04Z

5

The issue was the "http://database_server" it is a tcp stream so you need to just proxy_pass database_server

also keep alive is not a directive that goes in a tcp upstream server

answered Feb 22, 2016 at 4:21

Devonte

3,4955 gold badges22 silver badges15 bronze badges

1 Comment

sutan Over a year ago

technical implementation: in nginx.conf create a stream directive instead of the default http. We can implement the proxy_pass in the stream block. Refer to the stream TCP load balancer documentation

galeaspablo · Accepted Answer · 2024-11-29 20:13:13Z

-1

Nginx does not understand the PostgreSQL handshake. So all you can do is a traffic passthrough, which is insecure because it does not allow your clients to properly verify who they're connecting to.

We had to solve this exact problem, and we open sourced the solution,

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

answered Nov 29, 2024 at 20:13

galeaspablo

8856 silver badges16 bronze badges

Collectives™ on Stack Overflow

TCP proxy to postgres database as an upstream server in nginx

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related