Security Finding H3 — The Portal orac Session Token Is the User's Password Column

The customer portal authenticates every request by matching the request-controlled orac cookie for equality against User.password. There is no per-user uniqueness guarantee on that column, so a shared stored value lets one cookie authenticate as an arbitrary customer in the same vendor.

Owner: Jeff StolzCreated: 2026-08-08Status: Draft — for security reviewAudit ref: H3

Do not ship a patch from this doc. This is an architectural/security-sensitive change to the sole portal auth path. The deliverable here is the assessment and options; auth behavior must not change without security sign-off. orac's use must not be widened in the meantime.

TL;DR

🅰 The mechanism

A credential column is used as a session-lookup token.

get_auth_variables runs User.query.filter_by(password=orac, …).first() (vendor_front.py:99). No ORDER BY — on a duplicate stored value .first() returns a non-deterministic row.

🅱 Real exposure

Today's safety is incidental, not enforced.

The password column is indexed but not unique. Organically-created users are protected only by bcrypt's random salt — but live code paths (SSO customer login, seed/bulk data) write non-bcrypt values into the same column, and the documented shared-orac seed proves the invariant can break.

🅲 The fix is mostly built

A proper opaque per-session token already exists.

UserSession.session_token is unique, indexed, revocable, and minted with secrets.token_urlsafe(32) — and is already created on every portal login. The proper fix is to make orac carry that token instead of password.

How portal auth works today

orac is a long-lived (max_age = 1 year) httponly cookie. Its value is set directly from User.password at every portal login entry point:

Entry point	File:line	Value written to orac
PIN / magic-link verify	customer/routes.py:582	user.password
Magic-link controller	magic_link_controller.py:79	customer.password
FastPass	fast_pass_controller.py:173	customer.password
PR-preview quick login	preview_login_controller.py:65	user.password
Case impersonation	customer/routes.py:719	cust.password (30-min)

Every authenticated portal read then resolves the caller in one place:

# app/api/utils/vendor_front.py:99
cust = User.query.filter_by(
    password=orac, is_active=True, vendor=vendor.id, is_customer_user=True
).first()

Blast radius. get_auth_variables is the single auth entrypoint for the entire portal API surface — v1/v2/v3 reads and the Portal Studio config-resolve endpoints all funnel through it: task_controller, account_project_controller, conversations_controller, resources_controller, playbook_controller, user_projects_controller, customer_profile_controller, portal_favorite_controller, portal_config_controller, portal_studio_config_controller, and customer/routes.py. Any non-deterministic match here mis-authenticates all of them at once.

(1) Real-world exposure assessment

Is orac the plaintext or a hash?

Neither, uniformly. For a password/PIN/magic-link/FastPass user, orac is the bcrypt hash stored in User.password (passwords.py:8, bcrypt.hashpw(...gensalt())). For an SSO customer user, orac is the raw saml_state value (see below). The plaintext is never the cookie.

That the cookie is the stored value is itself a severity amplifier independent of collisions: a read-only DB leak (dump, replica, log) yields values that replay directly as a live customer session for every customer — no cracking required. This is the same class the team already fixed twice (see Appendix).

Is there any uniqueness guarantee on User.password?

No. The column is declared indexed but not unique (user/models.py:59):

password: Mapped[Internal[str]] = mapped_column(Text, nullable=False, index=True)

A repo-wide search of migrations/ finds no unique constraint on password. So the "one orac ⇒ one user" property is a runtime accident of bcrypt salting, not a database-enforced invariant.

How are customer passwords set/hashed?

• Normal path (invite / create):user/services.py:316 — hash_pass(binascii.hexlify(os.urandom(8)).decode()). A bcrypt hash of a random 64-bit value with a random salt. Two such users colliding is cryptographically negligible. For organically-created customer users, real-world collision risk is effectively zero.

• Vectors that bypass that safety — these write non-bcrypt or shared values into the same column that orac matches on:

  Vector	File:line	Value in password	On the orac path?
  SSO customer login	sso_strategies.py:76	user.saml_state (OAuth state / IdP access_token)	Yes (is_customer_user=True)
  Seed / bulk import / data migration	(documented shared-orac seed)	whatever the script sets — can be constant	Yes, if it writes customer rows
  API user	user/helpers.py:736	f"{api_key}_{vendor}" (deterministic, low-entropy)	No (is_customer_user=False)
  Integration user	user/helpers.py:763	f"{api_key}_{vendor_id}"	No (is_customer_user=False)

  The API/integration rows are out of the orac blast radius (the filter pins is_customer_user=True), but they demonstrate the column is a shared, multi-purpose credential field with no uniqueness invariant — the exact condition H3 flags.

  The SSO-customer case is the concrete, in-scope vector: saml_state is set from the OAuth state nonce / IdP access_token (platform_integrations/routes.py:729,784, sso_strategies.py:106/218/315), values that are externally influenced, not salted, and carry no DB uniqueness guarantee. Empty/duplicate state across two customer rows in one vendor is a plausible collision.

What actually happens on a collision

.first() with no ORDER BY lets Postgres return any matching row (physical scan order, which shifts with updates/vacuum). So a single orac can resolve to different customers on different requests — the worst failure mode: a caller non-deterministically authenticates as another customer in the same vendor and reads their projects, tasks, and comments. It is intra-vendor, cross-customer — adjacent to, but not the same as, the tenant-isolation work in Tenant-Isolation Enforcement.

