Tenant Isolation with Postgres Row-Level Security in Go

Multi-tenant security should not depend on your Go code. One missing if, and a client sees another client's data. Postgres can hold the wall for you, with Row-Level Security. You enable one policy per table, set the tenant on every connection, and the database filters on its own. Here is how I wired it in Go with pgx, the two traps that cost me time, and how I prove it with tests.

Who this is for: Go developers building a multi-tenant SaaS on Postgres who want isolation that holds, even when the code has a bug.

The setup

A tenant is a customer of your application. In a multi-tenant SaaS, many customers share one database. Their data lives in the same tables, split by a tenant_id column.

The risk is simple. If one query forgets its WHERE tenant_id = ... filter, a customer reads another customer's rows. In healthcare, finance, or with personal data, that is a serious leak.

I built this pattern on my own product, a rental-management tool with an AI legal advisor. I touched on RLS in my article on securing LLM agents. This is the full version.

RLS in one minute

Row-Level Security, or RLS, is a Postgres feature. It decides, row by row, what a query may read and write. You turn it on per table, with a policy.

A policy has two parts. USING filters the rows you read. WITH CHECK blocks writes that break the rule. Here is the conversations table with tenant isolation:

ALTER TABLE conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE conversations FORCE  ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON conversations
    FOR ALL
    USING      (tenant_id = current_setting('app.current_tenant_id')::uuid)
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

current_setting('app.current_tenant_id') reads a session variable. Your code fills it with the user's tenant. Then Postgres compares every row to that value. A query sees only its rows. The others do not exist for it.

FORCE ROW LEVEL SECURITY adds one guarantee. Without it, the table owner skips RLS. With it, even the owner obeys the policy.

None of this SQL is specific to Go. The policies and the session variable are the same whether your backend is Python, Node, Rails, or Java. What changes from one language to the next is how you set the tenant on each request. And that is where connection pooling gets tricky. I show that part in Go, with pgx.

Two Postgres roles, not one

RLS is useless if your application can turn it off. So I create two Postgres roles. A role is a database user.

The first, app_role, runs every user query. It is subject to RLS.

The second, system_role, has the BYPASSRLS attribute. It ignores RLS. I keep it for migrations, admin tasks, and the rare queries that must see several tenants.

CREATE ROLE app_role    WITH LOGIN PASSWORD '...';
CREATE ROLE system_role WITH LOGIN PASSWORD '...' BYPASSRLS;

The rule is simple. Code that answers users always goes through app_role. system_role stays for internal work, never for a user-triggered query. Two separate connection pools, one per role.

Set the tenant on every connection

The app.current_tenant_id variable must be set before every user query. Setting it by hand in each function repeats the missing-if problem.

So I do it once, in the connection pool. pgx is the Postgres driver for Go. Its pool can run a function on each connection before it hands it to you.

poolCfg.PrepareConn = func(ctx context.Context, conn *pgx.Conn) (bool, error) {
    tenantID := middleware.GetTenantID(ctx)
    if tenantID == "" {
        return true, nil
    }
    _, err := conn.Exec(ctx,
        "SELECT set_config('app.current_tenant_id', $1, false)", tenantID)
    if err != nil {
        return false, err
    }
    return true, nil
}

poolCfg.AfterRelease = func(conn *pgx.Conn) bool {
    _, _ = conn.Exec(context.Background(), "RESET app.current_tenant_id")
    return true
}

PrepareConn runs when the pool takes a connection out. It reads the tenant from the request context, then fills the variable. In older pgx versions, the hook is named BeforeAcquire.

AfterRelease runs when the connection comes back. It calls RESET. This detail is vital. A connection is reused by other requests. Without RESET, it keeps the last user's tenant. The next request would inherit it. That would be a leak.

The tenant comes from the context. I put it there from the user's JWT. A JWT is a signed token that proves who the user is. A middleware checks the token, reads the tenant, and stores it in the context.

First trap: RLS fails silently

RLS raises no error when it blocks. It makes rows invisible. That is the intended behavior, but it surprises people.

An example. Tenant B tries to update a tenant A row. RLS does not refuse. The UPDATE touches zero rows. No error. Your Go code thinks the update worked.

Same for a cross-tenant DELETE: zero rows, no error. And reading another tenant's row returns "nothing found", not "forbidden".

