Sample Environment

The sample environment that ships with XDM represents a database used by the human resources department of a company. It is shipped with XDM in the form of two Docker images:

docker.ubs-hainer.com/sample-environment:latest contains a sample PostgreSQL database.
docker.ubs-hainer.com/linuxserver/openssh-server:version-9.0_p1-r2 contains an SSH server

The PostgreSQL server listens for incoming connections on port 5432, and the SSH server listens for incoming connections on port 2222.

Installation of the sample environment

The sample environment can be installed on both Docker and Kubernetes. Please choose the platform that matches your setup.

Kubernetes
Docker

When accessing a service from XDM e.g. database-URLs or Server addresses the following pattern has to be used: <xdm-service>.<namespace>. So accessing the postgres sample-database would look like this: jdbc:postgresql://xdm-sample-database.xdm-samples:5432/production

To run the sample environment on Kubernetes, first create a new namespace, for example:

kubectl create namespace xdm-samples

Next, create a new deployment YAML file to install both services, for example sample-environment-deployment.yaml. The deployment file looks like this:

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: xdm-sample-database
spec:
  serviceName: xdm-sample-database
  selector:
    matchLabels:
      app: xdm-sample-database
  template:
    metadata:
      labels:
        app: xdm-sample-database
    spec:
      containers:
        - name: xdm-sample-database
          image: docker.ubs-hainer.com/sample-environment:latest
          imagePullPolicy: IfNotPresent
          env:
            - name: POSTGRES_USER
              value: postgres
            - name: POSTGRES_PASSWORD
              value: pass123postgres
            - name: PGDATA
              value: /var/lib/postgresql/data/files
          ports:
            - name: sql
              containerPort: 5432
          volumeMounts:
            - name: xdm-data
              mountPath: /xdmdata

        - name: openssh
          image: docker.ubs-hainer.com/linuxserver/openssh-server:version-9.0_p1-r2
          imagePullPolicy: IfNotPresent
          env:
            - name: PUID
              value: "1000"
            - name: PGID
              value: "1000"
            - name: USER_NAME
              value: sshuser
            - name: USER_PASSWORD
              value: pass123ssh
            - name: PASSWORD_ACCESS
              value: "true"
          ports:
            - name: ssh
              containerPort: 2222
          volumeMounts:
            - name: xdm-data
              mountPath: /xdmdata

      volumes:
        - name: xdm-data
          emptyDir: {}
---
apiVersion: v1
kind: Service
metadata:
  name: openssh
spec:
  type: ClusterIP
  selector:
    app: xdm-sample-database
  ports:
    - name: ssh
      port: 2222
      targetPort: 2222
      protocol: TCP
---
apiVersion: v1
kind: Service
metadata:
  name: xdm-sample-database
spec:
  type: ClusterIP
  selector:
    app: xdm-sample-database
  ports:
    - name: sql
      port: 5432
      targetPort: 5432
      protocol: TCP

Finally, apply the deployment file to the newly created xdm-samples namespace:

kubectl apply -f sample-environment-deployment.yaml -n xdm-samples

After that the services should run automatically

In the base installation package (xdm-installation.zip) you can find a directory named sample-environment. There you will find a predefined docker-compose.yaml file that contains the services mentioned above.

version: '3'
services:
  sample-ssh:
    image: docker.ubs-hainer.com/linuxserver/openssh-server:version-9.0_p1-r2
    ports:
      - '2222:2222'
    environment:
      - PUID=1000
      - PGID=1000
      - USER_NAME=sshuser
      - USER_PASSWORD=pass123ssh
      - PASSWORD_ACCESS=true
    volumes:
      - sample-shared-volume:/xdmdata

  sample-pg:
    image: docker.ubs-hainer.com/sample-environment
    ports:
      - '5432:5432'
    environment:
      - POSTGRES_PASSWORD=pass123postgres
    volumes:
      - sample-shared-volume:/xdmdata

volumes:
  sample-shared-volume:

