How to use certs instead of passwords for db authentication to cockroachdb?

[jessebot]

Hi Zitadel friends!

I'm deploying both the cockroachdb and zitadel helm charts through argocd. cockroachdb values:

conf:
  singe-node: true
statefulset:
  replicas: 1
init:
  jobAnnotations:
    argocd.argoproj.io/hook: Sync
  annotations:
    argocd.argoproj.io/hook: Sync
provisioning:
  enabled: true
  users:
    - name: zitadel
      # this sets password to NULL which forces cert auth which should prevent password authentication
      # ref: https://www.cockroachlabs.com/docs/stable/create-user#prevent-a-user-from-using-password-authentication
      password:
    - name: root
      password:
  databases:
    - name: zitadel
      owners:
        - zitadel
        - root

Here's the values.yaml for zitadel:

zitadel:
  # See all defaults here:
  # https://github.com/zitadel/zitadel/blob/main/cmd/defaults.yaml
  configmapConfig:
    Database:
      cockroach:
        Host: "zitadel-cockroachdb-public"
        User:
          SSL:
            Username: zitadel
            Mode: "verify-full"
        Admin:
          SSL:
            Username: root
            Mode: "verify-full"

    Machine:
      Identification:
        Hostname:
          Enabled: true
        Webhook:
          Enabled: false

    # setup ZITADEL with a service account
    FirstInstance:
      Org:
        Machine:
          Machine:
            # Creates a service account with the name zitadel-admin-sa,
            # which results in a secret 'zitadel-admin-sa' with a key 'zitadel-admin-sa.json'
            Username: zitadel-admin-sa
            Name: Admin
          MachineKey:
            Type: 1

  # Reference the name of the secret that contains the masterkey. The key should be named "masterkey".
  # Note: Either zitadel.masterkey or zitadel.masterkeySecretName must be set
  masterkeySecretName: "obfuscated"

  # The Secret containing the root CA Certificate at key ca.crt needed for establishing secure database connections
  dbSslRootCrtSecret: "zitadel-cockroachdb-ca-secret"

  # The Secret containing the client CA Certificate and key at tls.crt and tls.key needed for establishing secure database connections
  dbSslClientCrtSecret: "zitadel-cockroachdb-client-secret"

cockroachdb is up, but one of zitadel's helm hook setup jobs seems to fail with:

time="2023-08-17T16:24:46Z" level=info msg="initialization started" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/init.go:72"

time="2023-08-17T16:24:46Z" level=info msg="verify user" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/verify_user.go:38" username=zitadel

time="2023-08-17T16:24:46Z" level=info msg="verify database" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/verify_database.go:38" database=zitadel

time="2023-08-17T16:24:46Z" level=info msg="verify grant" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/verify_grant.go:33" database=zitadel user=zitadel

time="2023-08-17T16:24:46Z" level=info msg="verify zitadel" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/verify_zitadel.go:69" database=zitadel

time="2023-08-17T16:24:46Z" level=fatal msg="unable to initialize ZITADEL" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/init.go:68" error="ID=DATAB-0pIWD Message=Errors.Database.Connection.Failed Parent=(failed to connect to `host=zitadel-cockroachdb-public user=zitadel database=zitadel`: failed SASL auth (ERROR: password authentication failed for user zitadel (SQLSTATE 28P01)))"

I verified the secrets are in place and cockroach db is happy (both zitadel and cockroachdb are installed in the same namespace via the magic of argocd, ref: app, appSet):

$ kubectl get secrets
NAME                                TYPE                DATA   AGE
zitadel-cockroachdb-ca-secret       Opaque              2      39m
zitadel-cockroachdb-client-secret   kubernetes.io/tls   3      39m
zitadel-cockroachdb-node-secret     kubernetes.io/tls   3      39m

$ kubectl get pods
NAME                             READY   STATUS      RESTARTS   AGE
zitadel-cockroachdb-0            1/1     Running     0          39m
zitadel-cockroachdb-init-p5fzw   0/1     Completed   0          39m

What am I still doing wrong? 🤔

[jessebot]

Maybe since cockroachDb is only used for zitadel and restricted to this namespace anyway, I could just create one user for both zitadel and the admin user 🤔 ... I'll try that. It's a common approach with isolated databases in other stacks that use postgres, for instance. I'll report in findings as I go. This is the last thing blocking the setup of zitadel so that I can move onto testing the API and then I can go play with the Go sdk.

[jessebot]

yeah, nothing I seem to try works. I always get the same error:

time="2023-08-17T16:56:21Z" level=info msg="initialization started" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/init.go:72"

time="2023-08-17T16:56:21Z" level=info msg="verify user" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/verify_user.go:38" username=zitadel

time="2023-08-17T16:56:21Z" level=info msg="verify database" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/verify_database.go:38" database=zitadel

time="2023-08-17T16:56:21Z" level=info msg="verify grant" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/verify_grant.go:33" database=zitadel user=zitadel

