Setting up an external PostgreSQL database server

You need to create the certificates and database objects if you use the new external database server for the first time to configure the external PostgreSQL database for IM or Zen service.

Complete the following procedures to set up the external database server:

Setting up certificate for external database server

To set up the Intermediate certificate authority (CA), database server certificate, and database client certificate, complete the following steps:

  1. Set up the intermediate CA.

    1. Generate the CA certificate singing request (CSR).

      openssl req -new -nodes -text \
    -out root.csr \
    -keyout root.key \
    -subj "/CN=IBM <IM or Zen service> Intermidiate Certificate Authority"

      You can replace <IM or Zen service> with IM or Zen service.

    2. Sign a CA certificate with openssl.

      find / -name openssl.cnf 2> /dev/null

      openssl x509 -req -in root.csr \
    -text \
    -days 3650 \
    -extfile /etc/pki/tls/openssl.cnf \
    -extensions v3_ca \
    -signkey root.key -out root.crt

      chmod og-rwx root.key

  2. Set up the database server certificate.

    1. Generate the database server CSR.

      openssl req -new -nodes -text \
    -out server.csr \
    -keyout server.key \
    -subj "/CN=*.fyre.ibm.com"

      chmod og-rwx server.key

    2. Sign a database server certificate with the internal intermediate CA.

      openssl x509 -req \
    -in server.csr -text -days 730 \
    -CA root.crt -CAkey root.key -CAcreateserial \
    -out server.crt

  3. Set up a database client certificate.

    1. Generate the database client CSR.

      openssl req -new -nodes -text \
    -out client.csr \
    -keyout client.key \
    -subj "/CN=<username>"

      You can replace <username> with the database username such as im_user or zen_user.

      chmod og-rwx client.key

    2. Sign a database client certificate with the internal intermediate CA.

      openssl x509 -req \
    -in client.csr -text -days 730\
    -CA root.crt -CAkey root.key -CAcreateserial \
    -out client.crt

  4. Verify the root.crt, server.crt, and client.crt certificates.

    openssl x509 -in root.crt -noout -subject -issuer -startdate -enddate
openssl x509 -in server.crt -noout -subject -issuer -startdate -enddate
openssl x509 -in client.crt -noout -subject -issuer -startdate -enddate

  5. Export client.key, client.crt, and root.crt certificates in the PEM format

    openssl rsa -in client.key -outform PEM -out client_key.pem
openssl x509 -in client.crt -outform PEM -out client.pem
openssl x509 -in root.crt -outform PEM -out root.pem

  6. Verify the root.pem and client.pem certificates.

    openssl x509 -in root.pem -noout -subject -issuer -startdate -enddate
openssl x509 -in client.pem -noout -subject -issuer -startdate -enddate

Installing a PostgreSQL database

Install the PostgreSQL version 16 to set up the database server and connections. To install the PostgreSQL version 16, complete the following steps:

  1. Download and install the PostgreSQL version 16.

    sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql16-server

  2. Create the PostgreSQL database cluster.

    sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

  3. Enable and start the Postgresql-16 service.

    sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16
sudo systemctl status postgresql-16

Configuring a PostgreSQL database

To configure the PostgreSQL database for the new external database server, complete the following steps:

  1. Copy the server.crt, server.key, and root.crt files to the $PGDATAdirectory (/var/lib/pgsql/16/data) and change the owner of the files to postgres.

    cp -p server.crt /var/lib/pgsql/16/data/server.crt
cp -p server.key /var/lib/pgsql/16/data/server.key
cp -p root.crt /var/lib/pgsql/16/data/root.crt
chown postgres:postgres /var/lib/pgsql/16/data/server.*
chown postgres:postgres /var/lib/pgsql/16/data/root.*

  2. Access shell prompt for postgres to edit the files.

    su - postgres
cd $PGDATA

  3. Edit the postgresql.conf file to update the following values.

    listen_addresses = '*'

max_connections = 600

ssl = on
ssl_ca_file = 'root.crt'
ssl_cert_file = 'server.crt'
#ssl_crl_file = ''
#ssl_crl_dir = ''
ssl_key_file = 'server.key'

  4. Edit the pg_hba.conf file to add the following command to enable SSL encryption for database connections.

    hostssl all             all             0.0.0.0/0               cert

Creating database objects

To create the database objects, complete the following steps:

  1. Create the database, database user, and monitoring schema.

    • For Zen service, run the following command:

      psql -U postgres -tc "SELECT 1 FROM pg_database WHERE datname = 'zen'" | grep -q 1 || psql -U postgres -c "CREATE DATABASE zen"

psql -U postgres -tc "SELECT 1 FROM pg_user WHERE usename = 'zen_user'" | grep -q 1 || psql -U postgres -c "CREATE USER zen_user"

psql -U postgres -c "GRANT CONNECT ON DATABASE zen TO public;" -c "ALTER DATABASE zen OWNER TO zen_user;" -c "GRANT ALL PRIVILEGES ON DATABASE zen to zen_user;"

psql -U postgres -d zen -tc "SELECT 1 FROM information_schema.schemata WHERE schema_name = 'watchdog'" | grep -q 1 || psql -U postgres -d zen -c "CREATE SCHEMA watchdog;" -c "ALTER SCHEMA watchdog OWNER TO zen_user;" -c "GRANT ALL ON SCHEMA watchdog TO zen_user;"

psql -U postgres -d zen -c "ALTER DATABASE zen SET timezone TO 'Etc/UTC';"

      Zen supports the watchdog and public monitoring schemas. You can update the schema_name = in the command with watchdog or public for the Zen service.

    • For IM service, run the following command:

      psql -U postgres -tc "SELECT 1 FROM pg_database WHERE datname = 'im'" | grep -q 1 || psql -U postgres -c "CREATE DATABASE im"

psql -U postgres -tc "SELECT 1 FROM pg_user WHERE usename = 'im_user'" | grep -q 1 || psql -U postgres -c "CREATE USER im_user"

psql -U postgres -c "GRANT CONNECT ON DATABASE im TO public;" -c "ALTER DATABASE im OWNER TO im_user;" -c "GRANT ALL PRIVILEGES ON DATABASE im to im_user;"

psql -U postgres -d im -c "ALTER DATABASE im SET timezone TO 'Etc/UTC';"

      IM operator creates the required database schemas for the IM service.

  2. Restart the PostgreSQL database.

    sudo systemctl restart postgresql-16

You can start the external database connection for IM or Zen service after you set up the external database server. To configure an external PostgreSQL database for IM or Zen service, see Configuring an external PostgreSQL database for IM or Configuring an external PostgreSQL database for Zen.