PostgreSQL RLS for Multi-Tenant Trading
· 4 min read
How we implemented subscription tiers, token bucket rate limiting, and PostgreSQL Row-Level Security for tenant isolation.
The Multi-Tenancy Challenge
A SaaS trading platform needs:
- Data isolation - Users must never see each other's data
- Feature gating - Tiers unlock different capabilities
- Rate limiting - Prevent resource exhaustion
- Fair usage - Higher tiers get more resources
We implemented these at multiple layers: application (UserContext), database (RLS), and API (rate limiters).
Subscription Tiers
Three tiers with distinct capabilities:
| Feature | Free | Pro | Enterprise |
|---|---|---|---|
| Basic trading | Yes | Yes | Yes |
| Arbitrage detection | No | Yes | Yes |
| Copy trading | 1 | 10 | Unlimited |
| API rate limit | 10/s | 100/s | 1000/s |
| Orders/minute | 10 | 100 | 1000 |
| Max positions | 5 | 50 | 500 |
| Max position size | $100 | $10,000 | $100,000 |
| Priority support | No | No | Yes |
Tiers are defined in code with their limits:
pub enum Tier {
Free,
Pro,
Enterprise,
}
impl Tier {
pub fn limits(&self) -> TierLimits {
match self {
Tier::Free => TierLimits {
max_positions: 5,
max_position_size: 100.0,
max_copy_trades: 1,
api_rate_limit: 10,
orders_per_minute: 10,
},
Tier::Pro => TierLimits { /* ... */ },
Tier::Enterprise => TierLimits { /* ... */ },
}
}
}
User Context
The UserContext struct carries user state through request handling:
pub struct UserContext {
pub user_id: UserId,
pub tier: Tier,
api_limiter: Arc<RateLimiter>,
order_limiter: Arc<RateLimiter>,
position_count: AtomicU32,
copy_trade_count: AtomicU32,
}
Each request validates against the context:
impl UserContext {
pub fn validate_order(&self, size_usd: f64) -> Result<(), ContextError> {
let limits = self.limits();
// Check position count
if self.position_count() >= limits.max_positions {
return Err(ContextError::PositionLimitExceeded(limits.max_positions));
}
// Check order size
if size_usd > limits.max_position_size {
return Err(ContextError::OrderSizeExceeded(limits.max_position_size));
}
Ok(())
}
}
Token Bucket Rate Limiting
We use the token bucket algorithm for rate limiting:
pub struct RateLimiter {
capacity: u32, // Burst capacity
refill_rate: f64, // Tokens per second
tokens: AtomicU64, // Current tokens (scaled)
last_refill: Mutex<Instant>,
}
The algorithm:
- Bucket starts full (capacity = burst limit)
- Each request consumes one token
- Tokens refill at a steady rate
- If bucket empty, request is rejected
pub async fn try_acquire(&self) -> Result<(), RateLimitError> {
self.refill().await;
loop {
let current = self.tokens.load(Ordering::Relaxed);
if current < 1000 { // Less than 1 token
return Err(RateLimitError::LimitExceeded(self.capacity, Duration::from_secs(1)));
}
let new_value = current - 1000;
if self.tokens.compare_exchange(current, new_value, Ordering::Relaxed, Ordering::Relaxed).is_ok() {
return Ok(());
}
}
}
This allows bursts up to capacity while enforcing a sustained rate limit.
PostgreSQL Row-Level Security
Database isolation uses RLS policies:
-- Enable RLS on tables
ALTER TABLE positions ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE credentials ENABLE ROW LEVEL SECURITY;
-- Positions: users see only their own
CREATE POLICY positions_isolation ON positions
FOR ALL
USING (user_id = current_setting('app.current_user_id')::uuid);
-- Orders: users see only their own
CREATE POLICY orders_isolation ON orders
FOR ALL
USING (user_id = current_setting('app.current_user_id')::uuid);
-- Credentials: users see only their own
CREATE POLICY credentials_isolation ON credentials
FOR ALL
USING (user_id = current_setting('app.current_user_id')::uuid);
Before each request, we set the session variable:
pub async fn set_user_context(&self, user_id: &UserId) -> Result<(), DbError> {
sqlx::query(&format!(
"SET LOCAL app.current_user_id = '{}'",
user_id
))
.execute(&self.pool)
.await?;
Ok(())
}
RLS provides defense-in-depth: even if application code has a bug, the database enforces isolation.
Testing Strategy
57 tests verify multi-tenancy:
| Category | Tests |
|---|---|
| Tier limits | 12 |
| Rate limiting | 11 |
| UserContext | 18 |
| RLS policies | 16 |
Key tests include:
#[test]
fn test_feature_check_free_tier() {
let ctx = UserContext::free(UserId::new());
assert!(ctx.check_feature(Feature::BasicTrading).is_ok());
assert!(ctx.check_feature(Feature::Arbitrage).is_err());
}
#[tokio::test]
async fn test_api_rate_limiting() {
let ctx = UserContext::free(UserId::new());
// Free tier: 10 req/sec, 20 burst
for _ in 0..20 {
assert!(ctx.check_api_rate().await.is_ok());
}
assert!(ctx.check_api_rate().await.is_err());
}
Architecture Diagram
┌──────────────────────────────────────────────────────────────┐
│ API Request │
└──────────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────┐
│ 1. JWT Validation → Extract user_id and tier │
└──────────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────┐
│ 2. Load UserContext → Initialize rate limiters │
└──────────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────┐
│ 3. Check Rate Limits → Token bucket algorithm │
└──────────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────┐
│ 4. Check Feature Access → Tier allows this operation? │
└──────────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────┐
│ 5. Validate Limits → Position count, order size │
└──────────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────┐
│ 6. Set RLS Context → SET LOCAL app.current_user_id │
└──────────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────┐
│ 7. Execute Query → RLS enforces row-level isolation │
└──────────────────────────────────────────────────────────────┘
Lessons Learned
- Layer defenses - Application + database isolation
- Token bucket is versatile - Handles burst and sustained limits
- RLS is powerful - But requires careful policy design
- Test isolation explicitly - Don't assume it works
Multi-tenancy touches every layer of the application. Getting it right early prevents painful refactoring later.
