11  Setup PostgreSQL

Important

PostgreSQL runs in the master machine and outside the Kubernetes cluster.

Follow the installation at Section 52.1 and the configuration at Section 52.2.

Create the users orc and jhub for PostgreSQL:

sudo -u postgres psql

CREATE USER orc;

CREATE ROLE

CREATE USER jhub;

CREATE ROLE

Assign password for the users orc and jhub for PostgreSQL:

Important

The password is stored in KeePassXC.

ALTER USER orc WITH PASSWORD 'password';

ALTER ROLE

ALTER USER jhub WITH PASSWORD 'password';

ALTER ROLE

Exit psql shell.

\q

Make PostgreSQL to accept all incoming ssl connections by adding

# TYPE DATABASE USER ADDRESS METHOD
# use hostssl to require use of SSL for all connections
# hostssl: https://www.postgresql.org/docs/12/auth-pg-hba-conf.html
#hostssl  all  all 0.0.0.0/0 scram-sha-256
hostssl  all  all 0.0.0.0/0 md5
# host: This record matches connection attempts made using TCP/IP. host records match SSL or non-SSL connection attempts...
#host  all  all 0.0.0.0/0 md5
# https://dba.stackexchange.com/questions/8580/force-postgresql-clients-to-use-ssl
#hostssl  all  all 0.0.0.0/0 cert

at the beginning of /etc/postgresql/12/main/pg_hba.conf. And replace

listen_addresses = 'localhost'

with

listen_addresses = '*'

at /etc/postgresql/12/main/postgresql.conf.

Enable SSL in PostgreSQL configuration by replacing

ssl_min_protocol_version = 'TLSv1'

with

ssl_min_protocol_version = 'TLSv1.2'

and

ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'

with

ssl_ciphers = 'TLSv1.2:!aNULL'

at /etc/postgresql/12/main/postgresql.conf.

Optimize PostgreSQL by setting

shared_buffers = 6GB
effective_cache_size = 12GB
work_mem = 8MB
maintenance_work_mem = 512MB

in /etc/postgresql/12/main/postgresql.conf.

Reload and restart PostgreSQL:

sudo systemctl reload postgresql@12-main.service

sudo systemctl restart postgresql@12-main.service

Verify that PostgreSQL is running:

sudo systemctl status postgresql@12-main.service

Create database:

sudo -u postgres createdb \
    -O orc \
    -E UTF8 \
    -T template0 \
    gesisbinder