Secret byte limits measure ciphertext, which breaks near-cap secrets when dbcrypt is enabled #30441

The per-owner limits for user secrets and the per-build limits for workspace secrets are enforced by Postgres triggers that add up octet_length(value). When dbcrypt is enabled, value holds base64 ciphertext (nonce + plaintext + GCM tag). That is about 4/3 * (plaintext + 28) bytes, so a stored value is roughly a third larger than its plaintext.

As a result:

• The effective limit depends on whether encryption is on. With encryption, the 24 KiB env cap fits only about 18 KiB of plaintext, and the 200 KiB total cap fits about 150 KiB. codersdk/usersecretvalidation.go documents this as acceptable because stored bytes are an upper bound on what gets transmitted.
• Turning on encryption can break rows that already exist. A value that fit as plaintext may go over the limit once it is encrypted:
  • User secrets (trigger_user_secrets_per_user_limits, 000509_user_secrets_limits.up.sql): the trigger runs BEFORE INSERT OR UPDATE. When coder server dbcrypt rotate encrypts a user's secrets that are close to the cap, the UPDATE can fail with user has reached the user secrets ... limit.
  • Workspace secrets (trigger_workspace_secrets_per_build_limits, 000613_workspace_secrets.up.sql, feat: add workspace secrets table, queries, and RBAC #30248): the trigger runs only on BEFORE INSERT, so rotate succeeds. The next build then copies the previous build's secrets forward (persistSecrets in coderd/wsbuilder/wsbuilder.go, feat: set workspace secrets on workspace builds #30250), the INSERT goes over the limit, and the build fails with a 400. Ordinary start, stop, autostart and autostop builds that don't change any secret fail too. Autostop has no user to see the 400. The only recovery is to remove a secret by name on a build.

Workspace secrets intentionally follow the user secrets model for now. This issue tracks fixing both together.

Possible approaches

1. Check plaintext size in Go. Compute sizes from plaintext before dbcrypt encrypts the value. Keep the count check, and possibly a looser byte check, in the trigger as a backstop. For workspace secrets this is race-free, because one transaction writes all of a build's secrets. User secrets are written by concurrent API calls, so they would still need DB-side enforcement for the aggregate limits.
2. Store the plaintext length in a column, such as value_bytes. Triggers add up that column instead of octet_length(value), and dbcrypt keeps it unchanged. The DB stays the source of truth, and the limits behave the same with or without encryption.
3. Keep measuring stored bytes but make migrations safe. For example, rotate checks or reports rows that would go over the limit, and workspace secret copy-forward does not re-enforce limits on values that are carried over unchanged.

Acceptance

• The same plaintext secrets fit within the limits whether or not dbcrypt is enabled, or the difference is a documented and deliberate choice.
• Enabling encryption or running dbcrypt rotate on near-cap data does not fail, and later workspace builds that copy those secrets forward do not fail either.
• Regression tests for both tables: seed near-cap plaintext, enable encryption or run rotate, then update a user secret and run a workspace build.

Raised in review: #30249 (comment)

Generated by Coder Agents on behalf of @Emyrk.