Skip to content

Installation

Note

As a reminder, switching to root on Debian machines must be done with the following command:

1
su -

The instructions provided on this page should be followed for the installation of all nodes in the PostgreSQL cluster.

Systems settings

Connection to the machine

By default, there are two accounts on virtual appliances: a user account and a super-user account.

  • User account
    • Login : systancia
    • Password: systnci
  • Super-user account
    • Login : root
    • Password: systnci

Connect to the machine in console mode.

Note

The default keyboard layout is QWERTY.

Modification of the keyboard layout

You can change the keyboard layout with the following command line:

1
dpkg-reconfigure keyboard-configuration

A menu appears to allow you to choose another keyboard layout.

Then use the following command line to apply and save the settings:

1
setupcon -k --save

The settings will take effect immediately after this command is executed.

Configuration of the network

The virtual appliance may not receive a network configuration when it starts up for the following reasons:

  • The network interface name is not ens192: the appliance is configured for DHCP only on the ens192 interface
  • No DHCP server is available

To check the network interface name, its status, and the assigned IP addresses, run the following command as root:

1
ip -br a | grep -ve "^lo"
Example

After executing the command, the following response is displayed:

1
ens192      UP      10.100.0.51/24

The name of the network interface in the example is ens192, the interface is active and has the IP address 10.100.0.51/24.

If the name is different from ens192 or if the command does not return any network settings, you will need to configure the network manually using the following commands:

1
2
ip address add IP_ADDRESS/SUBNET dev DEV_NAME
ip route add default via GATEWAY

Replace:

  • IP_ADDRESS by the machine's IP address
  • SUBNET by the subnetwork
  • DEV_NAME name of the previously retrieved network interface
  • GATEWAY by the network gateway
Example

To configure the PostgreSQL node with the following information:

  • IP address: 10.100.0.51
  • Sub-network: /24 or 255.255.255.0
  • Network interface name: eth0
  • Network gateway: 10.100.0.254

The commands are as follows:

1
2
ip address add 10.100.0.51/24 dev eth0
ip route add default via 10.100.0.254

Note

This network configuration is temporary, therefore any server restart will reset the manual settings made with the ip address and ip route commands.
Permanent configuration will be performed by the PostgreSQL cluster configuration utility.

However, thanks to this network configuration, you can now access the PostgreSQL node via SSH.

Modification of the time zone

By default, the virtual appliance is set to the Europe/Paris time zone.

To change this time zone, first use the following command to retrieve the syntax of available time zones:

1
timedatectl list-timezones

Then use the following command line:

1
timedatectl set-timezone your_time_zone
Example

To set the time zone to London, the following command must be executed:

1
timedatectl set-timezone Europe/London

Check the server's time zone using the following command line:

1
timedatectl

Configuration of an NTP time server

Danger

It is strongly recommended that you configure a time server to keep the system clock up to date: any time difference between PostgreSQL nodes may cause instability or even cause the cluster to stop working.

The necessary steps are described on the NTP configuration page (the configuration is equivalent to that of cyberelements Cleanroom Mediation Controller servers).

Creating a PKI to generate PostgreSQL node certificates

Information

This section is optional if you already have a PKI from which you have been able to generate the certificates indicated in the prerequisites.
This section will present the various steps for creating a PKI and the certificates required for the PostgreSQL cluster. Note that it will not be indicated how to renew them, as the renewal of a certificate involves its re-creation.

Skip this step

Attention!

The procedure described has been validated on a Debian 12 machine with the openssl package. However, any machine with a recent version of OpenSSL (the validated version being 3.0.17) will be able to complete the procedure. Except for Windows or MacOS where the system commands will have to be adapted.

Definition of variables and customizations

This section provides various commands and configuration files that use variables that can be modified.
The table below summarizes all the values and allows you to define them so that you can modify them directly in the commands displayed on this page.

Value Variable Definition
PSQL_IP Virtual IP address of the PostgreSQL cluster.
PSQL_NAME Short name (not the FQDN) associated with the virtual IP address of the PostgreSQL cluster.
PSQL_1_IP IP address of the PostgreSQL cluster node n°1.
PSQL_1_NAME Short name (not the FQDN) of the PostgreSQL cluster node n°1.
PSQL_2_IP IP address of the PostgreSQL cluster node n°2.
PSQL_2_NAME Short name (not the FQDN) of the PostgreSQL cluster node n°2.
PSQL_3_IP IP address of the PostgreSQL cluster node n°3.
PSQL_3_NAME Short name (not the FQDN) of the PostgreSQL cluster node n°3.
CA_ROOT_NAME Name assigned to the root certification authority (CA) of the PKI.

