Kratos integration with Hydra fails when using CockroachDB as DSN but works with other SQL databases

[abhicodeswellenough]

I’m facing an issue while integrating Kratos with Hydra using CockroachDB as the DSN for Kratos. The problem occurs when the following oauth2_provider configuration is included in the Kratos YAML file:

Kratos.yml

oauth2_provider:
  url: http://hydra:4445 # Integrate Kratos with Hydra

Kratos.yml

version: v1.1.0

dsn: cockroach://root@cockroachd:26257/defaultdb?sslmode=disable&max_conns=20&max_idle_conns=4

serve:
  public:
    base_url: http://127.0.0.1:4433
    cors:
      enabled: true
  admin:
    base_url: http://127.0.0.1:4434

selfservice:
  default_browser_return_url: http://127.0.0.1:4455/
  allowed_return_urls:
    - http://127.0.0.1:4455

  methods:
    password:
      enabled: true
      config:
        min_password_length: 6
        identifier_similarity_check_enabled: false
        haveibeenpwned_enabled: false

  flows:
    error:
      ui_url: http://127.0.0.1:4455/error

    settings:
      ui_url: http://127.0.0.1:4455/settings
      privileged_session_max_age: 15m
      required_aal: highest_available

    logout:
      after:
        default_browser_return_url: http://127.0.0.1:4455/login

    login:
      ui_url: http://127.0.0.1:4455/login

    registration:
      ui_url: http://127.0.0.1:4455/registration
      after:
        password:
          hooks:
            - hook: session

log:
  format: text
  leak_sensitive_values: true

secrets:
  cookie:
    - PLEASE-CHANGE-ME-I-AM-VERY-INSECURE
  cipher:
    - 32-LONG-SECRET-NOT-SECURE-AT-ALL

identity:
  default_schema_id: default
  schemas:
    - id: default
      url: file:///etc/config/kratos/identity.schema.json

courier:
  smtp:
    connection_uri: smtps://test:test@mailslurper:1025/?skip_ssl_verify=true

oauth2_provider:
  url: http://hydra:4445 # Integrate Kratos and Hydra

hydra.yml

serve:
  cookies:
    same_site_mode: Lax

urls:
  self:
    issuer: http://127.0.0.1:4444
  consent: http://127.0.0.1:4455/consent
  login: http://127.0.0.1:4455/login
  logout: http://127.0.0.1:4455/logout
  identity_provider:
    publicUrl: http://127.0.0.1:4433
    url: http://127.0.0.1:4434


secrets:
  system:
    - youReallyNeedToChangeThis

oidc:
  subject_identifiers:
    supported_types:
      - pairwise
      - public
    pairwise:
      salt: youReallyNeedToChangeThis

log:
  leak_sensitive_values: true
  level: debug

quickstart-crub.yml

version: '3.7'

services:
  kratos-migrate:
    image: oryd/kratos:v0.10.0
    environment:
      - DSN=cockroach://root@cockroachd:26257/defaultdb?sslmode=disable&max_conns=20&max_idle_conns=4
    command: migrate sql -e --yes
    depends_on:
      cockroachd:
        condition: service_healthy
    volumes:
      - type: bind
        source: ./config
        target: /etc/config/kratos
    networks:
      - intranet
    restart: on-failure

  hydra-migrate:
    image: oryd/hydra:v2.2.0
    environment:
      - DSN=cockroach://root@cockroachd:26257/hydradb?sslmode=disable&max_conns=20&max_idle_conns=4
    command: migrate -c /etc/config/hydra/hydra.yml sql -e --yes
    volumes:
      - type: bind
        source: ./config
        target: /etc/config/hydra
        # read_only: false
    depends_on:
      cockroachd:
        condition: service_healthy
      kratos-migrate:
        condition: service_completed_successfully  # **Added Dependency**
    restart: on-failure
    networks:
      - intranet

  kratos:
    image: oryd/kratos:v0.10.0
    environment:
      - DSN=cockroach://root@cockroachd:26257/defaultdb?sslmode=disable&max_conns=20&max_idle_conns=4
    command: serve -c /etc/config/kratos/kratos.yml
    ports:
      - "4433:4433"
      - "4434:4434"
    volumes:
      - type: bind
        source: ./config
        target: /etc/config/kratos
    depends_on:
      cockroach-init:
        condition: service_completed_successfully
      kratos-migrate:
        condition: service_completed_successfully
      hydra-migrate:
        condition: service_completed_successfully
    networks:
      - intranet
    restart: on-failure

  hydra:
    image: oryd/hydra:v2.2.0
    ports:
      - "4444:4444" # Public port
      - "4445:4445" # Admin port
      - "5555:5555" # Port for hydra token user
    command: serve -c /etc/config/hydra/hydra.yml all --dev
    volumes:
      - type: bind
        source: ./config
        target: /etc/config/hydra
    environment:
      - DSN=cockroach://root@cockroachd:26257/hydradb?sslmode=disable&max_conns=20&max_idle_conns=4
    depends_on:
      hydra-migrate:
        condition: service_completed_successfully
    restart: unless-stopped
    networks:
      - intranet

  kratos-selfservice-ui-node:
    image: oryd/kratos-selfservice-ui-node:v1.3.1
    environment:
      - KRATOS_PUBLIC_URL=http://kratos:4433/
      - KRATOS_BROWSER_URL=http://127.0.0.1:4433/
      - COOKIE_SECRET=changeme
      - CSRF_COOKIE_NAME=ory_csrf_ui
      - CSRF_COOKIE_SECRET=changeme
    ports:
      - "4455:3000"
    depends_on:
      - kratos
    networks:
      - intranet
    restart: on-failure

  cockroachd:
    image: cockroachdb/cockroach:v22.2.6
    ports:
      - "26257:26257"
      - "8080:8080"
    command: start-single-node --insecure
    networks:
      - intranet
    volumes:
      - ./cockroach_data:/cockroach/cockroach-data
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:8080/health?ready=1"]
      interval: 3s
      timeout: 3s
      retries: 10
      start_period: 5s

  cockroach-init:
    image: cockroachdb/cockroach:v22.2.6
    depends_on:
      cockroachd:
        condition: service_healthy
    command: >
      bash -c "
        sleep 10 && 
        cockroach sql --insecure --host cockroachd --execute \"CREATE DATABASE IF NOT EXISTS kratosdb; CREATE DATABASE IF NOT EXISTS hydradb;\"
      "
    networks:
      - intranet
    restart: "no"  # Do not restart after completion

  # consent:
  #   environment:
  #     - HYDRA_ADMIN_URL=http://hydra:4445
  #   image: oryd/hydra-login-consent-node:v2.2.0
  #   ports:
  #     - "3000:3000"
  #   restart: unless-stopped
  #   networks:
  #     - intranet

networks:
  intranet:
    driver: bridge

Observations:

1. When I comment out the oauth2_provider section, Kratos works fine, even with CockroachDB.
2. The issue does not occur if I use a different SQL backend (e.g., PostgreSQL) instead of CockroachDB.
3. Hydra is up and running at http://hydra:4445, and the integration works perfectly with SQL databases other than CockroachDB.

Question:

Why does the integration with Hydra (oauth2_provider) fail when using CockroachDB as the DSN? How can I resolve this issue while keeping CockroachDB as the backend for Kratos?

I expect Kratos to use Hydra for authorization when the oauth2_provider configuration is enabled.

[abhicodeswellenough]

I have used wrong kratos image version and we can use this image
image: oryd/kratos:v1.1.0