DB Node Installation

Overall Installation Steps

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

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

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 12 of the PostgreSQL server is required for the N2ACD 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

Note that if an LDB or RDB instance is to be co-hosted with a DB instance, a separate PostgreSQL server must be used on a different port for each instance, as described in the database type instructions.

DBMaintain

N2ACD DB nodes use the third-party tool DBMaintain to manage the database over time. This is generally only required on the primary DB node.

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

Perl

The N2ACD DB node requires several Perl packages to be available prior to installation. The package names may vary depending on your OS type:

Note that packages marked with an asterisk must have the XX in their name changed to match your installed PostgreSQL version number.

For each of these packages (or with all as one command), execute the instructions specific to your operating system:

Lua

Each N2ACD DB instance requires Lua to be available at version 5.2+. Specifically, both lua and luac must be present in the PATH for all users. Consult your operating system documentation to determine the appropriate package to install.

If your distribution does not support Lua at version 5.2+, N-Squared maintains a pre-packaged, separately-installable version of Lua that meets N2ACD’s requirements. Contact N-Squared Support to discuss access to this if required.

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:

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:

Post-Installation Steps

Note: If automatic failover is desired for the database solution it is recommended that all tasks from DB Failover Installation are completed before any DML changes are performed on the target database.

Database Initialisation

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

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

• Service data schema: n2acd
• Audit information schema: audit
• Database schemas owner: n2acd_owner
• Runtime read-only access user: n2acd_ro
• GUI access user: n2acd_web

These instructions assume that you will use the following values for N2ACD 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.

1. Create the required users for database administration, runtime data retrieval, and GUI access. You will be prompted to enter a password for each user.
   
   Note that the administrative n2acd_owner is created with superuser privileges due to requiring the plperlu language for function creation; these instructions include steps to revoke this superuser if required once installation is complete. Whenever database updates are performed with dbmaintain, the configured user must have superuser privileges at the time the update is applied.

createuser -P -l -s n2acd_owner
createuser -P -l n2acd_web
createuser -P -l n2acd_ro
createuser -P -l n2acd_auditor

2. If the database does not already exist, create it.

createdb -O n2acd_owner n2in

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

psql n2in
CREATE SCHEMA IF NOT EXISTS audit AUTHORIZATION n2acd_owner;
CREATE SCHEMA IF NOT EXISTS n2acd AUTHORIZATION n2acd_owner;
\q

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

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

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

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

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

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

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

psql -h localhost -d n2in -U n2acd_owner
GRANT USAGE ON SCHEMA n2acd TO n2acd_web;
GRANT USAGE ON SCHEMA n2acd TO n2acd_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA n2acd GRANT SELECT ON TABLES TO n2acd_ro;
ALTER ROLE n2acd_auditor SET search_path TO audit,public;
GRANT USAGE ON SCHEMA audit TO n2acd_auditor;
GRANT SELECT ON TABLE audit.n2acd_audit TO n2acd_auditor;
\q

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

psql n2in
CREATE EXTENSION plperl;
CREATE EXTENSION plperlu;
\q

Database Creation

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

/usr/share/n2acd/db/servicedb/dbmaintain.properties

In the file, locate the following section:

database.url=jdbc:postgresql://REPLACE_WITH_DB_ADDRESS:REPLACE_WITH_DB_PORT/n2in
database.userName=n2acd_owner
database.password=REPLACE_WITH_DB_PASSWORD

Update these values to match the commands executed during database preparation. Note that the user selected must have superuser privileges every time dbmaintain is executed.

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

cd /usr/share/n2acd/db
export DBMAINTAIN_JDBC_DRIVER=/usr/share/dbmaintain/postgresql-42.3.1.jar
/usr/share/dbmaintain/dbmaintain.sh updateDatabase -config servicedb/dbmaintain.properties
cd -

If required, alter the n2acd_owner role to revoke superuser privileges once the database elements have been created. As noted above, this privilege must be present for whichever user is configured to execute database updates for future software releases.

psql n2in
ALTER USER n2acd_owner WITH NOSUPERUSER;
\q

Database Configuration

N2ACD includes a database function that requires a PostgreSQL environment variable.

To add this variable, access your PostgreSQL service configuration. This will differ depending on whether your OS uses systemd or not.

