PostgreSQL Row-Level Security for Multi-Tenant Payment Data

The Wake-Up Call

I've been building payment platforms for the better part of a decade, and the scariest bug I ever shipped wasn't a double-charge or a failed settlement. It was a missing WHERE tenant_id = ? clause on a reporting query. One merchant, for about forty minutes, could pull transaction summaries that included another merchant's data. Nobody noticed in production — we caught it in a staging review — but it shook me enough to rethink our entire approach to tenant isolation.

Up until that point, we relied on application-level filtering. Every query had a tenant_id condition tacked on by our ORM's default scope. It worked, mostly. But "mostly" isn't a word you want anywhere near PCI-scoped payment data. That's when I started looking seriously at PostgreSQL's Row-Level Security.

Why RLS Over Application-Level Filtering

The core argument is simple: application code is a suggestion, but a database policy is a guarantee. With application-level filtering, every developer on the team has to remember to include the tenant filter. Every new query, every raw SQL escape hatch, every ad-hoc report — all of them need that clause. One miss and you have a data leak.

RLS moves the enforcement down to the database engine itself. Even if your application code forgets the filter, PostgreSQL silently appends it. The rows simply don't exist from the perspective of the current session.

Setting Up RLS for Payment Tables

The setup is surprisingly straightforward. Let me walk through exactly what we did on our transactions table — the most sensitive table in our system.

Step 1: Enable RLS and Create the Policy

First, enable RLS on the table and create a policy that ties each row to the current session's tenant:

-- Enable row-level security on the transactions table
ALTER TABLE transactions ENABLE ROW LEVEL SECURITY;

-- Force RLS even for the table owner (important!)
ALTER TABLE transactions FORCE ROW LEVEL SECURITY;

-- Create a policy that filters rows by the session's tenant_id
CREATE POLICY tenant_isolation ON transactions
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- Same pattern for other payment tables
ALTER TABLE payment_methods ENABLE ROW LEVEL SECURITY;
ALTER TABLE payment_methods FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON payment_methods
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

The USING clause is the filter expression. PostgreSQL evaluates it for every row on every query. If it returns false, the row is invisible — not just for SELECT, but for UPDATE and DELETE too.

Step 2: Set the Tenant Context Per Request

In your application's middleware or connection pool setup, you set the session variable before any queries run:

-- At the start of each request/transaction
SET app.current_tenant = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890';

-- Now all queries on RLS-enabled tables are automatically filtered
SELECT * FROM transactions WHERE status = 'completed';
-- PostgreSQL internally adds: AND tenant_id = 'a1b2c3d4-...'

In our Go services, we wrap this in a middleware that runs at the start of every database transaction:

func SetTenantContext(ctx context.Context, db *sql.DB, tenantID string) (*sql.Tx, error) {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return nil, fmt.Errorf("begin tx: %w", err)
    }

    _, err = tx.ExecContext(ctx,
        "SET LOCAL app.current_tenant = $1", tenantID)
    if err != nil {
        tx.Rollback()
        return nil, fmt.Errorf("set tenant context: %w", err)
    }

    return tx, nil
}

Separate Policies for Reads and Writes

In practice, you often want different policies for different operations. For our payment tables, we use a permissive read policy but a more restrictive write policy that also validates the tenant on inserts:

-- Drop the simple policy and replace with specific ones
DROP POLICY tenant_isolation ON transactions;

-- Read policy: filter rows by tenant
CREATE POLICY tenant_read ON transactions
  FOR SELECT
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- Write policy: ensure new rows belong to the current tenant
CREATE POLICY tenant_write ON transactions
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