Preparation

The following actions will be performed in a new directory in order to gather all the new files in a single directory. Any directory can be used, but its access must be limited as much as possible. *Log in to PostgreSQL node n°1 as root and run the following commands:

1
2
3
mkdir /root/psql-certificates/
chmod 600 /root/psql-certificates/
cd /root/psql-certificates/

Creation of the root certification authority

Run the following command to create a new root CA. You will be prompted for a password to protect the CA:

1
openssl req -x509 -newkey rsa:4096 -days 3650 -sha512 -keyout CA_ROOT_NAME.key -out CA_ROOT_NAME.crt -subj "/CN=CA_ROOT_NAME" -extensions v3_ca -addext "subjectKeyIdentifier=hash" -addext "authorityKeyIdentifier=keyid:always,issuer:always" -addext "basicConstraints=critical,CA:true" -addext "keyUsage=critical,digitalSignature,cRLSign,keyCertSign"

Replace CA_ROOT_NAME with the name of the root CA if you haven't customized it here.

Creation of certificates

The commands required to generate certificates for PostgreSQL nodes are as follows:

1
2
3
openssl genrsa -out PSQL_1_NAME.key 4096
openssl req -new -key PSQL_1_NAME.key -out PSQL_1_NAME.csr -subj "/CN=PSQL_1_NAME"
openssl x509 -req -in PSQL_1_NAME.csr -CA CA_ROOT_NAME.crt -CAkey CA_ROOT_NAME.key -CAcreateserial -out PSQL_1_NAME.crt -days 1825 -sha512 -extfile <(printf "subjectKeyIdentifier=hash\nauthorityKeyIdentifier=keyid,issuer\nbasicConstraints=critical,CA:FALSE\nkeyUsage=critical,digitalSignature\nextendedKeyUsage=serverAuth,clientAuth\nsubjectAltName=DNS:PSQL_NAME,DNS:PSQL_1_NAME,IP:PSQL_IP,IP:PSQL_1_IP")

Replace CA_ROOT_NAME, PSQL_IP, PSQL_NAME, PSQL_1_IP and PSQL_1_NAME if you haven't customized it here.

1
2
3
openssl genrsa -out PSQL_2_NAME.key 4096
openssl req -new -key PSQL_2_NAME.key -out PSQL_2_NAME.csr -subj "/CN=PSQL_2_NAME"
openssl x509 -req -in PSQL_2_NAME.csr -CA CA_ROOT_NAME.crt -CAkey CA_ROOT_NAME.key -CAcreateserial -out PSQL_2_NAME.crt -days 1825 -sha512 -extfile <(printf "subjectKeyIdentifier=hash\nauthorityKeyIdentifier=keyid,issuer\nbasicConstraints=critical,CA:FALSE\nkeyUsage=critical,digitalSignature\nextendedKeyUsage=serverAuth,clientAuth\nsubjectAltName=DNS:PSQL_NAME,DNS:PSQL_2_NAME,IP:PSQL_IP,IP:PSQL_2_IP")

Replace CA_ROOT_NAME, PSQL_IP, PSQL_NAME, PSQL_2_IP and PSQL_2_NAME if you haven't customized it here.

1
2
3
openssl genrsa -out PSQL_3_NAME.key 4096
openssl req -new -key PSQL_3_NAME.key -out PSQL_3_NAME.csr -subj "/CN=PSQL_3_NAME"
openssl x509 -req -in PSQL_3_NAME.csr -CA CA_ROOT_NAME.crt -CAkey CA_ROOT_NAME.key -CAcreateserial -out PSQL_3_NAME.crt -days 1825 -sha512 -extfile <(printf "subjectKeyIdentifier=hash\nauthorityKeyIdentifier=keyid,issuer\nbasicConstraints=critical,CA:FALSE\nkeyUsage=critical,digitalSignature\nextendedKeyUsage=serverAuth,clientAuth\nsubjectAltName=DNS:PSQL_NAME,DNS:PSQL_3_NAME,IP:PSQL_IP,IP:PSQL_3_IP")