Update Database Configuration With systemd

Edit the service definition for the PostgreSQL database server. Note that the XX in the command must be changed to match your PostgreSQL server version number.

sudo systemctl edit postgresql-XX.service

Add the required environment variables to the service definition, updating the LUAC_PATH value as required for the location of the Lua compiler on your system:

[Service]
Environment="N2ACD_LUA_GENERATOR=/usr/share/n2acd/lib/N2/Flow/LuaGenerator.pm"
Environment="LUAC_PATH=/path/to/luac"

Restart the PostgreSQL service:

sudo systemctl restart postgresql

Update Database Configuration Without systemd

Add the environment variables for the PostgreSQL database server. Note that the XX in the command must be changed to match your PostgreSQL server version number, and the LUAC_PATH value must be changed as required for the location of the Lua compiler on your system:

sudo echo "N2ACD_LUA_GENERATOR='/usr/share/n2acd/lib/N2/Flow/LuaGenerator.pm'" >> /etc/postgresql/XX/main/environment
sudo echo "LUAC_PATH='/path/to/luac'" >> /etc/postgresql/XX/main/environment

Restart the PostegreSQL service following your normal procedure.

Additional Users

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

• n2acd_owner, who owns and has full permissions on everything in the N2ACD schema. This user is also used during database initialisation and any future updates.
• n2acd_web, who is granted individual, targeted permissions for screens operations.
• n2acd_ro, who has read-only access to the N2ACD 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 n2acd_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 n2acd_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 -l n2acd_oper

Update the user’s role and grants:

psql
-- Update initial permissions.
ALTER ROLE n2acd_oper SET search_path TO n2acd,public;
GRANT USAGE ON SCHEMA n2acd TO n2acd_oper;
GRANT SELECT ON ALL TABLES IN SCHEMA n2acd TO n2acd_oper;

-- Grant permission for standard configuration set functions.
GRANT EXECUTE ON FUNCTION n2acd.s_config_set TO n2acd_oper;
GRANT EXECUTE ON FUNCTION n2acd.s_config_set_insert TO n2acd_oper;
GRANT EXECUTE ON FUNCTION n2acd.s_config_set_update TO n2acd_oper;
GRANT EXECUTE ON FUNCTION n2acd.s_config_set_delete TO n2acd_oper;

-- Grant permission for additional configuration set functions.
GRANT EXECUTE ON FUNCTION n2acd.s_config_set_service_loader TO n2acd_oper;
GRANT EXECUTE ON FUNCTION n2acd.s_config_set_scp_service_loader_update TO n2acd_oper;
GRANT EXECUTE ON FUNCTION n2acd.s_config_set_sip_service_loader_update TO n2acd_oper;
GRANT EXECUTE ON FUNCTION n2acd.s_config_set_number_portability TO n2acd_oper;
GRANT EXECUTE ON FUNCTION n2acd.s_config_set_scp_number_portability_update TO n2acd_oper;
GRANT EXECUTE ON FUNCTION n2acd.s_config_set_sip_number_portability_update TO n2acd_oper;
\q

Refer to the configuration instructions for N2ACD configuration sets and service loaders for more information.

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

Database Maintenance

Partition Maintenance

The N2ACD auditing tables use partitioning to manage the history of data stored in the database. Database table partitioning ensures that under normal BAU activity audit data does not, over time, grow beyond planned sizing limits.

Database partitioning must be actively managed as the postgres database does not automate the creation and removal of database partitions. Active management is performed by a timer-based systemd service on one of the database nodes.

The service is installed automatically by the n2acd-db package. Follow these steps on installation to ensure that it is enabled on the primary database node:

First, use the systemd override method to set the correct PGPORT for connection. As root:

systemctl edit n2acd-db-maintain-n2acd-database

In the resulting file, define the environment variables to pass through:

Environment=PGPORT=5434

Set the PGPORT correctly for the installed environment. Save the override file, and then enable the service:

systemctl enable n2acd-db-maintain-n2acd-database.timer
systemctl start n2acd-db-maintain-n2acd-database.timer

Systemd timers can be reviewed using:

systemctl list-timers --all

Note that partition maintenance is run using a direct psql command which assumes psql n2in will log in without a username password explicity supplied. For this reason the script is configured to run as postgres.