PostgreSQL configuration

sudo apt install postgresql postgresql-client

version: '3.5'
services: 
    df2-postgres:
        container_name: df2-postgres
        image: postgres:{{ postgres_version }}
        command: -c ssl=on -c ssl_cert_file=/var/lib/postgresql/server.crt -c ssl_key_file=/var/lib/postgresql/server.key
        environment:
            POSTGRES_USER: "postgres"
            POSTGRES_PASSWORD: "bevault"
        volumes:
            - df2-postgres-data:/var/lib/postgresql/data
            - ./postgres/init:/docker-entrypoint-initdb.d/
            - ./postgres/ssl/server.crt:/var/lib/postgresql/server.crt
            - ./postgres/ssl/server.key:/var/lib/postgresql/server.key
        healthcheck:
            test: ["CMD-SHELL", "pg_isready -U postgres"]
            interval: 10s
            timeout: 5s
            retries: 5   
        shm_size: 1g # NOTE : see https://stackoverflow.com/a/56754077 for more information
        restart: always
        ports:
            - 5432:5432
volumes: 
  df2-postgres-data:

cd /opt/dfakto
mkdir -p ./postgres/ssl
apt install -y openssl
openssl req -new -text -subj /CN=localhost -out ./postgres/ssl/server.req # Define a password
openssl rsa -in privkey.pem -out ./postgres/ssl/server.key # Provide a password
openssl req -x509 -in ./postgres/ssl/server.req -text -key ./postgres/ssl/server.key -out ./postgres/ssl/server.crt
mv ./privkey.pem ./postgres/ssl/
chown 0:999 ./postgres/ssl/server.key # see https://stackoverflow.com/a/55072885
chmod 640 ./postgres/ssl/server.key
mkdir -p ./postgres/init
touch ./postgres/init/user_and_roles.sql # edit content of file with an editor and put the content detailled below
sudo docker-compose up -d

CREATE USER metavault WITH PASSWORD '{{ metavault_password }}' NOSUPERUSER CREATEDB CREATEROLE;
CREATE USER metavault_readonly WITH PASSWORD '{{ metavault_readonly_password }}';
CREATE ROLE stg_loader;
CREATE ROLE stg_reader;
CREATE ROLE dv_reader;
CREATE ROLE ref_reader;
CREATE ROLE dv_loader;
CREATE ROLE im_reader;
CREATE ROLE im_loader;
CREATE ROLE meta_reader;
CREATE ROLE df_operator;