Exposure verdict

• Organically-created customers: negligible collision risk (bcrypt salt).
• SSO customers + any seed/import/migration path: the safety net does not apply, and nothing structurally prevents a shared value. H3's core claim — no per-user uniqueness is enforced — is confirmed. The residual risk is real but bounded to those vectors; it is not a broad "any two customers collide" situation today.
• Independent of collisions: using the stored credential as the session token means credential-store exposure = session takeover, at portal scale.

(2) Options

A. Dedicated opaque per-session token (recommended target)

Make orac carry a purpose-built session token, matched instead of password. Crucially, this infrastructure already exists:

• UserSession.session_token — String(255), unique=True, index=True, with expires_dts, is_active, and invalidate(reason) (user/models.py:457–506).
• Minted by UserSessionService.generate_session_token() = secrets.token_urlsafe(32) (user_session_service.py:23).
• Already created on every portal login — login_app_user stores session["user_session_token"] (user_helper.py:44), and the session validator + logout already consume it (session_validator.py, logout_controller.py).

The change is essentially: set orac to session_token at the five login sites, and resolve the customer via UserSession (join to User, checking is_active/is_customer_user/vendor/not-expired) in get_auth_variables.

Wins: unique by construction; revocable (logout/rotation actually kills the cookie, which it cannot today); expiring (drops the 1-year replay window); and a credential leak no longer yields a session token. Costs: touches the sole portal auth path — needs careful migration (dual-read window so existing password-valued orac cookies don't all log out on deploy), and coverage for every entry point incl. impersonation and PR-preview.

B. Interim hardening (only if a fast risk-reducer is wanted first)

Reduce blast radius without re-architecting, if security wants a stopgap:

1. Fail closed on ambiguity. Replace .first() in get_auth_variables with a two-row probe (limit(2)); if more than one row matches, treat it as an auth failure (return no customer) and log/alert. This removes the wrong-customer outcome — a collision becomes a denial, never a cross-customer read. Small, well-contained, high value.
2. Enforce uniqueness on the auth value. Add a partial unique index over the value used for orac among is_customer_user=True, is_active=True rows, after an audit + dedupe of existing data (expand-and-contract; the migration must remediate drift, not guard — see .claude/rules/backend-models.md). Turns the incidental invariant into an enforced one.
3. Fix the SSO-customer writer so it never parks a low-entropy/duplicate value in password (route SSO customers through the same session-token mechanism, or at minimum a salted unique value).

Interim options are not a substitute for A — they harden the existing shape but keep a stored credential doubling as a replayable, non-expiring session token.

(3) Recommendation

1. Adopt Option A as the target: wire orac to the existing UserSession.session_token and match on UserSession, retiring the password-equality auth. The token model, generator, and login-time creation already exist, so this is far cheaper than it looks and closes the whole class (collisions, non-revocability, 1-year replay, leak-equals-takeover).
2. If a stopgap is needed before A lands, take interim step B.1 first (fail-closed on multi-match) — it is the smallest change that eliminates the cross-customer read, converting any collision into a safe denial.
3. Data audit regardless: run a read-only prod check for duplicate password values among active customer users per vendor to size real exposure before deciding urgency.

Suggested prod audit query (read-only, for security to run): count active customer users sharing a password value within a vendor —

SELECT vendor, password, COUNT(*)
FROM "user"
WHERE is_customer_user AND is_active
GROUP BY vendor, password
HAVING COUNT(*) > 1;

A non-empty result is direct evidence of exploitable collisions. Run via the prod-db-investigation recipe (reader host, read-only txn).

Risks & open questions

HighMigration logs everyone out

Switching the orac source without a dual-read window invalidates every live customer cookie on deploy.

Mitigation: transitional get_auth_variables that accepts either a UserSession token or a legacy password value, then drop the legacy arm after the cookie TTL rolls over.

MedImpersonation & preview paths

Case-impersonation (customer/routes.py:719) and PR-preview login set orac from password too and must migrate together.

Mitigation: mint a scoped, short-lived UserSession for impersonation instead of copying password.

Open questions

Does prod actually have duplicate customer password values? The audit query above answers it and sets urgency (interim B.1 now vs. straight to A).

Owner + sequencing vs. tenant-isolation work? This is intra-vendor, cross-customer; confirm it's tracked as its own thread rather than folded into RLS tenant isolation.

Appendix — this is the third defect in the same class

The password-as-token pattern has been narrowed twice before; H3 is the remaining core of it.

• rc#473 / ONRAMP-5238 — the bcrypt hash was embedded in a portal DTO's preview_link (…/gateway?orac=<user.password>), letting any portal caller harvest impersonation tokens. Fixed to a signed, short-lived token; guarded by test_rc_473_preview_link_password_leak.py.
• rc#1302 / ONRAMP-5340 — route_default matched a request-controlled orac against User.password; removed. Guarded by test_rc_1302_route_default_credential_filter.py, whose docstring names the root issue exactly: "treats a stored credential as a session-lookup token: a database read yields a value that can be replayed straight back as the cookie."

Both fixes removed symptoms. get_auth_variables is the cause still standing.