Row-Level Security Is the Audit Log Feature You're Not Using

The thing that keeps me up at night on healthcare projects isn't a breach — it's an audit that reveals someone quietly read records they had no business reading, and my application logs are silent about it. Row-level security in PostgreSQL solves a chunk of that problem in a way that application-layer code simply cannot: it enforces access control and audit trail requirements inside the database, where a bug in your Laravel middleware can't accidentally skip it.

The Problem With Audit Logs in Application Code

I've integrated with a handful of EHR systems over the years, and every healthcare client I work with eventually hits the same HIPAA requirement: you must log who accessed what PHI, when, and from where. The typical implementation is middleware or a model observer in Laravel that fires off a write to an audit_log table whenever a patient record is touched.

That works until it doesn't. A raw query bypasses your Eloquent observers. A queue job runs under a different code path. A developer writes a quick DB::select() during an incident and forgets. The audit trail has a hole, and you don't find out until a covered entity audit or, worse, a breach investigation.

The fix isn't better code review — it's moving the enforcement down a layer, to Postgres itself, where it can't be bypassed by application code at all.

What Row-Level Security Actually Does

RLS lets you attach policies to a table that control which rows a given database role can see or modify. When a role without BYPASSRLS privilege queries the table, Postgres silently appends your policy as a WHERE clause. The query can't return rows that fail the policy — not through Eloquent, not through raw PDO, not through psql on the command line if that role is used.

For audit logging, the trick is combining RLS with a Postgres trigger. The trigger writes to your audit table unconditionally; RLS controls who can read the audit table at all. Together they give you a write path you can't skip and a read path you can lock down to compliance officers and nothing else.

Setting It Up

Here's how I structure this on a real project. I'll use a simplified patient_records table and an audit_log table.

First, the audit table and the trigger:

CREATE TABLE audit_log (
    id          bigserial PRIMARY KEY,
    table_name  text NOT NULL,
    record_id   bigint NOT NULL,
    action      text NOT NULL,  -- SELECT, INSERT, UPDATE, DELETE
    actor_role  text NOT NULL,  -- db role
    app_user_id bigint,         -- set by application via session var
    accessed_at timestamptz NOT NULL DEFAULT now(),
    row_data    jsonb
);

-- Trigger function — fires on every access to patient_records
CREATE OR REPLACE FUNCTION audit_patient_access()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, actor_role, app_user_id, row_data)
    VALUES (
        TG_TABLE_NAME,
        NEW.id,
        TG_OP,
        current_user,
        current_setting('app.current_user_id', true)::bigint,
        to_jsonb(NEW)
    );
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_audit_patient
AFTER INSERT OR UPDATE ON patient_records
FOR EACH ROW EXECUTE FUNCTION audit_patient_access();

For SELECT auditing (the harder case), I use a view with INSTEAD OF logic or an explicit function call, because standard triggers don't fire on SELECT. More on that in a moment.

Now, lock down who can read the audit log using RLS:

ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;

-- Compliance officers can read everything
CREATE POLICY audit_read_compliance
    ON audit_log FOR SELECT
    TO compliance_role
    USING (true);

-- App role can insert but never read
CREATE POLICY audit_insert_app
    ON audit_log FOR INSERT
    TO app_role
    WITH CHECK (true);

-- No SELECT policy for app_role means it sees zero rows

The app_role is what your Laravel database connection runs as. It can write audit rows but it literally cannot query them back — RLS returns an empty result set, not an error. That distinction matters: it doesn't blow up existing code, it just silently enforces the boundary.

Wiring It Into Laravel

I set the app.current_user_id session variable on every request so the audit log captures the application-layer user ID, not just the database role:

// app/Providers/AppServiceProvider.php

public function boot(): void
{
    if (app()->runningInConsole()) {
        return;
    }

    DB::listen(function () {
        // Only set once per connection
    });

    // Better: fire after auth resolves
    Event::listen(\Illuminate\Auth\Events\Authenticated::class, function ($event) {
        $userId = $event->user->id;
        DB::statement("SELECT set_config('app.current_user_id', ?, false)", [(string) $userId]);
    });
}

