GSoC 2026 · Final report

Authentication and Role-Based Access Control for istSOS4

This is my Google Summer of Code 2026 final report for istSOS/istSOS4 (OSGeo). I built a full access-control layer on top of a SensorThings API server that shipped without one: application-layer credentials, row-level security enforced inside PostgreSQL, per-Network dataset scoping, a staged approval workflow and five external identity providers. I then audited the finished feature for privilege-boundary and data-leak defects and fixed every one found, along with everything raised in mentor review.

Contributor: Kinshuk Sanand
Organization: OSGeo / istSOS
Timeframe: May 1 – Oct 5, 2026
Repository: istSOS/istSOS4
GSoC project: Project page ↗
Mentors: Massimiliano Cannata, Daniele Strigaro, Claudio Primerano

33pull requests opened against upstream main — 18 merged, 15 closed

130files changed against the upstream main this branch is built on (f35ce01)

+12,775lines added and 1,086 removed against that same upstream main

16privilege and data-leak defects found and fixed across two review rounds

16SQL migrations, every one idempotent and guarded by its feature flag

797automated and scripted checks, including the 405-test OGC conformance suite — all passing

What changed, in plain terms

Upstream istSOS4 is a SensorThings API server whose security model existed mostly on paper. Every item on the left was true of istSOS/istSOS4:main before this work began; every item on the right is something a reviewer can exercise today.

Before — upstream main

No real per-user identity.The design assumed an individual PostgreSQL LOGIN role per user, but no code path ever created one — so any RLS policy scoped TO <username> could never match a real session, for anyone.
Blanket guest access.The anonymous-read policy was USING (true): every row of every dataset was exposed to unauthenticated requests, not just the ones meant to be public.
No external identity.Login was local-password only; no OIDC or SSO integration existed.
No self-registration or approval workflow.Accounts could only be created directly by an administrator via POST /Users.
Silent write failures.A PATCH/PUT blocked by RLS still returned 200 OK, so a caller couldn't tell a real write from a no-op.
Role-state leaks.The RESET ROLE pattern (77 call sites across 44 files) could leave a pooled connection holding an elevated role after a cancelled request.

After — this work

Application-layer credentials.Users are rows in sensorthings."User" with bcrypt hashes; the API connects as one service account and impersonates each caller with SET LOCAL ROLE plus a session variable — see §03.
Per-Network dataset scoping.Network, an existing istSOS entity, becomes the access-control unit: User.dataset_id holds a Network name and RLS restricts Datastream and Observation to it. It replaces an earlier is_public/ODRL prototype that was deliberately removed — see §06.
Narrow guest access.By default every route needs a token. With ANONYMOUS_VIEWER=1, a request without one reads shared reference data only — never Datastreams, Observations or Networks.
Five OIDC providers.Google, Microsoft, GitHub, ORCID and SWITCH edu-ID (scaffolded) via Authlib, all landing in the same pending queue as local sign-ups. Proven with a real authorization-code round trip, not just mocked claims.
Staged approval.POST /Register or a first OIDC login → pending → an administrator approves or rejects. The rules are identical for local and external identities, confirmed by a scripted parity check across 6 roles × 26 operations.
Verified writes.Every PATCH/PUT across all 9 SensorThings entities reports "0 rows matched", and the route turns that into a 403/404.
Reviewed twice.My own adversarial audit and a mentor review found 16 privilege and data-leak defects in the finished feature; all are fixed and re-verified.

Trust model & request lifecycle

Access is staged, never binary, and the staging is identical however an identity arrived.

Every arrow into APPROVED or REJECTED is an explicit administrator action — PATCH /Users/{id}/policy-approval or PATCH /Users/{id}/reject. Neither local registration nor an OIDC callback ever issues a usable token by itself.

The states are enforced by the same code path whether the identity is local or external: create_pending_oidc_user() writes exactly the role='pending' row that POST /Register does, so there is no OIDC fast lane and no way for an external identity to skip the waiting room.

In-depth workflow — how a request actually moves

The diagram above shows the states. This section walks every transition endpoint by endpoint, with the status codes each one actually returns.

A — Local registration → approval → first login

