Setting up an external PostgreSQL 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 signing request (CSR).

      openssl req -new -nodes -text \
          -out root.csr \
          -keyout root.key \
          -subj "/CN=IBM <IM or Zen service> Intermediate 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=*.example.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 \
          -extfile <(printf "subjectAltName=DNS:*.example.com,DNS:database.example.com")
       

      Replace example.com with the fully qualified domain names that match your environment.

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.

      Repeat this step for every PostgreSQL role that requires certificate authentication. For example, to create a certificate for the administrative postgres user, rerun the command with -out postgres.csr -keyout postgres.key -subj "/CN=postgres".

      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
       

      Run the same signing command for each CSR that you generated. For example, postgres.csr).

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
    

   Repeat the export commands for each client certificate by substituting the corresponding key and certificate names. For example, run the commands with postgres.key and postgres.crt to produce postgres_key.pem and postgres.pem for the postgres user.

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
    

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 $PGDATA directory (/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'
   #shared_buffers = 128MB
    

   The shared_buffers parameter allocates the memory for the database server to cache data. The default value of the #shared_buffers parameter is 128MB. You can set the value between 15% to 20% of the total system RAM. For example, if your system RAM size is 32 GB, the recommended value for the #shared_buffers parameter is 8 GB.

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
    

To create the database objects, complete the following steps:

1. Configure certificate authentication for the postgres administrative user before running any SQL commands.

   1. Copy the PEM-formatted client certificate, client key, and root certificate for the postgres user to the .postgresql directory on the host that runs the commands.

      mkdir -p ~/.postgresql
      cp postgres.pem ~/.postgresql/client.crt
      cp postgres_key.pem ~/.postgresql/client.key
      cp root.pem ~/.postgresql/root.crt
      chmod 600 ~/.postgresql/client.key ~/.postgresql/client.crt ~/.postgresql/root.crt
       

   2. Set the connection environment variables so that psql uses TLS certificate authentication.

      export PGHOST=<database-hostname>
      export PGPORT=5432
      export PGUSER=postgres
      export PGDATABASE=postgres
      export PGSSLMODE=verify-full
      export PGSSLROOTCERT=~/.postgresql/root.crt
      export PGSSLCERT=~/.postgresql/client.crt
      export PGSSLKEY=~/.postgresql/client.key
       

      Replace <database-hostname> with the fully qualified domain name of your PostgreSQL server.

   3. Verify that certificate authentication is working for the postgres user.

      psql -c "SELECT current_user, inet_client_addr();"
       

      The command should return postgres as the current_user without prompting for a password.

2. 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';"
      

     Note: 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';"
      

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

3. Restart the PostgreSQL database.

   sudo systemctl restart postgresql-16
    

You can start the external database connection for IM 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.