Migrations fail for YugabyteDB

[tinrab]

Describe the bug

Migrations don't work with PostgreSQL-compatible database YugabyteDB.

Reproducing the bug

Steps to reproduce the behavior:

1. Checkout repository at v1.8.5.
2. Create a new override file quickstart-yugabyte.yml:

version: '3'
services:
  hydra-migrate:
    image: oryd/hydra:v1.8.5
    environment:
      - DSN=postgres://yugabyte:yugabyte@yugabyte:5433/hydra?sslmode=disable&max_conns=20&max_idle_conns=4
    command:
      migrate sql -e --yes
    restart: on-failure
    depends_on:
      - yugabyte-init
  hydra:
    image: oryd/hydra:v1.8.5
    environment:
      - DSN=postgres://yugabyte:yugabyte@yugabyte:5433/hydra?sslmode=disable&max_conns=20&max_idle_conns=4
    depends_on:
      - hydra-migrate
  yugabyte:
    image: yugabytedb/yugabyte:2.3.3.0-b106
    entrypoint: bin/yugabyted
    command: start --daemon=false
    ports:
      - "5433:5433"
  yugabyte-init:
    image: yugabytedb/yugabyte:2.3.3.0-b106
    restart: on-failure
    entrypoint: /bin/bash -c
    command: |
      "ysqlsh -h yugabyte -p 5433 -c \"SELECT 1 FROM pg_database WHERE datname = 'hydra'\" | grep -q 1 \
        || ysqlsh -h yugabyte -p 5433 -c 'CREATE DATABASE hydra'"
    depends_on:
      - yugabyte

3. Start containers:

$ docker-compose -f quickstart.yml \
    -f quickstart-yugabyte.yml \
    up -d --force-recreate

Hydra migrate container logs

Could not apply migrations:
ERROR: dropping a primary key constraint is not yet supported (SQLSTATE 0A000)
error executing 20190100000009_client.postgres.up.sql, sql: ALTER TABLE hydra_client DROP CONSTRAINT hydra_client_pkey;
ALTER TABLE hydra_client ADD pk SERIAL;
ALTER TABLE hydra_client ADD PRIMARY KEY (pk);
CREATE UNIQUE INDEX hydra_client_idx_id_uq ON hydra_client (id);

github.com/gobuffalo/pop/v5.NewMigrationBox.func1.1
	/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.2.4/migration_box.go:37
github.com/gobuffalo/pop/v5.Migration.Run
	/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.2.4/migration_info.go:29
github.com/gobuffalo/pop/v5.Migrator.UpTo.func1.2
	/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.2.4/migrator.go:106
github.com/gobuffalo/pop/v5.(*Connection).Transaction.func1
	/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.2.4/connection.go:143
github.com/gobuffalo/pop/v5.commonDialect.Lock
	/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.2.4/dialect_common.go:30
github.com/gobuffalo/pop/v5.(*Connection).Transaction
	/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.2.4/connection.go:137
github.com/gobuffalo/pop/v5.Migrator.UpTo.func1
	/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.2.4/migrator.go:105
github.com/gobuffalo/pop/v5.Migrator.exec
	/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.2.4/migrator.go:279
github.com/gobuffalo/pop/v5.Migrator.UpTo
	/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.2.4/migrator.go:90
github.com/gobuffalo/pop/v5.Migrator.Up
	/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.2.4/migrator.go:82
github.com/ory/hydra/persistence/sql.(*Persister).MigrateUp
	/home/ory/persistence/sql/persister.go:52
github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL
	/home/ory/cmd/cli/handler_migrate.go:90
github.com/spf13/cobra.(*Command).execute
	/go/pkg/mod/github.com/spf13/cobra@v1.0.0/command.go:846
github.com/spf13/cobra.(*Command).ExecuteC
	/go/pkg/mod/github.com/spf13/cobra@v1.0.0/command.go:950
github.com/spf13/cobra.(*Command).Execute
	/go/pkg/mod/github.com/spf13/cobra@v1.0.0/command.go:887
github.com/ory/hydra/cmd.Execute
	/home/ory/cmd/root.go:57
main.main
	/home/ory/main.go:33
runtime.main
	/usr/local/go/src/runtime/proc.go:204
runtime.goexit
	/usr/local/go/src/runtime/asm_amd64.s:1374
github.com/ory/hydra/persistence/sql.(*Persister).MigrateUp
	/home/ory/persistence/sql/persister.go:52
