Up and running with the DB operator

Posted on Jun 20, 2023

#kubernetes #database #operator #postgresql

Looking around on the OperatorHub, the marketplace for Kubernetes Operators of various kinds, you will find many incarnations of “database operators”. However, most of them focus on either 1) running a database within Kubernetes (for example, the various Postgres operators) or 2) integrating with a cloud-managed database provider (such as Aiven). Unfortunately, none of them allow just managing an already existing database server.

What do I mean by manage? Creating databases, schemas, users, roles et cetera within the database server but not running the database itself.

Luckily, this new DB operator changes that: https://github.com/db-operator/db-operator.

The DB Operator eases the pain of managing PostgreSQL and MySQL instances for applications running in Kubernetes. The Operator creates databases and makes them available in the cluster via Custom Resource.

Features:
Create/Delete databases on the database server running outside/inside Kubernetes by creating Database custom resources;
Create Google Cloud SQL instances by creating DbInstance custom resource;
Automatically create backup CronJobs according to schedules;

# Prerequisite

The DB operator Helm chart currently require a functional cert-manager installation, which can be obtained with the following commands. For more details, please check cert-manager’s installation documentation.

1
2
3
4
5
6
7
8
helm repo add jetstack https://charts.jetstack.io
helm repo update
helm upgrade --install \
  cert-manager jetstack/cert-manager \
  --namespace cert-manager \
  --create-namespace \
  --version v1.12.0 \
  --set installCRDs=true

# Installation

Given we have cluster-admin access to a Kubernetes cluster (I’m using a v1.24.7 cluster in this tutorial), the DB operator can be quickly installed with the provided Helm chart. The following commands add the Helm chart repository and install the operator (with default settings) into a new namespace called db-operator. Note that we specifically install chart version 1.7.0, which installs DB operator version 1.10.0. You can find all available chart versions here and the corresponding operator versions here.

1
2
3
4
5
6
helm repo add db-operator https://db-operator.github.io/charts
helm upgrade --install --create-namespace \
    --namespace db-operator \
    db-operator \
    db-operator/db-operator\
    --version 1.7.0

Let’s check if the operator pod is running:

1
2
3
kubectl -n db-operator get pods
NAME                           READY   STATUS    RESTARTS   AGE
db-operator-65955cbfdf-4h9n7   1/1     Running   0          7s

Looks good! Let’s also check if the CRDs (Custom Resource Definitions) are installed:

1
2
3
kubectl get crd | grep kci.rocks
databases.kci.rocks                     2023-06-20T13:12:19Z
dbinstances.kci.rocks                   2023-06-20T13:12:19Z

Yes they are!

# Example database server

Just for demonstration purposes, we’ll run a PostgreSQL database in a separate namespace (using Bitnami’s Postgres Helm chart). It is worth noting that this database could run anywhere: the same Kubernetes cluster, a different Kubernetes cluster, an external host, the cloud, …

1
2
3
4
helm upgrade --install --create-namespace \
    --namespace example-postgres \
    postgresql \
    oci://registry-1.docker.io/bitnamicharts/postgresql

The admin user is postgres and the password can be extracted from a secret. We’ll need these in the next step.

1
2
3
POSTGRES_USER=postgres
POSTGRES_PASSWORD=$(kubectl get secret --namespace example-postgres postgresql \
                    -o jsonpath="{.data.postgres-password}" | base64 -d)

# Database instance

A database server (MySQL, PostgreSQL etc.) can host multiple “databases” (i.e. the result of a CREATE DATABASE my-db SQL query). The DB operator calls the “server” a database instance and the SQL database simply database.

To get started, we need to connect the DB operator to one database server (DbInstance). In this case we’ll use the Postgres instance created in the previous step.

Create a Secret containing the Postgres admin credentials:

1
2
3
kubectl -n db-operator create secret generic pg-admin-secret \
    --from-literal=user="${POSTGRES_USER}" \
    --from-literal=password="${POSTGRES_PASSWORD}"

and then the DbInstance resource itself:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
# kubectl apply -f - <<EOF
apiVersion: kci.rocks/v1beta1
kind: DbInstance
metadata:
  name: pg-instance
  # cluster-scoped resource, no namespace
spec:
  adminSecretRef:
    Name: "pg-admin-secret" # reference secret created by previous command
    Namespace: "db-operator"
  engine: "postgres"
  generic:
    host: "postgresql.example-postgres.svc.cluster.local"
    port: 5432 # default postgres port
