ums

CP-04: Multi-Tenant Repository with Row-Level Security

Field	Value
Pattern ID	CP-04
Type	Data Access / Security
ADR Reference	ADR-0010: Multi-Tenancy RLS Strategy
Language	TypeScript / NestJS / TypeORM + PostgreSQL
Last Review	2026-05-15	—

Intent

Enforce tenant data isolation at thedatabase levelusing Row-Level Security (RLS), preventing any query — regardless of ORM or direct SQL — from returning rows belonging to a different tenant. The application sets the tenant context once per request; the DB enforces isolation automatically.

---

PostgreSQL RLS Setup

-- Enable RLS on tenant-scoped tables
ALTER TABLE ums.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE ums.roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE ums.permissions ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their own tenant's rows
CREATE POLICY tenant_isolation ON ums.users
 USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation ON ums.roles
 USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- Service account must NOT be superuser (superusers bypass RLS)
-- GRANT SELECT, INSERT, UPDATE, DELETE ON ums.users TO ums_app;

---

TypeORM Subscriber — Set Tenant Context

// infrastructure/database/subscribers/tenant-context.subscriber.ts
@Injectable()
export class TenantContextSubscriber implements EntitySubscriberInterface {
 constructor(@InjectDataSource() private readonly ds: DataSource,
 private readonly tenantContext: TenantContext,) {
 this.ds.subscribers.push(this);
 }

 async beforeQuery(event: BeforeQueryEvent<unknown>): Promise<void> {
 const tenantId = this.tenantContext.getTenantId();
 if (tenantId) {
 await event.connection.query(`SELECT set_config('app.current_tenant_id', $1, true)`,
 [tenantId],);
 }
 }
}

---

Tenant Context (Request-Scoped)

// infrastructure/context/tenant-context.ts
@Injectable({ scope: Scope.REQUEST })
export class TenantContext {
 private _tenantId: string | null = null;

 setTenantId(tenantId: string): void {
 this._tenantId = tenantId;
 }

 getTenantId(): string | null {
 return this._tenantId;
 }
}

---

JWT Guard — Extract and Set Tenant

// infrastructure/guards/tenant.guard.ts
@Injectable()
export class TenantGuard implements CanActivate {
 constructor(private readonly jwtService: JwtService,
 private readonly tenantContext: TenantContext,) {}

 canActivate(context: ExecutionContext): boolean {
 const request = context.switchToHttp().getRequest();
 const token = request.headers.authorization?.replace('Bearer ', '');

 if (!token) throw new UnauthorizedException();

 const payload = this.jwtService.verify(token);
 if (!payload.tenantId) throw new ForbiddenException('No tenant claim in token.');

 this.tenantContext.setTenantId(payload.tenantId);
 return true;
 }
}

---

Repository Usage

// infrastructure/database/repositories/typeorm-user.repository.ts
@Injectable()
export class TypeOrmUserRepository implements IUserRepository {
 constructor(@InjectRepository(UserEntity)
 private readonly repo: Repository<UserEntity>,
 // TenantContext is NOT injected here — RLS enforces isolation at DB level) {}

 async findById(id: string): Promise<User | null> {
 // No WHERE tenant_id = ? needed — RLS filters automatically
 const entity = await this.repo.findOneBy({ id });
 return entity ? UserMapper.toDomain(entity) : null;
 }

 async findAll(): Promise<User[]> {
 // Returns only rows for the current tenant — RLS enforces this
 const entities = await this.repo.find();
 return entities.map(UserMapper.toDomain);
 }
}

---

Testing RLS in Integration Tests

// test/integration/rls.spec.ts
describe('RLS enforcement', () => {
 it('does not leak cross-tenant data', async () => {
 // Create users for two tenants
 await createUser(ds, { tenantId: 'tenant-A', email: 'a@a.com' });
 await createUser(ds, { tenantId: 'tenant-B', email: 'b@b.com' });

 // Set context to tenant-A
 await ds.query(`SELECT set_config('app.current_tenant_id', 'tenant-A', true)`);

 const users = await ds.getRepository(UserEntity).find();

 expect(users).toHaveLength(1);
 expect(users[0].email).toBe('a@a.com'); // tenant-B row must not appear
 });
});

---

Security Rules

1. The DB service account (ums_app) mustnotbe a superuser — superusers bypass RLS
2. The app.current_tenant_id setting is transaction-local (true as third argument to set_config) — it resets after each transaction
3. Admin / migration tasks use a separate migration role (ums_migrator) with BYPASSRLS privilege
4. Every integration test must assert that cross-tenant queries return zero rows

---

What NOT to Do

// Manual WHERE tenant_id filter — fragile, easy to forget
const users = await this.repo.find({ where: { tenantId: ctx.getTenantId() } });

// Superuser connection for application queries — bypasses RLS
// DATABASE_URL=postgres://postgres:pass@db/ums ← postgres is superuser

// Setting tenant context after queries have started
// TenantContext must be set in guard, before any repository call

---

Back to Canonical Patterns

Back to Architecture Portal

This site is open source. Improve this page.