Adding on-demand EDB Postgres databases

If you need to create EDB Postgres databases for your CP4BA deployment, you must access the EDB Postgres pod, and then run the SQL files for the new databases inside the pod.

About this task

If you decide at any point to use the EDB Postgres instance that you created for your CP4BA deployment for another capability, then you can run SQL files for new databases in the EDB Postgres pod. You then need to modify the EDB Cluster CR to grant access for the users of this database. You might also need to add new databases for the object stores in Content Platform Engine or new projects for Document Processing.

Procedure

Retrieve the EDB Cluster CR for the CP4BA deployment by running the following command.

oc get cluster
NAME                AGE   INSTANCES   READY   STATUS                     PRIMARY
common-service-db   16h   2           2       Cluster in healthy state   common-service-db-1
postgres-cp4ba      16h   1           1       Cluster in healthy state   postgres-cp4ba-1
zen-metastore-edb   16h   2           2       Cluster in healthy state   zen-metastore-edb-2

The EDB cluster for the CP4BA deployment in the example output is postgres-cp4ba. The PRIMARY column contains the primary pod name (postgres-cp4ba-1) for EDB Postgres.

Create a script file (newdb.sh) and copy it into the primary pod of the EDB Postgres cluster.

The following script is a sample to create a new EDB Postgres user who is called newuser2 and a new database called newdb2.

#!/bin/bash
set -x

dbuser=newuser2
dbname=newdb2
location='/var/lib/postgresql/data/newdatabase2/'
dbtablespace=new_tablespace2

mkdir $location
psql_cmd="psql -U postgres -c "
$psql_cmd "create user \"$dbuser\" with login;"
$psql_cmd "alter user  \"$dbuser\" with encrypted password 'newpwd2';"
$psql_cmd "create tablespace \"$dbtablespace\" owner \"$dbuser\" location '$location';

$psql_cmd "alter tablespace \"$dbtablespace\" owner to \"$dbuser\";"
$psql_cmd "grant create on tablespace \"$dbtablespace\" to \"$dbuser\";"
$psql_cmd "create database \"$dbname\"  owner \"$dbuser\" tablespace \"$dbtablespace\" template template0 encoding 'UTF8';"

$psql_cmd "alter database \"$dbname\" owner to \"$dbuser\";"
$psql_cmd "grant all privileges on database \"$dbname\" to \"$dbuser\";"
$psql_cmd "revoke connect on database \"$dbname\" from public;"
$psql_cmd "grant all privileges on database \"$dbname\" to \"$dbuser\";"
$psql_cmd "grant connect, temp, create on database \"$dbname\" to \"$dbuser\";"

psql_cmd="psql -d $dbname -U postgres -c "
$psql_cmd "create schema if not exists authorization \"$dbuser\";"
$psql_cmd "set role \"$dbuser\""

Copy the SQL script into the primary pod of the EDB Postgres cluster.
```
oc cp <sql_folder>/newdb.sh postgres-cp4ba-1:/var/lib/postgresql/data/
```
Remember: The primary pod name is postgres-cp4ba-1.
Open a session into the primary pod of the EDB Postgres cluster and run the newdb.sh script.
1. Go into the primary pod of the EDB Postgres cluster.
```
oc rsh postges-cp4ba-1
Defaulted container "postgres" out of: postgres, bootstrap-controller (init)
```
2. Run the newdb.sh script file.
```
cd /var/lib/postgresql/data/
chmod +x newdb.sh
./newdb.sh
```

If you created a user or role for the database, modify the EDB Cluster CR to add the user to the pg_hba.conf file.

Run the following command and then search for pg_hba when you are in the edit view.

oc edit cluster postgres-cp4ba

The pg_hba section has lines similar to the following example:

pg_hba:
    - hostssl gcddb gcduser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl icndb icnuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl adpggdb adpuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl adsruntimedb adsruntime 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl adsdesignerdb adsdesigner 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl os1db osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl bawdocs osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl bawdos osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl bawtos osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl basdb basuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca

Add the new database and new user to the list, and then save and exit the file.

The pg_hba section has new lines for each new user.

pg_hba:
    - hostssl gcddb gcduser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl icndb icnuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl adpggdb adpuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl adsruntimedb adsruntime 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl adsdesignerdb adsdesigner 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl os1db osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl bawdocs osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl bawdos osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl bawtos osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl basdb basuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    - hostssl newdb2 newuser2 10.254.0.0/16 scram-sha-256 clientcert=verify-ca

