iTranslated by AI

The content below is an AI-generated translation. This is an experimental feature, and may contain errors. View original article

🐘

Multi-Tenant Isolation with PostgreSQL RLS: Securing 4-Tier Data as a Solo Developer (Part 4)

2026/01/15に公開

!This article is a cross-post from Saru Blog.

 What you will learn in this articleComparison of data isolation patterns in multi-tenant SaaS
Practical usage of PostgreSQL Row-Level Security (RLS)
Designing RLS policies for a 4-tier structure (System/Provider/Reseller/Consumer)
How to set RLS context using Go and pgx
Techniques for detecting RLS leaks in tests

 IntroductionAs introduced in Part 1, Saru is a multi-tenant SaaS with a 4-tier account structure.
System Admin (manages the entire SMS platform)
    └── Provider (provides services)
            ├── Reseller (sells services)
            │       └── Consumer (purchases/manages)
            └── Consumer (direct purchase)
In this structure, data isolation is extremely critical.
Provider B must not see Provider A's customer data.
Reseller B must not see Reseller A's sales results.
Consumer B must not see Consumer A's subscription information.
It is difficult to prevent this completely at the application layer. Forgetting a WHERE clause or leaking authorization checks is especially likely to happen in solo development.
Therefore, I adopted PostgreSQL Row-Level Security (RLS).

 1. Comparison of Multi-tenant Isolation PatternsThere are three main approaches to multi-tenant data isolation.

 Pattern Comparison Table

Pattern
Isolation Level
Implementation Cost
Operational Cost
Scalability


Database per Tenant
Highest (physical)
High
High
Performance isolation◎, Ops△

Schema per Tenant
High (logical)
Medium
Medium (requires automation)
Medium

Shared Schema + RLS
High (depends on design)
Low
Low
High (depends on design)


 Database per TenantEach tenant has an independent database.
┌─────────────┐  ┌─────────────┐  ┌─────────────┐
│ Provider A  │  │ Provider B  │  │ Provider C  │
│   Database  │  │   Database  │  │   Database  │
└─────────────┘  └─────────────┘  └─────────────┘
Pros: Complete isolation, easy to backup/restore per tenant
Cons: DB instances increase with the number of tenants. Hard to manage in solo development.

 Schema per TenantSeparate schemas per tenant within a single database.