The false third argument to set_config means the setting is connection-scoped, not transaction-scoped — it persists for the life of the connection, which is what you want with a connection pool. If you're using PgBouncer in transaction mode, you need to reset it at the start of every transaction instead. That's a gotcha I'll come back to.

For the Eloquent side, I use a base model that wraps sensitive reads through a stored function that logs the access before returning the row:

// Usage in a controller
$record = PatientRecord::findWithAudit($patientId);

// In PatientRecord model
public static function findWithAudit(int $id): ?static
{
    // Calls a postgres function that logs the SELECT then returns the row
    $result = DB::selectOne(
        'SELECT * FROM get_patient_record_audited(?)',
        [$id]
    );

    return $result ? static::newFromBuilder((array) $result) : null;
}

The get_patient_record_audited function is a SECURITY DEFINER function owned by a superuser role, so it can write the audit row and return the patient data in one round trip. This is how you close the SELECT audit gap — wrap the read in a function that has guaranteed side effects.

The Gotchas That Will Bite You

PgBouncer in transaction mode. If your pool resets between transactions, set_config with false won't survive. You'll get NULL for app.current_user_id in your audit rows, which is useless. Either switch PgBouncer to session mode (fine for most apps, slightly lower connection concurrency) or call set_config inside a DB::transaction() wrapper that fires at the start of every operation. I've been burned by this on a Seattle biotech project where we were running transaction-mode pooling to handle burst traffic from a lab instrument feed. Audit rows had NULL user IDs for three weeks before anyone noticed.

SECURITY DEFINER is a loaded gun. Functions that run with elevated privileges need to be owned carefully. Lock down the search path explicitly:

CREATE OR REPLACE FUNCTION get_patient_record_audited(p_id bigint)
RETURNS patient_records LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, actor_role, app_user_id)
    VALUES ('patient_records', p_id, 'SELECT', current_user,
            current_setting('app.current_user_id', true)::bigint);

    RETURN QUERY SELECT * FROM patient_records WHERE id = p_id;
END;
$$;

Without SET search_path, a malicious or compromised role could shadow functions in the search path and redirect execution. It's in the Postgres docs but easy to skip.

RLS doesn't protect the table owner. If your migration user is also the table owner, it bypasses RLS entirely — owners have BYPASSRLS implicitly. Run migrations under a dedicated migration role and keep your application role separate. I keep three roles on HIPAA projects: migrator (owns tables, runs DDL), app_role (DML only, RLS enforced), compliance_role (SELECT on audit tables, read-only).

Triggers fire inside transactions. If your application rolls back a transaction, the trigger's audit insert rolls back too. This is usually fine — you don't need an audit trail for things that never committed — but be aware of it. If you need audit-on-attempt semantics (e.g., log failed access attempts), you need a separate audit pathway outside the main transaction, like a pg_notify listener or a deferred write to a separate connection.

When I'd Reach For This

I use this pattern on any project that touches PHI under HIPAA, or financial records with similar access-logging requirements. The rule of thumb: if a compliance auditor could subpoena your access logs, the logs need to be tamper-evident and bypass-proof. Application-layer logging isn't either.

I wouldn't use it for a general SaaS app with no regulatory requirements. RLS adds query planning overhead (small but real), and the SECURITY DEFINER function layer adds a maintenance surface. For a typical Laravel CRUD app, Eloquent observers on your models are plenty.

I also wouldn't use RLS as your only access control layer. It's a backstop, not a front door. Your application should still enforce permissions before a query reaches the database. Defense in depth means both layers, not just the database one.

Closing Thought

HIPAA's Technical Safeguards requirement for audit controls (§164.312(b)) doesn't specify how you implement them — it just says implement them. Putting that enforcement in the database rather than the application layer isn't clever engineering for its own sake; it's the only way to make the guarantee actually hold when your codebase has multiple entry points. Every shortcut I've seen in healthcare app logging eventually produces a gap in the audit trail. The trigger doesn't forget.