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/
- see https://www.postgresql.org/docs/9.6/backup.html
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.
- Update the postgresql.conf if needees a need to change the listenig port or interfaces:
-
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
- Pull the Postgres image
- Create the Postgres docker container
- initialize and create a peristent volume and set a password for the superuser postgres
- 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.