EOF

Check the status of the connection:

1
2
3
kubectl get dbinstance/pg-instance
NAME               PHASE     STATUS
pg-instance        Running   true

Now the DB operator can manage databases, users and roles within this database server.

In case the database instance does not enter the Running phase, check the logs of the operator:

1
2
3
4
5
kubectl -n db-operator logs deploy/db-operator --tail=10

level=info msg="Instance: name=pg-instance spec changed"
level=info msg="Instance: name=pg-instance Validating"
level=error msg="db conn test failed - failed to execute query: dial tcp 10.254.96.121:5672: connect: no route to host"

In the example above the port was not correctly specified, hence the operator could not connect to the database. In the following example, the password was not correctly specified in the secret referenced by the DbInstance:

1
level=error msg="db conn test failed - failed to execute query: pq: password authentication failed for user \"postgres\""

Also Network Policies and other types of firewalls can be common causes that prevent the operator from connecting to the database.

# Database

Finally, let’s create a new database for an arbitrary application. We’ll do this in a new namespace called my-app:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# kubectl create namespace my-app
# kubectl apply -f - <<EOF
apiVersion: kci.rocks/v1beta1
kind: Database
metadata:
  name: postgres-for-my-app
  namespace: my-app
spec:
  instance: "pg-instance" # must match with DbInstance name
  deletionProtected: false # Protection to not delete database when custom resource is deleted
  backup:
    enable: false
    cron: "0 0 * * *"
  # secret in which the credentials for the database should be stored (same namespace)
  secretName: example-postgres-db-credentials
  # the content of the secret
  secretsTemplates:
    # https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
    CONNECTION_STRING: "{{ .Protocol }}://{{ .UserName }}:{{ .Password }}@{{ .DatabaseHost }}:{{ .DatabasePort }}/{{ .DatabaseName }}"
    DB_PASS: "{{ .Password }}"
    DB_USER: "{{ .UserName }}"
    DB_HOST: "{{ .DatabaseHost }}"
    DB_PORT: "{{ .DatabasePort }}"
    DB_NAME: "{{ .DatabaseName }}"
    DB_TYPE: "{{ .Protocol }}"
EOF

Check the status:

1
2
3
kubectl -n my-app get database/postgres-for-my-app
NAME                  PHASE   STATUS   PROTECTED   DBINSTANCE    AGE
postgres-for-my-app   Ready   true     false       pg-instance   19s

and the Secret created by the operator:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
# kubectl -n my-app get secret/example-postgres-db-credentials -o yaml
apiVersion: v1
kind: Secret
metadata:
  creationTimestamp: "2023-06-20T14:18:23Z"
  labels:
    created-by: db-operator
  name: example-postgres-db-credentials
  namespace: my-app
type: Opaque
data:
  CONNECTION_STRING: postgresql://my-app-postgres-for-my-app:-__o9Yi3jE0M95S-66Bq6_0n@postgresql.example-postgres.svc.cluster.local:5432/my-app-postgres-for-my-app
  #                  ^^ base64 decoded ^^
  DB_HOST: cG9zdGdyZXNxbC5leGFtcGxlLXBvc3RncmVzLnN2Yy5jbHVzdGVyLmxvY2Fs
  DB_NAME: bXktYXBwLXBvc3RncmVzLWZvci1teS1hcHA=
  DB_PASS: LV9fbzlZaTNqRTBNOTVTLTY2QnE2XzBu
  DB_PORT: NTQzMg==
  DB_TYPE: cG9zdGdyZXNxbA==
  DB_USER: bXktYXBwLXBvc3RncmVzLWZvci1teS1hcHA=
  POSTGRES_DB: bXktYXBwLXBvc3RncmVzLWZvci1teS1hcHA=
  POSTGRES_PASSWORD: LV9fbzlZaTNqRTBNOTVTLTY2QnE2XzBu
  POSTGRES_USER: bXktYXBwLXBvc3RncmVzLWZvci1teS1hcHA=

The operator also created a ConfigMap containing the non-sensitive parts of the database connection:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# kubectl -n my-app get configmap example-postgres-db-credentials -o yaml
apiVersion: v1
kind: ConfigMap
metadata:
  creationTimestamp: "2023-06-20T14:18:23Z"
  labels:
    created-by: db-operator
  name: example-postgres-db-credentials
  namespace: my-app
