Bulletproof Multi-Tenancy: PostgreSQL Row-Level Security in Laravel

The Flaw in Application-Level Isolation

When architecting a B2B SaaS platform at Smart Tech Devs, data isolation is your highest priority. The standard approach in Laravel is using global scopes to automatically append a where('tenant_id', $id) clause to every database query. While Eloquent global scopes are convenient, they are applied at the application layer. If a developer accidentally uses withoutGlobalScopes(), or if a raw SQL query is executed bypassing Eloquent, Tenant A will suddenly see Tenant B's invoices. In enterprise software, this is a catastrophic data breach.

To build truly durable, sleep-well-at-night architecture, we must push multi-tenant isolation down to the absolute lowest layer: the database itself. We achieve this using PostgreSQL Row-Level Security (RLS).

What is Row-Level Security?

PostgreSQL RLS acts as an invisible bouncer at the database level. Once enabled on a table, you define strict SQL policies that dictate which rows are visible or modifiable based on the current database session context. If the database engine itself rejects the query based on the tenant ID, no amount of application-level bugs or bypassed scopes can leak the data.

Step 1: Enabling RLS in a Laravel Migration

We use raw SQL statements in our migrations to enable RLS and define our strict isolation policy. Let's secure our invoices table.

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

class CreateInvoicesTable extends Migration
{
    public function up(): void
    {
        Schema::create('invoices', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained();
            $table->decimal('amount', 10, 2);
            $table->timestamps();
        });

        // 1. Enable Row-Level Security on the table
        DB::statement('ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;');

        // 2. Force the policy even for table owners (crucial for safety)
        DB::statement('ALTER TABLE invoices FORCE ROW LEVEL SECURITY;');

        // 3. Create the Policy: A row is only visible if its tenant_id matches
        // the custom configuration variable 'app.current_tenant_id' set in the session.
        DB::statement("
            CREATE POLICY tenant_isolation_policy ON invoices
            USING (tenant_id = current_setting('app.current_tenant_id', true)::bigint);
        ");
    }

    public function down(): void
    {
        DB::statement('DROP POLICY IF EXISTS tenant_isolation_policy ON invoices;');
        Schema::dropIfExists('invoices');
    }
}

Step 2: Setting the Database Context in Middleware

Because the database policy requires app.current_tenant_id to evaluate the query, we must inject this variable into the PostgreSQL session at the very beginning of every HTTP request. We do this cleanly via a Laravel Middleware.

namespace App\Http\Middleware;

use Closure;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class SetPostgresTenantContext
{
    public function handle(Request $request, Closure $next)
    {
        // Assume tenant is resolved via domain or auth user
        $tenantId = $request->user()?->tenant_id;

        if ($tenantId) {
            // Inject the tenant ID directly into the PostgreSQL session
            DB::statement("SET app.current_tenant_id = '{$tenantId}'");
        } else {
            // For public routes, ensure it is completely nullified
            DB::statement("SET app.current_tenant_id = ''");
        }

        return $next($request);
    }
}

The Engineering ROI

By migrating your tenant scoping from PHP down to PostgreSQL, you achieve architectural invincibility regarding data leakage. Even if a junior developer writes DB::select('SELECT * FROM invoices'), PostgreSQL intercepts the request, checks the session's app.current_tenant_id, and returns only that specific tenant's records. It is the ultimate fail-safe for high-stakes B2B SaaS environments.