Replace CA_ROOT_NAME, PSQL_IP, PSQL_NAME, PSQL_3_IP and PSQL_3_NAME if you haven't customized it here.

The first command creates a new key, the second generates a certificate signing request CSR file, and the third creates the certificate itself.

The certificate still needs to be converted to P12, which will require you to set a password:

1
openssl pkcs12 -chain -CAfile CA_ROOT_NAME.crt -export -inkey PSQL_1_NAME.key -in PSQL_1_NAME.crt -out PSQL_1_NAME.p12

1
openssl pkcs12 -chain -CAfile CA_ROOT_NAME.crt -export -inkey PSQL_2_NAME.key -in PSQL_2_NAME.crt -out PSQL_2_NAME.p12

1
openssl pkcs12 -chain -CAfile CA_ROOT_NAME.crt -export -inkey PSQL_3_NAME.key -in PSQL_3_NAME.crt -out PSQL_3_NAME.p12

The files required to install the PostgreSQL cluster can be found here (please replace the variables CA_ROOT_NAME, PSQL_1_NAME, PSQL_2_NAME, and PSQL_3_NAME with the values you selected earlier):

  • /root/psql-certificates/CA_ROOT_NAME.crt: certificate of the root CA
  • /root/psql-certificates/PSQL_1_NAME.p12: certificate for the PostgreSQL node n°1
  • /root/psql-certificates/PSQL_2_NAME.p12: certificate for the PostgreSQL node n°2
  • /root/psql-certificates/PSQL_3_NAME.p12: certificate for the PostgreSQL node n°3

Installation of a PostgreSQL cluster

Information

The following steps must be performed on all three PostgreSQL nodes.

Preparation for the configuration of the PostgreSQL cluster

It is necessary to establish an SFTP or SCP connection in order to send to the appliance:

  • The certificate from the certification authority that signed the certificates for the PostgreSQL nodes
  • The PostgreSQL node certificate in P12 format

Please note that SFTP or SCP connections cannot be initialized using the root account. In addition, files can simply be placed in /home/systancia/.

It is necessary to move these files to a new directory:

1
2
3
mkdir /etc/ssl/certs/cleanroom/
mv /home/systancia/PSQL_?_NAME.p12 /etc/ssl/certs/cleanroom/
mv /home/systancia/ROOT_CA_NAME.crt /etc/ssl/certs/cleanroom/

Replace:

  • PSQL_?_NAME with the name of the certificate intended for the PostgreSQL node being installed
  • ROOT_CA_NAME with the name of the root certificate authority's certificate
1
2
3
mkdir /etc/ssl/certs/cleanroom/
cp /root/psql-certificates/PSQL_1_NAME.p12 /etc/ssl/certs/cleanroom/
cp /root/psql-certificates/CA_ROOT_NAME.crt /etc/ssl/certs/cleanroom/

The commands above must have been customized according to the customization settings. 

1
2
3
mkdir /etc/ssl/certs/cleanroom/
mv /home/systancia/PSQL_2_NAME.p12 /etc/ssl/certs/cleanroom/
mv /home/systancia/CA_ROOT_NAME.crt /etc/ssl/certs/cleanroom/

The commands above must have been customized according to the customization settings. 

1
2
3
mkdir /etc/ssl/certs/cleanroom/
mv /home/systancia/PSQL_3_NAME.p12 /etc/ssl/certs/cleanroom/
mv /home/systancia/CA_ROOT_NAME.crt /etc/ssl/certs/cleanroom/

The commands above must have been customized according to the customization settings.

Configuration of the PostgreSQL node

To start initializing the node configuration, the following commands must be executed:

1
2
cd /etc/ssl/certs/cleanroom/
ConfigureSystanciaVault.py

