TJWaterServerBinary/AUTHENTICATION_AND_USER_MANAGEMENT.md

TJWater Authentication and Metadata Management

Ownership

Keycloak owns login identity, credentials, token issuance, and token expiry. TJWater metadata stores only business snapshots and authorization data:

• users.keycloak_id is the stable identity binding.
• users.username, users.email, and users.last_login_at are Keycloak claim caches.
• users.role, users.is_active, and users.is_superuser control TJWater system access.
• user_project_membership.project_role controls project access.

The backend does not accept passwords, does not issue local JWTs, and does not trust frontend-supplied user IDs.

Fixed Project RBAC

Project roles are stored directly in user_project_membership.project_role; there is no separate role table or user-defined permission editor in this delivery.

Role	Main access
modeler	Model upload/import, simulation, burst, risk, and optimization analysis
dispatcher	SCADA cleaning, simulation and burst analysis
auditor	Project read access and project-scoped audit logs
viewer	WebGIS and read-only risk results

Legacy owner, admin, and member values remain supported for existing records. The backend is the authorization boundary; the frontend uses GET /api/v1/access/context only to hide unavailable menus and guard routes. System admins receive environment, membership, and global-audit permissions, but still need a project membership for project business APIs.

Login Snapshot Refresh

Every authenticated metadata-user resolution validates the Keycloak access token and reads sub, preferred_username, and email claims. The backend finds users by keycloak_id = sub, rejects inactive or missing users, then refreshes username, email, and last_login_at.

This keeps local display data current without changing the identity binding. There is no Keycloak webhook requirement; second-level user or permission sync is out of scope unless explicitly requested later.

Admin APIs

All admin APIs require metadata admin access: users.is_superuser = true or users.role = 'admin'.

User and membership management:

• GET /api/v1/admin/me
• POST /api/v1/admin/users/sync
• POST /api/v1/admin/users/sync/batch
• GET /api/v1/admin/users
• GET /api/v1/admin/users/{user_id}
• PATCH /api/v1/admin/users/{user_id}
• GET /api/v1/admin/projects/{project_id}/members
• POST /api/v1/admin/projects/{project_id}/members
• PATCH /api/v1/admin/projects/{project_id}/members/{user_id}
• DELETE /api/v1/admin/projects/{project_id}/members/{user_id}

Project configuration:

• GET /api/v1/admin/projects
• POST /api/v1/admin/projects
• PATCH /api/v1/admin/projects/{project_id}
• GET /api/v1/admin/projects/{project_id}/databases
• PUT /api/v1/admin/projects/{project_id}/databases
• DELETE /api/v1/admin/projects/{project_id}/databases/{db_role}
• POST /api/v1/admin/projects/{project_id}/databases/{db_role}/health

Secret Handling

Admins submit plaintext DSNs only through HTTPS admin APIs. Operators should not write encrypted columns manually.

• project_databases.dsn_encrypted is encrypted with DATABASE_ENCRYPTION_KEY.
• Admin responses return only has_dsn.
• Audit logs record whether a secret was updated, but never store plaintext DSNs or other secrets.

Generate the database encryption key with:

python -c "from cryptography.fernet import Fernet; print(Fernet.generate_key().decode())"

Keep keys stable for the lifetime of encrypted metadata. Rotating a key requires decrypting with the old key and re-encrypting with the new key.

Metadata Schema Patches

Apply metadata patches in order:

1. resources/sql/004_metadata_auth_management.sql
2. resources/sql/005_metadata_project_configuration.sql
3. resources/sql/006_metadata_rbac_roles.sql

004 creates Keycloak-backed metadata users and project memberships. 005 creates project and project database routing tables with uniqueness, role/type, and pool-size constraints. 006 extends existing membership constraints with the fixed delivery roles.

Frontend System Management

/system-admin is shown only when GET /api/v1/access/context returns environment.manage. The page lets admins maintain metadata users, project members, projects, project database routing for biz_data and iot_data, and connection health checks. This replaces direct SQL editing for normal project onboarding.

Hydraulic model authoring is outside the Web application. Models are prepared in the desktop modeling client and uploaded/imported by an authorized modeler; the system administrator configures the project environment and database routing.