PostgreSQL RLSでマルチテナント分離：4階層のデータを1人で守る【第4回】

1
2
3
4
5

-- アプリケーションが誤って全件取得を実行しても
SELECT * FROM accounts;

-- RLSにより、現在のテナントのデータしか返らない
-- (他テナントのデータは見えない)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- RLSを有効化（FORCEで所有者も対象に）
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE accounts FORCE ROW LEVEL SECURITY;

-- 1. System Admin: 全てにアクセス可能
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: 自身 + 配下のReseller/Consumer
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: 自身 + 配下のConsumer
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: 自身のみ
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
    );

1
2
3
4
5
6
7

-- 自分のPATのみ作成可能
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
    );

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

// 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
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

// 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
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

// 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 は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,
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

// backend/tests/integration/rls_isolation_test.go

// Provider A が Provider B のデータを見れないことを検証
func TestRLSIsolation_ProviderCannotAccessOtherProvider(t *testing.T) {
    pc := testutil.SetupPostgres(t)
    defer pc.TruncateTables(t)

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

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

    // Provider A として Provider B のアカウントを取得しようとする
    count := pc.CountWithTenant(t,
        providerA.Account.ID.String(), "provider",
        "SELECT COUNT(*) FROM accounts WHERE id = $1",
        providerB.Account.ID,
    )

    // RLS により見えない
    assert.Equal(t, 0, count,
        "Provider A は Provider B のアカウントを見ることができない")

    // 自身のアカウントは見える
    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 は自身のアカウントを見ることができる")
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

// CountWithTenant executes a count query with tenant context.
// RLS が正しく適用された状態でクエリを実行する。
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_config でRLSコンテキストを設定
    _, 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
}

1
2
3
4
5

-- これだけだとテーブル所有者はRLSをバイパスする
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

-- 所有者もRLS対象にする
ALTER TABLE accounts FORCE ROW LEVEL SECURITY;

1
2
3
4
5

-- 第2引数 true: 変数が未設定でも NULL を返す（エラーにならない）
current_setting('app.account_id', true)

-- 第2引数なし: 変数が未設定だとエラー
current_setting('app.account_id')

1
2
3
4
5
6
7
8
9

// ❌ トランザクション外では効かない
conn.Exec(ctx, "SET LOCAL app.account_id = '...'")
conn.Query(ctx, "SELECT * FROM accounts")

// ✅ トランザクション内で設定
tx, _ := conn.Begin(ctx)
tx.Exec(ctx, "SET LOCAL app.account_id = '...'")
tx.Query(ctx, "SELECT * FROM accounts")
tx.Commit(ctx)