Database Server Setup for PTS Applications

You can host the database server on the same machine as the application, for production deployment we recommend that you setup the database server on its own machine separate from the application host.

Install PostgreSQL

Default repositories contain Postgres packages, so we can install these using the apt packaging system. Install the package with the followng command:

sudo apt install postgresql postgresql-contrib

Configure PostgreSQL Server

The following steps will adjust the default configuration to make the server accessible on the network and allow remote clients to authenticate.

Configure Networking

The following steps will ensure that you can connect to the sql server from the outside. Open the posgres configuration file postgresql.conf in your favorite editor:

sudo nano /etc/posgresql/10/main/postgresql.conf

Change the listen_addresses line to allow incoming connections on all available IP interfaces:

listen_addresses = '*'

Configure Authentication

By default PosgreSql server relies on linux user accounts for authentication. This is generally useful on multi user environments, for our purposes it is more beneficial to rely solely on PostgreSql user accounts.

Open the posgres authentication configuration file pg_hba.conf in your favorite editor:

sudo nano /etc/posgresql/10/main/pg_hba.conf

Then change the following:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

to read as:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               trust
host    all             all             ::0/0                   trust

The complete configuration should read as follows:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             0.0.0.0/0               trust
# IPv6 local connections:
host    all             all             ::0/0                   trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             0.0.0.0/0               trust
host    replication     all             ::0/0                   trust

Restart PostgreSQL Service

We need to restart the service for the configuration changes to become effective:

sudo service postgresql restart

Create Primary Role and User

The following steps will give us credentials to work with for remote dba needs.

Start Interactive Prompt

This will log you into the PostgreSQL prompt, and from here you are free to interact with the database management system right away.

sudo -u postgres psql

Create Primary User

In PostgreSQL interactive prompt, enter the following:

CREATE USER johny WITH PASSWORD 'super$ecretPassword!';

Replace johny and super$ecretPassword! with values of your choice.

Create Db Admin Role

Admin role will grant all priviliges to its member user accounts.

For the purpose of this example we will call the role dbadmin, you can call it anything you like.

In PostgreSQL interactive prompt, enter the following:

CREATE ROLE dbadmin;
ALTER ROLE dbadmin WITH SUPERUSER;

Replace dbadmin with role name of your choice.

Add Db Admin Role to Primary User

We need to add our user to admin role to inherit unrestricted privileges. In PostgreSQL interactive prompt, enter the following:

GRANT dbadmin TO johny;

Replace dbadmin and johny with values you chose in previous steps.

Quit The Interactive Promtp

To return to regular linux shell enter the quit \q command:

postgres=# \q

Create Database

We need a database for our application. To create a database, in PostgreSQL interactive prompt, enter the following:

CREATE DATABASE pts_myproject;

Replace pts_myproject with a database name of your choice, we recommend adding pts_ prefix to indicate the database belongs to PTS infrastracture.

At this point we can use the database with the previously created admin credential. For production set up we recommend that you continue with the remaining steps of this guide.

Create Application User

It is always a good idea to create a PostgreSQL user that can perform only CRUD operations on the data. To do that, enter the following in the PostgreSQL interactive prompt:

CREATE USER appclient WITH PASSWORD 'super$ecretPassword!';
GRANT CONNECT ON DATABASE pts_myproject TO appclient;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appclient;

Replace pts_myproject with value you chose in previous steps, replace appclient and super$ecretPassword! with values of your choice.