Postgres installation for Orchesto

The database is used to hold a registry over objects and associated meta-data passed through and managed by Orchesto.

Requirements

• A Postgres database instance, accessible from the host running Orchesto
• At least version 9.5
• Collate=C set for the database cluster
• A database user with create database rights (or the database created beforehand)
• A login user to the target system with sudo rights

Installation options

• A docker instance
• Installed natively in the server OS
• In the same or a separate machine as where Orchesto is running

Best practise

The following is not decribed in this installation guide, but required in an enterprise setup.

• Include a database backup and disaster recover procedure
  • see https://www.postgresql.org/docs/9.6/backup.html
    • A high-availability (HA) setup with Postgres streaming replication from primary to a secondary node
  • The Postgres documentation lists a number of setup options, like Write-Ahead Log Shipping / Hot standby / Failover
  • Postgres Automated Failover (PAF) , an open source HA based on ClusterLabs Pacemaker / Corosync solution.
  • See also https://clusterlabs.github.io/PAF/

Connecting Orchesto to the Postgres database

After installing and configuring the database server, the connection parameters need to be set in the Orchesto config. It is possible to either use an environment variable, or set it as a config file passed as a start flag.

• Example of a config file content:

  • postgres://orchesto:SecretPassword@localhost:5432/orchesto?sslmode=disable

  • When starting the orchesto gateway, use the flag --dsn-file* path/file_name***

• Example of giving the postgres DSN as an environment variable

  • ORCHESTO_DSN="postgres://orchesto:SecretPassword@localhost:5432/orchesto?sslmode=disable"
  • Whith the following meaning:
    database_type://User:Password@HostName:Port/Database?sslmode
  • If running Orchesto as a Docker container, environment variables are give with the docker flag -e ORCHESTO_DSN=.

• The Password field can be left blank, if no password is set

• The Port (5432) should be changed to the correct port used by the Postgres engine

Postgres installed natively in a Linux OS

Installation on Ubuntu 18.04

• Installation of Postgres 10 from the standard apt repository
  • sudo apt install postgresql
• Init the DB
  • Ubuntu with version 10 installed

sudo /usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/data

Installation on Centos 7

• The current version of PostgreSQL in CentOS repository is 9.2, so we need to install a later version like 9.6 with RPM package

  • Download a later version (e.g., 9.6) from https://www.postgresql.org/download/linux/redhat/

  • Example:

    • sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    • sudo yum install postgresql96 postgresql96-server
    • The name of the package might differ depending on the version installed

    sudo /usr/pgsql-9.6/bin/postgres -V
    postgres (PostgreSQL) 9.6.15

• Inititialize the database

  • sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb

Configure the listening ports and interfaces

• Become the user postgres
  sudo su
su - postgres

• The databases and configuration files are in the Postgres users' home folder, normally /var/lib/pgsql/<version>/data
  * Example: /var/lib/pgsql/9.6/data

  • Update the postgresql.conf if needees a need to change the listenig port or interfaces:
    listen_addresses= and port=
    The default is to listen on the localhost interface and port 5432.

• Start the Postgres server and run enable to start at system reboot

sudo systemctl start postgresql-9.6
sudo systemctl enable postgresql-9.6

Create db-users (roles), database and set passwords

• It is advisable to set a password on the Postgres db user. The default is blank.
• The database user orchesto will own a database named orchesto created with LOCALE C and UTF8 encoding.

su - postgres
psql -c "ALTER ROLE postgres WITH PASSWORD 'StrongPassword';" 
psql -c "CREATE ROLE orchesto WITH LOGIN PASSWORD 'SecretPassword';"
psql -c "CREATE DATABASE orchesto with OWNER = orchesto LC_CTYPE 'C' LC_COLLATE 'C'  ENCODING=UTF8 TEMPLATE=template0;"

psql -c "\l"

List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 orchesto  | orchesto | UTF8     | C           | C           |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

Exit the postgres user shell exit

Force db users to use passwords

Edit the file pg_hba.conf in the Postgres data directory. See the man page for details.

• Example: sudo vim /var/lib/pgsql/9.6/data/pg_hba.conf

• The last part of the file contains definition of authentication method.

  • Change/add to Method md5 for the connection to the orchesto database, connecting from local host in the following way:

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

• In addition, if a password is set for the Postgres user, (as it is in This guide) and psql command is run from the local host.

  • Setting peer in the METHOD column allows Postgres commands to be run without credentials.
  • Setting Md5 in the METHOD column requires a password to be used.