github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL
	/home/ory/cmd/cli/handler_migrate.go:90
github.com/spf13/cobra.(*Command).execute
	/go/pkg/mod/github.com/spf13/cobra@v1.0.0/command.go:846
github.com/spf13/cobra.(*Command).ExecuteC
	/go/pkg/mod/github.com/spf13/cobra@v1.0.0/command.go:950
github.com/spf13/cobra.(*Command).Execute
	/go/pkg/mod/github.com/spf13/cobra@v1.0.0/command.go:887
github.com/ory/hydra/cmd.Execute
	/home/ory/cmd/root.go:57
main.main
	/home/ory/main.go:33
runtime.main
	/usr/local/go/src/runtime/proc.go:204
runtime.goexit
	/usr/local/go/src/runtime/asm_amd64.s:1374
github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL
	/home/ory/cmd/cli/handler_migrate.go:91
github.com/spf13/cobra.(*Command).execute
	/go/pkg/mod/github.com/spf13/cobra@v1.0.0/command.go:846
github.com/spf13/cobra.(*Command).ExecuteC
	/go/pkg/mod/github.com/spf13/cobra@v1.0.0/command.go:950
github.com/spf13/cobra.(*Command).Execute
	/go/pkg/mod/github.com/spf13/cobra@v1.0.0/command.go:887
github.com/ory/hydra/cmd.Execute
	/home/ory/cmd/root.go:57
main.main
	/home/ory/main.go:33
runtime.main
	/usr/local/go/src/runtime/proc.go:204
runtime.goexit
	/usr/local/go/src/runtime/asm_amd64.s:1374
Successfully applied migrations!

Hydra container logs

level=info msg=Successfully connected to SQL database backend audience=application dsn=postgres://*:*@yugabyte:5433/hydra?sslmode=disable service_name=ORY Hydra service_version=
level=fatal msg=Could not ensure that signing keys for "hydra.openid.id-token" exists. If you are running against a persistent SQL database this is most likely because your "secrets.system" ("SECRETS_SYSTEM" environment variable) is not set or changed. When running with an SQL database backend you need to make sure that the secret is set and stays the same, unless when doing key rotation. This may also happen when you forget to run "hydra migrate sql".. audience=application error=map[message:ERROR: relation "hydra_jwk" does not exist (SQLSTATE 42P01)] service_name= service_version=
Config file not found because "Config File ".hydra" Not Found in "[/]""
error: Config File ".hydra" Not Found in "[/]"
level=info msg=No tracer configured - skipping tracing setup audience=application service_name=ORY Hydra service_version=
level=info msg=Establishing connection with SQL database backend audience=application dsn=postgres://*:*@yugabyte:5433/hydra?sslmode=disable service_name=ORY Hydra service_version=
level=info msg=Successfully connected to SQL database backend audience=application dsn=postgres://*:*@yugabyte:5433/hydra?sslmode=disable service_name=ORY Hydra service_version=
level=fatal msg=Could not ensure that signing keys for "hydra.openid.id-token" exists. If you are running against a persistent SQL database this is most likely because your "secrets.system" ("SECRETS_SYSTEM" environment variable) is not set or changed. When running with an SQL database backend you need to make sure that the secret is set and stays the same, unless when doing key rotation. This may also happen when you forget to run "hydra migrate sql".. audience=application error=map[message:ERROR: relation "hydra_jwk" does not exist (SQLSTATE 42P01)] service_name= service_version=

Expected behavior

Should work the same as with quickstart-postgres.yml configuration.

Environment

• Hydra: v1.8.5
• Yugabyte: v2.3.3.0
• Docker: version 19.03.13, build 4484c46d9d

[aeneasr]

YugaByte is not a supported database. YugaByte does not support dropping foreign keys (similar to CockroachDB) so it is not compatible. There is unfortunately nothing we can do here right now as we do not have capacity to support yet another database! What you could try is use the CRDB migrations instead for YugaByte.

[zepatrik]

See also ory/kratos#715, same problem there. We propose possible workarounds in the other issue.

[tinrab]

I see. Writing custom migrations for an unsupported database is the solution then. Closing.

[zepatrik]

If you end up writing some, we would be happy to feature them as a related open source project.

[LeeWong]

Dropping a primary key is supported now according to yugabyte/yugabyte-db#8735 . Any luck on success?