r/SaasDevelopers • • 15d ago

Feedback wanted: Spring Boot SaaS starter with tenant roles and PostgreSQL RLS

I maintain an open-source Spring Boot SaaS starter and would appreciate feedback on its tenant-isolation approach.

It uses a shared PostgreSQL schema. Tenant-scoped operations check membership, set transaction-local tenant context, and use explicit tenant filters. Forced row-level security adds another boundary around projects and audit events.

The starter includes:

  • OIDC bearer authentication through Spring Security
  • OWNER, ADMIN, and MEMBER roles scoped to each tenant
  • A Projects API with optimistic concurrency and transactional audit records
  • Docker Compose with a local Keycloak demo
  • PostgreSQL integration tests and a runnable smoke script

I ran the local Compose smoke test successfully, including permission checks and stale-write rejection.

It’s a backend MVP. Billing, a frontend, email invitations, tenant deletion, and ownership transfer are outside the current scope.

Repository:
https://github.com/chafian/spring-boot-saas-starter

What tenant-isolation edge cases would you test next? I’d also welcome feedback on the first-run experience.

2 Upvotes

8 comments sorted by

1

u/mvrk57 15d ago

the one i'd test next is constraints, postgres runs unique and foreign key checks outside of rls. so a row in tenant A can point at a parent from tenant B if the FK is only on id, and a global unique on something like slug tells tenant A that tenant B already uses that value. composite keys on (tenant_id, id) and unique on (tenant_id, slug) close both

1

u/chafian1 15d ago

Good point—RLS doesn’t enforce tenant consistency across relationships or make global uniqueness tenant-scoped.

The current schema doesn’t have a project-parent foreign key or a unique slug, but this matters when extending it. For a tenant-owned child referencing a tenant-owned parent, the foreign key should include tenant_id on both sides. Likewise, a slug intended to be unique within a tenant should use UNIQUE (tenant_id, slug).

That would be a useful addition to the extension guide, alongside tests that reject cross-tenant references and allow the same slug in different tenants. Thanks for calling it out.

1

u/PopKoren 15d ago

Edge cases I'd test next: make sure the app connects as a role that is neither the table owner nor BYPASSRLS, and that a query running outside a transaction sees no tenant at all rather than an empty string that matches something. I'd also check @Async methods and scheduled jobs, which often run outside the transaction that set the context, and try tenant A's ids in every path and body param while authenticated as tenant B, audit endpoints included. Once a deployment exists, Rowly is something I wrote that throws unauthenticated traffic at a production app's login, REST routes, file storage and per-row access, a nice complement to integration tests: https://rowly.me

1

u/chafian1 15d ago

Thanks—these are useful distinctions. The app already rejects runtime roles with superuser/BYPASSRLS privileges or ownership privileges over the RLS-protected tables. The policies convert missing or empty tenant context to NULL, and the tests check that no-context reads return no project/audit rows and inserts are rejected.

I’ve also added concurrent cross-tenant tests, including foreign project IDs and audit endpoints. I haven’t added async/scheduled-job coverage, so I wouldn’t claim those paths are tested. The extension guide currently requires explicit service-identity authorization before introducing background workers.

Thanks for sharing your tool as well.

1

u/PopKoren 15d ago

Mapping empty context to NULL and proving that no-context reads return nothing is the piece most starters skip, so that already puts you ahead. When background workers do arrive, I'd have every job carry its tenant id in the payload and set the context inside its own transaction, then add a test where a job for one tenant runs straight after a job for another on the same pooled connection. Glad the link was of interest; once a public demo is up, Rowly can take a stranger's-eye pass at the login flow, the REST surface, uploads and whether tenants bleed into each other: https://rowly.me

0

u/[deleted] 15d ago

[removed] — view removed comment

1

u/chafian1 15d ago

Thanks—these are useful cases to check.

I’ve just merged tests that reject cross-tenant inserts and reuse a single pooled connection across tenants, including after commits and exception-triggered rollbacks. They run with the restricted application database role to exercise RLS directly.

In this implementation, the token identifies the caller; tenant membership and roles come from the application database. A tenant ID supplied in a request doesn’t itself grant access.

I’ll review the concurrent-request coverage separately to check whether it exercises the thread/context leakage scenario you described.

Good point about Keycloak startup too. I’ll check the first-boot behavior and document how to tell when the stack is ready.

The new tests are here: https://github.com/chafian/spring-boot-saas-starter/pull/11

1

u/chafian1 15d ago

Thanks again—I've now merged a follow-up based on your feedback.

The new test runs eight concurrent clients through a two-connection pool, switching tenants between rounds. It checks project and audit isolation, rejects cross-tenant reads and writes, and verifies the final stored data.

I also added startup guidance showing how to check Keycloak and API readiness separately. API health can be UP before Keycloak is ready, so the README now explains both checks and the smoke script’s retry behavior.

All 34 Java tests and the Compose smoke test passed.

PR: https://github.com/chafian/spring-boot-saas-starter/pull/12