Security Advisory: Plaintext Org Database Connection Strings

Severity: High
Affected versions: All versions prior to v1.0.187 where the encrypt-org-db-connection-strings.ts migration has not been run
Affected file: src/lib/org-db-crypto.ts
Fixed in: v1.0.187 (remediation requires manual migration steps — see below)

---

Summary

The decryptConnectionString() function in src/lib/org-db-crypto.ts contains a legacy fallback code path that detects plaintext (unencrypted) connection strings and returns them as-is, with only a console.warn() log message. This means that if the database encryption migration has not been completed in a given environment, Neon database connection strings — which include embedded credentials — may be stored and read back in plaintext from the organization_databases table.

The code comments indicate this is a known legacy path and instruct operators to "run the migration", but this is not enforced programmatically. There is no hard failure, no startup guard, and no guarantee the migration has been completed.

---

Technical Detail

The Vulnerable Code Path

Inside decryptConnectionString() (src/lib/org-db-crypto.ts), a conditional branch checks whether the stored value looks like an encrypted string. If it does not match the expected encrypted format, it is treated as a legacy plaintext value and returned directly:

// BEFORE (vulnerable)
if (isPlaintext(value)) {
  console.warn('Legacy plaintext value detected. Run the migration.');
  return value; // ← credentials returned unencrypted
}

This means any environment where the encrypt-org-db-connection-strings.ts migration script has not been executed will silently serve plaintext Neon connection strings containing database usernames and passwords.

Recommended Fix

The plaintext fallback must be removed. Any non-encrypted value should be treated as a hard error:

// AFTER (hardened)
if (isPlaintext(value)) {
  throw new Error(
    'Plaintext connection string detected in organization_databases. '
    + 'Encryption migration has not been completed. '
    + 'Run encrypt-org-db-connection-strings.ts before starting the application.'
  );
}

Additionally, a startup check should be added that queries the organization_databases table before the application begins serving requests and asserts that no plaintext connection strings remain.

---

Impact

• Credential exposure: Neon database usernames and passwords may be readable in plaintext by anyone with read access to the organization_databases table.
• Silent failure: There is no runtime error or alerting when plaintext values are served — only a console.warn() that may go unnoticed in production log volumes.
• Scope: Any organisation record whose connection string was written before encryption was in place, or in an environment where the migration was never run, is affected.

---

Remediation Steps

All operators must complete the following steps. Do not skip any step.

Step 1 — Audit your database

Connect to your production database and check for unencrypted values in the organization_databases table:

-- Connection strings that are NOT encrypted will typically not begin
-- with the expected ciphertext prefix used by org-db-crypto.
-- Adjust the prefix check to match your encryption scheme.
SELECT id, org_id, connection_string
FROM organization_databases
WHERE connection_string NOT LIKE 'enc:%'; -- replace 'enc:%' with your actual encrypted prefix

If any rows are returned, those connection strings are stored in plaintext and must be encrypted before deploying the hardened build.

Step 2 — Run the encryption migration

Execute the migration script in every environment (development, staging, production):

npx tsx scripts/encrypt-org-db-connection-strings.ts

Ensure you have the correct encryption key available in the environment before running the script.

Step 3 — Verify the migration

Re-run the audit query from Step 1. It must return zero rows. Do not proceed until this is confirmed.

Step 4 — Deploy v1.0.187

Once the migration is verified complete in all environments, deploy the updated application. The hardened decryptConnectionString() will throw immediately if any plaintext value is encountered, preventing silent credential exposure going forward.

---

Preventative Measures (Post-Remediation)

After completing remediation, the following controls should be implemented to prevent recurrence:

1. Hard failure on plaintext: The updated decryptConnectionString() throws rather than returning plaintext — any future plaintext value will cause an immediate, visible failure.
2. Startup assertion: Add a check at application startup that queries organization_databases and aborts if any unencrypted rows are found. This prevents the application from starting in an unmitigated state.
3. CI migration check: Consider adding a pre-deployment step that verifies migration state before allowing a production deploy.

---

Questions

If you are unsure whether your environment is affected, audit the database using the SQL query in Step 1 above. If plaintext rows are found, complete the full remediation sequence before upgrading.