Question
ORY Kratos registration fails in production PostgreSQL due to missing verification_token column
Rey Byte
0 reputation · 30 Jun 2023, 16:56 UTC
35.3K views1
Loading…
0 reputation · 30 Jun 2023, 16:56 UTC
The ORY Kratos self‑service registration endpoint works correctly when backed by a local SQLite database, but returns a 500 Internal Server Error in a production PostgreSQL deployment.
In the SQLite environment the ORM automatically creates the required verification_token column, masking the missing migration step, whereas the PostgreSQL schema lacks this column unless the explicit migrate_postgres.sql script is applied.
The unresolved decision is whether to rely on ORM auto‑migration for production or to enforce explicit migration execution as part of the release process.
Should the ORM be permitted to generate the schema automatically for PostgreSQL in production? Is it advisable to depend on ORM‑driven schema changes for a production database? Should the release pipeline guarantee that the PostgreSQL migration script is run before the service starts?
Do not let the ORM create the schema automatically in a production PostgreSQL environment. Always run the explicit migration script (or a verified migration step) before Kratos starts. Relying on auto‑migration risks silent schema drift and can break registration, password reset, and MFA flows.
Kratos stores the email‑verification token in the identity_verification table’s verification_token column. If that column is missing, the handler that writes the token fails with a database error, which Kratos surfaces as a 500 response. SQLite’s auto‑migration hides this problem because the driver automatically creates the column on first use.
ory
postgresqlpsql -d <db> -c "\d identity_verification"
verification_token is absent.schema_migrations (or migrations) table. Query it to see if the migration that adds verification_token has been applied:SELECT * FROM schema_migrations WHERE name LIKE '%add_verification_token%';
migrate_postgres.sql script. Example with the CLI:kratos migrate up
psql -d <db> -f migrate_postgres.sql
In production, treat database migrations as a distinct deployment step:
kratos migrate up before the container starts.identity_verification for the verification_token column and aborts if it is missing.Do you have a schema_migrations (or similar) table in your PostgreSQL instance, and is it populated with the latest migration IDs?
Use comments to ask for clarification. Post a solution as an answer.
4,300 reputation · 01 Jul 2023, 03:17 UTC
Kratos tracks applied migrations in the schema_migrations table. If the migration that adds the verification_token column has not run, the table will be missing that column and registration will fail.
psql -d <db> -c "SELECT * FROM schema_migrations WHERE name LIKE '%add_verification_token%';"
When the query returns no rows, run the migration CLI or apply migrate_postgres.sql before the service starts. Disabling ORM auto‑migration in production avoids race conditions and ensures all instances see the same schema.
| Step | Command / Action |
|---|---|
| Verify migration | SELECT * FROM schema_migrations … |
| Apply migration | kratos migrate up OR psql -f migrate_postgres.sql |
| Restart service | docker restart kratos |