1
POST /Register (public) — body: username, password, dataset_id, requested_role, explanation, contact_info. dataset_id names a Network; it's optional, and ignored when NETWORK=0. The password is bcrypt-hashed off the event loop and the row lands as role='pending', status='pending'. → 201 with the new user's id.
2
The applicant has zero privileges: POST /Login refuses a pending account with 403, so no token exists until step 4.
3
An administrator reviews the queue: GET /Users lists pending rows with their requested_role and dataset_id.
4
PATCH /Users/{id}/policy-approval (admin only) — optional role (defaults to the requested role) and dataset (defaults to the requested Network). It re-checks the target is still pending and not rejected, validates the Network, then sets the role and status='active' with plain UPDATEs; access comes from the static RLS policies, not a per-user one. → 200.
5
POST /Login — username and password → 200 with a bearer token. Every request from here runs under SET LOCAL ROLE <group> plus app.current_user_id (workflow C).

B — External identity (OIDC) first-time sign-up

Only steps 1 and 5 reach istSOS4 from the browser; step 6 is istSOS4 talking to the provider directly. The provider never sees istSOS4's database, and istSOS4 never sees the user's provider password — only the claims the provider releases.

1–4
As diagrammed above — istSOS4 never learns a password, only the claims (email, name, subject id) the provider releases after consent.
5–6
The callback calls create_pending_oidc_user(), keyed on (auth_provider, external_sub_id) rather than username — the same pending row workflow A produces. A colliding username is auto-resolved with a suffix (see §06); a colliding email on an existing, different account sets an advisory possible_duplicate_of hint for an admin, never an automatic merge.
7
First time: 202 Accepted, "wait for approval". An administrator approves through the same PATCH /Users/{id}/policy-approval that handles local sign-ups — one approval path, not two. On the next login the callback finds an approved row and returns 200 with a bearer token.

C — Every authenticated request: RLS enforcement

1
Authorization: Bearer <token> arrives, or doesn't.
2
Protected routes reject a missing or invalid token with 401. With ANONYMOUS_VIEWER=1, read routes use get_current_user_optional() instead: no token runs as guest, a valid token runs as that user with full scoping, and an invalid token is still a 401 — never a silent downgrade to guest.
3
Inside the request's transaction, set_role() issues SET LOCAL ROLE <group>, mapped from the RBAC role (viewer/editor/custom → user, obs_manager/sensor → sensor, no session → guest), and sets app.current_user_id.
4
Every query on that connection is now subject to PostgreSQL RLS. The query's own SQL never mentions the user; the database enforces the boundary regardless of what the application asked for.
5
SET LOCAL ROLE is transaction-scoped: it reverts on COMMIT or ROLLBACK, including the implicit rollback of a cancelled request, so an elevated role can never leak onto the next request that reuses the pooled connection.
6
For a write, UPDATE … RETURNING id either returns a row (the write happened) or nothing (RLS excluded it), and the route turns "nothing" into an explicit 403 — never a bare 200.

D — Data governance: Network scoping

1
With ANONYMOUS_VIEWER=0 (the default) every route requires a valid token, and an anonymous GET returns 401 before RLS is reached. With ANONYMOUS_VIEWER=1, a token-less guest can read only the shared reference tables (Things, Sensors, Locations, ObservedProperties, HistoricalLocations, FeaturesOfInterest) — never Datastreams, Observations or Networks.
2
An approved identity's dataset_id holds a Network name; NULL means unrestricted. With NETWORK=0 it is ignored everywhere — at the entry points and in the RLS predicates.
3
current_app_user_network_id() resolves that name to a Network id and fails closed: an unknown or deleted name resolves to no rows, never to everything.
4
Datastream and Observation policies check network_id = current_app_user_network_id(), so a scoped viewer or editor sees exactly its own Datastreams and their Observations. The history tables behind $as_of time-travel queries carry the same policies. Reference tables stay shared by design.

Row-level security: the bug that made it a no-op

