No household-level DB enforcement — isolation relies solely on application code #13

New Issue

marcel · 2026-04-02T11:21:28+02:00

marcel commented

2026-04-02 11:21:28 +02:00

Problem

All household data isolation is enforced at the application layer via HouseholdResolver. There is no row-level security (RLS) or equivalent at the database level. If any service method forgets to filter by householdId, data leaks across households.

Currently all service methods do check householdId correctly, but this is a fragile pattern for a multi-tenant application.

Affected files

All service classes that take householdId as a parameter
All repository classes that should filter by household

Recommended fix

Add defense-in-depth via one of:

PostgreSQL Row-Level Security policies on household-scoped tables
Hibernate @Filter with @FilterDef that automatically applies a household_id predicate
Both

This ensures a missed application-layer check doesn't result in cross-tenant data exposure.

Severity

Medium — defense-in-depth concern; current code is correct but fragile.

## Problem All household data isolation is enforced at the application layer via `HouseholdResolver`. There is no row-level security (RLS) or equivalent at the database level. If any service method forgets to filter by `householdId`, data leaks across households. Currently all service methods do check `householdId` correctly, but this is a fragile pattern for a multi-tenant application. ## Affected files - All service classes that take `householdId` as a parameter - All repository classes that should filter by household ## Recommended fix Add defense-in-depth via one of: 1. PostgreSQL Row-Level Security policies on household-scoped tables 2. Hibernate `@Filter` with `@FilterDef` that automatically applies a `household_id` predicate 3. Both This ensures a missed application-layer check doesn't result in cross-tenant data exposure. ## Severity Medium — defense-in-depth concern; current code is correct but fragile.

marcel added the kind/security priority/medium labels 2026-04-02 11:21:50 +02:00

marcel commented

2026-04-03 09:25:12 +02:00

👨‍💻 Kai — Frontend Engineer

This is a backend/DB concern, but there's a frontend angle worth flagging:

Impact on me

If a service method ever forgets to filter by householdId, the API will silently return wrong data — and my components will render it without any indication something is off. The frontend has no way to detect cross-household leakage.

Questions I'd want answered before we ship

Will the fix produce any observable API changes (new error codes, changed response shapes)? If RLS or the Hibernate filter causes unexpected query failures, will they surface as 500s or clean 403s?
If a Hibernate @Filter is added, does it apply automatically to all session-scoped requests? Is there any risk it bleeds between concurrent requests in a shared session/connection pool context?

What I'd ask the backend to guarantee

Any filter activation must happen before the first DB call in a request — ideally in a Spring interceptor or HandlerInterceptor, not scattered per service. If it's done in HouseholdResolver, confirm that runs early enough.
Error responses from a failed filter (e.g., no householdId on the session) should return a structured JSON { "error": "..." } body, not a raw Spring exception page — my error handling in +page.server.ts depends on predictable shapes.

Low-risk, high-value suggestion

Once RLS or the filter is in place, consider adding a canary integration test that bypasses the application layer and queries the DB directly as a non-owner role — proves the DB-level enforcement actually works independently of the code.

## 👨‍💻 Kai — Frontend Engineer This is a backend/DB concern, but there's a frontend angle worth flagging: **Impact on me** - If a service method ever forgets to filter by `householdId`, the API will silently return wrong data — and my components will render it without any indication something is off. The frontend has no way to detect cross-household leakage. **Questions I'd want answered before we ship** - Will the fix produce any observable API changes (new error codes, changed response shapes)? If RLS or the Hibernate filter causes unexpected query failures, will they surface as 500s or clean 403s? - If a Hibernate `@Filter` is added, does it apply automatically to all session-scoped requests? Is there any risk it bleeds between concurrent requests in a shared session/connection pool context? **What I'd ask the backend to guarantee** - Any filter activation must happen before the first DB call in a request — ideally in a Spring interceptor or `HandlerInterceptor`, not scattered per service. If it's done in `HouseholdResolver`, confirm that runs early enough. - Error responses from a failed filter (e.g., no `householdId` on the session) should return a structured JSON `{ "error": "..." }` body, not a raw Spring exception page — my error handling in `+page.server.ts` depends on predictable shapes. **Low-risk, high-value suggestion** - Once RLS or the filter is in place, consider adding a canary integration test that bypasses the application layer and queries the DB directly as a non-owner role — proves the DB-level enforcement actually works independently of the code.