data:
  DB_CONN: postgresql.example-postgres.svc.cluster.local
  DB_PORT: "5432"
  DB_PUBLIC_IP: ""

# Inspection

So what happened in the Postgres database? Let’s find out by connecting to the database server as an admin user.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
kubectl -n example-postgres port-forward svc/postgresql 5432:5432 &

PGPASSWORD="${POSTGRES_PASSWORD}" psql -U "${POSTGRES_USER}" -h localhost

postgres=# \l
                                                    List of databases
            Name            |  Owner   | Encoding |   Collate   |    Ctype    |             Access privileges
----------------------------+----------+----------+-------------+-------------+-------------------------------------------------------------------------------
 my-app-postgres-for-my-app | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres, postgres=CTc/postgres, "my-app-postgres-for-my-app"=CTc/postgres
 postgres                   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0                  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres, postgres=CTc/postgres
 template1                  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres, postgres=CTc/postgres
(4 rows)

We can see that the operator created a database called my-app-postgres-for-my-app (according to the naming schema <NAMESPACE>-<DATABASE_RESOURCE_NAME>) and granted access privileges (C → CREATE DATABASE,SCHEMA,TABLESPACE, T → TEMPORARY, c → CONNECT) to the role (user) named “my-app-postgres-for-my-app” (the other three databases come by default with all Postgres installations).

# Connection

As a final validation, let’s create a Pod that uses the credentials from the Secret (created by the operator) and connect to the database.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
# kubectl create --force -f - <<EOF
apiVersion: v1
kind: Pod
metadata:
  name: demo-pod
  namespace: my-app
spec:
  containers:
  - name: example-app
    image: "docker.io/library/postgres"
    command: ["sleep", "infinity"]
    env:
    - name: CONNECTION_STRING
      valueFrom:
        secretKeyRef:
          name: example-postgres-db-credentials
          key: CONNECTION_STRING

EOF

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
kubectl -n my-app get pod
NAME       READY   STATUS    RESTARTS   AGE
demo-pod   1/1     Running   0          7s

kubectl -n my-app exec -it demo-pod -- bash

root@demo-pod:/# echo "$CONNECTION_STRING"
postgresql://my-app-postgres-for-my-app:-__o9Yi3jE0M95S-66Bq6_0n@postgresql.example-postgres.svc.cluster.local:5432/my-app-postgres-for-my-app

root@demo-pod:/# psql "$CONNECTION_STRING"
psql (15.3 (Debian 15.3-1.pgdg120+1))

my-app-postgres-for-my-app=> CREATE SCHEMA my_app;
CREATE SCHEMA
my-app-postgres-for-my-app=> SET search_path = 'my_app';
SET
my-app-postgres-for-my-app=> CREATE TABLE my_table(id serial PRIMARY KEY, name VARCHAR (255) UNIQUE NOT NULL);
CREATE TABLE                                          ^
my-app-postgres-for-my-app=> INSERT INTO my_table VALUES(1234, 'foobar');
INSERT 0 1
my-app-postgres-for-my-app=> SELECT * FROM my_table;
  id  |  name
------+--------
 1234 | foobar
(1 row)

We can connect to and use our new database!

# Cleanup

Unless the field spec.deletionProtected is set to true in the Database resource, the operator will automatically clean up the database (DROP DATABASE) after the resource gets deleted. It’s a good idea to enable the deletion protection for production deployments to avoid accidental data loss when the resource gets deleted or recreated.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
kubectl -n my-app delete database/postgres-for-my-app

PGPASSWORD="${POSTGRES_PASSWORD}" psql -U "${POSTGRES_USER}" -h localhost -c '\l'

                                                    List of databases
            Name            |  Owner   | Encoding |   Collate   |    Ctype    |             Access privileges
----------------------------+----------+----------+-------------+-------------+-------------------------------------------
 postgres                   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0                  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                              +
                            |          |          |             |             | postgres=CTc/postgres
 template1                  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                              +
                            |          |          |             |             | postgres=CTc/postgres
(3 rows)

We can remove the rest of the resources by deleting the namespaces:

1
2
3
kubectl delete namespace/my-app --wait
kubectl delete namespace/db-operator --wait
kubectl delete namespace/example-postgres --wait

Happy operating!