PostgreSQL RLS for Multi-Tenant Trading

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:

1. Data isolation - Users must never see each other's data
2. Feature gating - Tiers unlock different capabilities
3. Rate limiting - Prevent resource exhaustion
4. 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:

1. Bucket starts full (capacity = burst limit)
2. Each request consumes one token
3. Tokens refill at a steady rate
4. 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

1. Layer defenses - Application + database isolation
2. Token bucket is versatile - Handles burst and sustained limits
3. RLS is powerful - But requires careful policy design
4. Test isolation explicitly - Don't assume it works

Multi-tenancy touches every layer of the application. Getting it right early prevents painful refactoring later.