Navigation :

DB Node Installation

Overall Installation Steps

The high-level steps for installing and configuring N2CUG DB nodes are:

Determine the server(s) that will supply the DB logical component, bearing in mind the supported operating systems and minimum server requirements.
Ensure the installation pre-requisites are met.
Install the DB package.
Perform any required post-installation steps.
Update the DB configuration as desired.

Installation Pre-requisites

OS-specific Setup

Refer to the specific Red Hat or Debian instructions for any pre-requisites as required.

PostgreSQL Server

The PostgreSQL database server must be installed and configured. Follow the installation procedure for this, noting any prerequisites.

At least version 10 of the PostgreSQL server is required for the N2CUG platform.

In order to perform the database initialisation steps, the PostgreSQL server must be running on the database instance and must be listening and able to be connected to from the command line, i.e. assuming that the PostgreSQL default user is used, this should succeed:

su - postgres
psql

DBMaintain

N2CUG DB nodes use the third-party tool DBMaintain to manage the database over time.

This tool can be obtained by:

Installation from the N-Squared RPM repository for Red Hat 8 or Red Hat 7. Contact N-Squared Support to discuss access to this if required.
Direct download of binaries from the official DBMaintain website.

If you are using an N-Squared packaged installation, DBMaintain will be installed in /usr/share/dbmaintain. For direct download, it may be placed in any convenient location. Commands for using DBMaintain are listed below, but may require updating the path appropriately for your environment.

Java

Usage of the DBMaintain tool requires a JDK or JRE of at least Java 5 to be available on the system. The default Java installation is acceptable in most cases.

To find the local java version, execute:

java -version

If Java is not installed or the version is not at least 5.x, install a new Java version as follows. The commands to execute will depend on your operating system type and version.

RHEL 8

On Red Hat Enterprise Linux 8 or similar platforms, execute:

sudo dnf install java-11-openjdk
sudo alternatives --install /usr/bin/java java /usr/java/latest/bin/java 1
sudo alternatives --config java

RHEL 7

On Red Hat Enterprise Linux 7 or similar platforms, execute:

sudo yum install java-11-openjdk
sudo alternatives --install /usr/bin/java java /usr/java/latest/bin/java 1
sudo alternatives --config java

Ubuntu

On Ubuntu or similar platforms, execute:

sudo apt install default-jdk

Installation Steps

Follow the appropriate installation steps depending on your installation sources.

Note that the packages only need to be installed on nodes where a database schema needs to be created and maintained. This will generally only be the primary database instance.

From N-Squared Repository

Execute the instructions specific to your operating system:

RHEL 8	Other RPM-based Systems	DEB-based Systems
`dnf install n2cug-db`	`yum install n2cug-db`	`apt-get install n2cug-db`

As Manual Installation

Transfer the provided package file to the target node, then follow the instructions specific to your operating system.

Execute (adjusting as appropriate for package location and version details) the following:

RPM-based Systems	DEB-based Systems
`sudo rpm -Uvh /path/to/n2cug-db-M.m.p-b.noarch.rpm`	`sudo dpkg -i /path/to/n2cug-db_M.m.p-b_all.deb`

Post-Installation Steps

Database Initialisation

N2CUG uses the database maintenance tool DBMaintain to upgrade and manage its database automatically.

N2CUG uses the following (unchangeable) database schemas and users:

CUG data schema: n2cug
Audit information schema: audit

These instructions assume that you will use the following values for N2CUG database storage:

Database name: n2in

These values may be modified as required for your installation by updating the installation steps below accordingly.

Database Preparation

The commands in this section must be executed as the OS user that is running the PostgreSQL server. By default, this is postgres.

Create the required users for database administration, runtime data retrieval, and GUI access. You will be prompted to enter a password for each user.

createuser -P -l n2cug_owner
createuser -P -l n2cug_web
createuser -P -l n2cug_ro

If the database does not already exist, create it.

createdb -O n2cug_owner n2in

If the database schemas do not already exist, create them.

psql n2in
CREATE SCHEMA IF NOT EXISTS audit AUTHORIZATION n2cug_owner;
CREATE SCHEMA IF NOT EXISTS n2cug AUTHORIZATION n2cug_owner;
\q

Confirm database login as n2cug_owner works, and set the schema search path.

psql -h localhost -U n2cug_owner n2in
ALTER ROLE n2cug_owner SET search_path TO n2cug,public;
\q

Confirm database login as n2cug_web works, and set the schema search path.

