Setup Nginx Reverse Proxy to access PostgreSQL database remotely

In this tutorial, we will be discussing how we can leverage the power of Nginx to create a reverse proxy for our PostgreSQL server. This will allow us to access our PostgreSQL database from remote servers. So for example, let’s say you have an app hosted in a vps and you would like to access the app db from your local computer. Then if you setup the nginx reverse proxy on your vps server, you will be able to connect to postgres db directly from your local computer’s Pgadmin4 app by using your vps ip address and port!

Why Nginx?

Nginx will act as an intermediary service to process the client requests. It is also known for better performance since it uses an event-driven architecture to deal with the client requests asynchronously. Nginx can parse and analyze website URLs fast and serve static content. An Nginx reverse proxy also acts as a line of defense for our PostgreSQL server. Configuring a reverse proxy ensures that the identity of our PostgreSQL server remains unknown, so the PostgreSQL server will be safe from port-scanning and enumeration attacks.

How?

You can follow the step by step guide below on how to install the necessary dependencies and get up and running. We are assuming you already have postgresql installed, if you don’t have it. You can easily install it

Install Nginx

Run the following commands to install nginx in ubuntu:

apt update
apt install nginx

For other distro or operating system, please see the nginx installation documentation.

Unlink default configuration

By default, nginx will create a record in the sites-enabled for a virtual host. We can safely unlink it:

unlink /etc/nginx/sites-enabled/default

Add Nginx Server Configuration

Now, let’s update the nginx configuration. First we will create a new folder webapp in the sites-available folder of nginx. Inside this folder we will keep the server config in a file named: db. Of course, you can name them the way you want. So go ahead open up your text editor e.g. vim, nano, emacs, etc. and create the file. In my case, I’m going to use vim:

vim /etc/nginx/sites-available/webapp/db

in the db file, we will write the following server block:

server {
        listen   5431 so_keepalive=on;
        
        allow <ip_address>;
        deny all;
	
        proxy_connect_timeout 60s;
        proxy_socket_keepalive on;
	    proxy_pass localhost:5432;
}

Notice, we told nginx to listen to port 5431. using the allow we can tell nginx, only the ip address mentioned in the configuration can use the reverse proxy to connect & communicate with our postgresql server. So, if you want to connect from your local computer you can put your own ip address here: allow <your_ip_address>; You can allow multiple ip addresses as well using multiple allow

We also tell nginx to deny all. This makes sure, Nginx will deny all the requests coming from any other ip addresses.
Later, we set a timeout of 60s and finally pass/forward the request to postgresql server hosted in the localhost port 5432. Depending on your installation you may have to change these address to match yours. Once, the config file is created we can link it to sites-enabled by using symbolic link:

ln -s /etc/nginx/sites-available/webapp/db /etc/nginx/sites-enabled/webapp/db

Update nginx.conf

Now, we will have to edit the nginx.conf file. So, open the nginx.conf file in a text editor:

vim /etc/nginx/nginx.conf 

Append the following block of code in the existing nginx.conf

stream {
       access_log  /var/log/nginx/db.access.log;
       error_log  /var/log/nginx/db.error.log;
       include /etc/nginx/sites-enabled/webapp/db;
}

The above stream block tells nginx to include the server block from the mentioned file: /etc/nginx/sites-enabled/webapp/db TCP is the default protocol for the stream context, so we don’t have to mention it.

Test & reload Nginx

Now, we will test the nginx configuration file using the command:

nginx -t

If we don’t get any error and the configuration files are ok. Then we can reload nginx to start serving:

service nginx reload

That’s it! Now, you should be able to access the postgres database from the ip address mentioned in the nginx configuration. If it doesn’t work, make sure you have the 5431 port open (Our nginx server was configured to listen for the tcp network requests in the port 5431)