The current GETOK database model is relatively simple. It is designed to generally satisfy the following:

• Answer security auditing questions (who did what promotion event actions and when)
• Model the relation between users and acronyms (who owns acronym and is allowed to deploy promotions)

GETOK database is implemented with High Availability and has a Database Backup strategy.

The following is an entity relational diagram of the getok database model.

Figure 1 - An ER Diagram of the GETOK Database

The production Postgres databases are hosted on Openshift in high availability mode through Patroni. By design, this database is not accessible to any applications or users outside of the cluster and project namespace it resides in. As such, in order to gain direct access to the database, the auditor in question must have the following satisfied:

• Have a valid Github account with the correct permissions to prerequisite bcgov organizations
• Have at minimum edit permissions on the correct Openshift namespace which contains the database they need access to

An auditor must have a minimally working understanding of Openshift in order to navigate and gain access to the correct DB pod resources should they want to directly access to the live production database. At minimum, the auditor must have the following:

• Have the Openshift CLI tool installed on their machine
• Be able to view Openshift secrets related to the Patroni DB to login
• Have the ability to view the state of the Openshift project
• Allowed to directly connect to an existing pod in that project.

Assuming all prerequisites are satisfied, the auditor will want to perform the following steps in order to establish a connection to the DB.

1. Login to Openshift via command line oc login https://YOUROPENSHIFTCLUSTER.HERE --token=YOURTOKENHERE. You can copy the login command by right clicking your username on the Openshift web control panel and selecting Copy Login Command.

2. Ensure you are in the right project namespace after login by running oc project <NAMESPACE>. You can get a list of projects you have access to by running oc projects. This list will be in the format:

3. You will need to figure out which running pod you need to connect to. You can get a list of pods with oc get pods -n NAMESPACE --selector cluster-name=master. You should be able to see something like the following:

   NAME               READY     STATUS    RESTARTS   AGE
   patroni-master-0   1/1       Running   0          10d
   patroni-master-1   1/1       Running   0          10d
   patroni-master-2   1/1       Running   0          10d

4. Normally you will want to connect to the pod numbered 0. In order to expose the pod's database port locally, you need to create a temporary port-forward to that pod. This can be achieved by doing oc port-forward -n NAMESPACE patroni-master-0 15432:5432. The default Postgres port is 5432, and the previous command forwards that port to 15432 on your local machine. Alternatively, you can achieve steps 3 and 4 in one command by unix chaining:

export NAMESPACE=<YOURNAMESPACE>
oc -n $NAMESPACE port-forward $(oc -n $NAMESPACE get pods -o name --selector role=master,cluster-name=master) 15432:5432

5. 1. Once you have a successful port-forward, you may use your database application of choice to connect to the database at the forwarded port.
   2. If you have Postgres installed, you can open a new terminal window and start psql and connect to the database "getok" using command line: psql -h localhost -p 15432 -d getok -U postgres
   3. It will prompt you for a password which you will need to acquire from OpenShift Console --> Resources --> Secrets --> Patroni-Master-Secret (Use the project environment corresponding to the namespace you setup your portforwarding to) Reveal and copy the super user password to paste back in your terminal window.

At this point you are ready to begin using sql commands.

As of this time, there is no frontend panel designed to provide a view of actions that occured on the GETOK app. However, all critical actions are logged by the application (i.e. someone pushes out an appConfig update). In order to acquire these questions in the event of a security audit, the following lists a couple of projected common SQL queries that may be used.

• What did user "IDIR" do on getok?

select lh."createdAt", u."keycloakId", u."username", a."acronym", lh."env", l."appConfig"
from lifecycle_history as lh
inner join "user" as u
on lh."userId" = u."userId"
inner join lifecycle as l
on lh."lifecycleId" = l."lifecycleId"
inner join acronym as a
on l."acronymId" = a."acronymId"
where u."username" like '%jerho%'

• What happened between a timeframe?

select lh."createdAt", u."keycloakId", u."username", a."acronym", lh."env", l."appConfig"
from lifecycle_history as lh
inner join "user" as u
on lh."userId" = u."userId"
inner join lifecycle as l
on lh."lifecycleId" = l."lifecycleId"
inner join acronym as a
on l."acronymId" = a."acronymId"
where lh."createdAt" between '2019-08-01' and '2019-08-20'