Auth: persist Streamlit login across reload with revocable sessions (Demo)

[falltwo]

Problem

The app currently stores authenticated identity only in st.session_state. Streamlit resets Session State when a browser reload creates a new WebSocket session, so users must sign in again after refresh even though their credentials are still valid.

This is an existing authentication-lifecycle gap, not a regression introduced by PR #4.

Proposed direction (design only; implementation not yet authorized)

If separately approved, implement a Demo-scoped, revocable server-side session while retaining the existing SQLite users and role model:

1. A successful password login creates a cryptographically random opaque token.
2. The browser cookie contains only that raw token.
3. SQLite stores only the token hash plus session metadata.
4. Every restoration revalidates the session and reloads the user's current role/name from the users table.

This design is explicitly Demo-only because a Streamlit browser component writes the cookie with JavaScript and therefore cannot provide HttpOnly protection. It must not be represented as production-ready authentication.

Required controls

• Generate tokens with a CSPRNG and at least 256 bits of entropy.
• Add an auth_sessions table containing, at minimum:
  • token_hash with a unique constraint
  • username
  • created_at
  • expires_at
  • last_seen_at
  • revoked_at
• Never store the raw token in SQLite.
• Never store passwords, usernames, roles, or authorization claims in the cookie.
• Use an absolute TTL of at most 8 hours and an idle TTL of at most 30 minutes.
• Issue a new token after every successful login; never adopt a token supplied before authentication.
• Restore identity only after checking token format, hash match, absolute expiry, idle expiry, revocation, user existence, and the user's current database role.
• Revalidate the token on every app rerun before rendering protected pages and immediately before privileged mutations; never authorize from cached st.session_state identity or role alone.
• Revoke the current session on logout and clear both cookie and Streamlit Session State.
• Revoke existing sessions when a user's password changes; role changes must take effect on the next validation and must not preserve stale privileges.
• Fail closed to the login page if SQLite is unavailable, the component fails, or cookie/session data is malformed or inconsistent.
• Use a __Host- cookie name with Secure, SameSite=Strict, Path=/, no Domain, and Max-Age no longer than the server-side TTL. Verify these properties on the actual Demo origin.
• Clear the cookie using the same name, path, domain scope, and security attributes used when issuing it, with Max-Age=0.
• Keep Streamlit XSRF protection enabled; SameSite=Strict is defense in depth, not a substitute for CSRF/XSRF protection.
• Do not place the token in URLs, query parameters, UI text, application logs, audit logs, exceptions, test snapshots, or Git history.
• Pin the cookie component to an exact version and review its source/build output before adoption. It must not transmit cookie values or telemetry externally.

Acceptance criteria

• Login followed by browser refresh restores the same user without asking for the password again.
• Missing, malformed, forged, expired, idle-expired, and revoked tokens all return to the login page.
• SQLite contains only a token hash; the raw token is absent from the database and logs.
• Re-login rotates the token.
• Logging out revokes the server-side session; another tab/browser holding the token is denied on its next server-side validation, and no subsequent protected action can execute. No real-time push invalidation is claimed.
• Deleting a user prevents restoration of that user's existing session.
• Removing the admin role prevents the existing session from approving or rejecting protected actions on its next validation.
• Database and cookie-component failures fail closed without leaving partial sessions.
• Browser-level verification confirms the required cookie attributes on the target origin.
• A repository/log scan confirms that no raw session token is present.

Test plan

• Unit tests for token creation, hashing, expiry, idle expiry, lookup, rotation, and revocation.
• Integration tests that restore identity into a new empty Streamlit state and reject every invalid-state case above.
• Authorization regression test proving that a role downgrade is effective for protected approval actions.
• Two-browser manual or automated E2E test: login, refresh, copy/reuse token, logout in browser A, and confirm both browsers can no longer access protected pages with that token.

Non-goals

• Production-ready identity assurance.
• OIDC/provider integration.
• Long-lived “remember me” sessions.
• Multi-node session replication.
• Storing identity or role directly in browser-controlled state.
• Query-parameter or local-storage authentication.
• Password-login rate limiting and account lockout are not delivered by this issue. Until separately addressed, this Demo must not be exposed as an unrestricted public Internet login service.

Stop condition

If no candidate cookie component passes source review, avoids external transmission, or supports the required cookie attributes on the Demo origin, do not implement this option. Reopen the decision and move to native OIDC or a correctly isolated authentication proxy.

References

• Streamlit Session State limitations
• Streamlit context cookies are read-only
• Streamlit native OIDC authentication

Related work

• PR Add governed PO approvals and ERP CSV exchange #4 establishes the protected purchase-order approval path whose admin actions must be rechecked after session restoration.