Configure a Reverse Proxy for PostgreSQL with Nginx

The title says it all, in this article we will see how to configure a reverse proxy for PostgreSQL using Nginx server.

But why even do that?

Firstly, Nginx adds an additional layer of security for our database. Nginx provides a whole host of options which makes it easy to manage access and protect the database. For example, we can configure such that only a narrow set of IP addresses can access the database.

Assumptions

I am assuming that you have at least a minimal working experience with Nginx. Also you should already have the Nginx server installed, setup and running. If not, please do that and come back here.

Also you should have a PostgreSQL database setup and running. Keep the port number of the database server handy as we will be using it in configuration file. For the purpose of this article, let’s assume that it is up and running on it’s default port — 5432.

The How

If you notice, the nginx.config file contains a http block. That block is used to configure webapps which will be using HTTP or HTTPS.

Well, PostgreSQL doesn’t use HTTP or HTTPS so we cannot use the http block. We will have to add a new block called stream in the same level as http block. Inside that stream block, we will config a server block to listen on 9856 which will simply proxy_pass to the actual PostgreSQL.

After this edit, your configuration will look something similar to this:

If you prefer to keep the server block config in a separate directory, you can do it and simply use the include directory.

Test and Reload

Finally when all of this is done, test and reload the Nginx server by executing the below commands.

# nginx -t
# nginx -s reload

I personally prefer to reload the server instead of restarting to minimize the downtime. Do whatever suits your needs.

A word of caution. If nginx -t command outputs any errors, fix them first before reloading the config.

SSL

You can also configure ssl in the server block. I will probably edit this section in the future, or write a separate article about it. For now if you want to know more, simply google — ngx_stream_ssl_module