ko-chan

Posted on Jan 15 • Originally published at ko-chan.github.io

PostgreSQL RLS for Multi-Tenant Isolation: Protecting 4-Tier Data as a Solo Developer [Part 4]

#postgres #rls #multitenant #go

This article was originally published on Saru Blog.

What You'll Learn

Comparison of data isolation patterns for multi-tenant SaaS
Practical usage of PostgreSQL Row-Level Security (RLS)
RLS policy design for 4-tier hierarchy (System/Provider/Reseller/Consumer)
Setting RLS context with Go + pgx
Detecting RLS leaks through testing

Introduction

As introduced in Part 1, Saru is a multi-tenant SaaS with a 4-tier account structure.

System Admin (manages the entire SMS platform)
    └── Provider (offers services)
            ├── Reseller (sells services)
            │       └── Consumer (purchases/manages)
            └── Consumer (direct sales)

In this structure, data isolation is critical.

Provider A's customer data must not be visible to Provider B
Reseller A's sales records must not be visible to Reseller B
Consumer A's subscription info must not be visible to Consumer B

Preventing this entirely at the application layer is difficult. Forgotten WHERE clauses and missing permission checks are especially common in solo development.

That's why I adopted PostgreSQL Row-Level Security (RLS).

1. Comparing Multi-Tenant Isolation Patterns

There are three main approaches to multi-tenant data isolation.

Pattern Comparison

Pattern	Isolation Level	Implementation Cost	Operational Cost	Scalability
Database per Tenant	Highest (physical)	High	High	Performance isolation excellent, operations challenging
Schema per Tenant	High (logical)	Medium	Medium (automation required)	Medium
Shared Schema + RLS	High (design-dependent)	Low	Low	High (design-dependent)

Database per Tenant

Each tenant has an independent database.

┌─────────────┐  ┌─────────────┐  ┌─────────────┐
│ Provider A  │  │ Provider B  │  │ Provider C  │
│   Database  │  │   Database  │  │   Database  │
└─────────────┘  └─────────────┘  └─────────────┘

Pros: Complete isolation, easy per-tenant backup/restore

Cons: DB instances grow with tenant count. Unmanageable for solo development.

Schema per Tenant

Separate schemas for each tenant within a single database.

┌────────────────────────────────────┐
│           Single Database          │
│  ┌──────┐  ┌──────┐  ┌──────┐     │
│  │ A.   │  │ B.   │  │ C.   │     │
│  │schema│  │schema│  │schema│     │
│  └──────┘  └──────┘  └──────┘     │
└────────────────────────────────────┘

Pros: Logical isolation, per-tenant operations possible

Cons: Schema creation and migration automation required for new tenants. Operations can get complex.

