cockroachdb supported?

[ghost]

Greetings,

I am trying to setup a cockroach backend for Dex right now. I have had success with the postgres backend. However, I can't get the initial migration to happen with cockroach. Do I use type: postgres? If anyone can help me figure this out, then i'd be happy to add it to the docs.

Below is my setup

docker-compose.yml

storage:
  type: postgres
  config:
    host: roach-0
    port: 9000
    database: dex
    user: dex_admin
    password: password
    ssl:
      mode: disable

cockroach is running in insecure mode. You might try the following setup:

version: "3.9"
services:
roach-0:
  container_name: roach-0
  image: cockroachdb/cockroach:latest
  volumes:
    - cockroach_data_0:/cockroach/cockroach-data
  command: start --insecure --locality=datacenter=vita_net --join=roach-0,roach-1,roach-2
  ports:
    - "9000:26257"
    - "8080:8080"
  networks:
    - vita_net
  hostname: roach-0
roach-1:
  container_name: roach-1
  image: cockroachdb/cockroach:latest
  volumes:
    - cockroach_data_1:/cockroach/cockroach-data
  command: start --insecure --locality=datacenter=vita_net --join=roach-0,roach-1,roach-2
  networks:
    - vita_net
  hostname: roach-1
roach-2:
  container_name: roach-2
  image: cockroachdb/cockroach:latest
  volumes:
    - cockroach_data_2:/cockroach/cockroach-data
  command: start --insecure --locality=datacenter=vita_net --join=roach-0,roach-1,roach-2
  networks:
    - vita_net
  hostname: roach-2
dex:
  image: quay.io/dexidp/dex
  volumes:
    - "./dex-dev-config.yaml:/dex-config.yaml"
  command: ["dex", "serve", "/dex-config.yaml"]
  depends_on:
    - roach-0
  ports:
    - "5554:5554"
    - "5555:5555"
    - "5556:5556"
    - "5557:5557"
  restart: always

volumes:
cockroach_data_0:
cockroach_data_1:
cockroach_data_2:

networks:
vita_net:
  driver: bridge

dex-dev-config.yaml

issuer: http://127.0.0.1:5556/dexv
 storage:
  type: postgres
  config:
    host: roach-0
    port: 9000
    database: dex
    user: dex_admin
    password: password
    ssl:
      mode: disable


enablePasswordDB: true

staticPasswords:
  - email: "[email protected]"
    # bcrypt hash of the string "password"
    hash: "$2a$10$2b2cU8CPhOTaGrs1HRQuAueS7JTT5ZHsHSzYiFPm1leZck7Mc8T4W"
    username: "admin"
    userID: "08a8684b-db88-4b73-90a9-3cd1661f5466"

After running docker-compose up, you'll need this init.sql in the same directory as the init.sh script

init.sql

CREATE USER dex_admin WITH CREATEDB;
CREATE DATABASE dex 
WITH
    ENCODING   = 'UTF8'
    CONNECTION LIMIT = -1;
GRANT ALL PRIVILEGES ON DATABASE dex TO dex_admin;

init.sh

#!/usr/bin/env bash

set -e

docker exec -it roach-0 /cockroach/cockroach init --insecure

cockroach sql --user=root \
              --insecure \
              --host=localhost:9000 \
              --file init.sql

[sagikazarmark]

Your config seems correct to me on first sight. Unfortunately, I'm not very familiar with CockroachDB.

Can you enable debug logging to see if any error gets ignored somewhere?

logger:
  level: "debug"

And then post logs here?

[sagikazarmark]

It seems to be a DNS issue to me, at least based on the log.

Another thing that might go wrong is that Dex is not on the same network as the cockroach containers. container_name might also interfere with things.

If I were you, I'd start with a minimal setup (single cockroach instance, no network trickery, etc) and go from there.

[ghost]

Wow, yes I forgot to set dex to the same network. Once, I did that the logs did change a bit.

compose_stacks_dex_1 exited with code 2
dex_1      | time="2021-08-16T19:25:28Z" level=info msg="config using log level: debug"
dex_1      | time="2021-08-16T19:25:28Z" level=info msg="config issuer: http://127.0.0.1:5556/dex"
dex_1      | failed to initialize storage: failed to perform migrations: creating migration table: dial tcp 192.168.32.4:9000: connect: connection refused
compose_stacks_dex_1 exited with code 2
dex_1      | time="2021-08-16T19:25:31Z" level=info msg="config using log level: debug"
dex_1      | time="2021-08-16T19:25:31Z" level=info msg="config issuer: http://127.0.0.1:5556/dex"
dex_1      | failed to initialize storage: failed to perform migrations: creating migration table: dial tcp 192.168.32.4:9000: connect: connection refused
compose_stacks_dex_1 exited with code 2
dex_1      | time="2021-08-16T19:25:34Z" level=info msg="config using log level: debug"
dex_1      | time="2021-08-16T19:25:34Z" level=info msg="config issuer: http://127.0.0.1:5556/dex"
dex_1      | failed to initialize storage: failed to perform migrations: creating migration table: dial tcp 192.168.32.4:9000: connect: connection refused

