SQLAlchemy Composite Foreign Keys for Multi-Tenant Hierarchical Permissions: Enforcing (tenant_id, role_id) Constraints

I burned this one hard in CitizenApp's early months. A customer's support team member somehow gained admin access to a completely different tenant's workspace. The bug? We assigned roles using only user_id and role_id, never checking that the role's tenant_id matched the user's tenant_id. The database had no constraint preventing it. Application-level filtering caught 99% of cases, but a race condition during a bulk role migration created that one golden window where the star-alignment permissions leaked across tenant boundaries.

It took three hours of log archaeology to find it. It would have taken three seconds for a composite foreign key constraint to prevent it.

The Problem With Single-Column Foreign Keys in Multi-Tenant Systems

Most developers reach for the obvious schema first:

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    tenant_id = Column(Integer, ForeignKey("tenants.id"), nullable=False)

class Role(Base):
    __tablename__ = "roles"
    id = Column(Integer, primary_key=True)
    tenant_id = Column(Integer, ForeignKey("tenants.id"), nullable=False)
    name = Column(String, nullable=False)

class UserRole(Base):
    __tablename__ = "user_roles"
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    role_id = Column(Integer, ForeignKey("roles.id"), nullable=False)

This looks reasonable until you realize: the database doesn't know that user_id and role_id must belong to the same tenant. You're enforcing that constraint in your ORM query logic or middleware. That's a tax on every permission check, and it's a tax developers pay inconsistently.

The fix is composite foreign keys. Instead of trusting application code to filter correctly, make the database enforce it structurally.

Composite Foreign Keys: Database-Level Tenant Isolation

Here's the corrected schema:

from sqlalchemy import ForeignKey, UniqueConstraint, Table, Column, Integer, String, Index
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class Tenant(Base):
    __tablename__ = "tenants"
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    tenant_id = Column(Integer, ForeignKey("tenants.id"), nullable=False)
    email = Column(String, nullable=False)
    __table_args__ = (
        UniqueConstraint("tenant_id", "email", name="uq_user_tenant_email"),
    )

class Role(Base):
    __tablename__ = "roles"
    id = Column(Integer, primary_key=True)
    tenant_id = Column(Integer, ForeignKey("tenants.id"), nullable=False)
    name = Column(String, nullable=False)
    __table_args__ = (
        UniqueConstraint("tenant_id", "id", name="uq_role_tenant_id"),
    )

class UserRole(Base):
    __tablename__ = "user_roles"
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, primary_key=True)
    role_id = Column(Integer, nullable=False, primary_key=True)
    tenant_id = Column(Integer, nullable=False, primary_key=True)

    __table_args__ = (
        # Composite FK: enforces role belongs to user's tenant
        ForeignKey(
            ["tenant_id", "role_id"],
            ["roles.tenant_id", "roles.id"],
            name="fk_userrole_role_tenant"
        ),
        # Composite FK: enforces user belongs to this tenant
        ForeignKey(
            ["user_id", "tenant_id"],
            ["users.id", "users.tenant_id"],
            name="fk_userrole_user_tenant"
        ),
    )

The magic is in __table_args__. The first composite FK says: "tenant_id + role_id must exist as a (tenant_id, id) pair in the roles table." The second says: "user_id + tenant_id must match a user in their tenant." Together, they guarantee that a user and their assigned roles live in the same tenant.

Try to insert a cross-tenant assignment:

# Tenant 1 user, Tenant 2 role
from sqlalchemy import create_engine
from sqlalchemy.orm import Session

engine = create_engine("postgresql://...")
Base.metadata.create_all(engine)

with Session(engine) as session:
    # This will FAIL with IntegrityError
    session.execute("""
        INSERT INTO user_roles (user_id, role_id, tenant_id)
        VALUES (1, 999, 2)
    """)
    session.commit()

The database rejects it instantly. No ORM query tricks needed. No race condition window.

Why I Prefer Composite FKs Over Application Filters

