airajena opened a new pull request, #63:
URL: https://github.com/apache/fineract-business-intelligence/pull/63
## Audit Fixes
### 🔴 Critical
| Finding | Status |
|---|---|
| RLS fails open to `'admin'` when `current_username()` is empty | ✅ Fixed:
empty/None now yields zero rows (fail-closed), not admin access |
| RLS injectable via unescaped username interpolation | ✅ Fixed: quotes are
escaped (`replace("'", "''")`) before interpolation |
| Test suite concealed the RLS bug (stripped the vulnerable line before
testing) | ✅ Fixed: tests now render through real Jinja, exercising the actual
line |
| No database-level RLS (`CREATE POLICY`), only app-layer SQL text | ❌ Not
done: deliberately deferred, see below |
| Historical portfolio figures wrong (current balance replicated across all
history) | ✅ Fixed: rebuilt via point-in-time reconstruction from the
transaction ledger |
| `loan_status_id = 300` filter made closed loans vanish from history
retroactively | ✅ Fixed: filter removed, closed loans now stay in historical
snapshots |
| PAR bands based on bucket config (`min_age_days`), not real DPD | ✅ Fixed:
DPD now computed from `m_loan_repayment_schedule`, not bucket config |
| Per-batch commits break run atomicity (`WatermarkManager.update()`
committing mid-run) | ✅ Fixed: commit removed, atomicity owned solely by
`run()` |
### 🟡 Smaller Items
| Finding | Status |
|---|---|
| NULL cursor columns silently skipped, causing hard failures on NOT NULL
columns | ✅ Fixed: coalesced to epoch sentinel |
| COB gate too loose (any completed job, not specifically Close-of-Business)
| ✅ Fixed: scoped to `job_name` |
| Bind-parameter ceiling risk (~51k params against 65535 limit) | ✅ Fixed:
chunked inserts |
| No TLS on database connections | ✅ Fixed: configurable `ssl_mode` per
connection |
| Deletes never propagate (upsert-only, orphans persist forever) | ✅ Fixed:
new reconcile mode, verified live |
---
## What's Not Done & Why: Native Postgres Row-Level Security
### Context
All audit items are resolved **except one**: native database-level RLS via
Postgres `CREATE POLICY`.
### Why it wasn't implemented
All Superset users share a **single database login**
(`WAREHOUSE_READER_USER`) from a shared connection pool. Postgres RLS works by
checking *"who is the current user?"* but since everyone uses the same login,
Postgres cannot distinguish between users on its own.
To make this work, you'd need to identify the real human user per-query by
running:
```sql
SET app.username = 'actual_user'
```
before every query, and the only place in Superset to inject this is the
`DB_CONNECTION_MUTATOR` hook.
**The risk:** If Superset reuses a pooled connection without re-running that
hook (which we could not reliably verify), **User A's session variable could
leak onto User B's connection**, silently bypassing office-level data
isolation. That is a worse outcome than the current state.
Given this risk and no safe way to verify Superset's exact connection
checkout/reuse behavior within the project scope, this was deliberately left as
a documented gap.
### What was done instead
The existing Jinja-templated application-layer RLS (in each Superset
dataset's SQL) was hardened:
- **Fail-closed**: empty or missing username yields zero rows returned, not
full admin access
- **Injection-safe**: usernames are escaped before interpolation into SQL
- **Fully tested**: unit + integration tests covering both fail-closed and
injection cases
### Current risk posture
| Layer | Status |
|---|---|
| Application-layer RLS (Superset dataset SQL) | ✅ Hardened: fail-closed +
injection-safe |
| Database-layer RLS (Postgres `CREATE POLICY`) | ❌ Not implemented: known
gap |
> ⚠️ The application-layer RLS depends on every dataset's SQL being written
correctly. It is safe and tested, but not database-enforced.
Fixes:
[62](https://github.com/apache/fineract-business-intelligence/issues/62)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