Looks like it has found the right port at least.

[ghost]

Ok, yes good catch. In a minimal deployment, and also in the one above (with the dns problem fixed), the logs are the same. The right port is found on cockroach, but the connection is refused.

I suspect that this is due to cockroach requiring a connection string that looks like

postgres://root@roach-0:9000/dex?sslmode=disable

while Dex might be producing something that looks like

postgres://root:@roach-0:9000/dex?sslmode=disable (empty password)

Given a dex config that looks like:

storage:
  type: postgres
  config:
    host: roach-0
    port: 9000
    database: dex
    user: root
    password: ""
    ssl:
      mode: disable

[ghost]

Fixed the issues. Thanks for being my rubber duck.

If anyone is interested in the config:

docker-compose.yml

version: "3.9"
services:
  roach-0:
    container_name: roach-0
    image: cockroachdb/cockroach:latest
    volumes:
      - cockroach_data_0:/cockroach/cockroach-data
    command: start --insecure --locality=datacenter=vita_net --join=roach-0,roach-1,roach-2
    ports:
      - "9000:26257"
      - "8080:8080"
    networks:
      - vita_net
    hostname: roach-0
  roach-1:
    container_name: roach-1
    image: cockroachdb/cockroach:latest
    volumes:
      - cockroach_data_1:/cockroach/cockroach-data
    command: start --insecure --locality=datacenter=vita_net --join=roach-0,roach-1,roach-2
    networks:
      - vita_net
    hostname: roach-1
  roach-2:
    container_name: roach-2
    image: cockroachdb/cockroach:latest
    volumes:
      - cockroach_data_2:/cockroach/cockroach-data
    command: start --insecure --locality=datacenter=vita_net --join=roach-0,roach-1,roach-2
    networks:
      - vita_net
    hostname: roach-2
  dex:
    image: quay.io/dexidp/dex
    volumes:
      - "./dex-dev-config.yaml:/dex-config.yaml"
    command: ["dex", "serve", "/dex-config.yaml"]
    depends_on:
      - roach-0
    ports:
      - "5554:5554"
      - "5555:5555"
      - "5556:5556"
      - "5557:5557"
    restart: always
    networks:
      - vita_net
    hostname: dex

volumes:
  cockroach_data_0:
  cockroach_data_1:
  cockroach_data_2:

networks:
  vita_net:
    driver: bridge

dex-dev-config.yaml

issuer: http://127.0.0.1:5556/dex

storage:
  type: postgres
  config:
    host: roach-0
    port: 26257
    database: dex
    user: root
    password: ""
    ssl:
      mode: disable

# Configuration for the HTTP endpoints.
web:
  http: 0.0.0.0:5556
  # Uncomment for HTTPS options.
  # https: 127.0.0.1:5554
  # tlsCert: /etc/dex/tls.crt
  # tlsKey: /etc/dex/tls.key

telemetry:
  http: 0.0.0.0:5558

logger:
  level: "debug"
  format: "text"

enablePasswordDB: true

staticPasswords:
  - email: "[email protected]"
    # bcrypt hash of the string "password"
    hash: "$2a$10$2b2cU8CPhOTaGrs1HRQuAueS7JTT5ZHsHSzYiFPm1leZck7Mc8T4W"
    username: "admin"
    userID: "08a8684b-db88-4b73-90a9-3cd1661f5466"

init.sql

CREATE USER dex_admin WITH CREATEDB;
CREATE DATABASE dex 
WITH
    ENCODING   = 'UTF8'
    CONNECTION LIMIT = -1;
GRANT ALL PRIVILEGES ON DATABASE dex TO dex_admin;

init.sh

#!/usr/bin/env bash

set -e

docker exec -it roach-0 /cockroach/cockroach init --insecure

cockroach sql --user=root \
              --insecure \
              --host=localhost:9000 \
              --file init.sql

Instructions:

• all of the above files in the same directory
• run docker-compose up -d
• run init.sh

Then wait for Dex to restart and run the migration.

[sagikazarmark]

Cool, I'm glad you were able to fix it.