Title: dbcrypt: `Decrypt`/`Rotate` miss several tables, causing FK violation on revoke · Issue #25381 · coder/coder · GitHub
Open Graph Title: dbcrypt: `Decrypt`/`Rotate` miss several tables, causing FK violation on revoke · Issue #25381 · coder/coder
X Title: dbcrypt: `Decrypt`/`Rotate` miss several tables, causing FK violation on revoke · Issue #25381 · coder/coder
Description: Disclosure: created using Claude Opus 4.7, validated by me Running coder server dbcrypt decrypt (and rotate) fails when any of the following tables hold rows referencing dbcrypt_keys.active_key_digest: crypto_keys.secret_key_id (migratio...
Open Graph Description: Disclosure: created using Claude Opus 4.7, validated by me Running coder server dbcrypt decrypt (and rotate) fails when any of the following tables hold rows referencing dbcrypt_keys.active_key_dig...
X Description: Disclosure: created using Claude Opus 4.7, validated by me Running coder server dbcrypt decrypt (and rotate) fails when any of the following tables hold rows referencing dbcrypt_keys.active_key_dig...
Opengraph URL: https://github.com/coder/coder/issues/25381
X: @github
Domain: github.com
{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"dbcrypt: `Decrypt`/`Rotate` miss several tables, causing FK violation on revoke","articleBody":"_Disclosure: created using Claude Opus 4.7, validated by me_\n\nRunning `coder server dbcrypt decrypt` (and `rotate`) fails when any of the following tables hold rows referencing `dbcrypt_keys.active_key_digest`:\n\n- `crypto_keys.secret_key_id` (migration `000251_crypto_keys.up.sql`, Sep 2024)\n- `mcp_server_configs.api_key_value_key_id`, `custom_headers_key_id`, `oauth2_client_secret_key_id` (migration `000447_mcp_server_configs.up.sql`)\n- `mcp_server_user_tokens.access_token_key_id`, `refresh_token_key_id` (migration `000447_mcp_server_configs.up.sql`)\n\nExample failure:\n\n```\n2026-05-15 08:20:41.982 [info] connected to postgres\n2026-05-15 08:20:41.983 [info] decrypting user tokens user_count=2\n2026-05-15 08:20:41.985 [info] decrypting chat provider keys provider_count=0\n2026-05-15 08:20:41.985 [info] decrypting ai provider settings provider_count=2\n2026-05-15 08:20:41.986 [info] decrypting ai provider keys key_count=1\nEncountered an error running \"coder server dbcrypt decrypt\", see \"coder server dbcrypt decrypt --help\" for more information\nerror: rotate ciphers: revoke key: pq: update or delete on table \"dbcrypt_keys\" violates foreign key constraint \"crypto_keys_secret_key_id_fkey\" on table \"crypto_keys\"\n```\n\n## Root cause\n\n`enterprise/dbcrypt/cliutil.go`'s `Decrypt` and `Rotate` iterate over a fixed list of tables (user_links, external_auth_links, user_chat_provider_keys, user_secrets, chat_providers, ai_providers, ai_provider_keys), clearing the `*_key_id` columns so the old dbcrypt key can be revoked. Both functions then call `RevokeDBCryptKey` for the key(s).\n\nTables added after these helpers were written are missing from the loops:\n\n- `crypto_keys` — added in #14662 (Sep 2024), `secret` is encrypted via `dbCrypt.InsertCryptoKey` (`enterprise/dbcrypt/dbcrypt.go:348`), but `cliutil.go` has no `crypto_keys` loop.\n- `mcp_server_configs` and `mcp_server_user_tokens` — added in migration `000447`, encrypted at rest but never decrypted by these commands.\n\nBecause the rows still reference `dbcrypt_keys(active_key_digest)`, Postgres rejects the revoke with the FK constraint error above. Same defect exists in `Rotate` when the old key has any rows in these tables.\n\n## Workaround\n\nManually clear the unhandled tables before re-running. For `crypto_keys` specifically:\n\n```sql\nUPDATE crypto_keys SET secret = NULL, secret_key_id = NULL;\n```\n\n(Workspace apps / OIDC convert / tailnet resume keys regenerate on demand, which is what a `decrypt` would imply anyway.)\n\nFor `mcp_server_configs` / `mcp_server_user_tokens` the equivalent clears must be done per affected column.\n\n## Fix\n\nAdd `crypto_keys`, `mcp_server_configs`, and `mcp_server_user_tokens` to both `Decrypt` and `Rotate` in `enterprise/dbcrypt/cliutil.go`. Cover each with a regression test in `enterprise/dbcrypt/cliutil_test.go` matching the existing `TestRotateAIProviders` pattern (seed a row under one cipher, rotate/decrypt, assert the FK no longer points at the revoked key).\n\nWorth auditing the `Delete` SQL (`sqlDeleteEncryptedUserTokens` in `cliutil.go`) for the same gap.","author":{"url":"https://github.com/dannykopping","@type":"Person","name":"dannykopping"},"datePublished":"2026-05-15T08:27:30Z","url":"https://github.com/coder/coder/issues/25381"}
| react-profiling | 0 |
| react-import-map | react,react-dom,react-dom/client,react-dom/profiling,react-is,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@b8488ee2c322 |
| route-pattern | /_view_fragments/issues/show/:user_id/:repository/:id/issue_layout(.:format) |
| route-controller | voltron_issues_fragments |
| route-action | issue_layout |
| fetch-nonce | v2:25213a8b-5977-7287-a590-57060def28bd |
| current-catalog-service-hash | 81bb79d38c15960b92d99bca9288a9108c7a47b18f2423d0f6438c5b7bcd2114 |
| request-id | B3B8:A99C2:267B662:30F2A05:6B017CDD |
| html-safe-nonce | 45fe25c65a1c1586de5adf9d3562804b2196a67c679e875548fb7285b1b9031a |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJCM0I4OkE5OUMyOjI2N0I2NjI6MzBGMkEwNTo2QjAxN0NERCIsInZpc2l0b3JfaWQiOiI5NjUxMTQyMDQ0MzQ1NjIyNjkiLCJyZWdpb25fZWRnZSI6ImlhZCIsInJlZ2lvbl9yZW5kZXIiOiJpYWQifQ== |
| visitor-hmac | b42c1f544b3a6ea191d41289bbf299cee1f443509ab6954a29f1e386b86101f9 |
| hovercard-subject-tag | issue:4452449622 |
| github-keyboard-shortcuts | repository,issues,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | / |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/_view_fragments/issues/show/coder/coder/25381/issue_layout |
| twitter:image | https://opengraph.githubassets.com/8e21efb2eec56fd0f8def8c03c9eb4f84a60a5d7457b753dd7550651882a579f/coder/coder/issues/25381 |
| twitter:card | summary_large_image |
| og:image | https://opengraph.githubassets.com/8e21efb2eec56fd0f8def8c03c9eb4f84a60a5d7457b753dd7550651882a579f/coder/coder/issues/25381 |
| og:image:alt | Disclosure: created using Claude Opus 4.7, validated by me Running coder server dbcrypt decrypt (and rotate) fails when any of the following tables hold rows referencing dbcrypt_keys.active_key_dig... |
| og:image:width | 1200 |
| og:image:height | 600 |
| og:site_name | GitHub |
| og:type | object |
| og:author:username | dannykopping |
| hostname | github.com |
| expected-hostname | github.com |
| None | cc5dfdd32f62ebd3223cab3af3aa2b268d3605218237326a48a99d1efd89495c |
| turbo-cache-control | no-preview |
| go-import | github.com/coder/coder git https://github.com/coder/coder.git |
| octolytics-dimension-user_id | 95932066 |
| octolytics-dimension-user_login | coder |
| octolytics-dimension-repository_id | 440752086 |
| octolytics-dimension-repository_nwo | coder/coder |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 440752086 |
| octolytics-dimension-repository_network_root_nwo | coder/coder |
| turbo-body-classes | logged-out env-production page-responsive |
| disable-turbo | false |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | 8c77c9e8439031ad54c75a250694842892f693e1 |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
| react-app-name | issues |
Links:
Viewport: width=device-width