bypass zitadel's database init

Per the issue linked below, Zitadel (an IDP), does not let user initialize their own database during their “init” phase without giving zitadel the postgres root password.

https://github.com/zitadel/zitadel/issues/5810#issuecomment-1654642463

For reference, the phases are detailed here:

https://zitadel.com/docs/self-hosting/manage/updating_scaling

To work around this, create the schema in a separate temporary instance of postgres using the default “init” flow (with some default root password), dump the SQL and then restore it into our destination database. Then, we can run Zitadel from the setup phase.

# quick and dirty postgres instance

Something basic like this suffices:

$ docker run --rm --name postgres \
    --network postgres \
    -p 5432:5432 \
    -e POSTGRES_DB=postgres \
    -e POSTGRES_USER=postgres \
    -e POSTGRES_PASSWORD=postgres \
    postgres:latest

# quick and dirty zitadel

We can use a compose file here, since they provided us one, to initialize the database we just ran.

networks:
  postgres:
    external: true

services:
  zitadel:
    restart: 'unless-stopped'
    networks:
      - postgres
    image: 'ghcr.io/zitadel/zitadel:stable'
    command: 'start-from-init --masterkeyFromEnv --tlsMode disabled'
    environment:
      # postgres details: it doesn't matter here since we'll only dump the
      # initialized SQL schema
      ZITADEL_DATABASE_POSTGRES_HOST: postgres
      ZITADEL_DATABASE_POSTGRES_PORT: 5432
      ZITADEL_DATABASE_POSTGRES_DATABASE: zitadel
      ZITADEL_DATABASE_POSTGRES_USER_USERNAME: zitadel
      ZITADEL_DATABASE_POSTGRES_USER_PASSWORD: zitadel
      ZITADEL_DATABASE_POSTGRES_USER_SSL_MODE: disable
      ZITADEL_DATABASE_POSTGRES_ADMIN_USERNAME: postgres
      ZITADEL_DATABASE_POSTGRES_ADMIN_PASSWORD: postgres
      ZITADEL_DATABASE_POSTGRES_ADMIN_SSL_MODE: disable
      ZITADEL_DATABASE_POSTGRES_MAXOPENCONNECTIONS: "25"
      ZITADEL_DATABASE_POSTGRES_MAXIDLECONNECTIONS: "10"
      # first org/admin connections and credentials
      ZITADEL_EXTERNALSECURE: false
      ZITADEL_EXTERNALDOMAIN: "<domain.org>"
      ZITADEL_LOGSTORE_ACCESS_STDOUT_ENABLED: "true"
      ZITADEL_FIRSTINSTANCE_ORG_HUMAN_PASSWORDCHANGEREQUIRED: "false"
      ZITADEL_FIRSTINSTANCE_ORG_NAME: "<auth.domain.org>"
      ZITADEL_FIRSTINSTANCE_ORG_HUMAN_USERNAME: "<auth_admin_username>"
      ZITADEL_FIRSTINSTANCE_ORG_HUMAN_PASSWORD: "<auth_admin_password>"
      ZITADEL_MASTERKEY: "<some_long_alphanumeric_string>"
      # change the hashing here, if the production zitadel instance
      # uses a different hash, you won't be able to login with the
      # first_instance credentials above.
      ZITADEL_SYSTEMDEFAULTS_PASSWORDHASHER_HASHER_ALGORITHM: "scrypt"
      ZITADEL_SYSTEMDEFAULTS_PASSWORDHASHER_HASHER_COST: "5"

# dumping the database

$ docker exec -t postgres pg_dump -c -U zitadel > /tmp/zitadel-init.sql

# restoring to production database

Assuming the database is zitadel and the user is zitadel:

$ docker exec -i postgres psql -U zitadel -d zitadel < /tmp/zitadel-init.sql