time="2023-08-17T16:56:21Z" level=info msg="verify zitadel" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/verify_zitadel.go:69" database=zitadel

time="2023-08-17T16:56:21Z" level=fatal msg="unable to initialize ZITADEL" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/init.go:68" error="ID=DATAB-0pIWD Message=Errors.Database.Connection.Failed Parent=(failed to connect to `host=zitadel-cockroachdb-public user=zitadel database=zitadel`: failed SASL auth (ERROR: password authentication failed for user zitadel (SQLSTATE 28P01)))"

[jessebot]

cockroachDb seemed like a great idea, because it would allow restrictions to only certificateAuthenticaion, but I can't get this to work (and they don't have an easy way to pass in an existing secret for a user's password in their init job in their helm chart). I know postgresql a lot better, so I'll just use that since I just noticed that's an option 💙 ✨ If I can get that working, I might submit a PR to add some docs to this repo as I haven't seen any for postgres here in this repo or on the k8s docs on the zitadel website.

[fforootd]

Just to get this right, you want to authenticate from zitadel to CRDB with client certificate auth instead of password, right?

In that case you would need to pass the private key as this variable https://github.com/zitadel/zitadel/blob/main/cmd/defaults.yaml#L98

Some inspiration how you can start zitadel with client cert auth can be found in this compose file too https://zitadel.com/docs/self-hosting/deploy/loadbalancing-example

[jessebot]

awesome, thank you! I will try this later today!

[jessebot]

I got around to looking at this more, and I'm confused, because the secrets seem to already exist:

$ kubectl get secrets
NAME                                TYPE                DATA   AGE
zitadel-core-key                    Opaque              1      11m
zitadel-db-credentials              Opaque              4      11m
zitadel-cockroachdb-ca-secret       Opaque              2      10m
zitadel-cockroachdb-client-secret   kubernetes.io/tls   3      10m
zitadel-cockroachdb-node-secret     kubernetes.io/tls   3      10m

here's the init job's pod description:

Name:             zitadel-init-smnls
Namespace:        zitadel
Priority:         0
Service Account:  zitadel
Node:             ax102/136.243.32.57
Start Time:       Sat, 19 Aug 2023 20:29:56 +0200
Labels:           app.kubernetes.io/component=init
                  app.kubernetes.io/instance=zitadel
                  app.kubernetes.io/name=zitadel
                  batch.kubernetes.io/controller-uid=08f092d9-9d67-4922-9db4-51c281ac78cd
                  batch.kubernetes.io/job-name=zitadel-init
                  controller-uid=08f092d9-9d67-4922-9db4-51c281ac78cd
                  job-name=zitadel-init
Annotations:      <none>
Status:           Running
IP:               10.42.0.52
IPs:
  IP:           10.42.0.52