You can also start the sample environment alongside XDM. In this case, you have to add both services to your currently used docker-compose.yaml file. If you use the default docker-compose.yaml, both services are already predefined and only need to be enabled by removing the pound keys (#).

If the sample environment is started alongside XDM, the TCP ports on which the PostgreSQL database and the SSH server listen for connections will not be exposed outside of the Docker network. This means that XDM itself can connect to both PostgreSQL and SSH by using the host names sample-pg and sample-ssh, but other programs cannot connect from outside the Docker network.

When the sample environment is started separately, ports 5432 (PostgreSQL) and 2222 (SSH) will be exposed so that it is possible to connect from other machines by accessing these ports on the host where the sample environment is running.

Starting and stopping the sample environment

If you run the sample-environment separately, open a shell and navigate to the sample-environment directory inside your XDM installation. Then use the following command to start the services:

docker compose up -d

To shut it down, use:

docker compose down

If the sample environment services are defined inside the standard docker-compose.yaml file they will start together with the other XDM services. If XDM is already running and the sample environment services were added afterward, start them explicitly with:

docker compose up -d sample-pg sample-ssh

To stop only the sample environment services (without stopping XDM), use:

docker compose down sample-pg sample-ssh

Use lookup tables

During the tutorial, lookup tables are used to modify data.

These lookup tables provide a pre-defined set of first and last names, addresses and banking information. The lookup tables are used by the pre-defined XDM masking methods and can be downloaded from https://products.ubs-hainer.com/downloads/XDM3/Lookup-Tables-2.3.232/XDM3_Lookup_Tables.zip.

We will add them later in the tutorial.

Overview of the sample environment

The sample PostgreSQL instance contains two databases:

Database production
- Schema production
Database testing
- Schema qa1
- Schema qa2
- Schema qa3

Inside each schema, the following tables exist:

departments

Contains one entry per department with department number (dept_no, primary key) and department name. This table has 9 rows.
employees

Contains one entry per employee with employee number (emp_no, primary key) and additional details about the employee, such as name or date of birth. This table has 300,024 rows.
dept_manager

Keeps track of who was the manager of each department during which period of time. This table is a child table of the the table departments (via the column dept_no) and the table employees (via the column emp_no). This table has 24 rows.
dept_emp

Keeps track of which employee works in which department during which period of time. This table has 331,603 rows.
salaries

Keeps track of each employee’s salary and the salary’s duration. This table is a child table of the table employees (via the column emp_no). This table has 2,844,047 rows.
titles

Keeps track of the duration for which each employee had a particular job title. This table is a child table of the table employees (via the column emp_no). This table has 443,308 rows.

The following image illustrates the relationship between the tables:

In the schema production, the tables contain data. In the schemas qa1, qa2 and qa3, the tables are initially empty.

Three different user accounts exist in the sample PostgreSQL instance:

Username Password Description

Username	Password	Description
`prodrw`	`pass123prod`	Has read and write access to the tables in the schema `production`.
`prodro`	`pass456prod`	Has read-only access to the tables in the schema `production`.
`qa`	`pass789qa`	Has read and write access to the tables in the schemas `qa1`, `qa2` and `qa3`.

prodrw

pass123prod

Has read and write access to the tables in the schema production.

prodro

pass456prod

Has read-only access to the tables in the schema production.

qa

pass789qa

Has read and write access to the tables in the schemas qa1, qa2 and qa3.

The three different QA departments have the following requirements for their test data provisioning process:

qa1 needs to have a full copy of all tables.
qa2 needs to have a full copy of all tables, but employee names and dates of birth must be masked
qa3 needs to have a copy of the records of those employees that work in the department Customer Service, and employee names and dates of birth must be masked.

An SSH server is running alongside the database server. The directory /xdmdata is shared between the two servers, and the following SSH user is pre-configured:

Username Password Description

Username	Password	Description
`sshuser`	`pass123ssh`	Has access to the `/xdmdata` directory on the server.

sshuser

pass123ssh

Has access to the /xdmdata directory on the server.

Previous section: Overview | Next section: Infrastructure objects