local   all             all                                     md5

Firewall settings

In order to secure the Postgres instance, configure the firewall (firewalld in Centos, ufw in Ubuntu) to either block access from remote hosts or allow depending on the needs.

Postgres running as a docker instance

1. Pull the Postgres image
2. Create the Postgres docker container
   • initialize and create a peristent volume and set a password for the superuser postgres
3. Create a user and database to be used by the Orchesto application
   • Remember to add a database backup procedure

Pull Docker images

sudo docker pull postgres

sudo docker inspect postgres |grep "PG_VERSION"

The inspect command shows which version of the Postgress image is pulled. The requirements are that the version is at least 9.6

Start the Postgres docker container, initialize and set a password for the Postgres superuser

Tip

If both the Orchesto gateway and the Postgres database are Docker containers in the same Docker host, they must be attached to a shared network.
The easiest way is to connect the containers to the Docker hosts network as follows:
--network host

• If running the Docker Postgres container and Orchesto as a Docker container in the same host
  sudo docker run --name orchesto-postgres --network host -e POSTGRES_PASSWORD=StrongPassword -e POSTGRES_INITDB_ARGS="--locale=C --encoding=UTF8" -d -p 5432:5432 -v pgdata:/var/lib/postgresql/data postgres

• If Orchesto is not running as a Docker container in the same host, exclude the --network host flag.
  sudo ocker run --name orchesto-postgres -e POSTGRES_PASSWORD=StrongPassword -e POSTGRES_INITDB_ARGS="--locale=C --encoding=UTF8" -d -p 5432:5432 -v pgdata:/var/lib/postgresql/data postgres

When started, the container should automatically create a persistent container volume named pgdata. It can be checked with this command:

sudo docker volume ls

There should be a volume listes as follows:

DRIVER              VOLUME NAME
local               pgdata

• check that Postgres is running using the command docker ps
  The output will show:

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
ae9e854828da        postgres            "docker-entrypoint.s…"   1 hours ago         Up 1 hours                              orchesto-postgres

Create a database user and database for the Orchesto application

• These set of commands will access the running Docker instance of Postgres using a psql shell

  • If the Postgres instance is already configured with a password for the database role postgres, then there is a password prompt.
  • The password in our example is StrongPassword, but should of cource be changed to something else.

• Start an interactive shell to the running Postgres container.
  sudo docker exec -it orchesto-postgres psql -U postgres

• This will automatically start the psql command in a shell, with the output looking as follows:

Password for user postgres:
psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=#

• Execute the required Postgres commands to create a Role and a Database

ALTER ROLE postgres WITH PASSWORD 'StrongPassword';

CREATE ROLE orchesto WITH LOGIN PASSWORD 'SecretPassword';

CREATE DATABASE orchesto 
with OWNER = orchesto 
LC_CTYPE 'C' LC_COLLATE 'C'  ENCODING=UTF8 
TEMPLATE=template0;

• The output from the three commands should say:

ALTER ROLE
CREATE ROLE
CREATE DATABASE

• Check that the database is created as expected at the postgres=# prompt:

 \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 orchesto  | orchesto | UTF8     | C       | C     |
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)

• Exit back do the HOST shell:
  \q

Perform additional best practise configurations

As described in the previous chapter, the Postgres database used by Orchesto needs to be secured, backed up and preferably configured in a HA environment for a production environment.

The initial settings for database access is probably to unrestricted. The settings is controlled in the file pg_hba.conf located in the persistent volume.

• Edit the file sudo vim /var/lib/docker/volumes/pgdata/_data/pg_hba.conf
• Set the Method to md5 to force passwords for all access.

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

host all all all md5

The last line in the file defines accessing the postgres db remotely, and should should read md5 to require a valid password.

• Restart the Postgres Docker container for the security changes to take effect

How to restart the Postgres Docker container

• The Postgres Docker Container can now be stopped and started with:

sudo docker container stop orchesto-postgres
sudo docker container start orchesto-postgres

• Since the database is configured with persistent storage, the database previously created will be left intact. Verify by listing the databases, using the newly created database role orchesto. The password in our example was SecretPassword

sudo docker exec -i orchesto-postgres psql -U orchesto -c "\l"

Note

If You have configured the pg_hba.conf file with connection authentication method "md5" instead of the default "trust" , You will be prompted for the orchesto database user password.