How to Install PostgreSQL on an Ubuntu VPS Running Nginx

Brief description

Having a remote database system with a client library, one may not want to expose the database directly to the internet but instead, use a web server like Nginx to handle client communication with the database.

In this article, step by step instructions on how to configure Nginx to connect to PostgreSQL database directly is covered.

Special note: consult HostAdvice's  Best PostgreSQL hosting  page to find the leading web hosts in this category, including expert and user reviews.

Overview

Nginx is a very popular web server currently. It boasts the ability to host large and high traffic sites. Besides being cheap, it can also serve multi purposes such as being used as a web server or a reverse proxy. Using PostgreSQL with Nginx as the web server provides an excellent way of protecting the database from online predators as it is not directly exposed to the internet.

Configuring nginx to interact with PostgreSQL

Install PostgreSQL

Get new PostgreSQL packages using apt-get command and Install, include the additional –contrib package that provides us with some additional functionality.

$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

That is it. We can now go over how it works.

Using PostgreSQL

PostgreSQL uses “roles” to handle authentication and authorisation. PostgreSQL uses ident authentication. It means that if a “role” exists in Postgres, a Linux username with the same name as the role will be able to sign in to that role.

Switching to the Postgres account

During our installation, a default user account called postgres is created. For us to use Postgres, we have to log in that account. To switch over to the postgres account type the following;

$ sudo -i  -u postgres

Access the Postgres prompt by typing;

$  psql

PostgreSQL is now available

Create a database called Mytest with the below command;

postgres=# CREATE DATABASE mytest;

Create a user named “wambui” with a password “mypass” with the below command

CREATE USER wambui WITH PASSWORD 'mypass';

Grant wambui, created above access to the new database mytest with this command

GRANT ALL PRIVILEGES ON DATABASE mytest TO wambui;

Install and configure Nginx.

By default, Apache is the web server. However, we want to configure PostgreSQL with Nginx as the web server. Therefore, we shall install Nginx.

sudo apt-get install nginx

It automatically starts Nginx as the web server. You also start the service manually with this command;

sudo service nginx start

ngx_postgres

For Nginx to communicate with PostgreSQL additional modules are required. To install the modules run the below command assuming the installation path is /opt/nginx \

./configure --prefix=/opt/nginx \

           --add-module=/path/to/ngx_postgres

           --add-module=/path/to/rds-json-nginx-module

           --add-module=/path/to/form-input-nginx-module

           --add-module=/path/to/ngx_devel_kit

           make -j2

           make install

Direct communication to PostgreSQL is through ngx_postgres which is an upstream module. Response is in rds and is compatible with ngx_rds_json and ngx_drizzle.

The sample configuration in this tutorial returns contents of the table employees in rds format.

http {
   upstream database {
       postgres_server  127.0.0.1 dbname=mytest
                        user=wambui password=mypass;
   }
   server {
       location / {
           postgres_pass   database;
           postgres_query  "select * from employees";
       }
   }
}

Conclusion

Now that we have installed the PostgreSQL database, we can now combine two of the internet’s most powerful tools, PostgreSQL and Nginx. A combination of this two technologies enhances the database and the web security.

 

Check out the top 3 Linux hosting services

Was this article helpful?