Auth, Authorization & Multi-Tenancy

Multi-tenant data isolation patterns

One app serving many organizations requires careful isolation. Three architectural patterns: separate databases (strongest isolation, expensive), separate schemas (moderate isolation, good balance), and shared schema with tenant_id column (simplest, requires discipline). Most SaaS apps use shared schema because it scales, but you must enforce tenant_id on every query.

Advanced

6 min read

Why multi-tenancy isolation matters

Imagine Company A and Company B both use your SaaS app. Alice (Company A) should never see Bob's (Company B) data. This is not just a permission problem — it's an architectural one.

Bad: trust authorization alone

# WRONG — relies only on authorization check
@app.get('/documents')
def get_documents():
    user = request.user
    # assumes authorization will filter by user_id
    return db.query('SELECT * FROM documents WHERE user_id = ?', user.id)

If someone finds a vulnerability (e.g., passing user_id=999 in a parameter), they can access other users' data.

Better: enforce tenant isolation at the architecture level

# CORRECT — the database itself enforces tenant boundaries
@app.get('/documents')
def get_documents():
    user = request.user
    org_id = user.organization_id
    # even if the attacker guesses a user_id, they can't cross org boundaries
    return db.query(
        'SELECT * FROM documents WHERE user_id = ? AND organization_id = ?',
        user.id, org_id
    )

Three isolation patterns

Pattern 1: Separate database per tenant (strongest)

Each organization gets its own PostgreSQL database:

Company A: postgres://postgres:pw@db-a.internal/
Company B: postgres://postgres:pw@db-b.internal/
Company C: postgres://postgres:pw@db-c.internal/

Pros:

  • Strongest isolation: complete data separation.
  • Easy to scale individual tenants (Company A needs more resources, provision more).
  • Easy to migrate/delete: drop a database to remove a tenant.

Cons:

  • Most expensive (one database server per tenant, or many databases on one server).
  • Operational overhead (backups, migrations, monitoring per database).
  • Onboarding is slower (create database, run migrations, seed data).

When to use: ultra-high-security requirements (HIPAA, finance), very large tenants who need isolation.

Pattern 2: Separate schema per tenant (moderate)

All organizations share one PostgreSQL instance, but each gets its own schema:

CREATE SCHEMA company_a;
CREATE SCHEMA company_b;
CREATE SCHEMA company_c;
 
-- Company A's documents table
CREATE TABLE company_a.documents (
    id UUID PRIMARY KEY,
    title TEXT,
    owner_id UUID
);
 
-- Company B's documents table (same structure, different schema)
CREATE TABLE company_b.documents (
    id UUID PRIMARY KEY,
    title TEXT,
    owner_id UUID
);

Pros:

  • Stronger isolation than shared schema.
  • Cheaper than separate databases.
  • Easy to migrate/delete: drop a schema.

Cons:

  • Still multiple schemas to manage.
  • All queries must reference the schema.
  • Onboarding requires schema creation and migration.

When to use: medium to large SaaS with moderate tenant sizes.

Pattern 3: Shared schema with tenant_id (simplest, most common)

All organizations share all tables. Isolation is enforced via a tenant_id (or organization_id) column:

CREATE TABLE documents (
    id UUID PRIMARY KEY,
    organization_id UUID NOT NULL,  -- tenant identifier
    title TEXT,
    owner_id UUID
);
 
CREATE INDEX idx_documents_org_id ON documents(organization_id);

Pros:

  • Simplest to implement.
  • Easiest to scale (single database).
  • Fastest onboarding (no schema creation).
  • Easiest for reporting/analytics (query across tenants).

Cons:

  • Weakest isolation: relies on discipline (always including tenant_id in queries).
  • One mistake (missing tenant_id filter) exposes all data.
  • Requires constant vigilance.

When to use: most SaaS apps (unless isolation requirements are exceptionally high).

Enforcing tenant_id at query time (shared schema)

If you use the shared schema pattern, every single query must include the tenant_id filter:

# WRONG — missing organization_id filter
@app.get('/documents/<doc_id>')
def get_document(doc_id):
    return db.query('SELECT * FROM documents WHERE id = ?', doc_id)
 
# CORRECT — always filter by organization_id
@app.get('/documents/<doc_id>')
def get_document(doc_id):
    user = request.user
    return db.query(
        'SELECT * FROM documents WHERE id = ? AND organization_id = ?',
        doc_id, user.organization_id
    )

Automation via middleware:

