notes/grandcentrix/db-operator.md

db-operator

I've created a flexible server here: https://github.com/grandcentrix/platform-operations/pull/3591/files

You can switch to that branch and exec terraform output to get creds

Then I've create a local k8s cluster using kind and deployed db-operator there

# helmfile.yaml
---
repositories:
  - name: db-operator
    url: https://db-operator.github.io/charts/
  - name: jetstack
    url: https://charts.jetstack.io

releases:
  - name: cert-manager
    chart: jetstack/cert-manager
    version: v1.11.0
    namespace: cert-manager
    createNamespace: true
    values:
      - installCRDs: true

  - name: db-operator
    chart: db-operator/db-operator
    version: 1.8.0
    namespace: db-operator
    installed: true
 
 - name: db-instance
    installed: true
    namespace: db-operator
    chart: db-operator/db-instances
    version: 1.4.1
    values:
      - dbinstances:
          azure-postgres:
            monitoring:
              enabled: false
            adminSecretRef:
              Name: postgres-generic-admin-secret
              Namespace: db-operator
            engine: postgres
            generic:
              host: gcx-solutions-postgresql-flexi.postgres.database.azure.com
              port: 5432
            secrets:
              adminUser: ***
              adminPassword: ***
            sslConnection:
              enabled: true
              skipVerify: false

$ helmfile -l name=cert=manager sync
$ helmfile -l name=db-operator sync
$ helmfile -l name=db-instance sync
$ kubectl get dbin
NAME             PHASE     STATUS
azure-postgres   Running   true

Then create a database resource

# db.yaml
---
apiVersion: "kinda.rocks/v1beta1"
kind: "Database"
metadata:
  name: postgres-db
spec:
  secretName: bega-pg-sec
  instance: azure-postgres
  deletionProtected: false
  postgres:
    extensions:
      - uuid-ossp
    schemas:
      - application
  secretsTemplates:
    CONNECTION_STRING: "jdbc:{{ .Protocol }}://{{ .DatabaseHost }}:5432/{{ .DatabaseName }}?ssl=true&sslmode=require&currentSchema=application"
  backup:
    enable: false
    cron: ""
  cleanup: true

$ kubectl apply -f db.yaml
$ kubectl get db
NAME          PHASE   STATUS   PROTECTED   DBINSTANCE       AGE
postgres-db   Ready   true     false       azure-postgres   17m

And then you can get data from the bega-pg-sec to connect to the database.

This db-operator doesn't really work with schemas, it can create them, and drop the public one. So the approach of using schemas for applications won't really work. because it creates a user per db. We can add mode schemas to .spec.postgres.schemas, and then add another templated secret that will create a connection string for that schema, but a new user won't be created and applications will have to share one user.

If the deletionProtected is set to true, database on the server won't be removed when database resource is gone.