Dom
Dom•3y ago

Connect to Azure Database for PostgreSQL flexible servers with require_secure_transport ON

The question partly relates to https://github.com/zitadel/zitadel/issues/6106 The official Azure docs for connecting via SSL are here: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-connect-tls-ssl#applications-that-require-certificate-verification-for-tlsssl-connectivity We deploy (or want to 🤣 ) Zitadel using the official helm chart https://github.com/zitadel/zitadel-charts/tree/main via helmfile The source error that deviates from https://zitadel.com/docs/self-hosting/manage/database and zitadel#6106 is: host=[REDACTED].postgres.database.azure.com user=[REDACTED] database=postgres: server error (FATAL: no pg_hba.conf entry for host \"[REDACTED]\", user \"[REDACTED]\", database \"postgres\", no encryption (SQLSTATE 28000))) Obviously due to the fact that I try to connect with SSL disable. Now, I can not really stitch 1+1 together how I would configure that with the values possible: https://github.com/zitadel/zitadel-charts/blob/main/charts/zitadel/values.yaml. Disabling SSL is not an option atm, see: https://github.com/zitadel/zitadel/issues/3598#issuecomment-1610968551 Does anyone have a working example of using SSL? What mode would I pick? https://www.postgresql.org/docs/current/libpq-ssl.html (I believe verify-full is wrong as the cert seems to be somewhat generic...) This is my current config fully rendered into the ConfigMap (thread). I see the values dbSslRootCrt, dbSslRootCrtSecret (secret of the first one) and dbSslClientCrtSecret, but I can not stitch these two together. Any pointer helps ❤️ Its to note that we don't need TLS, our Gateways do that for us. PS: Yes I will (try to) move it into the secretConfig once it works.
7 Replies
Dom
DomOP•3y ago
data:
+   zitadel-config-yaml: |-
+
+     Database:
+       cockroach:
+         Admin:
+           SSL:
+             Mode: verify-full
+         Host: crdb-public
+         User:
+           SSL:
+             Mode: verify-full
+       postgres:
+         Admin:
+           Password: [REDACTED]
+           SSL:
+             Cert: null
+             Key: null
+             Mode: disable
+             RootCert: null
+           Username: [REDACTED]
+         Database: zitadel
+         Host: [REDACTED].postgres.database.azure.com
+         MaxConnIdleTime: 5m
+         MaxConnLifetime: 1h
+         MaxOpenConns: 50
+         Options: null
+         Port: "5432"
+         User:
+           Password: [REDACTED]
+           SSL:
+             Cert: null
+             Key: null
+             Mode: disable
+             RootCert: null
+           Username: [REDACTED]
+     DefaultInstance:
+       InstanceName: [REDACTED]
+     ExternalDomain: [REDACTED]
+     ExternalPort: 443
+     ExternalSecure: true
+     Log:
+       Level: info
+     LogStore:
+       Access:
+         Stdout:
+           Enabled: true
+     Machine:
+       Identification:
+         Hostname:
+           Enabled: true
+         Webhook:
+           Enabled: false
+     TLS:
+       Enabled: false
data:
+   zitadel-config-yaml: |-
+
+     Database:
+       cockroach:
+         Admin:
+           SSL:
+             Mode: verify-full
+         Host: crdb-public
+         User:
+           SSL:
+             Mode: verify-full
+       postgres:
+         Admin:
+           Password: [REDACTED]
+           SSL:
+             Cert: null
+             Key: null
+             Mode: disable
+             RootCert: null
+           Username: [REDACTED]
+         Database: zitadel
+         Host: [REDACTED].postgres.database.azure.com
+         MaxConnIdleTime: 5m
+         MaxConnLifetime: 1h
+         MaxOpenConns: 50
+         Options: null
+         Port: "5432"
+         User:
+           Password: [REDACTED]
+           SSL:
+             Cert: null
+             Key: null
+             Mode: disable
+             RootCert: null
+           Username: [REDACTED]
+     DefaultInstance:
+       InstanceName: [REDACTED]
+     ExternalDomain: [REDACTED]
+     ExternalPort: 443
+     ExternalSecure: true
+     Log:
+       Level: info
+     LogStore:
+       Access:
+         Stdout:
+           Enabled: true
+     Machine:
+       Identification:
+         Hostname:
+           Enabled: true
+         Webhook:
+           Enabled: false
+     TLS:
+       Enabled: false
FFO
FFO•3y ago
It is strange that you have cockroach and postgres in you config, that should not happen 😄 Can you share your values file? @Elio what is you take on this? The SSL settings I think should be mode: verify-full and you need to pass along the RootCert you want to use to make the verification. Like in the M$ example you provided.
Dom
DomOP•3y ago
Yeah I found it fishy but I thought thats how it should be. The docs say postgres wins so I thought, alright fine. 
zitadel:
  dbSslRootCrtSecret: null
  dbSslClientCrtSecret: null
  masterkey: ref+azurekeyvault://redacted-vault/main-key
  configmapConfig:
    ExternalDomain: [REDACTED] # ! Changing this breaks the system
    ExternalPort: 443 # ! Changing this breaks the system
    ExternalSecure: true # ! Changing this breaks the system
    Log:
      Level: 'info'
    LogStore:
      Access:
        Stdout:
          Enabled: true
    TLS:
      Enabled: false # Ingress does this
    DefaultInstance:
      InstanceName: IAM
    Database:
      postgres:
        Host: ref+azurekeyvault://redacted-vault/host
        Port: ref+azurekeyvault://redacted-vault/port
        Database: ref+azurekeyvault://redacted-vault/database
        MaxOpenConns: 50
        MaxConnLifetime: 1h
        MaxConnIdleTime: 5m
        Options:
        User:
          Username: zitadel
          Password: zitadel
          SSL:
            Mode: disable
            RootCert:
            Cert:
            Key:
        Admin:
          Username: ref+azurekeyvault://redacted-vault/username
          Password: ref+azurekeyvault://redacted-vault/password
          SSL:
            Mode: disable
            RootCert:
            Cert:
            Key:
