Row-Level Security in PostGraphile: Powerful, Brittle, and Misunderstood

PostGraphile's row-level security integration is one of the most elegant things I've seen in the GraphQL ecosystem — and one of the easiest ways to accidentally expose data you thought you'd locked down, or hide data you thought was visible. After using it on three production projects, I have opinions.

What Problem This Actually Solves

The standard approach to authorization in a GraphQL API is middleware: you check permissions in your resolver before you touch the database. It works, but it scatters your access rules across application code. Every resolver is a potential gap. Every new query someone adds to the schema needs someone to remember to add the check.

PostgreSQL's row-level security (RLS) moves those rules into the database itself. You define policies on tables — "a user can only see rows where tenant_id matches their session setting" — and the database enforces them unconditionally. Doesn't matter how you query. The database is the authority.

PostGraphile leans into this hard. Instead of building a permission layer in the application, you configure Postgres roles, set session-local variables via pgSettings, and let the database handle authorization. The theory is sound: your rules live in one place, they're enforced at the storage layer, and your GraphQL schema becomes a thin translation layer.

I bought in on this architecture for a healthcare-adjacent project — a biotech client managing study participant records — and it mostly worked beautifully. "Mostly" is doing a lot of work in that sentence.

A Working Setup

Here's the actual pattern I use. You need three moving parts: a Postgres role, RLS policies on your tables, and a pgSettings function in PostGraphile's config.

First, the database side:

-- Create a role that PostGraphile connects as
CREATE ROLE app_user NOLOGIN;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Enable RLS on the table
ALTER TABLE study_participants ENABLE ROW LEVEL SECURITY;
ALTER TABLE study_participants FORCE ROW LEVEL SECURITY;

-- Policy: users see only their own tenant's records
CREATE POLICY tenant_isolation ON study_participants
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Policy: admins see everything
CREATE POLICY admin_bypass ON study_participants
  USING (current_setting('app.current_role') = 'admin');