def enforce_tenant_id(user):
    """Returns a context that enforces tenant_id on all queries."""
    org_id = user.organization_id
    
    class TenantAwareDB:
        def query(self, sql, *args):
            # Ensure organization_id is in the WHERE clause
            if 'organization_id' not in sql:
                raise ValueError("Query must filter by organization_id")
            # Inject org_id into query
            return db.query(sql, *args, org_id)
    
    return TenantAwareDB()
 
@app.get('/documents/<doc_id>')
def get_document(doc_id):
    user = request.user
    db = enforce_tenant_id(user)
    return db.query('SELECT * FROM documents WHERE id = ? AND organization_id = ?', doc_id)

Even better: use an ORM that supports row-level security (RLS):

-- PostgreSQL Row-Level Security
CREATE POLICY tenant_isolation ON documents
    USING (organization_id = current_user_org_id());
 
-- All queries are automatically filtered by organization_id

Multi-tenant writes

Writing data also requires tenant_id:

@app.post('/documents')
def create_document():
    user = request.user
    title = request.json.get('title')
    
    # WRONG — doesn't set organization_id
    db.execute(
        'INSERT INTO documents (id, title, owner_id) VALUES (?, ?, ?)',
        uuid.uuid4(), title, user.id
    )
    
    # CORRECT — explicitly set organization_id
    db.execute(
        'INSERT INTO documents (id, title, owner_id, organization_id) VALUES (?, ?, ?, ?)',
        uuid.uuid4(), title, user.id, user.organization_id
    )

Tenant isolation in caching

If you cache query results, include tenant_id in the cache key:

# WRONG — cache key doesn't include tenant_id
cache_key = f"documents_{doc_id}"
if cache_key in redis:
    return redis.get(cache_key)  # might return data from another tenant!
 
# CORRECT — include tenant_id
cache_key = f"documents_{user.organization_id}_{doc_id}"
if cache_key in redis:
    return redis.get(cache_key)

Testing tenant isolation

Write tests that verify a user from one tenant can't access data from another:

def test_users_cannot_access_other_tenants_data():
    # Create two organizations
    org_a = create_organization("Company A")
    org_b = create_organization("Company B")
    
    # Create users
    alice = create_user("alice", org_a)
    bob = create_user("bob", org_b)
    
    # Alice creates a document in Company A
    doc = create_document("Secret", owner=alice, org=org_a)
    
    # Bob (in Company B) tries to read it
    bob_session = authenticate(bob)
    response = bob_session.get(f'/documents/{doc.id}')
    
    # Bob should get 404, not the document
    assert response.status_code == 404

Common mistakes

Forgetting to add organization_id to bulk operations

# WRONG
db.execute('UPDATE documents SET status = ? WHERE status = ?', 'archived', 'pending')
# This updates ALL pending documents across ALL organizations!
 
# CORRECT
db.execute(
    'UPDATE documents SET status = ? WHERE status = ? AND organization_id = ?',
    'archived', 'pending', user.organization_id
)

Not indexing on organization_id

-- SLOW (full table scan)
SELECT * FROM documents WHERE id = ? AND organization_id = ?
 
-- FAST (indexed lookup)
CREATE INDEX idx_documents_org_id ON documents(organization_id);
CREATE INDEX idx_documents_org_id_id ON documents(organization_id, id);

Storing sensitive data in logs

If logs include queries, they might expose tenant_id values. Sanitize log output.

Not auditing cross-tenant access

Monitor for queries without organization_id filters. Alert on suspicious patterns.

Tenant isolation in microservices

In a microservice architecture, enforce tenant_id across services:

# API Gateway adds organization_id to request context
@app.post('/api/documents')
def create_document():
    user = request.user
    org_id = request.headers.get('X-Organization-ID')
    
    # Pass org_id to downstream services
    response = service_a.call('/internal/create-document', {
        'title': request.json['title'],
        'organization_id': org_id
    })

Each service must:

  1. Accept organization_id as a parameter.
  2. Verify the user belongs to that organization.
  3. Use organization_id in all database queries.

Further reading

Check your understanding

A quick comprehension check — not tracked, not graded, just for you.

1. What happens if a shared-schema multi-tenant app forgets to filter by organization_id in a query?

2. Which isolation pattern is strongest but most expensive?

3. Why is an index on organization_id important in shared-schema multi-tenant?

4. What is PostgreSQL Row-Level Security (RLS)?

← Refresh tokens and token rotationProduction authentication gotchas →