marcel commented

2026-04-03 09:25:25 +02:00

🛠️ Backend Engineer

This is exactly the kind of defense-in-depth gap that bites multi-tenant apps in production. The current pattern is correct but one missed householdId filter in any service method is a data breach. Let's fix that.

On the three options — my take

PostgreSQL RLS: Most robust. Enforced unconditionally at the DB level, even if someone bypasses the application entirely (e.g., a migration script, a batch job, a future admin tool). The tradeoff: requires setting app.current_household_id as a session variable on every connection, which is non-trivial with a connection pool (HikariCP). You need a ConnectionCustomizer or a Statement executed on connection borrow. Doable, but adds connection lifecycle complexity.
Hibernate @Filter / @FilterDef: Simpler to integrate — activate in a HandlerInterceptor or AOP advice, applied to all queries through the JPA session. The gap: doesn't protect against native queries or JDBC calls that bypass Hibernate. Also easy to forget to activate on a new request path.
Both: The right answer for a multi-tenant app with real security requirements. RLS as the last line; Hibernate filter as the application-layer enforcement. More setup work up front, but dramatically reduces the blast radius of any future mistake.

Questions before implementing

Are there any native @Query(nativeQuery = true) annotations in the repositories currently? Those won't be covered by Hibernate filters and would need manual household_id predicates.
Does the app use any background jobs or scheduled tasks that operate across households? Those would need to either disable the filter explicitly or use a superuser DB role that bypasses RLS.
What PostgreSQL role does the app connect as? For RLS to be meaningful, the app role should not be a superuser (superusers bypass RLS by default unless FORCE ROW LEVEL SECURITY is set on the table).

Suggested implementation order

