4.15. postgresql

By default PostgreSQL is used for all servers and the same role is shared for it. This is optional for Orthanc. There you could also disable this by setting or_use_postgresql to false. Then SQLite would be used. Technically the Tryton kernel used by GNU Health also supports other backends but PostgreSQL is strongly recommended thus this is not configurable inside this project.

The postgresql role installs the necessary packages and creates PostgreSQL users and databases for further use.
Configuration options can be found in inventories/dev/group_vars/gnuhealth/vars.yml prefixed by psql_. For example encoding, locale and timezone can also be set. If PostgreSQL is on a separate system, the database user should have a password and the connection should be encrypted. By default a certificate for TLS (psql_use_cert) and a scram-sha-256 password (psql_use_pw) is created if the variables psql_domain and nginx_application_domain are not identical.
The list psql_hba_rules contains one rule for local UNIX socket access. For remote access uncomment the second line and set the right IP address. It enables remote access for an IP (range), scram-sha-256 passwords, TLS and your newly created database user. This way the risk of undesirable access is reduced.
Finally shared_buffers are set to 40% of available memory which is recommended for performance tuning.

On application level the default URI already contains parameters for verifying the TLS connection (see gh_psql_uri_remote). Generally there are three different ways for applications to communicate to PostgreSQL:

  • UNIX socket (local)

  • TCP port 5432, unencrypted (remote, forbidden by default)

  • TCP port 5432, encrypted (remote)

Unencrypted remote access is forbidden by the line in psql_hba_rules. Changing hostssl to host allows both encrypted and unencrypted access.

This role also contains configuration options and automated tasks for preparing the GNU Health demo database and the Thalamus federation database.

If backups are realized using Barman you can find some relevant configuration options below.

PostgreSQL documentation:
https://www.postgresql.org/docs/14/index.html

  • Parameters:

    • psql_db_users: PostgreSQL users to create

    • psql_dbs: PostgreSQL databases to create, remove or restore by given owner

    • psql_listen_remote: Whether PostgreSQL server is expected to listen to remote connections

    • psql_use_pw: If true use password for users

    • psql_use_cert: If true handle certificate

    • psql_pw: Password for user

    • psql_db_template: Template used for database creation

    • psql_domain: Domain name of the server

    • psql_hba_rules: pg_hba rules to manage access. The only present line allows local access, below you can find remote access and barman backup access

    • psql_shared_buffers: Set shared buffers to increase performance

    • psql_encryption: Password encryption method

    • psql_tls_min: Minimum TLS version to avoid outdated versions with security problems

    • psql_set_encoding: If true set encoding

    • psql_encoding: Encoding value to

    • psql_set_locale: If true set locale

    • psql_locale: Locale value to set

    • psql_set_timezone: If true set timezone inside PostgreSQL

    • psql_timezone: Timezone value to set

    • psql_set_datestyle: If true set datestyle to control order of months, days and years

    • psql_datestyle: Datestyle value

    • psql_barman: Whether barman handles backups

    • psql_barman_host: Domain name of barman server

    • psql_barman_host_port: SSH port of barman server

    • psql_barman_sync: If this server is in synchronization with barman server. This option should allow to have RPO=0 but likely decreases performance.

    • psql_barman_super_pw: Password of barman super user

    • psql_barman_replication_pw: Password of barman replication user

  • Defaults:

    • psql_template_comment: Comment put on top of every template delivered by Ansible

    • psql_cert_path: Path where the server certificate should be put

    • psql_key_path: Path where the key should be put

    • psql_package_version_pinning: If true pin package to version below

    • psql_package_version: Exact package version for installation

    • psql_ssh_key_path: Path of the private SSH key (public will be the same + ‘.pub’)

    • psql_ssh_dir: Directory for postgres users SSH, will be created if not present

    • psql_gh_db_user: PostgreSQL user as owner for GNU Health demo database

    • psql_gh_demo_db_name: Name of GNU Health demo database

    • psql_gh_demo_db_version: Version of GNU Health demo database

    • psql_th_db_user: PostgreSQL user as owner for Thalamus federation database

    • psql_th_db_name: Name of Thalamus federation database

    • psql_barman_super_user: Name of PostgreSQL super user for barman

    • psql_barman_replication_user: Name of PostgreSQL replication user for barman

    • psql_barman_config_name: Config name for WAL archive command

    • psql_synchronous_standby_names: Config name for barman server if psql_barman_sync

    • psql_copy_dump: If the dump for restoring a database is not already on the target system copy it over there

  • Variables:

    • psql_packages: System packages needed for installation depending on OS family

    • psql_os_user: Name of OS user for PostgreSQL, until here always ‘postgres’

    • psql_home: Home directory of ‘postgres’ user

    • psql_data_path_freebsd: Data path on FreeBSD, used to check if initdb was called

    • psql_become_method_unprivileged_users: Set to make become_user work on Suse and to Ansible default otherwise

    • psql_become_exe_unprivileged_users: Set to make become_user work on Suse and to Ansible default otherwise