Application-level filtering is optional. A junior developer queries user_roles without joining to verify tenant_id. A stored procedure gets written that forgets the filter. A caching layer returns stale data. Filtering happens in the controller, but some service method bypasses it.

Composite constraints are mandatory and testable at the schema level. Every insert, update, and delete that violates the rule fails the same way, in the same place, with the same error. You can validate your schema in tests without touching application code.

def test_composite_fk_prevents_cross_tenant_roles():
    """Verify that assigning a role from tenant B to a user in tenant A fails."""
    from sqlalchemy import text, exc

    with Session(engine) as session:
        # Tenants
        t1 = Tenant(id=1, name="Acme")
        t2 = Tenant(id=2, name="Globex")
        session.add_all([t1, t2])
        session.flush()

        # Users in each tenant
        u1 = User(id=1, tenant_id=1, email="[e-mail]")
        u2 = User(id=2, tenant_id=2, email="[e-mail]")
        session.add_all([u1, u2])
        session.flush()

        # Roles in each tenant
        r1 = Role(id=1, tenant_id=1, name="Admin")
        r2 = Role(id=2, tenant_id=2, name="Admin")  # Same name, different tenant
        session.add_all([r1, r2])
        session.flush()

        # Attempt: assign Globex admin role to Acme user
        try:
            session.execute(text("""
                INSERT INTO user_roles (user_id, role_id, tenant_id)
                VALUES (1, 2, 1)
            """))
            session.commit()
            assert False, "Should have raised IntegrityError"
        except exc.IntegrityError:
            pass  # Expected

This test is a schema contract. If someone refactors the foreign key logic and forgets composite constraints, the test fails immediately.

Gotcha: Cascading Deletes and Composite Keys

Here's where I tripped up: if you're tempted to add cascade="all, delete" to make cleanup automatic, understand what happens.

# DON'T do this lightly
class UserRole(Base):
    __tablename__ = "user_roles"

    user_id = Column(
        Integer,
        ForeignKey("users.id", ondelete="CASCADE"),
        primary_key=True
    )
    role_id = Column(Integer, primary_key=True)
    tenant_id = Column(Integer, primary_key=True)

If a user is deleted, all their role assignments cascade-delete automatically. That's good. But when you have composite keys, deleting a tenant doesn't automatically cascade through user_roles—it only cascades from the users and roles tables separately. You might orphan records or create locking issues during bulk deletes.

My preference: Use explicit cascade on the user side only, and handle role orphaning with application-level soft deletes or audit triggers.

class UserRole(Base):
    __tablename__ = "user_roles"
    user_id = Column(
        Integer,
        ForeignKey("users.id", ondelete="CASCADE"),
        primary_key=True
    )
    role_id = Column(Integer, primary_key=True)
    tenant_id = Column(Integer, primary_key=True)

    __table_args__ = (
        ForeignKey(
            ["tenant_id", "role_id"],
            ["roles.tenant_id", "roles.id"],
            name="fk_userrole_role_tenant"
            # ondelete="CASCADE" omitted; roles table deletion handled separately
        ),
    )

What I Missed Early On

I initially thought composite foreign keys were only for legacy systems. I was wrong. They're the foundational defense for any multi-tenant system with role hierarchies. The tiny schema complexity—a few extra columns in join tables—pays dividends in prevented bugs.

If you're building a SaaS with role-based access, do this from day one. Your future self, reviewing logs at 2 AM, will thank you.

Comments

[Mads Hansen]

The invariant is exactly right, but there are two SQLAlchemy/PostgreSQL details worth fixing in the sample. Multi-column references use ForeignKeyConstraint, not ForeignKey, and every referenced column tuple needs a matching unique/primary constraint in the same order. So both users and roles should expose UNIQUE (tenant_id, id), while user_roles declares ForeignKeyConstraint(["tenant_id", "user_id"], ["users.tenant_id", "users.id"]) and the equivalent role constraint. Without the user-side composite uniqueness, PostgreSQL will reject that FK definition even though users.id alone is unique. For a live migration, I would also add the constraints NOT VALID, clean existing violations, then VALIDATE CONSTRAINT before making the application rely on them.