The second command will launch a utility that will require the following information:

  1. New passwords for the root and systancia local users
  2. Name of the PostgreSQL node (not the FQDN)
  3. The IP address of the PostgreSQL node, the name of its network interface, the default network gateway to use, as well as the DNS servers and DNS suffix
  4. The version of PostgreSQL; we recommend using version 15
  5. The access port to the PostgreSQL cluster; we recommend using port 5432
  6. Password for the PostgreSQL account used for replication and for pg_rewind
  7. Name and IP address of a second PostgreSQL node
  8. Name and IP address of the last PostgreSQL node
  9. Virtual IP address of the PostgreSQL cluster
  10. The IP addresses (separated by commas) of the cyberelements Cleanroom Edge Gateways or any other machines that need to connect to the cluster
  11. Name and password of the PostgreSQL user account
  12. The IP addresses or networks (separated by commas) authorized to connect via SSH to PostgreSQL nodes
  13. Certificate information for the PostgreSQL node (node certificate in P12 format + root CA certificate)

After entering this information, a summary of all your entries will be provided before validation.
After confirming the information provided, a message will appear regarding changes to the /etc/ssh/sshd_config file. Please select the default option, keep the local version currently installed.

In order to apply the configuration, a restart is required:

1
reboot
Tip for speeding up the installation of PostgreSQL nodes n°2 and n°3

After installing PostgreSQL node n°1, you can retrieve the configuration settings used to set it up from the /etc/cleanroom-vault.ini file.

Example of the /etc/cleanroom-vault.ini file

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
[local]
name = PSQL-1
ip = 172.16.10.51

[cluster]
server1 = PSQL-2,172.16.10.52
server2 = PSQL-3,172.16.10.53

[ssl]
ca = MY-ROOT-CA.crt
client_cert = /etc/cleanroomvault.crt
client_key = /etc/cleanroomvault.key
peer_cert = /etc/cleanroomvault.crt
peer_key = /etc/cleanroomvault.key

[postgresql]
port = 5432
cluster = cleanroomvault5
version = 15

[patroni]
api_port = 8008
replication_password = R3pl1cationP@SSw0rd
rewind_password = Rew1ndP@SSw0rd

[vip]
interface = ens192
ip = 172.16.10.50

[cleanroom]
gw_ips = 172.16.10.10, 172.16.10.11
db_user = DbUser
db_password = DbP@SSw0rd

[networking]
interface = ens192
ip = 172.16.10.51/24
gateway = 172.16.10.254
dns = 172.16.10.100
dnssuffix = domain.local

[ssh]
allow = 10.0.20.0/24

Simply modify the highlighted lines with the values of the PostgreSQL node being installed, based on the file you retrieved from PostgreSQL node n°1.

Danger

If you reuse the example file, you will need to modify all of the following highlighted values according to your environment:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
[local]
name = PSQL-1
ip = 172.16.10.51

[cluster]
server1 = PSQL-2,172.16.10.52
server2 = PSQL-3,172.16.10.53

[ssl]
ca = MY-ROOT-CA.crt
client_cert = /etc/cleanroomvault.crt
client_key = /etc/cleanroomvault.key
peer_cert = /etc/cleanroomvault.crt
peer_key = /etc/cleanroomvault.key

[postgresql]
port = 5432
cluster = cleanroomvault5
version = 15

[patroni]
api_port = 8008
replication_password = R3pl1cationP@SSw0rd
rewind_password = Rew1ndP@SSw0rd

[vip]
interface = ens192
ip = 172.16.10.50

[cleanroom]
gw_ips = 172.16.10.10, 172.16.10.11
db_user = DbUser
db_password = DbP@SSw0rd

[networking]
interface = ens192
ip = 172.16.10.51/24
gateway = 172.16.10.254
dns = 172.16.10.100
dnssuffix = domain.local

[ssh]
allow = 10.0.20.0/24

Additional configurations for patronictl

To enable the use of PostgreSQL cluster management commands with patronictl, there are still a few commands that need to be run as root on the PostgreSQL node:

1
2
3
4
sed -i "s/2380/2380, 8008/g" /etc/nftables.conf
systemctl reload nftables
cp /etc/patroni/ca.crt /usr/local/share/ca-certificates/PSQL-CA.crt
update-ca-certificates

Creation of the default database required for installing cyberelements Cleanroom

When installing cyberelements Cleanroom, you must first create a database named default.
To do this, fill out the following table to customize the command you will need to run from any single node in the PostgreSQL cluster:

Value Variable Definition
PSQL_VIP Virtual IP address of the PostgreSQL cluster.
DB_USER The database username specified during PostgreSQL node configuration

Command to be executed from any of the nodes in the PostgreSQL cluster:

1
createdb -h PSQL_VIP -U DB_USER default