Row-level security as a tenancy boundary in Laravel and PostgreSQL

In a multi-tenant application, the most expensive bug is one customer seeing another customer's data. In EduByte, our open-source school platform, a "customer" is a school, and the data is student records, marks and fees. We wanted isolation that holds even when a developer forgets a where clause.

So EduByte puts every institution in one PostgreSQL database — pooled tenancy — and makes the database itself refuse cross-tenant access with row-level security (RLS). This post walks through how that works and how we check it on every pull request.

Why not just scope queries in the application?

The usual Laravel approach is a global scope that adds where tenant_id = ? to every model query. It works, until it doesn't:

• a raw query or DB::table() call skips the scope;
• a report joins a table whose model was never scoped;
• a queued job runs without the request that set the tenant.

Each of these is one missed line away from a data leak, and none of them fails loudly. Application scoping is still useful for convenience, but we didn't want it to be the boundary.

The pieces

There are four, and each one closes a gap the others leave open.

1. A function that reads the current tenant

The current institution lives in a transaction-local setting, app.tenant_id. A small SQL function reads it and returns NULL when it is missing:

CREATE OR REPLACE FUNCTION app_current_tenant() RETURNS uuid
LANGUAGE sql STABLE AS $$
  SELECT NULLIF(current_setting('app.tenant_id', true), '')::uuid
$$;

The second argument to current_setting — true — means "return empty instead of raising an error when the setting isn't defined". With no tenant context, the function returns NULL, and tenant_id = NULL matches nothing. No context means no rows, which is the failure mode you want.

2. Forced RLS on every tenant table

Migrations call a helper that enables and forces RLS, then installs one standard policy:

ALTER TABLE students ENABLE ROW LEVEL SECURITY;
ALTER TABLE students FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_rows ON students
  USING (tenant_id = app_current_tenant())
  WITH CHECK (tenant_id = app_current_tenant());

USING filters what a query can see, update or delete. WITH CHECK stops a row from being inserted for another tenant, or moved to one. FORCE matters because, by default, a table's owner is exempt from its own policies.

In the Laravel migration it is a single line:

Schema::create('students', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->uuid('tenant_id');
    // ...
});

Rls::enable('students');

3. A runtime role that cannot bypass RLS

PostgreSQL superusers and roles with BYPASSRLS ignore policies entirely. So EduByte uses two roles:

-- Owns the schema and runs migrations.
CREATE ROLE edulab_owner LOGIN NOSUPERUSER NOCREATEROLE BYPASSRLS;

-- What the application connects as at runtime.
CREATE ROLE edulab_app LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOBYPASSRLS;

Migrations run as the owner (php artisan migrate --database=pgsql_owner). The application only ever connects as edulab_app, which has data privileges and nothing else. Even a bug that builds the wrong SQL runs under the policies.

4. Setting the tenant per transaction with SET LOCAL

Every authenticated request is wrapped in a transaction by middleware, and the first thing that transaction does is set the context:

DB::select(
    "SELECT set_config('app.tenant_id', ?, true),
            set_config('app.user_id', ?, true)",
    [$tenantId, $userId]
);

The final true makes the setting local to the transaction — the same as SET LOCAL. When the transaction commits or rolls back, the setting is gone. That is essential with connection pooling: a pooled connection can never carry one request's tenant into the next request.

Foreign keys that respect tenants

RLS controls which rows you can see, but a plain foreign key would still let a row in school A reference a row in school B if someone supplied the ID. EduByte closes that with composite keys:

ALTER TABLE sections ADD CONSTRAINT sections_tenant_id_id_unique UNIQUE (tenant_id, id);

ALTER TABLE enrolments
  ADD CONSTRAINT enrolments_section_id_tenant_fk
  FOREIGN KEY (tenant_id, section_id) REFERENCES sections (tenant_id, id);

Now the database refuses any reference that crosses institutions, regardless of what the application sends.

Proving it, not assuming it

Rules that rely on every developer remembering them eventually get forgotten. So the rules are checked by machines.

php artisan db:check-rls runs in CI on every pull request. It fails if any table with a tenant_id column lacks forced RLS, or if the runtime role is able to bypass it. A new module that forgets Rls::enable() cannot be merged.

php artisan db:isolation-check is the stronger test. Connected as the runtime role, it takes two institutions and, for every tenant table, proves that:

• with no institution context, the table reads as empty;
• under institution A, none of B's rows are visible;
• under A, B's rows cannot be updated or deleted;
• under A, a row for B cannot be inserted, and A's rows cannot be moved to B.

It then calls every institution route through the real HTTP stack with each school's session, checking that no response contains the other school's identifiers and that requests using the other school's record IDs fail. Everything runs in a transaction that is rolled back at the end.

Trade-offs

Pooled tenancy with RLS is not free:

• Cross-tenant work needs a deliberate path. Platform-wide reporting runs through a few SECURITY DEFINER functions owned by the owner role, which return counts — never rows.
• Every request is a transaction. That is a small cost, and it gives you consistent reads and clean rollback on errors as a bonus.
• Policies must stay simple. A policy is evaluated for every row a query touches, so ours compare one indexed column against one function call.

For EduByte, those costs buy a property that matters more than almost anything else in a school system: one institution's data cannot reach another's, and we can show it on every build.

Takeaways

1. Put the tenant boundary in the database, not only in the ORM.
2. Use FORCE ROW LEVEL SECURITY and a runtime role with NOBYPASSRLS.
3. Set the tenant with transaction-local settings so pooled connections stay clean.
4. Use composite foreign keys so references can't cross tenants.
5. Check all of it automatically, in CI, on every change.

EduByte is MIT-licensed; you can read more about the project on our open-source page.