-- Update policy: can only modify own rows, can't change tenant_id
CREATE POLICY tenant_update ON transactions
  FOR UPDATE
  USING (tenant_id = current_setting('app.current_tenant')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

Performance: It's Not Free, But It's Close

The first question everyone asks: "What's the performance hit?" In our benchmarks on a table with 50 million transactions across 200 tenants, the overhead was negligible — about 2-3% on indexed queries. The key is having the right index:

-- This index is critical for RLS performance
CREATE INDEX idx_transactions_tenant_id ON transactions (tenant_id);

-- For queries that also filter by status or date, use a composite index
CREATE INDEX idx_transactions_tenant_status ON transactions (tenant_id, status);
CREATE INDEX idx_transactions_tenant_created ON transactions (tenant_id, created_at DESC);

Without the index, PostgreSQL has to do a sequential scan and evaluate the policy expression for every row. With the index, the planner pushes the tenant filter down and uses an index scan. Run EXPLAIN ANALYZE on your queries after enabling RLS — you should see the policy predicate show up as a filter condition that uses the index.

The Gotchas That Will Bite You

After running RLS in production for over two years, here are the things that caught us off guard:

Superusers Bypass RLS

By default, table owners and superusers bypass all RLS policies. This is by design — PostgreSQL assumes the owner should have full access. For payment data, this is terrifying. The fix is FORCE ROW LEVEL SECURITY:

-- Without this, the table owner sees ALL rows regardless of policy
ALTER TABLE transactions FORCE ROW LEVEL SECURITY;

Even with FORCE, actual superuser roles still bypass RLS. Our solution: the application connects as a non-superuser role, and we have a separate, tightly controlled admin role for migrations and maintenance.

pg_dump Bypasses RLS

This one is subtle. When you run pg_dump, it typically connects as a superuser, which means it dumps all rows regardless of policies. This is usually what you want for backups, but be aware of it if you're generating tenant-specific exports. For tenant-scoped dumps, we use COPY with a non-superuser connection that has the tenant context set.

Missing Session Variable = No Rows

If app.current_tenant isn't set and you query an RLS-enabled table, current_setting() throws an error by default. You can make it return an empty string with current_setting('app.current_tenant', true), but then the query silently returns zero rows. We prefer the error — it's a loud signal that something is wrong in the middleware.

Testing RLS Policies

We run a dedicated test suite that verifies tenant isolation. The pattern is straightforward: insert data for two tenants, set the context to one, and assert the other's data is invisible:

-- Test: Tenant A cannot see Tenant B's transactions
BEGIN;
  SET LOCAL app.current_tenant = 'tenant-a-uuid';
  INSERT INTO transactions (id, tenant_id, amount, status)
    VALUES (gen_random_uuid(), 'tenant-a-uuid', 100.00, 'completed');

  SET LOCAL app.current_tenant = 'tenant-b-uuid';
  INSERT INTO transactions (id, tenant_id, amount, status)
    VALUES (gen_random_uuid(), 'tenant-b-uuid', 200.00, 'completed');

  -- Switch to Tenant A and verify isolation
  SET LOCAL app.current_tenant = 'tenant-a-uuid';
  SELECT count(*) FROM transactions;
  -- Expected: 1 (only Tenant A's transaction)

  SET LOCAL app.current_tenant = 'tenant-b-uuid';
  SELECT count(*) FROM transactions;
  -- Expected: 1 (only Tenant B's transaction)
ROLLBACK;

We also test the negative cases: attempting to insert a row with a mismatched tenant_id, updating a row to change its tenant_id, and querying without setting the session variable at all. These should all fail or return empty results.

Is It Worth It?

Absolutely. RLS isn't a silver bullet — you still need application-level checks for business logic, and you still need to think carefully about your database roles and connection management. But as a defense-in-depth layer for multi-tenant payment data, it's hard to beat. The performance cost is minimal, the setup is straightforward, and the peace of mind is enormous. I sleep better knowing that even if someone on the team writes a bad query, the database itself won't let tenant data leak across boundaries.

References

• PostgreSQL Documentation — Row Security Policies
• PostgreSQL Documentation — CREATE POLICY
• PostgreSQL Documentation — SET (Session Variables)

Disclaimer: This article reflects the author's personal experience and opinions. Product names, logos, and brands are property of their respective owners. Pricing and features mentioned are subject to change — always verify with official documentation.