Security Server Installation Guide for Ubuntu

X-ROAD 7

Version: 2.47
Doc. ID: IG-SS

This document is licensed under the Creative Commons Attribution-ShareAlike 3.0 Unported License. To view a copy of this license, visit http://creativecommons.org/licenses/by-sa/3.0/

• License
• 1 Introduction
  • 1.1 Target Audience
  • 1.2 Terms and abbreviations
  • 1.3 References
• 2 Installation
  • 2.1 Prerequisites to Installation
  • 2.2 Reference Data
    • 2.2.1 Network Diagram
    • 2.3.1 RIA IP's for Whitelisting
  • 2.3 Requirements for the Security Server
  • 2.4 Preparing OS
  • 2.5 Setup Package Repository
  • 2.6 Remote Database Setup (optional)
  • 2.7 Disable the Messagelog Addon before Installation (optional)
  • 2.8 Security Server Installation
  • 2.9 Post-Installation Checks
  • 2.10 Installing the Support for Hardware Tokens
  • 2.11 Installing the Support for Environmental Monitoring
• 3 Security Server Initial Configuration
  • 3.1 Prerequisites
  • 3.2 Reference Data
  • 3.3 Configuration
  • 3.4 Configuring firewall
    • 3.4.1 Accepting Connections
  • 3.5 Configuring configuration backup encryption
• 4 Installation Error handling
  • 4.1 Cannot Set LC_ALL to Default Locale
  • 4.2 PostgreSQL Is Not UTF8 Compatible
  • 4.3 Could Not Create Default Cluster
  • 4.4 Is Postgres Running On Port 5432?
  • 4.5 Different versions of xroad-* packages after successful upgrade
  • 4.6 ERROR: Upgrade supported from version X.Y.Z or newer
• Annex A Security Server Default Database Properties
• Annex B Default Database Users
• Annex C Deployment Options
  • C.1 General
  • C.2 Local Database
  • C.3 Remote Database
  • C.4 High Availability Setup
  • C.5 Load Balancing Setup
  • C.6 Summary
• Annex D Create Database Structure Manually

The intended audience of this Installation Guide are X-Road Security server system administrators responsible for installing and using X-Road software. The daily operation and maintenance of the Security Server is covered by its User Guide [UG-SS].

The document is intended for readers with a moderate knowledge of Linux server management, computer networks, and the X-Road working principles.

See X-Road terms and abbreviations documentation [TA-TERMS].

1. [UG-SS] X-Road 7. Security Server User Guide. Document ID: UG-SS

2. [TA-TERMS] X-Road Terms and Abbreviations. Document ID: TA-TERMS.

3. [UG-SYSPAR] X-Road: System Parameters User Guide. Document ID: UG-SYSPAR.

4. [IG-XLB] X-Road: External Load Balancer Installation Guide. Document ID: IG-XLB.

There are multiple alternatives how the Security Server can be deployed. The options are described in Annex C Deployment Options.

The Security Server is officially supported on the following platforms:

• Ubuntu Server 20.04 or 22.04 Long-Term Support (LTS) operating system on a x86-64 platform.
• Red Hat Enterprise Linux (RHEL) 7 and 8 (x86-64).

NB: RIA provides support only for Security Servers which are installed on the Ubuntu operating system.

The software can be installed both on physical and virtualized hardware (of the latter, Xen and Oracle VirtualBox have been tested).

Note: The information in empty cells should be determined before the server’s installation, by the person performing the installation.

Caution: Data necessary for the functioning of the operating system is not included.

It is strongly recommended to protect the security server from unwanted access using a firewall (hardware or software based). The firewall can be applied to both incoming and outgoing connections depending on the security requirements of the environment where the security server is deployed. It is recommended to allow incoming traffic to specific ports only from explicitly defined sources using IP filtering. Special attention should be paid with the firewall configuration since incorrect configuration may leave the security server vulnerable to exploits and attacks.

The table below lists the required connections between different components.