Shared Schema + RLS (Saru's Choice)

All tenants share the same schema, with RLS controlling access.

┌────────────────────────────────────┐
│           Single Schema            │
│  ┌──────────────────────────────┐  │
│  │     accounts, products,      │  │
│  │     subscriptions, ...       │  │
│  │     + RLS Policies           │  │
│  └──────────────────────────────┘  │
└────────────────────────────────────┘

Pros: Simple operations, easy migrations, adding tenants is just inserting rows

Cons: RLS policy design and testing are critical. Per-tenant recovery is difficult.

2. Why I Chose RLS

The Reality of Solo Development

Challenge	RLS Solution
Forgotten WHERE clauses	Automatic filtering at DB level
Missing permission checks	Policy violations = invisible data
Onboarding new developers	Policies ensure automatic isolation
Adding tenants	Just insert a new row

The Greatest Benefit: Last Line of Defense

Even if there are bugs in application code, RLS prevents data leaks.

-- Even if the app accidentally fetches all rows
SELECT * FROM accounts;

-- RLS returns only current tenant's data
-- (other tenants' data is invisible)

Prerequisites:

Tenant context set via session variables (e.g., SET app.account_id = '...')
RLS policies defined on target tables

Exceptions:

Superusers and roles with BYPASSRLS privilege can bypass RLS
DB admin roles need separate protection

Still, a design where "app bugs don't cause leaks" is reassuring for solo development.

3. RLS Policy Design for 4-Tier Hierarchy

3.1 RLS Context Variables

Use PostgreSQL's SET LOCAL to set context per request.

Variable	Description	Example
`app.account_id`	Current account ID	`550e8400-e29b-...`
`app.account_type`	Account type	`provider`, `consumer`
`app.bypass_rls`	RLS bypass flag (System only)	`true`, `false`

Important: SET LOCAL is only effective within a transaction. It automatically resets when the transaction ends.

3.2 Policies for the accounts Table

Define policies for each of the 4 tiers.

-- Enable RLS (FORCE applies to owner too)
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE accounts FORCE ROW LEVEL SECURITY;

-- 1. System Admin: Access to everything
CREATE POLICY system_admin_all_accounts ON accounts
    FOR ALL
    USING (
        current_setting('app.bypass_rls', true) = 'true'
        OR current_setting('app.account_type', true) = 'system'
    );

-- 2. Provider: Self + subordinate Resellers/Consumers
CREATE POLICY provider_accounts ON accounts
    FOR ALL
    USING (
        current_setting('app.account_type', true) = 'provider'
        AND (
            id = current_setting('app.account_id', true)::UUID
            OR provider_id = current_setting('app.account_id', true)::UUID
        )
    );

-- 3. Reseller: Self + subordinate Consumers
CREATE POLICY reseller_accounts ON accounts
    FOR ALL
    USING (
        current_setting('app.account_type', true) = 'reseller'
        AND (
            id = current_setting('app.account_id', true)::UUID
            OR reseller_id = current_setting('app.account_id', true)::UUID
        )
    );

-- 4. Consumer: Self only
CREATE POLICY consumer_accounts ON accounts
    FOR ALL
    USING (
        current_setting('app.account_type', true) = 'consumer'
        AND id = current_setting('app.account_id', true)::UUID
    );

Notes:

current_setting(..., true) returns NULL when unset (no error)
Comparing with NULL yields NULL, which RLS treats as denied
Roles with BYPASSRLS privilege can bypass even with FORCE ROW LEVEL SECURITY

3.3 Policy Behavior Visualization

Logged in as Provider A (app.account_type = 'provider', app.account_id = A)
    ├── Provider A's data: ✓ Visible
    ├── Reseller A1's data: ✓ Visible (provider_id = A)
    ├── Consumer A1's data: ✓ Visible (provider_id = A)
    ├── Provider B's data: ✗ Not visible
    └── Consumer B1's data: ✗ Not visible

3.4 INSERT/UPDATE Control with WITH CHECK

USING filters reads, but WITH CHECK is needed for writes.
Saru uses this on the api_keys table.

-- Can only create own PATs
CREATE POLICY api_keys_user_insert ON api_keys
    FOR INSERT
    WITH CHECK (
        tenant_id = current_setting('app.account_id', true)::UUID
        AND user_id = current_setting('app.user_id', true)::UUID
    );

Current limitation: WITH CHECK is not defined on the accounts table.
Currently controlled by foreign key constraints and application layer, but considering adding it.

4. Implementation with Go + pgx

4.1 Tenant Context Struct

// backend/internal/domain/tenant/context.go

type AccountType string

const (
    AccountTypeSystem   AccountType = "system"
    AccountTypeProvider AccountType = "provider"
    AccountTypeReseller AccountType = "reseller"
    AccountTypeConsumer AccountType = "consumer"
)

// Context holds tenant-specific information for the current request.
type Context struct {
    AccountID   uuid.UUID
    AccountType AccountType
    UserID      *uuid.UUID
    ProviderID  *uuid.UUID
    BypassRLS   bool
    Scopes      []string
}

4.2 RLS Context Setting Function

Execute SET LOCAL within a transaction.

// backend/internal/infrastructure/postgres/tenant_context.go

// SetTenantContextTx sets the RLS context variables on a transaction.
func SetTenantContextTx(ctx context.Context, tx pgx.Tx, tc *tenant.Context) error {
    // Set account_id for RLS policies
    if _, err := tx.Exec(ctx,
        fmt.Sprintf("SET LOCAL app.account_id = '%s'", tc.AccountID.String()),
    ); err != nil {
        return fmt.Errorf("failed to set app.account_id: %w", err)
    }

    // Set account_type for RLS policies
    if _, err := tx.Exec(ctx,
        fmt.Sprintf("SET LOCAL app.account_type = '%s'", tc.AccountType),
    ); err != nil {
        return fmt.Errorf("failed to set app.account_type: %w", err)
    }

    // Set bypass_rls flag (only for system admin)
    bypassRLS := "false"
    if tc.BypassRLS {
        bypassRLS = "true"
    }
    if _, err := tx.Exec(ctx,
        fmt.Sprintf("SET LOCAL app.bypass_rls = '%s'", bypassRLS),
    ); err != nil {
        return fmt.Errorf("failed to set app.bypass_rls: %w", err)
    }

    return nil
}

Future improvement: String concatenation with fmt.Sprintf poses risks if values contain special characters.
Test code uses set_config($1, $2, true) format, and production code should be updated similarly.
// Safer implementation
_, err := tx.Exec(ctx,
    "SELECT set_config('app.account_id', $1, true)",
    tc.AccountID.String(),
)

4.3 Context Builders

Generate context for each account type.

// BuildSystemContext builds a tenant context for system admin operations.
func BuildSystemContext(userID *uuid.UUID) *tenant.Context {
    return &tenant.Context{
        AccountID:   uuid.Nil,
        AccountType: tenant.AccountTypeSystem,
        UserID:      userID,
        BypassRLS:   true,  // System Admin bypasses RLS
        Scopes:      []string{"*:*"},
    }
}

// BuildProviderContext builds a tenant context for provider operations.
func BuildProviderContext(accountID uuid.UUID, userID *uuid.UUID, scopes []string) *tenant.Context {
    return &tenant.Context{
        AccountID:   accountID,
        AccountType: tenant.AccountTypeProvider,
        UserID:      userID,
        ProviderID:  &accountID,
        BypassRLS:   false,
        Scopes:      scopes,
    }
}

5. Detecting RLS Leaks Through Testing

5.1 Integration Test Design Philosophy

RLS tests verify "what should be visible is visible, what shouldn't is not".

// backend/tests/integration/rls_isolation_test.go

// Verify Provider A cannot access Provider B's data
func TestRLSIsolation_ProviderCannotAccessOtherProvider(t *testing.T) {
    pc := testutil.SetupPostgres(t)
    defer pc.TruncateTables(t)

    // Create Provider A
    providerA := testutil.CreateProviderAccount(t, pc, "ProviderA")

    // Create Provider B
    providerB := testutil.CreateProviderAccount(t, pc, "ProviderB")

    // Try to access Provider B's account as Provider A
    count := pc.CountWithTenant(t,
        providerA.Account.ID.String(), "provider",
        "SELECT COUNT(*) FROM accounts WHERE id = $1",
        providerB.Account.ID,
    )

    // Not visible due to RLS
    assert.Equal(t, 0, count,
        "Provider A cannot see Provider B's account")

    // Own account is visible
    count = pc.CountWithTenant(t,
        providerA.Account.ID.String(), "provider",
        "SELECT COUNT(*) FROM accounts WHERE id = $1",
        providerA.Account.ID,
    )
    assert.Equal(t, 1, count,
        "Provider A can see their own account")
}

5.2 Test Helper

CountWithTenant executes queries in an RLS-enabled session.

// CountWithTenant executes a count query with tenant context.
// Runs queries with RLS properly applied.
func (pc *PostgresContainer) CountWithTenant(
    t *testing.T,
    accountID, accountType, query string,
    args ...interface{},
) int {
    ctx := context.Background()
    conn, err := pc.Pool.Acquire(ctx)
    require.NoError(t, err)
    defer conn.Release()

    // Set RLS context with set_config
    _, err = conn.Exec(ctx, `
        SELECT set_config('app.account_id', $1, false),
               set_config('app.account_type', $2, false)
    `, accountID, accountType)
    require.NoError(t, err)

    var count int
    err = conn.QueryRow(ctx, query, args...).Scan(&count)
    require.NoError(t, err)

    return count
}

5.3 Test Cases to Verify

Test Case	Verification
Same-tier isolation	Provider A cannot see Provider B
Parent-child relationship	Provider can see subordinate Resellers/Consumers
Cross-hierarchy	Reseller A cannot see Reseller B's Consumers
System Admin	Can access all data

Future additions: Leak tests for JOIN queries, verification with aggregate queries

6. Lessons Learned from RLS Design

6.1 FORCE ROW LEVEL SECURITY is Required

-- This alone lets table owner bypass RLS
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

-- Include owner in RLS enforcement
ALTER TABLE accounts FORCE ROW LEVEL SECURITY;

If you see all data during development, check for FORCE.

Exception: Superusers and roles with BYPASSRLS attribute can bypass even with FORCE.

6.2 The Second Argument of current_setting

-- Second argument true: returns NULL when unset (no error)
current_setting('app.account_id', true)

-- Without second argument: errors when unset
current_setting('app.account_id')

Use true in RLS policies for fallback behavior when unset.
Comparing with NULL yields NULL, which RLS treats as denied.

6.3 SET LOCAL Only Works Within Transactions

// ❌ Doesn't work outside transaction
conn.Exec(ctx, "SET LOCAL app.account_id = '...'")
conn.Query(ctx, "SELECT * FROM accounts")

// ✅ Set within transaction
tx, _ := conn.Begin(ctx)
tx.Exec(ctx, "SET LOCAL app.account_id = '...'")
tx.Query(ctx, "SELECT * FROM accounts")
tx.Commit(ctx)

SET LOCAL automatically resets when the transaction ends.

pgx note: Connections borrowed from a pool must stay within BEGIN~COMMIT on the same connection.

7. RLS Performance

General Trends

RLS overhead varies significantly based on data volume, query patterns, and policy complexity.
External benchmarks report the following, though results vary by conditions:

Source	Conditions	Reported Value
Supabase	Simple policies + indexed	~5-15%
AntStack	No index, large data	10x+ degradation

Note: Queries with many JOINs can increase costs even with simple policies.

Saru's Approach

Data volume is currently small, so formal benchmarking hasn't been done.
However, these considerations were made during design to prevent degradation:

Indexes on RLS-target columns like provider_id, owner_id
Policies use only simple equality comparisons (no subqueries or function calls)
Cast comparison values to match column type (UUID), not the column itself

Future Plans

As data grows, the following will be implemented:

Regular checks with EXPLAIN ANALYZE
- Verify RLS policies are using indexes
- Check for plan degradation in JOIN queries
Comparative measurements on representative queries
- Simple SELECT / JOIN-heavy / Aggregate queries
- Impact when row counts are skewed by tenant
Optimization as needed
- Wrap current_setting() in (SELECT current_setting(...)) for initPlan
- Consider security barrier views

8. Future Improvements

The implementation described has room for improvement:

Item	Current State	Improvement
WITH CHECK on accounts	Not defined	Add constraints for INSERT/UPDATE
SET LOCAL implementation	`fmt.Sprintf`	Change to `set_config($1, $2, true)` format
bypass_rls protection	App-layer control	Restrict with DB role permissions for SET
JOIN tests	Not implemented	Add leak tests for related table joins

In solo development, I take the approach of "build something that works, then improve" rather than aiming for perfection.
Having RLS as a defense layer allows for confident incremental improvements.

Summary

Item	Implementation
Isolation method	Shared Schema + RLS
Context setting	`SET LOCAL app.*` via pgx
Policy design	Separate policies per tier
Bypass	`app.bypass_rls = 'true'` (System Admin only)
Testing	Integration tests verify "visible/not visible"

RLS is incredibly reassuring as a "last line of defense." Even if the app has bugs, the DB prevents data leaks.

For complex multi-tenant systems in solo development, RLS is a strong choice.

Series Articles

Part 1: Tackling Unmaintainable Complexity with Automation
Part 2: Automated WebAuthn Testing in CI
Part 3: Next.js × Go Monorepo Architecture
Part 4: PostgreSQL RLS for Multi-Tenant Isolation (This article)

DEV Community