Verify that the pg_hba settings are updated in the EDB Postgres pod.

Go back into the EDB Postgres pod.
```
oc rsh postgres-cp4ba-1
```
Go to the directory that contains the Postgres configurations.
```
cd /var/lib/postgresql/data/pgdata
```
Note: Configuration and data files that are used by a database cluster are stored together within the data directory of the cluster, commonly referred to as PGDATA. A common location for PGDATA is /var/lib/pgsql/data. The PGDATA directory contains several directories and control files. For example, the cluster configuration files postgresql.conf, pg_hba.conf, and pg_ident.conf are often stored in PGDATA, although it is possible to place them elsewhere. For more information, see PostgreSQL database file layout .

Use the cat command to view the updated pg_hba.conf file.

The following example shows the result of the cat command on the pg_hba.conf file.

cat pg_hba.conf
#
# FIXED RULES
#
# Grant local access ('local' user map)
local all all peer map=local
# Require client certificate authentication for the streaming_replica user
hostssl postgres streaming_replica all cert
hostssl replication streaming_replica all cert
hostssl all cnp_pooler_pgbouncer all cert
#
# USER-DEFINED RULES
#
hostssl gcddb gcduser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
hostssl icndb icnuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
hostssl adpggdb adpuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
hostssl adsruntimedb adsruntime 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
hostssl adsdesignerdb adsdesigner 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
hostssl os1db osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
hostssl bawdocs osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
hostssl bawdos osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
hostssl bawtos osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
hostssl basdb basuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
hostssl newdb2 newuser2 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
#
# DEFAULT RULES
#
host all all all scram-sha-256

Verify the connectivity to the new database with the new user.

Go back into the primary pod of the EDB Postgres cluster.
```
oc rsh postgres-cp4ba-1
```
Connect to the new database newdb2 with the user newuser2.
```
psql -U newuser2 -d newdb2 -h localhost
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
```
The password that is specified in the sample script is newpwd2.

Type "help" for help.

List the databases in newdb2.

newdb2=> \l

The output shows a list of databases

                                  List of databases
     Name      |    Owner    | Encoding | Collate | Ctype |      Access privileges
---------{}{}{}{}{}{}{}{}{}{}{}-{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{+}{}{}{}-------------------------
 adpggdb       | adpuser     | UTF8     | C       | C     | =T/adpuser                 +
               |             |          |         |       | adpuser=CTc/adpuser
 adsdesignerdb | adsdesigner | UTF8     | C       | C     | =T/adsdesigner             +
               |             |          |         |       | adsdesigner=CTc/adsdesigner
 adsruntimedb  | adsruntime  | UTF8     | C       | C     | =T/adsruntime              +
               |             |          |         |       | adsruntime=CTc/adsruntime
 app           | app         | UTF8     | C       | C     |
 basdb         | basuser     | UTF8     | C       | C     | =T/basuser                 +
               |             |          |         |       | basuser=CTc/basuser
 bawdocs       | osuser      | UTF8     | C       | C     | =T/osuser                  +
               |             |          |         |       | osuser=CTc/osuser
 bawdos        | osuser      | UTF8     | C       | C     | =T/osuser                  +
               |             |          |         |       | osuser=CTc/osuser
 bawtos        | osuser      | UTF8     | C       | C     | =T/osuser                  +
               |             |          |         |       | osuser=CTc/osuser
 gcddb         | gcduser     | UTF8     | C       | C     | =T/gcduser                 +
               |             |          |         |       | gcduser=CTc/gcduser
 icndb         | icnuser     | UTF8     | C       | C     | =T/icnuser                 +
               |             |          |         |       | icnuser=CTc/icnuser
 newdb2        | newuser2    | UTF8     | C       | C     | =T/newuser2                +
               |             |          |         |       | newuser2=CTc/newuser2
 os1db         | osuser      | UTF8     | C       | C     | =T/osuser                  +
               |             |          |         |       | osuser=CTc/osuser
 postgres      | postgres    | UTF8     | C       | C     |
 template0     | postgres    | UTF8     | C       | C     | =c/postgres                +
               |             |          |         |       | postgres=CTc/postgres
 template1     | postgres    | UTF8     | C       | C     | =c/postgres                +
               |             |          |         |       | postgres=CTc/postgres
(15 rows)

Exit the psql prompt.
```
newdb2=> \q
```