Two consequences for your code. One, never read a missing error as success on a write. Check the affected-row count when an operation must change something. Two, treat "zero rows" as a clean "not found", not as a bug.

Second trap: forgetting the tenant

What happens if the variable is never set? The policy cannot evaluate. The query fails.

That is a good default. No tenant, no data. RLS fails closed, never open. A missing variable blocks everything instead of showing everything.

I double this guarantee in the application. A middleware rejects any request without a tenant, with a 403. The database is the last wall. The middleware is the first.

Infinite recursion, error 42P17

Here is the bug that cost me the most time. It happens when two tables protect each other.

My conversations table had a policy that looked at conversation_participants. And conversation_participants had a policy that looked at conversations. Each policy triggered the other. Postgres looped, then returned SQLSTATE 42P17: infinite recursion in a policy.

The fix is two words: SECURITY DEFINER. I move the access logic into a function. The function checks access once, and runs with its creator's rights. So it ignores RLS inside. No more loop.

CREATE FUNCTION user_can_access_conversation(conv_id uuid, uid uuid)
RETURNS boolean AS $$
    SELECT EXISTS (
        SELECT 1 FROM conversations
        WHERE id = conv_id AND owner_id = uid
    ) OR EXISTS (
        SELECT 1 FROM conversation_participants
        WHERE conversation_id = conv_id AND user_id = uid
    );
$$ LANGUAGE sql SECURITY DEFINER STABLE;

CREATE POLICY conversations_access ON conversations
    USING (user_can_access_conversation(id, current_setting('app.current_tenant_id')::uuid));

A rule was born from that bug. A policy must not query another table whose own policy points back to the first. If you need that cross-logic, route it through a SECURITY DEFINER function.

Prove isolation with tests

RLS is a security rule. An untested security rule does not exist. So I write integration tests. They start a real Postgres in a container, with the real policies.

The test sets tenant A, then tries to read tenant B's data. It must get zero rows. It tries a cross-tenant write. It must be blocked.

// Tenant A sees only its own rows.
require.NoError(t, db.SetTenant(ctx, tenantA))
repo := NewConversationRepo(db.TenantPool)

got, err := repo.List(ctx)
require.NoError(t, err)
require.Len(t, got, 1)            // only tenant A rows

// Tenant A cannot read a tenant B conversation.
_, err = repo.Get(ctx, tenantBConversationID)
require.Error(t, err)            // not found

// A write with the wrong tenant is blocked by WITH CHECK.
_, err = repo.Create(ctx, &Conversation{TenantID: tenantB})
require.Error(t, err)

I test all four operations: read, create, update, delete. And I check at the raw SQL level, with a count(*), that each tenant sees only its rows. These tests run on every commit, in CI.

The checklist before production

Before you ship a multi-tenant SaaS on Postgres, check every box.

• [ ] Every multi-tenant table has ENABLE and FORCE ROW LEVEL SECURITY
• [ ] Every policy has a USING for reads and a WITH CHECK for writes
• [ ] Two roles: an app role under RLS, a system role with BYPASSRLS
• [ ] User-facing code always goes through the app role
• [ ] The tenant is set in the pool hook, and reset when the connection returns
• [ ] The tenant comes from the JWT, never from a request parameter
• [ ] A middleware rejects any request with no tenant, with a 403
• [ ] Your code treats "zero rows" as a not-found, not as a successful write
• [ ] No policy queries another table whose policy points back to it
• [ ] Cross-table access logic goes through a SECURITY DEFINER function
• [ ] Integration tests prove isolation for reads and writes

What to remember

Filtering by tenant in Go code works, until the day an if is missing. RLS moves the wall into the database. There, no code bug can get around it.

The cost is two roles, one pool hook, and a few traps to know: the silent failure, the forgotten variable, the 42P17 recursion. Once past them, you have isolation that holds on its own.

Building a multi-tenant SaaS in Go and want a second pair of eyes on your isolation? That is exactly what I do. Write to me.

---

Originally published at jrobineau.com.

I'm Jules Robineau, a senior Go backend and DevSecOps freelancer based in Paris. I build and harden production AI/backend systems at scale (25M+ users). CompTIA PenTest+, Top 1% TryHackMe. Services · GitHub · LinkedIn

Sources: PostgreSQL: Row Security Policies, PostgreSQL: Error Codes (42P17), pgx: pgxpool, Crunchy Data: RLS for Tenants.