Audit all @Query methods in all repositories — list which ones filter by household_id and which don't.
Add findByIdAndHouseholdId variants where missing (related to #12).
Add Hibernate @Filter on household-scoped entities, activate in interceptor.
Add RLS policies on the most sensitive tables (ingredients, recipes, shopping lists, meal plans) as a second layer.
Write a test that deliberately omits the filter activation and verifies RLS blocks access.

## 🛠️ Backend Engineer This is exactly the kind of defense-in-depth gap that bites multi-tenant apps in production. The current pattern is correct but one missed `householdId` filter in any service method is a data breach. Let's fix that. **On the three options — my take** - **PostgreSQL RLS**: Most robust. Enforced unconditionally at the DB level, even if someone bypasses the application entirely (e.g., a migration script, a batch job, a future admin tool). The tradeoff: requires setting `app.current_household_id` as a session variable on every connection, which is non-trivial with a connection pool (HikariCP). You need a `ConnectionCustomizer` or a `Statement` executed on connection borrow. Doable, but adds connection lifecycle complexity. - **Hibernate `@Filter` / `@FilterDef`**: Simpler to integrate — activate in a `HandlerInterceptor` or AOP advice, applied to all queries through the JPA session. The gap: doesn't protect against native queries or JDBC calls that bypass Hibernate. Also easy to forget to activate on a new request path. - **Both**: The right answer for a multi-tenant app with real security requirements. RLS as the last line; Hibernate filter as the application-layer enforcement. More setup work up front, but dramatically reduces the blast radius of any future mistake. **Questions before implementing** - Are there any native `@Query(nativeQuery = true)` annotations in the repositories currently? Those won't be covered by Hibernate filters and would need manual `household_id` predicates. - Does the app use any background jobs or scheduled tasks that operate across households? Those would need to either disable the filter explicitly or use a superuser DB role that bypasses RLS. - What PostgreSQL role does the app connect as? For RLS to be meaningful, the app role should not be a superuser (superusers bypass RLS by default unless `FORCE ROW LEVEL SECURITY` is set on the table). **Suggested implementation order** 1. Audit all `@Query` methods in all repositories — list which ones filter by `household_id` and which don't. 2. Add `findByIdAndHouseholdId` variants where missing (related to #12). 3. Add Hibernate `@Filter` on household-scoped entities, activate in interceptor. 4. Add RLS policies on the most sensitive tables (ingredients, recipes, shopping lists, meal plans) as a second layer. 5. Write a test that deliberately omits the filter activation and verifies RLS blocks access.

marcel commented

2026-04-03 09:25:37 +02:00

🧪 QA Engineer

The current state — "correct but fragile" — is exactly the kind of risk that needs test coverage to prove the invariant holds and to catch regressions the moment someone adds a new service method without the filter.

What's missing in the test suite right now

No cross-household isolation tests that I can see. For every household-scoped entity (recipes, ingredients, shopping lists, meal plans, staples), we need at least one test that: creates data in household A, authenticates as a user from household B, and asserts the API returns 0 results / 404 / 403 — never household A's data.
No test that deliberately bypasses the Hibernate filter (if added) to verify RLS holds independently.

Test cases I'd add as part of this fix

For every major GET endpoint on household-scoped resources:

shouldReturn404WhenResourceBelongsToADifferentHousehold()
shouldReturnEmptyListWhenHouseholdHasNoData() (distinguishes "no data" from "wrong household")
shouldNotLeakHouseholdADataToHouseholdBUser()

For the Hibernate filter path:

A test that creates a request context without activating the filter and verifies the query either fails safely or RLS catches it.

Integration test architecture question

Are we using Testcontainers with a real PostgreSQL instance for integration tests? RLS policies will only be verifiable with a real DB — H2 or mocking won't cut it here.
How are test households seeded? We need at least two households with overlapping data shapes (same recipe names, same ingredient UUIDs if possible) to make isolation failures obvious.

Coverage concern

This is a security invariant, not just a business rule. I'd argue every household-scoped repository method needs a cross-household test, not just the ones we're fixing today. Can we make that a policy going forward?

## 🧪 QA Engineer The current state — "correct but fragile" — is exactly the kind of risk that needs test coverage to prove the invariant holds and to catch regressions the moment someone adds a new service method without the filter. **What's missing in the test suite right now** - No cross-household isolation tests that I can see. For every household-scoped entity (recipes, ingredients, shopping lists, meal plans, staples), we need at least one test that: creates data in household A, authenticates as a user from household B, and asserts the API returns 0 results / 404 / 403 — never household A's data. - No test that deliberately bypasses the Hibernate filter (if added) to verify RLS holds independently. **Test cases I'd add as part of this fix** For every major GET endpoint on household-scoped resources: - `shouldReturn404WhenResourceBelongsToADifferentHousehold()` - `shouldReturnEmptyListWhenHouseholdHasNoData()` (distinguishes "no data" from "wrong household") - `shouldNotLeakHouseholdADataToHouseholdBUser()` For the Hibernate filter path: - A test that creates a request context without activating the filter and verifies the query either fails safely or RLS catches it. **Integration test architecture question** - Are we using Testcontainers with a real PostgreSQL instance for integration tests? RLS policies will only be verifiable with a real DB — H2 or mocking won't cut it here. - How are test households seeded? We need at least two households with overlapping data shapes (same recipe names, same ingredient UUIDs if possible) to make isolation failures obvious. **Coverage concern** - This is a security invariant, not just a business rule. I'd argue every household-scoped repository method needs a cross-household test, not just the ones we're fixing today. Can we make that a policy going forward?

marcel commented

2026-04-03 09:25:52 +02:00

🔒 Sable — Security Engineer

This is the right issue to raise and the right time to address it — before the surface area grows. Let me add some threat model depth.

Why "correct but fragile" is a real risk

Every new developer, every new feature, every new service method is a potential miss. Application-layer-only isolation means the security model lives in developer discipline, not in an enforceable control. That's not a security model — it's a convention.
The blast radius of a single missed householdId filter is full cross-tenant read access on that resource type. In a meal planning app with dietary restrictions and household membership data, that's a meaningful privacy breach.

RLS implementation specifics to get right

The app DB role must not be a superuser. PostgreSQL superusers bypass RLS unless ALTER TABLE ... FORCE ROW LEVEL SECURITY is set. Verify the role used by HikariCP.
Session variable approach: SET LOCAL app.current_household_id = '...' must be set within the same transaction/connection that executes the query. With HikariCP's connection pool, connections are reused — a stale app.current_household_id from a previous request is a serious risk. Use SET LOCAL (transaction-scoped) not SET SESSION (connection-scoped).
RLS policies should use USING for read operations and WITH CHECK for write operations. Both are needed.
Consider a separate readonly PostgreSQL role for SELECT queries with more restrictive RLS, versus the app role for writes.

Hibernate filter gaps

@Filter only applies to JPQL/HQL and criteria queries routed through Hibernate. Any @Query(nativeQuery = true), any JdbcTemplate call, any EntityManager.createNativeQuery() bypasses it entirely. Audit for those before claiming the filter provides coverage.
The filter must be activated on every request path that touches household data — including async tasks, scheduled jobs, and event listeners. Missing one is equivalent to missing a householdId check today.

Questions before closing this

What PostgreSQL role does the production app use? Is it a superuser?
Are there any native queries or JDBC calls in the codebase today?
Are there background jobs (e.g., scheduled cleanup, notification dispatch) that need explicit exemption or their own DB role?
Will we add FORCE ROW LEVEL SECURITY to all household-scoped tables as a backstop?

My recommendation: Prioritize RLS with SET LOCAL + FORCE ROW LEVEL SECURITY on the most sensitive tables first (meals, shopping lists, recipes). Add the Hibernate filter as a second layer. Audit native queries. Write a test that connects to the DB as the app role without setting the session variable and verifies no household data is returned.

## 🔒 Sable — Security Engineer This is the right issue to raise and the right time to address it — before the surface area grows. Let me add some threat model depth. **Why "correct but fragile" is a real risk** - Every new developer, every new feature, every new service method is a potential miss. Application-layer-only isolation means the security model lives in developer discipline, not in an enforceable control. That's not a security model — it's a convention. - The blast radius of a single missed `householdId` filter is full cross-tenant read access on that resource type. In a meal planning app with dietary restrictions and household membership data, that's a meaningful privacy breach. **RLS implementation specifics to get right** - The app DB role **must not be a superuser**. PostgreSQL superusers bypass RLS unless `ALTER TABLE ... FORCE ROW LEVEL SECURITY` is set. Verify the role used by HikariCP. - Session variable approach: `SET LOCAL app.current_household_id = '...'` must be set within the same transaction/connection that executes the query. With HikariCP's connection pool, connections are reused — a stale `app.current_household_id` from a previous request is a serious risk. Use `SET LOCAL` (transaction-scoped) not `SET SESSION` (connection-scoped). - RLS policies should use `USING` for read operations and `WITH CHECK` for write operations. Both are needed. - Consider a separate `readonly` PostgreSQL role for SELECT queries with more restrictive RLS, versus the app role for writes. **Hibernate filter gaps** - `@Filter` only applies to JPQL/HQL and criteria queries routed through Hibernate. Any `@Query(nativeQuery = true)`, any `JdbcTemplate` call, any `EntityManager.createNativeQuery()` bypasses it entirely. Audit for those before claiming the filter provides coverage. - The filter must be activated on **every** request path that touches household data — including async tasks, scheduled jobs, and event listeners. Missing one is equivalent to missing a `householdId` check today. **Questions before closing this** - What PostgreSQL role does the production app use? Is it a superuser? - Are there any native queries or JDBC calls in the codebase today? - Are there background jobs (e.g., scheduled cleanup, notification dispatch) that need explicit exemption or their own DB role? - Will we add `FORCE ROW LEVEL SECURITY` to all household-scoped tables as a backstop? **My recommendation**: Prioritize RLS with `SET LOCAL` + `FORCE ROW LEVEL SECURITY` on the most sensitive tables first (meals, shopping lists, recipes). Add the Hibernate filter as a second layer. Audit native queries. Write a test that connects to the DB as the app role without setting the session variable and verifies no household data is returned.

marcel commented

2026-04-03 09:26:03 +02:00

🎨 Atlas — UI/UX Designer

This is infrastructure work with no direct UI output, but there are user-facing design considerations worth flagging now rather than retrofitting later.

Surfaces where isolation failures would be visible to users

The meal planner (C1) renders a week of meals. If a household isolation bug exposed another household's meal plan, users would see unfamiliar recipes in their planner — a jarring, trust-destroying experience.
The shopping list (D1) shows items that members act on collaboratively. Cross-household leakage here could cause a household to buy someone else's groceries — a real-world consequence.
The variety score on C1 is calculated from the current household's meal plan. A cross-household data leak here would produce a meaningless score — silently wrong, no error state shown.

Error state design question

If the RLS or Hibernate filter rejects a query (e.g., missing household context on the session), the API will return an error. What does the UI show?
We don't yet have a designed "unexpected error" or "data unavailable" state for C1 or D1. Before implementing this fix, I'd want to agree on: what does a household context failure look like to the user? A generic error banner? A redirect to login? An empty state with a retry button?
The answer affects how +page.server.ts error handling is written, which affects what Kai implements.

Suggestion

Let's define the error state for "household context unavailable" as part of this issue, even if it's just a placeholder empty state today. It ensures the fix is complete end-to-end and we don't ship a backend control with no user-facing error handling.

## 🎨 Atlas — UI/UX Designer This is infrastructure work with no direct UI output, but there are user-facing design considerations worth flagging now rather than retrofitting later. **Surfaces where isolation failures would be visible to users** - The meal planner (C1) renders a week of meals. If a household isolation bug exposed another household's meal plan, users would see unfamiliar recipes in their planner — a jarring, trust-destroying experience. - The shopping list (D1) shows items that members act on collaboratively. Cross-household leakage here could cause a household to buy someone else's groceries — a real-world consequence. - The variety score on C1 is calculated from the current household's meal plan. A cross-household data leak here would produce a meaningless score — silently wrong, no error state shown. **Error state design question** - If the RLS or Hibernate filter rejects a query (e.g., missing household context on the session), the API will return an error. What does the UI show? - We don't yet have a designed "unexpected error" or "data unavailable" state for C1 or D1. Before implementing this fix, I'd want to agree on: what does a household context failure look like to the user? A generic error banner? A redirect to login? An empty state with a retry button? - The answer affects how `+page.server.ts` error handling is written, which affects what Kai implements. **Suggestion** - Let's define the error state for "household context unavailable" as part of this issue, even if it's just a placeholder empty state today. It ensures the fix is complete end-to-end and we don't ship a backend control with no user-facing error handling.

marcel referenced this issue

2026-04-03 09:26:28 +02:00

Shopping list addItem does not validate ingredient belongs to household #12

marcel referenced this issue

2026-04-03 09:26:39 +02:00

Shopping list addItem does not validate ingredient belongs to household #12

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: marcel/mealprep#13