psql -h localhost -U n2cug_web n2in
ALTER ROLE n2cug_web SET search_path TO n2cug,public;
\q

Confirm database login as n2cug_ro works, and set the schema search path.

psql -h localhost -U n2cug_ro n2in
ALTER ROLE n2cug_ro SET search_path TO n2cug,public;
\q

Grant specific permissions to the non-administrative users (each command must be executed separately).

psql -h localhost -d n2in -U n2cug_owner
GRANT USAGE ON SCHEMA n2cug TO n2cug_web;
GRANT USAGE ON SCHEMA n2cug TO n2cug_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA n2cug GRANT SELECT ON TABLES TO n2cug_ro;
\q

Install the extensions to enable the use of Perl for functions.

psql n2in
CREATE EXTENSION plperl;
\q

Database Creation

Before executing the commands to put the N2CUG database elements into the database, edit the file:

/usr/share/n2cug/db/dbmaintain.properties

In the file, locate the following section:

database.url=jdbc:postgresql://localhost:5432/n2in
database.userName=n2cug_owner
database.password=n2cug_owner

Ensure these values match the commands executed during database preparation and that the correct database listening port is used (if not the PostgreSQL default).

To automatically install the database elements, execute the following commands:

cd /usr/share/n2cug/db
export DBMAINTAIN_JDBC_DRIVER=/usr/share/dbmaintain/postgresql-42.3.1.jar
/usr/share/3rdparty/dbmaintain/dbmaintain.sh updateDatabase -config dbmaintain.properties
cd -

Additional Users

The instructions in this section include the creation of three database users, as described in database initialisation:

n2cug_owner, who owns and has full permissions on everything in the N2CUG schema. This user is also used during database initialisation and any future updates.
n2cug_web, who is granted individual, targeted permissions for screens operations.
n2cug_ro, who has read-only access to the N2CUG schema.

It is highly recommended that an additional user be created for platform-specific configuration and day-to-day maintenance tasks in order to leave the n2cug_owner user and its wide-ranging permissions alone as much as practicable.

The instructions below set out how to create a suitably-privileged operational database user. They assume that you have assigned the username n2cug_oper, but this name can be altered as required.

All instructions below are intended to be executed as the database superuser (normally postgres).

Create the new user. You will be prompted for a password:

createuser -P n2cug_oper

Update the user’s role and grants, updating the database name if necessary:

psql -d n2in

-- Update initial permissions.
ALTER ROLE n2cug_oper LOGIN;
ALTER ROLE n2cug_oper SET search_path TO n2cug,public;
GRANT USAGE ON SCHEMA n2cug TO n2cug_oper;

-- Grant permission for sequence creation.
GRANT ALL ON SEQUENCE n2cug.cug_group_cug_group_id_seq TO n2cug_oper;
GRANT ALL ON SEQUENCE n2cug.cug_group_number_list_status_group_status_id_seq TO n2cug_oper;
GRANT ALL ON SEQUENCE n2cug.cug_user_cug_user_id_seq TO n2cug_oper;
GRANT ALL ON SEQUENCE n2cug.cug_user_number_list_status_user_status_id_seq TO n2cug_oper;
GRANT ALL ON SEQUENCE n2cug.number_entry_number_entry_id_seq TO n2cug_oper;
GRANT ALL ON SEQUENCE n2cug.number_list_number_list_id_seq TO n2cug_oper;

-- Grant premission on tables.
GRANT INSERT ON TABLE audit.n2cug_audit TO n2cug_oper;
GRANT ALL ON TABLE n2cug.cug_group TO n2cug_oper;
GRANT ALL ON TABLE n2cug.cug_user TO n2cug_oper;
GRANT ALL ON TABLE n2cug.number_list TO n2cug_oper;
GRANT ALL ON TABLE n2cug.number_entry TO n2cug_oper;
GRANT ALL ON TABLE n2cug.cug_group_number_list_status TO n2cug_oper;
GRANT ALL ON TABLE n2cug.cug_user_number_list_status TO n2cug_oper;
\q

Firewall

The firewall (if any) on the DB node must be updated to allow:

Inbound database requests on the listening PostgreSQL port(s).
If more than one database instance is planned, outbound database requests to all other DB instance(s).

The exact commands to do this will depend both on the firewall on your platform and also which port(s) are in use. For example, to allow the default PostgreSQL port when using firewalld, the commands might be:

firewall-cmd --zone=public --add-port=5432/tcp --permanent
service firewalld restart