┌────────────────────────────────────┐
│           Single Database          │
│  ┌──────┐  ┌──────┐  ┌──────┐     │
│  │ A.   │  │ B.   │  │ C.   │     │
│  │schema│  │schema│  │schema│     │
│  └──────┘  └──────┘  └──────┘     │
└────────────────────────────────────┘
Pros: Logical isolation, operations per tenant possible
Cons: Requires automation for schema creation and migration when adding tenants. Operations can become complex.

 Shared Schema + RLS (Saru's choice)All tenants share the same schema, and access is controlled via RLS.
┌────────────────────────────────────┐
│           Single Schema            │
│  ┌──────────────────────────────┐  │
│  │     accounts, products,      │  │
│  │     subscriptions, ...       │  │
│  │     + RLS Policies           │  │
│  └──────────────────────────────┘  │
└────────────────────────────────────┘
Pros: Simple operations, easy migrations, adding a tenant is just row insertion
Cons: Design and testing of RLS policies are critical. Recovery per tenant is difficult.

 2. Why did I choose RLS?
 The Reality of Solo Developers

Challenge
Solution via RLS


Forgetting WHERE clauses
Automatic filtering at the DB level

Leaking authorization checks
Data is invisible if policy is violated

New developers joining
Automatically isolated if policies are defined

Adding tenants
Just insert new rows


 The biggest advantage: The final line of defenseEven if there is a bug in the application code, RLS prevents data leaks.
-- Even if the application accidentally executes a full fetch
SELECT * FROM accounts;

-- RLS returns only data for the current tenant
-- (Other tenants' data is invisible)
Prerequisites:
Tenant context is set via session variables (e.g., SET app.account_id = '...')
RLS policies are defined on the target tables
Exceptions:
Superusers or roles with BYPASSRLS privileges can bypass RLS
Roles used for DB administration require separate protection
Even so, a design where "bugs in the app don't leak data" is especially reassuring for solo development.

 3. 4-Tier RLS Policy Design
 3.1 RLS Context VariablesUse PostgreSQL's SET LOCAL to configure the context for each request.


Variable Name
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 valid within a transaction. It is automatically reset when the transaction ends.

 3.2 Policies for the accounts TableDefine policies corresponding to each of the four tiers.
-- Enable RLS (FORCE applies it to the table owner as well)
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 not set (it does not cause an error)
Comparison with NULL results in NULL, which is treated as a denial in RLS
Even with FORCE ROW LEVEL SECURITY, roles with BYPASSRLS privileges can bypass RLS

 3.3 Behavioral Image of PoliciesLogged 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: ✗ Invisible
    └── Consumer B1's data: ✗ Invisible

 3.4 Controlling INSERT/UPDATE using WITH CHECKUSING is a filter for reading, but WITH CHECK is also required for writes. In Saru, I use this for the api_keys table.
-- Only own PATs can be created
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 Issue: WITH CHECK is not yet defined for the accounts table.

It is controlled by foreign key constraints and the application layer, but I am considering adding it in the future.

 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 FunctionExecute 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 Improvements: String assembly with fmt.Sprintf carries risks if values contain special characters.

Test code uses the set_config($1, $2, true) format, and I plan to improve the production code similarly.
// Safer implementation
_, err := tx.Exec(ctx,
    "SELECT set_config('app.account_id', $1, true)",
    tc.AccountID.String(),
)

 4.3 Context BuilderGenerate context based on 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 with Tests
 5.1 Integration Test Design StrategyRLS testing verifies that "things that should be visible are visible, and things that shouldn't be are not."
// backend/tests/integration/rls_isolation_test.go

// Verify that Provider A cannot see 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 retrieve 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,
    )

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

    // Self 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 HelperCountWithTenant is a helper that executes queries in a session where RLS is enabled.
// CountWithTenant executes a count query with tenant context.
// Executes the query with RLS correctly 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()

    // Configure 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 Content


Same-tier isolation
Provider A cannot see Provider B

Parent-child relationship
Provider can see subordinate Resellers/Consumers

Cross-tier
Reseller A cannot see Consumers under Reseller B

System Admin
Can access all data

Upcoming Additions: Leak tests for queries involving JOINs, verification with aggregation queries

 6. Lessons Learned from RLS Design
 6.1 FORCE ROW LEVEL SECURITY is Necessary-- By itself, the table owner bypasses RLS
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

-- Make RLS apply to the owner as well
ALTER TABLE accounts FORCE ROW LEVEL SECURITY;
If you find yourself thinking "Wait, I can see all the data" during development, check for FORCE.
Exceptions: Superusers or roles with BYPASSRLS attributes can bypass RLS even with FORCE.

 6.2 Second Argument of current_setting-- Second argument true: Returns NULL if variable is not set (no error)
current_setting('app.account_id', true)

-- No second argument: Error if variable is not set
current_setting('app.account_id')
Specify true in RLS policies to allow fallback behavior. Comparison with NULL results in NULL, which is treated as a denial in RLS.

 6.3 SET LOCAL is only for transactions// ❌ Does not work outside of a transaction
conn.Exec(ctx, "SET LOCAL app.account_id = '...'")
conn.Query(ctx, "SELECT * FROM accounts")

// ✅ Set within a transaction
tx, _ := conn.Begin(ctx)
tx.Exec(ctx, "SET LOCAL app.account_id = '...'")
tx.Query(ctx, "SELECT * FROM accounts")
tx.Commit(ctx)
SET LOCAL is automatically reset when the transaction ends.
pgx Note: Connections borrowed from the pool must be closed within the same connection between BEGIN and COMMIT.

 7. RLS Performance
 General TrendsThe overhead of RLS varies significantly based on data volume, query type, and policy complexity. Reports in public benchmarks vary, but results are highly conditional.


Source
Conditions
Reported Value


Supabase
Simple policy + indices
A few % to 15%

AntStack
No indices + massive data
10x degradation

Caution: In queries with many joins, costs can increase even with simple policies.

 Response in SaruData volume is currently low, so no full-scale benchmarking has been performed. However, I kept the following in mind during the design phase to prevent performance degradation:
Indices on RLS-targeted columns like provider_id, owner_id, etc.
Policies consist only of simple equality comparisons (no subqueries or function calls).
Casting comparison values to match the column type (UUID) (avoid casting on the column side).

 Future PlansI plan to implement the following as data grows:
Periodic checks with EXPLAIN ANALYZE
Check if RLS policies are using indices.
Monitor if query plans deteriorate in join queries.
Comparative measurements with representative queries
Simple SELECT / heavy JOIN / aggregation queries
Impact of uneven tenant data distribution
Optimization as needed
Wrapping current_setting() in (SELECT current_setting(...)) to force initPlan.
Consideration of security barrier views.

 8. Future ImprovementsThere is room for improvement in the implementation presented in this article:


Item
Status
Improvement Plan


WITH CHECK for accounts
Undefined
Add constraints for INSERT/UPDATE

SET LOCAL implementation
fmt.Sprintf
Change to set_config($1, $2, true) format

bypass_rls protection
Controlled in app layer
Restrict SET via DB role permissions

JOIN tests
Not implemented
Add leak tests for related table joins

In solo development, I adopt an approach of "making it work, then improving it" rather than aiming for perfection. With RLS as a defense layer, I can safely make improvements incrementally.

 Conclusion

Item
Implementation


Isolation Method
Shared Schema + RLS

Context Setting

SET LOCAL app.* via pgx

Policy Design
Separate policy per hierarchy

Bypass

app.bypass_rls = 'true' (System Admin only)

Testing
Verify "visible/invisible" with integration tests

RLS is very reassuring as a "last line of defense." Even if the application has bugs, data leaks can be prevented at the DB level. If you are handling complex multi-tenancy in solo development, I believe RLS is a strong choice.
Series Articles
Part 1: Tackling complexity I couldn't maintain alone with automation
Part 2: Automating WebAuthn authentication tests in CI
Part 3: Next.js × Go Monorepo structure
Part 4: PostgreSQL RLS for multi-tenant isolation (This article)

iTranslated by AI

Multi-Tenant Isolation with PostgreSQL RLS: Securing 4-Tier Data as a Solo Developer (Part 4)

What you will learn in this article

Introduction

1. Comparison of Multi-tenant Isolation Patterns

Pattern Comparison Table

Database per Tenant

Schema per Tenant

Shared Schema + RLS (Saru's choice)

2. Why did I choose RLS?

The Reality of Solo Developers

The biggest advantage: The final line of defense

3. 4-Tier RLS Policy Design

3.1 RLS Context Variables

3.2 Policies for the `accounts` Table

3.3 Behavioral Image of Policies

3.4 Controlling INSERT/UPDATE using WITH CHECK

4. Implementation with Go + pgx

4.1 Tenant Context Struct

4.2 RLS Context Setting Function

4.3 Context Builder

5. Detecting RLS Leaks with Tests

5.1 Integration Test Design Strategy

5.2 Test Helper

5.3 Test Cases to Verify

6. Lessons Learned from RLS Design

6.1 FORCE ROW LEVEL SECURITY is Necessary

6.2 Second Argument of current_setting

6.3 SET LOCAL is only for transactions

7. RLS Performance

General Trends

Response in Saru

Future Plans

8. Future Improvements

Conclusion

Discussion

Pattern	Isolation Level	Implementation Cost	Operational Cost	Scalability
Database per Tenant	Highest (physical)	High	High	Performance isolation◎, Ops△
Schema per Tenant	High (logical)	Medium	Medium (requires automation)	Medium
Shared Schema + RLS	High (depends on design)	Low	Low	High (depends on design)

Challenge	Solution via RLS
Forgetting WHERE clauses	Automatic filtering at the DB level
Leaking authorization checks	Data is invisible if policy is violated
New developers joining	Automatically isolated if policies are defined
Adding tenants	Just insert new rows

Variable Name	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`

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

Source	Conditions	Reported Value
Supabase	Simple policy + indices	A few % to 15%
AntStack	No indices + massive data	10x degradation

Item	Status	Improvement Plan
WITH CHECK for accounts	Undefined	Add constraints for INSERT/UPDATE
SET LOCAL implementation	`fmt.Sprintf`	Change to `set_config($1, $2, true)` format
bypass_rls protection	Controlled in app layer	Restrict `SET` via DB role permissions
JOIN tests	Not implemented	Add leak tests for related table joins

Item	Implementation
Isolation Method	Shared Schema + RLS
Context Setting	`SET LOCAL app.*` via pgx
Policy Design	Separate policy per hierarchy
Bypass	`app.bypass_rls = 'true'` (System Admin only)
Testing	Verify "visible/invisible" with integration tests