zitadel:
  dbSslRootCrtSecret: null
  dbSslClientCrtSecret: null
  masterkey: ref+azurekeyvault://redacted-vault/main-key
  configmapConfig:
    ExternalDomain: [REDACTED] # ! Changing this breaks the system
    ExternalPort: 443 # ! Changing this breaks the system
    ExternalSecure: true # ! Changing this breaks the system
    Log:
      Level: 'info'
    LogStore:
      Access:
        Stdout:
          Enabled: true
    TLS:
      Enabled: false # Ingress does this
    DefaultInstance:
      InstanceName: IAM
    Database:
      postgres:
        Host: ref+azurekeyvault://redacted-vault/host
        Port: ref+azurekeyvault://redacted-vault/port
        Database: ref+azurekeyvault://redacted-vault/database
        MaxOpenConns: 50
        MaxConnLifetime: 1h
        MaxConnIdleTime: 5m
        Options:
        User:
          Username: zitadel
          Password: zitadel
          SSL:
            Mode: disable
            RootCert:
            Cert:
            Key:
        Admin:
          Username: ref+azurekeyvault://redacted-vault/username
          Password: ref+azurekeyvault://redacted-vault/password
          SSL:
            Mode: disable
            RootCert:
            Cert:
            Key:
The top two nulls I tried to at least make the init-container work but then hit the blocks with the SSL 🤤 That is my full values of the chart.
The SSL settings I think should be mode: verify-full and you need to pass along the RootCert you want to use to make the verification. Like in the M$ example you provided.
Do I pass the cert from Microsoft also to the top-level db* params somewhere?
Dom
DomOP•3y ago
Alright, made it work 😓 You guys help me out even though PSQL is an enterprise feature. So, I'd like to pay back something and improve your docs. I think I also found one or two bugs (depends if you sum them up). Here is a redacted version that works with SSL against Azure Flexible Postgres. Some points: 1. You need to trick the chart into not rendering the CRDB secrets by nulling values. 2. How to use RootCert is very foggy, one has to drill down deep holes to fill the correct "thing" to put there 😁 3. The CRDB configMap always renders but I somehow feel that is also an issue in zitadel-charts not zitadel itself. Let me know if you'd like to pick up something into your backlogs yourself or else I try to at least extend the docs a bit 🚀 I see the challenge that the docs must basically merge zitadel and zitadel-charts 😏

SPOILER_zitadel_flex...

FFO
FFO•3y ago
Oh wow thanks for sharing 🤩 @Elio do you have an idea whats wrong with our helm-chart?
Elio
Elio•3y ago
I think we should discuss config variants in this issue https://github.com/zitadel/zitadel-charts/issues/91 and give it a high priority
GitHub
Add and test examples for common values · Issue #91 · zitadel/zitad...
As a user, I want to have an example for my use case, so that I can create a fast proof of concept. Acceptance Criteria An example for ZITADEL with an insecure Cockroach DB is available and automat...
Dom
DomOP•3y ago
My pleasure, please let me know where I can contribute docs from our experience. I would write the section about Azure Flex PSQL if there is one 🚀

Did you find this page helpful?