It is strongly recommended to protect the security server from unwanted access using a firewall (hardware or software based). The firewall can be applied to both incoming and outgoing connections depending on the security requirements of the environment where the security server is deployed. It is recommended to allow incoming traffic to specific ports only from explicitly defined sources using IP filtering. Special attention should be paid with the firewall configuration since incorrect configuration may leave the security server vulnerable to exploits and attacks.

The following network diagram is an example of a simple stand-alone Security Server setup. Attention should be paid when configuring the firewall of your Security Server, as misconfigurations (e.g. exposing port 80/tcp to the public internet) can leave your server vulnerable. IP whitelisting should be used for all ports that are open to the external network.

Allowing incoming connections from the Monitoring Security Server on ports 5500/tcp and 5577/tcp (monitoring server IP's) is necessary for the X-Road Center to be able to monitor the ecosystem and provide statistics and support for Members.

Caution: The enabling of auxiliary services which are necessary for the functioning and management of the operating system (such as DNS, NTP, and SSH) stay outside the scope of this guide.

Minimum recommended hardware parameters:

• the server’s hardware (motherboard, CPU, network interface cards, storage system) must be supported by Ubuntu in general;
• a 64-bit dual-core Intel, AMD or compatible CPU; AES instruction set support is highly recommended;
• 4 GB RAM;
• a 100 Mbps network interface card;
• if necessary, interfaces for the use of hardware tokens.

Requirements to software and settings:

• an installed and configured Ubuntu 20.04 LTS or 22.04 LTS x86-64 operating system;
• if the Security Server is separated from other networks by a firewall and/or NAT, the necessary connections to and from the Security Server are allowed (reference data: 1.4; 1.5; 1.6; 1.7). The enabling of auxiliary services which are necessary for the functioning and management of the operating system (such as DNS, NTP, and SSH) stay outside the scope of this guide;
• if the Security Server has a private IP address, a corresponding NAT record must be created in the firewall (reference data: 1.9).

• Add an X-Road system administrator user (reference data: 1.3) whom all roles in the user interface are granted to. Add a new user with the command

    sudo adduser <username>
  

  User roles are discussed in detail in X-Road Security Server User Guide [UG-SS]. Do not use the user name xroad, it is reserved for the X-Road system user.

• Set the operating system locale. Add following line to the /etc/environment file.

    LC_ALL=en_US.UTF-8
  

• Ensure that the packages locales and software-properties-common are present

    sudo apt-get install locales software-properties-common
  

• Ensure that the locale is available

    sudo locale-gen en_US.UTF-8
  

Add the X-Road repository’s signing key to the list of trusted keys (reference data: 1.2):

curl https://x-tee.ee/packages/test/xroad/xroad.pub | sudo gpg --dearmor -o /etc/apt/keyrings/xroad.gpg

Add X-Road package repository (reference data: 1.1)

echo "deb [signed-by=/etc/apt/keyrings/xroad.gpg] https://x-tee.ee/packages/test/xroad $(lsb_release -sc)-current main" | sudo tee /etc/apt/sources.list.d/xroad.list

Update package repository metadata:

sudo apt update

If you are installing the default setup with local PostgreSQL database and want to enable the messagelog addon, continue at section 2.8. If you need to customize database properties and e.g. use a remote database or disable the messagelog addon, read on.

This is an optional step.

Optionally, the Security Server can use a remote database server. To avoid installing the default local PostgreSQL server during Security Server installation, first install the xroad-database-remote -package.

sudo apt install xroad-database-remote

For the application level backup and restore feature to work correctly, it is important to verify that the local PostgreSQL client has the same or later major version than the remote database server and, if necessary, install a different version of the postgresql-client package (see https://www.postgresql.org/download/linux/ubuntu/)

psql --version
psql (PostgreSQL) 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)

psql -h <database host> -U <superuser> -tAc 'show server_version'
10.16 (Ubuntu 10.16-0ubuntu0.18.04.1)

The Security Server installer can create the database and users for you, but you need to create a configuration file containing the database administrator credentials.

For advanced setup, e.g. when using separate servers for the databases, sharing a database with several Security Servers, or if storing the database administrator password on the Security Server is not an option, you can create the database users and structure manually as described in Annex D Create Database Structure Manually and then continue to section 2.7. Otherwise, perform the following steps:

Create the property file:

sudo touch /etc/xroad.properties
sudo chown root:root /etc/xroad.properties
sudo chmod 600 /etc/xroad.properties

Edit /etc/xroad.properties. See the example below. Replace parameter values with your own.

postgres.connection.password = <database superuser password>
postgres.connection.user = <database superuser name, postgres by default>

Note. If Microsoft Azure database for PostgreSQL is used, the connection user needs to be in format username@hostname.

Before continuing, test that the connection to the database works, e.g.

psql -h <database host> -U <superuser> -tAc 'show server_version'

For additional security, the postgresql.connection.* properties can be removed from the /etc/xroad.properties file after installation (keep the other properties added by the installer).

It is possible to preconfigure the Security Server installation so that the messagelog addon will be automatically disabled after the installation process is done. This also skips the creation of the messagelog database.

In order to skip messagelog database creation and disable the messagelog addon, run the following command to add a boolean value into the debconf database before installing the Security Server

sudo debconf-set-selections <<< 'xroad-addon-messagelog xroad-addon-messagelog/enable-messagelog boolean false'

Issue the following command to install the Security Server packages (use package xroad-securityserver-ee to include configuration specific to Estonia; use package xroad-securityserver-fi to include configuration specific to Finland; use package xroad-securityserver-is to include configuration specific to Iceland):

sudo apt update
sudo apt install xroad-securityserver

Upon the first installation of the packages, the system asks for the following information.

• Account name for the user who will be granted the rights to perform all activities in the user interface (reference data: 1.3).

• Database server URL. Locally installed database is suggested as default but remote databases can be used as well. In case remote database is used, one should verify that the version of the local PostgreSQL client matches the version of the remote PostgreSQL server.

• The Distinguished Name of the owner of the user interface's and management REST API's self-signed TLS certificate (Subject DN) and its alternative names (subjectAltName) (reference data: 1.8; 1.10). The certificate is used for securing connections to the user interface and to the management REST API. The name and IP addresses detected from the operating system are suggested as default values.

  • The Subject DN must be entered in the format:

        /CN=server.domain.tld
    

  • All IP addresses and domain names in use must be entered as alternative names in the format:

        IP:1.2.3.4,IP:4.3.2.1,DNS:servername,DNS:servername2.domain.tld
    

• The Distinguished Name of the owner of the TLS certificate that is used for securing the HTTPS access point of information systems (reference data: 1.8; 1.11). The name and IP addresses detected from the system are suggested as default values.

  • The Subject DN must be entered in the format:

      /CN=server.domain.tld
    

  • All IP addresses and domain names in use must be entered as alternative names in the format:

      IP:1.2.3.4,IP:4.3.2.1,DNS:servername,DNS:servername2.domain.tld
    

The meta-package xroad-securityserver also installs metaservices module xroad-addon-metaservices, messagelog module xroad-addon-messagelog and WSDL validator module xroad-addon-wsdlvalidator. The meta-packages xroad-securityserver-ee, xroad-securityserver-fi, xroad-securityserver-is, and xroad-securityserver-fo install operational data monitoring module xroad-addon-opmonitoring.

N.B. In case configuration specific to Estonia (package xroad-securityserver-ee) is installed, connections from client applications are restricted to localhost by default. To enable client application connections from external sources, the value of the connector-host property must be overridden in the /etc/xroad/conf.d/local.ini configuration file. Changing the system parameter values is explained in the System Parameters User Guide [UG-SS].

The installation is successful if system services are started and the user interface is responding.

• Ensure from the command line that X-Road services are in the running state (example output follows):

  sudo systemctl list-units "xroad-*"
  
  UNIT                           LOAD   ACTIVE SUB     DESCRIPTION
  xroad-addon-messagelog.service loaded active running X-Road Messagelog Archiver
  xroad-base.service             loaded active exited  X-Road initialization
  xroad-confclient.service       loaded active running X-Road confclient
  xroad-monitor.service          loaded active running X-Road Monitor
  xroad-proxy-ui-api.service     loaded active running X-Road Proxy UI REST API
  xroad-proxy.service            loaded active running X-Road Proxy
  xroad-signer.service           loaded active running X-Road signer

• Ensure that the Security Server user interface at https://SECURITYSERVER:4000/ (reference data: 1.8; 1.6) can be opened in a Web browser. To log in, use the account name chosen during the installation (reference data: 1.3). While the user interface is still starting up, the Web browser may display a connection refused -error.

To configure support for hardware security tokens (smartcard, USB token, Hardware Security Module), act as follows.

1. Install the hardware token support module using the following command:

   sudo apt-get install xroad-addon-hwtokens
   

2. Install and configure a PKCS#11 driver for the hardware token according to the manufacturer's instructions.

3. Add the path to the PKCS#11 driver to the file /etc/xroad/devices.ini (as described in the example given in the file).

4. After installing and configuring the driver, the xroad-signer service must be restarted:

   sudo service xroad-signer restart
   

If you are running a high availability (HA) hardware token setup (such as a cluster with replicated tokens) then you may need to constrain the token identifier format such that the token replicas can be seen as the same token. The token identifier format can be changed in /etc/xroad/devices.ini via the token_id_format property (default value: {moduleType}{slotIndex}{serialNumber}{label}). Removing certain parts of the identifier will allow the HA setup to work correctly when one of the tokens goes down and is replaced by a replica. For example, if the token replicas are reported to be on different slots the {slotIndex} part should be removed from the identifier format.

Depending on the hardware token there may be a need for more additional configuration. All possible configurable parameters in the /etc/xroad/devices.ini are described in the next table.

Note 1: Only parameter library is mandatory, all the others are optional. Note 2: The item separator of the type STRING LIST is ",".

The support for environmental monitoring functionality on a Security Server is provided by package xroad-monitor that is installed by default. The package installs and starts the xroad-monitor process that will gather and make available the monitoring information.

During the Security Server initial configuration, the server’s X-Road membership information and the software token’s PIN are set.

Configuring the Security Server assumes that the Security Server owner is a member of the X-Road.

ATTENTION: Reference items 2.1 - 2.3 in the reference data are provided to the Security Server owner by the X-Road central’s administrator.

The Security Server code and the software token’s PIN will be determined during the installation at the latest, by the person performing the installation.

To perform the initial configuration, open the address

https://SECURITYSERVER:4000/

in a Web browser (reference data: 1.8; 1.6). To log in, use the account name chosen during the installation (reference data: 1.3).

Upon first log-in, the system asks for the following information.

• The global configuration anchor file (reference data: 2.1).

  Please verify anchor hash value with the published value.

If the configuration is successfully downloaded, the system asks for the following information.

• The Security Server owner’s member class (reference data: 2.2).

• The Security Server owner’s member code (reference data: 2.3).

  If the member class and member code are correctly entered, the system displays the Security Server owner’s name as registered in the X-Road center.

• Security server code (reference data: 2.4), which is chosen by the Security Server administrator and which has to be unique across all the Security Servers belonging to the same X-Road member.

• Software token’s PIN (reference data: 2.5). The PIN will be used to protect the keys stored in the software token. The PIN must be stored in a secure place, because it will be no longer possible to use or recover the private keys in the token once the PIN has been lost.

It is strongly recommended to protect the Security Server from unwanted access using a firewall (hardware or software based). The firewall can be applied to both incoming and outgoing connections depending on the security requirements of the environment where the Security Server is deployed.

Special attention should be paid with the firewall configuration since incorrect configuration may leave the Security Server vulnerable to exploits and attacks. This type of abuse could result in compromised access to the Security Server and the data that is exchanged through it.

It is recommended to allow incoming traffic to specific ports only from explicitly defined sources using IP filtering. Access for ports 8080, 8443 and 4000 should be especially defined, as these ports are used for making X-Road queries and accessing the user interface.

When installing the Security Server, it is strongly recommended to look over the list of ports at 2.2 Reference Data and define firewall access rules for specific hosts based on their descriptions.

The Security Server has a special [proxy] parameter connector-host which determines the interfaces that the Security Server uses to listen for incoming connections. The default value for this parameter in the default X-Road packages is 0.0.0.0, which makes the Security Server accept connections from any server. For country-specific defaults, please refer to the system parameters documentation.

The parameter can be changed by following the System Parameters guide.

It is possible to automatically encrypt Security Server configuration backups. Security server uses The GNU Privacy Guard (https://www.gnupg.org) for backup encryption and verification. Backups are always signed, but backup encryption is initially turned off. To turn encryption on, please override the default configuration in the file /etc/xroad/conf.d/local.ini, in the [proxy] section (add or edit this section).

[proxy]
backup-encryption-enabled = true
backup-encryption-keyids = <keyid1>, <keyid2>, ...

To turn backup encryption on, please change the backup-encryption-enabled property value to true. By default, backups are encrypted using Security Server's backup encryption key. Additional encryption keys can be imported in the /etc/xroad/gpghome keyring and key identifiers listed using the backup-encryption-keyids parameter. It is recommended to set up at least one additional key, otherwise the backups will be unusable in case Security Server's private key is lost. It is up to Security Server's administrator to check that keys used are sufficiently strong, there are no automatic checks.

Warning. All keys listed in backup-encryption-keyids must be present in the gpg keyring or backup fails.

All these keys are used to encrypt backups so that ANY of these keys can decrypt the backups. This is useful both for verifying encrypted backups' consistency and decrypting backups in case Security Server's backup encryption key gets lost for whatever reason.

To externally verify a backup archive's consistency, Security Server's backup encryption public key has to be exported and imported into external GPG keyring. Note that this can be done only after Security Server has been initialised - the Security Server backup encryption key is generated during initialisation.

To export Security Server's backup encryption public key use the following command:

gpg --homedir /etc/xroad/gpghome --armor --output server-public-key.gpg --export AA/GOV/TS1OWNER/TS1

where AA/GOV/TS1OWNER/TS1 is the Security Server id.

The key can then be moved to an external host and imported to GPG keyring with the following command:

gpg --homedir /your_gpg_homedir_here --import server-public-key.gpg

If running the locale command results in the error message

locale: Cannot set LC_ALL to default locale: No such file or directory,

then the support for this particular language has not been installed. To install it, run the command (the example uses the English language):

sudo apt-get install language-pack-en

Then, to update the system’s locale files, run the following commands (the example uses the US locale):

sudo locale-gen en_US.UTF-8
sudo update-locale en_US.UTF-8

Set operating system locale. Add following line to /etc/environment file:

LC_ALL=en_US.UTF-8

After updating the system’s locale settings, it is recommended to restart the operating system.

If the Security Server installation is aborted with the error message

postgreSQL is not UTF8 compatible,

then the PostgreSQL package is installed with a wrong locale. One way to resolve it is to remove the data store created upon the PostgreSQL installation and recreate it with the correct encoding.

WARNING: All data in the database will be erased!

sudo pg_dropcluster --stop 10 main
LC_ALL="en_US.UTF-8" sudo pg_createcluster --start 10 main

To complete the interrupted installation, run the command

sudo apt-get -f install

If the following error message is displayed during PostgreSQL installation:

Error: The locale requested by the environment is invalid.
Error: could not create default cluster. Please create it manually with pg_createcluster 10 main –start,

use the following command to create the PostgreSQL data cluster:

LC_ALL="en_US.UTF-8" sudo pg_createcluster --start 10 main

The interrupted installation can be finished using

sudo apt-get -f install

If the following error message appears during installation

Is postgres running on port 5432 ?
Aborting installation! please fix issues and rerun with apt-get -f install,

check if any of the following errors occurred during the installation of PostgreSQL.

• Error installing the data cluster. Refer to section “Could not create default cluster”.

• The PostgreSQL data cluster installed during the installation of the Security Server is not configured to listen on port 5432. To verify and configure the listening port, edit the PostgreSQL configuration file in /etc/postgresql/10/main/postgresql.conf. If you change the listening port, the postgresql service must be restarted.

The interrupted installation can be finished using

sudo apt-get -f install

Sometimes, after using sudo apt-get upgrade command, some of the packages are not upgraded. In the following example xroad-securityserver package version is still 6.8.3 although other packages are upgraded to 6.8.5:

# sudo dpkg -l | grep xroad-
ii xroad-addon-messagelog 6.8.5.20160929134539gitfe60f90
ii xroad-addon-metaservices 6.8.5.20160929134539gitfe60f90
ii xroad-addon-wsdlvalidator 6.8.5.20160929134539gitfe60f90
ii xroad-common 6.8.5.20160929134539gitfe60f90
ii xroad-jetty9 6.8.5.20160929134539gitfe60f90
ii xroad-proxy 6.8.5.20160929134539gitfe60f90
ii xroad-securityserver-ee 6.8.3-3-201605131138

apt-get upgrade command doesn’t install new packages - in this particular case new packages xroad-monitor and xroad-addon-proxymonitor installation is needed for upgrade of xroad-securityserver package.

To be sure that packages are installed correctly please use sudo apt upgrade or sudo apt full-upgrade commands.

The following error message may come up during the Security Server upgrade.

ERROR: Upgrade supported from version X.Y.Z or newer

Upgrading the packages from the current version to the target version is not supported directly. The fix is to upgrade the Security Server to the target version step by step.

A guide to upgrading from an older version of X-Road can be found here. Follow the chapter which contains your current X-Road version.

/etc/xroad/db.properties

# connection.url format: jdbc:postgresql://<hostname>:<port>/<database name>

serverconf.hibernate.connection.url = jdbc:postgresql://127.0.0.1:5432/serverconf
serverconf.hibernate.connection.username = serverconf
serverconf.hibernate.connection.password = <randomly generated password>
serverconf.hibernate.connection.driver_class = org.postgresql.Driver
serverconf.hibernate.dialect = ee.ria.xroad.common.db.CustomPostgreSQLDialect
serverconf.hibernate.hikari.dataSource.currentSchema = serverconf,public
serverconf.hibernate.jdbc.use_streams_for_binary = true

messagelog.hibernate.connection.url = jdbc:postgresql://127.0.0.1:5432/messagelog
messagelog.hibernate.connection.username = messagelog
messagelog.hibernate.connection.password = <randomly generated password>
messagelog.hibernate.connection.driver_class = org.postgresql.Driver
messagelog.hibernate.dialect = ee.ria.xroad.common.db.CustomPostgreSQLDialect
messagelog.hibernate.hikari.dataSource.currentSchema = messagelog,public
messagelog.hibernate.jdbc.use_streams_for_binary = true

op-monitor.hibernate.connection.url = jdbc:postgresql://127.0.0.1:5432/op-monitor
op-monitor.hibernate.connection.username = opmonitor
op-monitor.hibernate.connection.password = <randomly generated password>
op-monitor.hibernate.connection.driver_class = org.postgresql.Driver
op-monitor.hibernate.hikari.dataSource.currentSchema = opmonitor,public
op-monitor.hibernate.jdbc.use_streams_for_binary = true

X-Road Security Server has multiple deployment options. The simplest choice is to have a single Security Server with local database. This is usually fine for majority of the cases, but there are multiple reasons to tailor the deployment.

The simplest deployment option is to use a single Security Server with local database. For development and testing purposes there is rarely need for anything else, but for production the requirements may be stricter.

It is possible to use a remote database with Security Server. This option is sometimes used in development and testing when there's need to externalize the database state.

Security server supports a variety of cloud databases including AWS RDS and Azure Database for PostgreSQL. This deployment option is useful when doing development in cloud environment, where use of cloud native database is the first choice.

In production systems it's rarely acceptable to have a single point of failure. Security server supports provider side high availability setup via so called internal load balancing mechanism. The setup works so that the same member / member class / member code / subsystem / service code is configured on multiple Security Servers and X-Road will then route the request to the server that responds the fastest. Note that this deployment option does not provide performance benefits, just redundancy.

Busy production systems may need scalable performance in addition to high availability. X-Road supports external load balancing mechanism to address both of these problems simultaneously. A load balancer is added in front of a Security Server cluster to route the requests based on selected algorithm. This deployment option is extensively documented in [IG-XLB].

The following table lists a summary of the Security Server deployment options and indicates whether they are aimed for development or production use.

Depending on installed components, the Security Server uses one to three databases (catalogs):

• serverconf for storing Security Server configuration (required)
• messagelog for storing message records (optional, but installed by default)
• op-monitor for operational monitoring data (optional)

These databases can be hosted on one database server (default setup), or you can use several servers.

Login to the database server(s) as the superuser (postgres by default) to run the commands, e.g.

psql -h <database host>:<port> -U <superuser> -d postgres

Run the following commands to create the necessary database structures. If necessary, customize the database and role names to suit your environment (e.g when the same database server is shared between several Security Server instances, it is necessary to have separate database names and roles for each server).

serverconf (required)

By default, the database, database user, and schema use the same name of serverconf, and the admin user is named with _admin suffix (e.g. serverconf_admin).

CREATE DATABASE <serverconf_database> ENCODING 'UTF8';
REVOKE ALL ON DATABASE <serverconf_database> FROM PUBLIC;
CREATE ROLE <serverconf_admin_user> LOGIN PASSWORD '<serverconf_admin_user password>';
GRANT <serverconf_admin_user> TO <superuser>;
GRANT CREATE,TEMPORARY,CONNECT ON DATABASE <serverconf_database> TO <serverconf_admin_user>;
\c <serverconf_database>
CREATE EXTENSION hstore;
CREATE SCHEMA <serverconf_schema> AUTHORIZATION <serverconf_admin_user>;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO <serverconf_admin_user>;
CREATE ROLE <serverconf_database_user> LOGIN PASSWORD '<serverconf_database_user password>';
GRANT <serverconf_database_user> TO <superuser>;
GRANT TEMPORARY,CONNECT ON DATABASE <serverconf_database> TO <serverconf_database_user>;
GRANT USAGE ON SCHEMA public TO <serverconf_database_user>;
GRANT USAGE ON SCHEMA <serverconf_schema> TO <serverconf_database_user>;
GRANT SELECT,UPDATE,INSERT,DELETE ON ALL TABLES IN SCHEMA <serverconf_schema> TO <serverconf_database_user>;
GRANT SELECT,UPDATE ON ALL SEQUENCES IN SCHEMA <serverconf_schema> TO <serverconf_database_user>;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA <serverconf_schema> TO <serverconf_database_user>;

messagelog (required by xroad-addon-messagelog)

By default, the database, database user, and schema use the same name of messagelog, and the admin user is named with _admin suffix (e.g. messagelog_admin).

CREATE DATABASE <messagelog_database> ENCODING 'UTF8';
REVOKE ALL ON DATABASE <messagelog_database> FROM PUBLIC;
CREATE ROLE <messagelog_admin_user> LOGIN PASSWORD '<messagelog_admin_user password>';
GRANT <messagelog_admin_user> TO <superuser>;
GRANT CREATE,TEMPORARY,CONNECT ON DATABASE <messagelog_database> TO <messagelog_admin_user>;
\c <messagelog_database>
CREATE SCHEMA <messagelog_schema> AUTHORIZATION <messagelog_admin_user>;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO <messagelog_admin_user>;
CREATE ROLE <messagelog_database_user> LOGIN PASSWORD '<messagelog_database_user password>';
GRANT <messagelog_database_user> TO <superuser>;
GRANT TEMPORARY,CONNECT ON DATABASE <messagelog_database> TO <messagelog_database_user>;
GRANT USAGE ON SCHEMA public TO <messagelog_database_user>;
GRANT USAGE ON SCHEMA <messagelog_schema> TO <messagelog_database_user>;
GRANT SELECT,UPDATE,INSERT,DELETE ON ALL TABLES IN SCHEMA <messagelog_schema> TO <messagelog_database_user>;
GRANT SELECT,UPDATE ON ALL SEQUENCES IN SCHEMA <messagelog_schema> TO <messagelog_database_user>;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA <messagelog_schema> TO <messagelog_database_user>;

op-monitor (optional, required by xroad-opmonitor)

If operational monitoring is going to be installed, run additionally the following commands. Again, the database and role names can be customized to suit your environment. By default, the database is named op-monitor, database user and schema both are named opmonitor, and the admin user is named with _admin suffix (e.g. opmonitor_admin).

CREATE DATABASE <opmonitor_database> ENCODING 'UTF8';
REVOKE ALL ON DATABASE <opmonitor_database> FROM PUBLIC;
CREATE ROLE <opmonitor_admin_user> LOGIN PASSWORD '<opmonitor_admin_user password>';
GRANT <opmonitor_admin_user> TO <superuser>;
GRANT CREATE,TEMPORARY,CONNECT ON DATABASE <opmonitor_database> TO <opmonitor_admin_user>;
\c <opmonitor_database>
CREATE SCHEMA <opmonitor_schema> AUTHORIZATION <opmonitor_admin_user>;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO <opmonitor_admin_user>;
CREATE ROLE <database_user> LOGIN PASSWORD '<opmonitor_database_user password>';
GRANT <opmonitor_database_user> TO <superuser>;
GRANT TEMPORARY,CONNECT ON DATABASE <opmonitor_database> TO <opmonitor_database_user>;
GRANT USAGE ON SCHEMA public TO <opmonitor_database_user>;
GRANT USAGE ON SCHEMA <opmonitor_schema> TO <opmonitor_database_user>;
GRANT SELECT,UPDATE,INSERT,DELETE ON ALL TABLES IN SCHEMA <opmonitor_schema> TO <opmonitor_database_user>;
GRANT SELECT,UPDATE ON ALL SEQUENCES IN SCHEMA <opmonitor_schema> TO <opmonitor_database_user>;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA <opmonitor_schema> TO <opmonitor_database_user>;

Lastly, customize the database connection properties to match the values used when creating the database.

Note. When using Microsoft Azure PostgreSQL, the user names need to be in format username@hostname in the properties files.

Create the configuration file /etc/xroad.properties.

sudo touch /etc/xroad.properties
sudo chown root:root /etc/xroad.properties
sudo chmod 600 /etc/xroad.properties

Edit /etc/xroad.properties and add/update the following properties (if you customized the role names, use your own). The admin users are used to run database migrations during the install and upgrades.

serverconf.database.admin_user = <serverconf_admin_user>
serverconf.database.admin_password = <serverconf_admin_user password>
messagelog.database.admin_user = <messagelog_admin_user>
messagelog.database.admin_password = <messagelog_admin_user password>
op-monitor.database.admin_user = <opmonitor_admin_user>
op-monitor.database.admin_password = <opmonitor_admin_user password>

Create the /etc/xroad/db.properties file

sudo mkdir /etc/xroad
sudo chown xroad:xroad /etc/xroad
sudo chmod 751 /etc/xroad
sudo touch /etc/xroad/db.properties
sudo chmod 0640 /etc/xroad/db.properties
sudo chown xroad:xroad /etc/xroad/db.properties

Edit the /etc/xroad/db.properties file and add/update the following connection properties (if you customized the database, user, and/or role names, use the customized values). The database connection url format is jdbc:postgresql://<database host>:<port>/<database name>

serverconf.hibernate.connection.url = jdbc:postgresql://<database host>:<port>/<serverconf_database>
serverconf.hibernate.connection.username = <serverconf_database_user>
serverconf.hibernate.connection.password = <serverconf_database_user password>
serverconf.hibernate.hikari.dataSource.currentSchema = <serverconf_schema>,public

messagelog.hibernate.connection.url = jdbc:postgresql://<database host>:<port>/<messagelog_database>
messagelog.hibernate.connection.username = <messagelog_database_user>
messagelog.hibernate.connection.password = <messagelog_database_user password>
messagelog.hibernate.hikari.dataSource.currentSchema = <messagelog_schema>,public

op-monitor.hibernate.connection.url = jdbc:postgresql://<database host>:<port>/<opmonitor_database>
op-monitor.hibernate.connection.username = <opmonitor_database_user>
op-monitor.hibernate.connection.password = <opmonitor_database_user password>
op-monitor.hibernate.hikari.dataSource.currentSchema = <opmonitor_schema>,public