Add multi-user support: RLS + security-barrier views for per-user data isolation

[cchwala]

Note: Updated according to new findings, see comment below.

Multi-User Architecture with Row-Level Security

Implement support for multiple isolated users on a single shared stack.
Strong data isolation via PostgreSQL RLS (where available) and security-barrier views (where RLS is incompatible with TimescaleDB compression). No network config changes needed to add users.

Design goals: single external URL + SFTP port · DB-enforced isolation · ~3 GB RAM for 10 users · never delete raw data

---

PR Roadmap

#	Branch	Scope	Breaking?
1	feat/db-add-user-id	Add user_id to all tables; update cml_data_1h GROUP BY and compression segmentby; migration SQL + updated init.sql	No
2	feat/db-roles-rls	Per-user DB roles; RLS on cml_metadata/cml_stats; security-barrier views for cml_data and cml_data_1h; migration SQL	No
3	feat/parser-user-id	USER_ID env var; db_writer.py injects it; updated tests	No
4	feat/sftp-multi-user	Multi-user SFTP entrypoint; per-user volumes and parser instances in docker-compose.yml; SSH key generation	No
5	feat/webserver-auth	Flask-Login; auth.py; login/logout routes; per-user DB connections; login template	Yes — all routes require login
6	feat/web-api-upload	/api/upload with API-key auth; drag-and-drop UI on /data-uploads	No
7	feat/user-onboarding	scripts/add_user.sh; scripts/hash_password.py; updated README	No

PRs 1–4 are safe to merge to main at any time. PR 5 is the "go live" milestone for multi-user.

[cchwala]

Architecture decision record: RLS isolation strategy for cml_data

During PR2 (feat/db-roles-rls) we discovered a hard constraint in TimescaleDB:

RLS and compression are mutually exclusive on the same hypertable.
ENABLE ROW LEVEL SECURITY fails if timescaledb.compress is set, and vice versa — regardless of statement order.

Since compression is non-negotiable (it delivers the 10–20× storage reduction that makes the single-server model viable), we cannot use PostgreSQL's native RLS on cml_data.

Options considered

Option	Isolation mechanism	Compression	Notes
Native RLS on cml_data	DB-enforced	❌ Must be dropped	Unacceptable — defeats the purpose
Security-barrier view cml_data_secure	DB-enforced on reads via view	✅ Kept	Chosen
Application WHERE user_id = ? only	Application-level	✅ Kept	Weaker — no DB enforcement

Chosen approach: security-barrier views

The cml_data_secure view (and cml_data_1h_secure for the aggregate) use PostgreSQL's security_barrier option with WHERE user_id = current_user:

CREATE VIEW cml_data_secure WITH (security_barrier) AS
SELECT * FROM cml_data
WHERE user_id = current_user
WITH CHECK OPTION;

security_barrier prevents the query optimizer from pushing caller-supplied predicates above the filter, closing SQL injection leakage. WITH CHECK OPTION rejects writes through the view where user_id != current_user.

Resulting isolation model

Table / View	Isolation mechanism	Enforced by
cml_data (direct)	SET ROLE + parameterized queries	Application (PR5)
cml_data_secure	Security-barrier view	DB — reads and direct writes
cml_metadata	Native RLS	DB
cml_stats	Native RLS	DB
cml_data_1h_secure	Security-barrier view	DB — reads

The architecture doc (docs/multi-user-architecture.md) has been updated to reflect this.