The single most consequential finding of the project. Earlier PRs (#185 onward) built RLS policies that looked correct, compiled and shipped — but could never match a real session, for any user.

Confirmed against pg_policy before the fix: policies scoped TO <username> existed, but no code path had ever created a PostgreSQL role for an individual user — SET ROLE "alice" would itself fail. Migration 006_session_scoped_rls_policies.sql replaced per-username policies with static, group-scoped ones (viewer/editor/… → PostgreSQL group roles user/sensor/qc) differentiated by the app.current_user_id session variable. The rest of the project is built on this model.

The static policies are scoped by Network, not just by role: for Datastream and Observation, the USING clause also checks network_id = current_app_user_network_id(), resolved from the caller's dataset_id through a SECURITY DEFINER helper. One role gets no blanket policy at all — custom starts at zero access, and an administrator writes per-user, per-row rules for it via POST /Policies. Each rule also requires the caller to still hold the custom role, so it stops applying the moment that user is moved to another role.

Pull request ledger

33 PRs were opened against istSOS/istSOS4: 18 merged and 15 closed without merging. The 18 merged PRs are targeted security fixes to the pre-existing RBAC scaffold. The 13 core GSoC PRs were closed on purpose, because the finished work is integrated from the fork as a whole. The fork below is the home of the latest work. Not shown: #144 (a lint fix, closed) and #147 (an early GET /Permissions capability contract, closed before its design question was answered).

Development fork KinshukSS2/istSOS4 Where the GSoC work was built and verified. It is integrated into istSOS4 as one unit from the docs/swagger-api-documentation branch. 130 files · +12,775 / −1,086 lines vs upstream Open the fork ↗

PR	Title	Status	Opened	Base / stacking
Phase 0 — RBAC hardening · 18 merged into main
#44	Prevent SQL injection in user-management DDL	Merged	Mar 5	main
#46	`.env.example` + setup instructions	Merged	Mar 5	main
#48	Fix write-pool guard for unconfigured `POSTGRES_PORT_WRITE`	Merged	Mar 6	main
#50	Timeout & error handling on authentication connections	Merged	Mar 6	main
#58	Guard auth Redis calls	Merged	Mar 13	main
#62	Fix deprecation warnings	Merged	Mar 13	main
#68	Tighten exception handling	Merged	Mar 13	main
#71	Fix missing auth header crash	Merged	Mar 13	main
#72	Validate secret key config	Merged	Mar 13	main
#76	Check revocation on refresh	Merged	Mar 14	main
#87	Auth header Redis TTL fix	Merged	Mar 15	main
#88	Replace deprecated `Body` example syntax	Merged	Mar 15	main
#90	Validate `SET ROLE` identifiers (SQL-injection hardening)	Merged	Mar 15	main
#106	Require auth for `/Users` when `AUTHORIZATION=1`	Merged	Mar 20	main
#108	Harden policy-update SQL composition	Merged	Mar 20	main
#109	Harden custom-policy creation for multi-user targets	Merged	Mar 20	main
#110	Handle pool-acquire type errors in `authenticate_user`	Merged	Mar 20	main
#116	Block `/Refresh` for revoked tokens	Merged	Mar 21	main

GSoC core deliverables, closed upstream and delivered in the fork 13

These PRs were closed on purpose. The GSoC work is integrated into istSOS4 as a whole from the fork, not merged one PR at a time. They are kept here as the incremental design and review history.

PR	Title	Status	Opened	Base / stacking
#185	Auto-create RLS policy on user creation	Closed	May 30	main
#188	Identity linking & JIT provisioning for OIDC users	Closed	Jun 8	main
#189	Password updates & input validation for local users	Closed	Jun 8	stacked on #188
#190	Role reassignment for active users	Closed	Jun 8	stacked on #189
#191	Finalize app-layer auth pivot, deprecate legacy DDL	Closed	Jun 14	main
#197	Application-layer auth foundation (Phase 0)	Closed	Jul 13	stacked on #191
#198	Audit log ledger & STAC service account	Closed	Jul 13	stacked on #197
#199	Public self-registration for restricted datasets	Closed	Jul 16	stacked on #198
#200	Admin approval for restricted datasets	Closed	Jul 16	stacked on #199
#201	Public data access prototype (superseded — see §06)	Closed	Jul 17	stacked on #200
#212	External identity providers + RLS enforcement fix	Closed	Aug 23	stacked on #201 — contains it fully
#213	Swagger/OpenAPI documentation for the whole auth surface	Closed	Aug 23	stacked on #212 — contains #201 and #212, plus the mentor-review fixes and the upstream merge
#215	Network-scoped Observation reads and writes	Closed	Sep 2	branched from an earlier #213; #213 now carries equivalent Observation scoping

Capability deep dives

What each capability does, where it lives, and which PRs shipped it.

Application-layer credentials, with zero-disruption migration

Users stopped being PostgreSQL LOGIN roles and became plain rows: sensorthings."User".password holds a bcrypt hash, verified with passlib on the Python side. Accounts created before this change have password IS NULL; authenticate_user() falls back to a direct pg_authid check on their first login and backfills the hash, so no existing account needed a reset. The fallback is marked in the code as temporary — it can be deleted once no pre-migration account remains.

set_role() was rewritten from SET ROLE "<username>" (targeting a role that was never created) to SET LOCAL ROLE <group>, which is transaction-scoped and reverts by itself. That made upstream's manual RESET ROLE calls unnecessary and closed a pool-leak bug class outright. The only ones left are in two upstream helpers, where they run just before the transaction ends.

#189#191#197

Network-scoped dataset access

#201 originally shipped a per-row is_public boolean and an ODRL-flavored policy prototype. On review it depended on a column no write endpoint ever let an owner set, and it layered a second, parallel access model on top of RBAC — so it was removed in full: the column, the migration and the guest-visibility policies. In its place, Network, istSOS's own entity, became the scoping unit: User.dataset_id holds a Network name and the standard RLS policies (§04) check it directly.

The same scoping covers the history tables behind $as_of time-travel queries, so a past snapshot can't expose another Network's data. With NETWORK=0, dataset_id is ignored entirely. With ANONYMOUS_VIEWER=0 anonymous access is denied outright; with ANONYMOUS_VIEWER=1 a token-less guest reads shared reference data only.

#212#213#215

Restricted registration & admin approval

POST /Register is intentionally unauthenticated; the security boundary is downstream, in the database. A fresh row lands as role='pending', status='pending', which matches no RLS policy anywhere, and password hashing runs off the event loop (asyncio.to_thread) so a slow bcrypt call can't stall other requests.

An administrator then calls PATCH /Users/{id}/policy-approval — the single approval endpoint for both local and OIDC sign-ups (an earlier duplicate, POST /Users/{id}/activate, was merged into it). It checks the target is still pending and not rejected, takes an optional role (defaulting to the one the applicant requested) and dataset, and validates the Network. PATCH /Users/{id}/reject rejects instead; a rejected applicant may re-register under the same username, which resets them to pending.

#199#200#213

The custom role — per-user, per-row RLS rules

Every other role has a standing policy. custom has none: a custom user sees nothing until an administrator writes rules for them via POST /Policies. Each rule is scoped to the shared group role and gated on current_app_user_role() = 'custom' AND current_app_user_id() = ANY([...]), so it stops applying as soon as the user is moved to another role, and applies again if they're moved back.

Because each rule is its own condition, an administrator can grant read on one row and read+write on another for the same user in a single call — verified live. The payload contract ({users, name, permissions}) is unchanged. For the static role types (viewer, editor, sensor, qc, obs_manager) there is nothing to create, so the endpoint now returns 400 instead of a silent 200.

#212#213

External identity — Google, Microsoft, GitHub, ORCID, SWITCH edu-ID

Provider-agnostic /auth/{provider}/login and /auth/{provider}/callback routes via Authlib. A provider only registers if both its CLIENT_ID and CLIENT_SECRET are set, so unsetting either disables just that one. An external identity that has never logged in lands in the same pending queue as a local registration; there is no separate, less-guarded path for OIDC accounts.

Identity is tracked by a unique (auth_provider, external_sub_id) pair, independent of the display username, so a username collision between people from different providers — or with an existing local account — doesn't fail the sign-up. It's auto-resolved with a deterministic suffix, and if the colliding email belongs to an existing account, an advisory possible_duplicate_of pointer is set for an admin to see. Accounts are never merged automatically.

#188#212

Role reassignment, password updates & deactivation

PATCH /Users/{id}/role is administrator-only. The target must exist and be active (pending accounts go through approval instead), the role must be assignable (administrator and pending are rejected), the last remaining administrator can't be demoted, and an optional dataset re-scopes the user's Network ("" clears it) after checking the Network exists. It is the only way to change a role: PATCH /Users/{id} edits contact details and URI and rejects role with 400.

PATCH /Users/{id}/password can be called by the account owner or an administrator. It rejects OIDC accounts, which have no local credential, and enforces the password rule (8+ characters, at least one digit and one symbol). DELETE /Users/{id} deactivates rather than deletes: the row stays, the username stays reserved, and an already-issued token stops working on its next use.

#189#190#213#215

Swagger / OpenAPI documentation overhaul

Before this PR, /Login, /Refresh, /Logout and both OIDC routes were hidden from the schema (include_in_schema=False), and none of the 77 endpoints documented an error response. All five auth routes are now visible, the auth and RBAC endpoints share responses= fragments that match the codebase's real error shapes ({"detail"}, {"message"} and {"code","type","message"} coexist by design), every request model has real field descriptions, and a landing page in Swagger documents the trust model and RLS.

A second security scheme, BearerAuth, lets an OIDC identity — which has no local password — authorize in Swagger UI by pasting its bearer token. A companion step-by-step testing guide covers every capability in this report — see §08.

#213

Test coverage & verification evidence

Two layers: a pytest suite for unit-level and mocked coverage, and scripted end-to-end harnesses that drive the real API against a real, freshly built database — none of these numbers is a mock's opinion of what should happen.

Suite	Result	Covers
pytest (unit)	95 passed	SQL-injection hardening, password and username validation, the RLS silent-write regression, approval for local and OIDC accounts, role-override handling, and the mentor-review fixes.
run_gsoc_acceptance.py	88 / 88	The full auth surface end to end — registration through deactivation, Network scoping, policies, session lifecycle, privilege boundaries and the untouched upstream SensorThings contract — in one run against a live stack.
run_auth_parity.py	156 / 156	Local vs. OIDC privilege parity: 6 roles × 26 operations, diffed. Local and external identities behave identically once approved, with one documented exception (password change).
run_oidc_e2e.py	19 / 19	A genuine OAuth 2.0 authorization-code round trip — a real RS256-signed `id_token` and real JWKS verification — from first login through pending, admin approval and a scoped data read.
run_rls_leak_audit.py	34 / 34	A user scoped to one Network systematically probing every read, write, navigation and expand path into another Network's data.
OGC conformance	405 / 405	The SensorThings API v1.1 conformance suite (Sensing Core, Create-Update-Delete and Filtering classes), run with every fix so security changes can't silently break the upstream contract.

What "verified" means

Every number above comes from a run against a database wiped and rebuilt from the committed migrations immediately beforehand — never a long-lived dev database that might hide stale state. Ground-truth row counts are pulled from PostgreSQL directly and compared against the API's response to the same query, under a scoped user's token, in the same script.

Hands-on testing guide

A companion site turns every capability in this report into a step-by-step Swagger test, with the exact request to send, the response to expect and the code behind each result.

Companion site

istSOS4 Swagger Guide

32 tests that exercise authentication, roles and row-level security from the Swagger page, starting from a fresh stack.

Part ALocal account lifecycle12
Part BExternal (OIDC) authentication4
Part CData visibility & Network scoping10
Part DThe custom role5

Open the guide

Setup & reproduction

A fresh clone reproduces the full stack, including the auth schema, from either compose file. Both build every service from source rather than pulling a published image, because no published tag contains this project's migrations or code yet.

1 — Clone & configure

git clone https://github.com/KinshukSS2/istSOS4.git
cd istSOS4
git checkout docs/swagger-api-documentation
cp .env.testing .env
# .env.testing is .env.example with AUTHORIZATION, NETWORK, VERSIONING
# and DUMMY_DATA switched on. .env.example keeps upstream's defaults
# (all off) for CI and plain deployments. Outside local testing, set
# your own SECRET_KEY (openssl rand -hex 32) and passwords.

2 — Start the stack

docker compose up -d --build
# on first init the database applies istsos_schema.sql, istsos_auth.sql,
# istsos_schema_versioning.sql, functions.sql and migrations 001–016

Existing database?

PostgreSQL runs init scripts only on an empty data volume. If a volume from an older checkout already exists, either reset it with docker compose down -v or apply database/migrations/*.sql to it by hand — every migration is idempotent.

3 — Open Swagger

http://localhost:8018/istsos4/v1.1/docs
# Authorize -> username: admin, password: your ISTSOS_ADMIN_PASSWORD
# or paste an OIDC-issued bearer token under BearerAuth

4 — Test external auth without a real provider

docker compose -f docker-compose.yml -f docker-compose.e2e.yml up -d --build
python api/tests/e2e/run_oidc_e2e.py
# a self-contained fake OpenID Connect provider: it generates its own
# RSA keypair, signs real RS256 id_tokens and serves a real JWKS, and
# Authlib validates it exactly as it would Google

5 — (Optional) enable real identity providers

# in .env — leave a pair empty to disable that provider
GOOGLE_CLIENT_ID=…            GOOGLE_CLIENT_SECRET=…
MICROSOFT_CLIENT_ID=…         MICROSOFT_CLIENT_SECRET=…
GITHUB_CLIENT_ID=…            GITHUB_CLIENT_SECRET=…
ORCID_CLIENT_ID=…             ORCID_CLIENT_SECRET=…
# redirect URI to register with each provider:
# {HOSTNAME}{SUBPATH}{VERSION}/auth/{provider}/callback