PostgreSQL Row-Level Security

Tip: Enable RLS

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Without policies, no rows are visible after enabling RLS.

Gotcha: RLS Doesn't Apply to Table Owner

The table owner bypasses RLS by default. Use FORCE ROW LEVEL SECURITY to include the owner.

Tip: Create a Policy

CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

Each tenant sees only their own rows.

Gotcha: Set Context Before Queries

SET app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';

Must be set per connection. Use middleware in Laravel.

Tip: Multiple Policies

CREATE POLICY admin_all ON orders FOR ALL TO admin USING (true);
CREATE POLICY user_own ON orders FOR SELECT TO user USING (user_id = current_user);

Different policies for different roles.

Gotcha: RLS and Performance

RLS adds a WHERE clause to every query. Index the policy columns for good performance.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

Row-Level Security (RLS) is PostgreSQL's most underused feature in Laravel applications. I've used RLS to enforce multi-tenant data isolation at the database level, providing a defense-in-depth layer beyond application-level scoping. The setup requires enabling RLS on the table and creating policy functions that check current_setting('app.current_user_id'). Laravel sets this at the start of each request via a database connection configuration. Combined with application-level policies, this creates true defense in depth.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)