Controlled By:  Job/zitadel-init
Init Containers:
  chown:
    Container ID:  containerd://d2c28f97bc7ec78343c225d4f0105a0609c7cd222e1dbf32df266575cc907396
    Image:         alpine:3.11
    Image ID:      docker.io/library/alpine@sha256:bcae378eacedab83da66079d9366c8f5df542d7ed9ab23bf487e3e1a8481375d
    Port:          <none>
    Host Port:     <none>
    Command:
      sh
      -c
    Args:
      cp /db-ssl-client-crt/* /chowned-secrets/ && cp /db-ssl-root-crt/* /chowned-secrets/ &&  chown -R 1000:1000 /chowned-secrets/* && chmod 400 /chowned-secrets/*
    State:          Terminated
      Reason:       Completed
      Exit Code:    0
      Started:      Sat, 19 Aug 2023 20:29:56 +0200
      Finished:     Sat, 19 Aug 2023 20:29:56 +0200
    Ready:          True
    Restart Count:  0
    Environment:    <none>
    Mounts:
      /chowned-secrets from chowned-secrets (rw)
      /db-ssl-client-crt from db-ssl-client-crt (rw)
      /db-ssl-root-crt from db-ssl-root-crt (rw)
      /var/run/secrets/kubernetes.io/serviceaccount from kube-api-access-b2m5f (ro)
Containers:
  zitadel-init:
    Container ID:  containerd://0fcd77e7c9c76b657775b1a9d7b01748d397f1092927689a951c22fb6285f2d1
    Image:         ghcr.io/zitadel/zitadel:v2.28.1
    Image ID:      ghcr.io/zitadel/zitadel@sha256:82b80f32cd5c55a89f6a5984df2dfe21c77d57bfc9186a292e6978fa19282738
    Port:          <none>
    Host Port:     <none>
    Args:
      init
      --config
      /config/zitadel-config-yaml
    State:          Waiting
      Reason:       CrashLoopBackOff
    Last State:     Terminated
      Reason:       Error
      Exit Code:    1
      Started:      Sat, 19 Aug 2023 20:31:27 +0200
      Finished:     Sat, 19 Aug 2023 20:31:28 +0200
    Ready:          False
    Restart Count:  4
    Environment:
      POD_IP:                                          (v1:status.podIP)
      ZITADEL_DATABASE_COCKROACH_USER_SSL_ROOTCERT:   /.secrets/ca.crt
      ZITADEL_DATABASE_COCKROACH_ADMIN_SSL_ROOTCERT:  /.secrets/ca.crt
      ZITADEL_DATABASE_COCKROACH_ADMIN_SSL_CERT:      /.secrets/tls.crt
      ZITADEL_DATABASE_COCKROACH_ADMIN_SSL_KEY:       /.secrets/tls.key
      ZITADEL_EXTERNAL_DOMAIN:                        zitadel.vleermuis.tech
    Mounts:
      /.secrets from chowned-secrets (rw)
      /config from zitadel-config-yaml (rw)
      /var/run/secrets/kubernetes.io/serviceaccount from kube-api-access-b2m5f (ro)
Conditions:
  Type              Status
  Initialized       True
  Ready             False
  ContainersReady   False
  PodScheduled      True
Volumes:
  zitadel-config-yaml:
    Type:      ConfigMap (a volume populated by a ConfigMap)
    Name:      zitadel-config-yaml
    Optional:  false
  db-ssl-root-crt:
    Type:        Secret (a volume populated by a Secret)
    SecretName:  zitadel-cockroachdb-ca-secret
    Optional:    false
  db-ssl-client-crt:
    Type:        Secret (a volume populated by a Secret)
    SecretName:  zitadel-cockroachdb-client-secret
    Optional:    false
  chowned-secrets:
    Type:       EmptyDir (a temporary directory that shares a pod's lifetime)
    Medium:
    SizeLimit:  <unset>
  kube-api-access-b2m5f:
    Type:                    Projected (a volume that contains injected data from multiple sources)
    TokenExpirationSeconds:  3607
    ConfigMapName:           kube-root-ca.crt
    ConfigMapOptional:       <nil>
    DownwardAPI:             true
QoS Class:                   BestEffort
Node-Selectors:              <none>
Tolerations:                 node.kubernetes.io/not-ready:NoExecute op=Exists for 300s
                             node.kubernetes.io/unreachable:NoExecute op=Exists for 300s
Events:
  Type     Reason     Age                  From               Message
  ----     ------     ----                 ----               -------
  Normal   Scheduled  2m15s                default-scheduler  Successfully assigned zitadel/zitadel-init-smnls to ax102
  Normal   Pulled     2m15s                kubelet            Container image "alpine:3.11" already present on machine
  Normal   Created    2m15s                kubelet            Created container chown
  Normal   Started    2m15s                kubelet            Started container chown
  Warning  BackOff    58s (x7 over 2m12s)  kubelet            Back-off restarting failed container zitadel-init in pod zitadel-init-smnls_zitadel(f700df9b-a083-4edf-857e-4df197854a14)
  Normal   Pulled     44s (x5 over 2m14s)  kubelet            Container image "ghcr.io/zitadel/zitadel:v2.28.1" already present on machine
  Normal   Created    44s (x5 over 2m14s)  kubelet            Created container zitadel-init
  Normal   Started    44s (x5 over 2m14s)  kubelet            Started container zitadel-init

but the logs just show:

time="2023-08-19T18:34:12Z" level=info msg="initialization started" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/init.go:72"

time="2023-08-19T18:34:12Z" level=info msg="verify user" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/verify_user.go:38" username=zitadel

time="2023-08-19T18:34:12Z" level=info msg="verify database" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/verify_database.go:38" database=zitadel

time="2023-08-19T18:34:12Z" level=info msg="verify grant" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/verify_grant.go:33" database=zitadel user=zitadel

time="2023-08-19T18:34:12Z" level=info msg="verify zitadel" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/verify_zitadel.go:69" database=zitadel

time="2023-08-19T18:34:12Z" level=fatal msg="unable to initialize ZITADEL" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/init.go:68" error="ID=DATAB-0pIWD Message=Errors.Database.Connection.Failed Parent=(failed to connect to `host=zitadel-cockroachdb-public user=zitadel database=zitadel`: failed SASL auth (ERROR: password authentication failed for user zitadel (SQLSTATE 28P01)))"

Can you please explain what these values in values.yaml do?

  # The Secret containing the root CA Certificate at key ca.crt needed for establishing secure database connections
  dbSslRootCrtSecret: "zitadel-cockroachdb-ca-secret"

  # The Secret containing the client CA Certificate and key at tls.crt and tls.key needed for establishing secure database connections
  dbSslClientCrtSecret: "zitadel-cockroachdb-client-secret"

I've been passing them in thinking that they are the tls.key and tls.crt required here 🤔

This is important becuase I can't proceed without the certs, due to this bug, so I can't actually use just postgres with a username and password, for instance.