Express PostgreSQL API

The Express PostgreSQL API is make a secure and reliable API use to Login, Registration and Verify USER.

Getting started

Requirements

• Node
• PostgreSQL
• OpenSSL

Create Database

First, You need to create a database in PostgreSQL. after that, you need to create 2 tables in that.

the query of tables are following.

1. Client Table

This table stores information about users like Name, Email, Phone Number, etc.

CREATE TABLE public.clienttable
(
  c_id serial NOT NULL PRIMARY KEY,
  fname character varying(50) NOT NULL,
  lname character varying(50) NOT NULL,
  username character varying(50) NOT NULL,
  email character varying(355) NOT NULL,
  cnumber character varying(355) NOT NULL,
  varified boolean NOT NULL,
  created_on timestamp without time zone NOT NULL,
  last_login timestamp without time zone,
  tccheck boolean NOT NULL,
  password_expiry boolean,
  salt character varying NOT NULL,
  hash character varying NOT NULL
)

2. User Activity

In this table is stored activity of the user when the user Login, make Registration, Change Password.

  CREATE TABLE public.user_activity
(
  a_id serial NOT NULL PRIMARY KEY,
  c_id integer NOT NULL,
  login_ip inet NOT NULL,
  activity character varying NOT NULL,
  activity_datetime timestamp without time zone NOT NULL,
  CONSTRAINT c_id
      FOREIGN KEY(c_id) 
	  REFERENCES clienttable(c_id)
  )

Generating keys using OpenSSL

Now you need to create Private and Public Key to generate a Token.

For that first you need to create Key Folder in root

1 .Generate an RSA private key, of size 2048, and output it to a file named private.key:

openssl genrsa -out key/private.key 2048

2 .Extract the public key from the key pair, which can be used in a certificate:

openssl rsa -in key/private.key -outform PEM -pubout -out key/public.key

Note: make sure both key private.key and public.key save in key folder.

Install API

Now you need to install API. For Installation, you need to follow the below steps.

Step 1: get the goodies

Note: if you don't have git, you can download a zip file of the project instead.

git clone https://github.com/jsuyog2/express-postgresql-api.git api
cd api
npm install

Step 2: add your configuration

Add your Postgres connection information to config/index.json.txt and rename it index.json. Information on the config options can be found here.

Step 3: fire it up!

npm start

Architecture

Due credit

The real credit for this project goes to the great folks behind the following open source softwares and modules:

Softwares

• PostgreSQL
• OpenSSL
• Express
• JWT Token

How it works

The core of the project is Express.

Express.js, or simply Express, is a back end web application framework for Node.js, released as free and open-source software under the MIT License. It is designed for building web applications and APIs. It has been called the de facto standard server framework for Node.js.

All routes are stored in the routes folder and are automatically loaded on start. Check out the routes readme for more information.

OpenSSL is used for the generation of private and public keys.

OpenSSL is a software library for applications that secure communications over computer networks against eavesdropping or need to identify the party at the other end. It is widely used by Internet servers, including the majority of HTTPS websites.

OpenSSL is using to encrypt JWT Temporary Token. For generation of keys read documentation Generating keys using OpenSSL.

Data securely transmitting information using JWT Token

JSON Web Token (JWT) is an open standard (RFC 7519) that defines a compact and self-contained way for securely transmitting information between parties as a JSON object. This information can be verified and trusted because it is digitally signed. JWTs can be signed using a secret (with the HMAC algorithm) or a public/private key pair using RSA or ECDSA.

Once the user is logged in, each subsequent request will include the JWT, allowing the user to access routes, services, and resources that are permitted with that token. JWT Token is secured using public/private key pairs.

Modules

• pbkdf2-password
• NODEMAILER
• request-ip

Password is secure and hashed using pbkdf2-password

Easy salt/password creation for Node.js.

Sends a mail to user for verification or change password using NODEMAILER.

Nodemailer is a module for Node.js applications to allow easy as cake email sending.

Retrieving IP address of user for encryption Login JWT Token using request-ip.

A tiny Node.js module for retrieving a request's IP address.