Then the PostGraphile config, inside a Laravel app using the postgraphile Node process via a sidecar (yes, we run PostGraphile alongside Laravel; I'll write about that setup separately):

const { postgraphile } = require('postgraphile');

app.use(
  postgraphile(process.env.DATABASE_URL, 'public', {
    pgSettings: async (req) => {
      // req.user is set by your auth middleware
      const user = req.user;
      if (!user) {
        return {
          role: 'app_anonymous',
          'app.current_tenant_id': null,
          'app.current_role': 'anonymous',
        };
      }
      return {
        role: 'app_user',
        'app.current_tenant_id': user.tenantId,
        'app.current_role': user.role,
      };
    },
    enableQueryBatching: true,
    dynamicJson: true,
    graphiql: process.env.NODE_ENV !== 'production',
  })
);

For the Laravel side that validates JWTs and passes the user context into the PostGraphile request:

// In a Laravel controller acting as a proxy to PostGraphile
public function graphql(Request $request): JsonResponse
{
    $user = auth()->user();

    $response = Http::withHeaders([
        'X-User-Id'    => $user?->id,
        'X-Tenant-Id'  => $user?->tenant_id,
        'X-User-Role'  => $user?->role ?? 'anonymous',
        'Content-Type' => 'application/json',
    ])->post(config('services.postgraphile.url'), $request->json()->all());

    return response()->json($response->json(), $response->status());
}

That header-passing approach means PostGraphile's pgSettings callback reads from trusted internal headers, not from raw user input. PostGraphile sits behind Laravel, not directly on the internet. This is load-bearing for the security model.

The Gotchas That Will Bite You

Silent empty sets are the biggest footgun.

When RLS blocks access, Postgres doesn't throw an error for SELECT. It returns zero rows. If your policy is wrong — wrong variable name, wrong type cast, misconfigured role — your GraphQL query returns [] and your client has no idea whether that's "no data" or "you're not allowed to see this data." I spent an afternoon on a bug where current_setting('app.current_tenant_id') was returning an empty string because I'd forgotten to set the variable for unauthenticated requests, and the UUID cast was silently failing in a way that matched nothing.

For writes, it's different — a blocked INSERT or UPDATE raises an error. But reads fail silently. This is a Postgres design decision, not a PostGraphile bug, but PostGraphile doesn't do anything to surface it either.

My fix: add a diagnostic query to your dev workflow. After any policy change:

SET ROLE app_user;
SET app.current_tenant_id = '00000000-0000-0000-0000-000000000000';
SET app.current_role = 'user';

SELECT COUNT(*) FROM study_participants;
-- If this returns 0 and you expect rows, something is wrong.

FORCE ROW LEVEL SECURITY matters more than you think.

By default, the table owner bypasses RLS. If PostGraphile connects as the table owner — which is common in development setups where you just use your superuser connection string — your policies are completely ignored. Everything looks fine in dev, everything is broken in production when you switch roles. Add FORCE ROW LEVEL SECURITY to every table and save yourself the discovery.

current_setting() throws if the variable isn't set.

The two-argument form current_setting('app.current_tenant_id', true) returns null instead of throwing when the variable is missing. Use it. Every policy should use the safe form:

CREATE POLICY tenant_isolation ON study_participants
  USING (
    tenant_id = current_setting('app.current_tenant_id', true)::uuid
  );

Except now you have a different problem: if the variable is null, the UUID cast fails. You need to handle that:

CREATE POLICY tenant_isolation ON study_participants
  USING (
    current_setting('app.current_tenant_id', true) IS NOT NULL
    AND tenant_id = current_setting('app.current_tenant_id', true)::uuid
  );

Verbose, but correct.

Joins don't always respect RLS the way you expect.

If you have a view or a function that joins two RLS-protected tables, the policies apply based on the security context of the view/function, not necessarily what you'd intuit. Views created with SECURITY DEFINER run as the view owner and bypass RLS. Views created with SECURITY INVOKER (or just plain views) run as the calling role and respect RLS. PostGraphile's smart comments and smart tags can create views behind the scenes — know which mode you're in.

Connection pooling and session settings are in tension.

PostGraphile supports PgBouncer in transaction-mode pooling, but SET commands (which is how pgSettings works) are session-scoped in Postgres. In transaction mode, your settings don't persist across queries in the way you might expect, and you need to use SET LOCAL or the pgSettings function, which PostGraphile handles correctly — but only if you configure it right. I got burned on this with PgBouncer in statement mode, which doesn't work with RLS at all. Use transaction mode or session mode with PgBouncer. Statement mode is a no-go.

When I'd Reach For This

I use this architecture when the data model has a strong, consistent multi-tenancy axis — every table has a tenant_id, every query should be scoped to it, and there's no legitimate reason for one tenant to ever see another's data. That describes most of the SaaS-style apps I build. RLS-in-PostGraphile is genuinely great for this because the enforcement is absolute and doesn't depend on any application developer remembering to add a where clause.

I'd also use it when the client has compliance requirements that benefit from database-level audit trails and access controls. Telling an auditor "access is enforced by the database engine itself, not application code" is a defensible position.

I would not use it for complex, fine-grained permission systems — row-and-column-level permissions that vary by user attribute, role hierarchy, resource ownership, and time of day. RLS policies in SQL get unwieldy fast. When the policies get complex enough that I'm writing PL/pgSQL functions inside USING clauses, I start questioning the architecture. At that point, a proper authorization layer in the application — something like Oso or a hand-rolled policy engine — is easier to reason about, test, and audit.

I also wouldn't use PostGraphile's RLS integration as my only security layer. It's a strong layer, but pgSettings relies on your application correctly setting those variables. If your auth middleware has a bug, or a code path bypasses it, the database gets wrong values and returns wrong data. Defense in depth means you also validate at the API layer, not just the database layer.

Closing Take

PostGraphile plus RLS is the right architecture for the right problem — genuinely pushing authorization concerns to where they belong. But it requires you to actually understand PostgreSQL's security model, not just copy-paste policies from a blog post (including this one). The silent-empty-set failure mode alone has cost me more debugging hours than I'd like to admit. Go in with eyes open, test your policies explicitly, and don't let the elegance of the theory make you complacent about validating the implementation.