Roles and Permissions Schema Design for Multi-Tenant RBAC (Part 2)

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Roles belong to a tenant; permissions do not. A permission describes what the software can do, so it is system-wide and every tenant adapts to it. This part derives the roles, members_roles, roles_pems, and pems tables, and shows how a module and method pair extracted from your routing becomes the permission check your middleware runs on every request.

Multi-Tenant RBAC Data Model, Part 2: Roles, Permissions, and the Routing Table

Roles carry a tenant_id; permissions do not. That one asymmetry is the whole design. A role is something a tenant invents - "Regional Manager" means whatever that company decides it means - while a permission describes a capability of the software, and every tenant runs the same software. This part builds the four remaining tables on top of the tenants, users, and members foundation from Part 1, and then shows where the permission rows actually come from: your routing table.

We are assuming a micro framework with no opinion about authorization - no built-in users, no roles, no permissions, nothing to configure. Everything gets built from scratch. That sounds like more work, and it is, but it means every table below exists for a reason we can state out loud.

The Complete Multi-Tenant RBAC Schema

Here is where Part 1 was heading. Seven tables, and every relationship is one we can now derive.

Read it from the outside in. users and tenants are the two independent things. members sits between them as the tenant-scoped membership. roles hangs off tenants. pems hangs off nothing at all. And two junction tables - members_roles and roles_pems - carry the two N:N relationships that make role-based access control work: a member has many roles, and a role grants many permissions.

Why Roles Belong to a Tenant

roles carries tenant_id, and the unique constraint spans (tenant_id, name) rather than name alone. Both facts follow from the same observation: the word "Admin" is not a global concept. Every tenant gets to have one, and two tenants' Admins have nothing to do with each other.

Scoping the constraint to the pair is what makes that possible. A single-column unique index on name would mean the first tenant to create "Manager" takes the name away from everyone else - which is exactly the kind of accidental cross-tenant coupling that only shows up in production, on the day your second customer signs up. Scoping it to (tenant_id, name) says the real rule out loud: names must be unique within a tenant, and mean nothing outside it.

Why Permissions Are System-Wide, Not Tenant-Scoped

Now the table that surprises people: pems has no tenant_id.

The instinct in a multi-tenant schema is to stamp tenant_id on everything, because tenant isolation is the thing you are most afraid of getting wrong. But a permission is not tenant data. A permission says this software has an endpoint that archives a course. That fact is produced by your codebase, not by your customer. Every tenant is running the same deployment, hitting the same routes, exercising the same capabilities. There is exactly one true list of them.

Think about what a tenant_id on pems would actually commit you to:

• Duplication with no variance. With 40 modules averaging 5 methods each, you have 200 permission rows. Add a tenant_id and 500 tenants turn that into 100,000 rows that are all copies of the same 200 facts.
• A write on every deploy. Ship a new endpoint and you now have to fan a new permission row out to every tenant, forever, as a migration that grows with your customer count.
• Rows that can disagree. The moment the same permission exists 500 times, 500 copies can drift. One tenant's row says the module is course, another says courses, and the middleware silently stops matching.

So the direction of adaptation runs one way: tenants adapt to permissions, not the other way around. A tenant cannot invent a capability the software does not have. What a tenant can do is compose - build its own roles out of the fixed permission vocabulary, and call them whatever it likes. That is the entire freedom a tenant needs, and roles already provides it.

This is also why pems sits alone on the diagram with a single line into it. It has no foreign key to anything. It is a vocabulary, and vocabularies do not belong to anyone.

	roles	pems
Scope	One tenant	The whole system
Defined by	The customer	The codebase
Changes when	An admin edits them	You deploy a new route
Unique over	(tenant_id, name)	(module, method)
Row count	Grows with customers	Grows with features

Where Permission Rows Come From: Module and Method

If permissions are produced by the codebase, they should be read from the codebase rather than typed by hand into a seed file. That is what module and method are for.

Organize every feature as a module, and every module as a set of methods. course has list, read, create, update, archive. report has list, read, export. The pair is what identifies a capability, which is why the unique constraint on pems spans (module, method) - the same reasoning as (tenant_id, name) on roles, applied to a different pair. Neither half means anything alone; create is not a permission, and course is not a permission, but course.create is.

The way to keep that list honest is to attach the pair to the route itself. Whatever your framework's routing looks like, every route gains two pieces of metadata:

GET    /courses            -> module: course,  method: list
POST   /courses            -> module: course,  method: create
GET    /courses/{id}       -> module: course,  method: read
PATCH  /courses/{id}       -> module: course,  method: update
POST   /courses/{id}/archive -> module: course, method: archive

Now the routing table is the permission list. At deploy time, walk the registered routes, collect the distinct (module, method) pairs, and upsert them into pems. Adding an endpoint adds a permission automatically. More importantly, it becomes structurally difficult to ship an unguarded endpoint, because the route that has no module and method is the one that stands out in review.

How Default Roles Bootstrap a New Tenant

There is a third piece of metadata on the route, and it is the one that pays off biggest: which default roles this capability belongs to.

POST   /configs   -> module: config,  method: create,  default_roles: {Admin}
POST   /courses   -> module: course,  method: create,  default_roles: {Admin, Manager}
GET    /courses   -> module: course,  method: list,    default_roles: {Admin, Manager, User}

Creating a system config is an owner-level act, so it is {Admin} and nothing else. Creating a course is ordinary operational work, so {Admin, Manager}. Listing courses is something everyone does, so all three. The developer writing the endpoint is the person best placed to make that call, and they make it once, at the route, next to the code it guards. That array lands in pems.default_role_names.

Now invert it at tenant creation. Every permission already knows which default roles it belongs to, so the bootstrap routine reads the whole pems table, groups by role name, and creates Admin, Manager and User for the new tenant with their grants already correct. No per-tenant configuration, no checklist, no human deciding for the four hundredth time that a manager can create a course. A brand-new tenant gets a complete, working set of roles for free, on the first request after signup.

Those default roles are read-only. A tenant can assign them to members as they are, but nobody - not even the tenant admin - can edit their permissions. Customization happens by duplicate and modify: need a "Course Coordinator" who is a Manager without billing access? Copy Manager, drop the permissions that do not apply, save it under a new name. The copy belongs to the tenant and is theirs to change however they like.

That restriction looks like a limitation and is actually the thing that makes the whole system maintainable, for a reason that only shows up on the next deploy. Hold onto it.

Either way, this is the composition freedom from earlier made concrete: a tenant cannot invent capabilities, but it can slice the fixed vocabulary any way it likes, starting from a sensible default instead of a blank page.

Storing this as an array rather than another junction table is deliberate. These are role names, not foreign keys - they cannot point at any tenant's roles rows, because at deploy time those rows do not exist yet, and after bootstrap each tenant has its own. The array is a template, read once per tenant and never joined against. Array types are first-class in PostgreSQL, and this is exactly the case they are for.

What Happens to Existing Tenants When You Add a Route

Nothing - until a sync runs, no existing tenant can use the new capability. Deploy course.archive with {Admin, Manager} and the permission row lands in pems immediately, but roles_pems holds no grant for it: tenant number one signed up last year, and its Admin role predates the route. So the deploy pipeline ends with a command - call it syncrolepems - that walks every tenant and syncs its default roles against the pems table: after it runs, each default role grants exactly the permissions that name it in default_role_names - no more, no less.

In member terms: every member holding a default role picks up the new permission the moment the pipeline runs syncrolepems, in every tenant at once. Members on custom roles get nothing - their roles stay exactly as their admin built them, until that admin grants the new permission on purpose.

This is where the read-only rule pays off. No tenant can have edited a default role, so there is no local intent to preserve - no diffing against the last run, no working out which permissions are new, just asserting the desired state in both directions. The removal direction is the one an incremental, add-only backfill silently gets wrong: decide that archiving a course is an owner-level act after all, drop Manager from the route's default_role_names, and the next deploy revokes the grant from every tenant's Manager, because the desired state no longer contains it. Re-running is free - the composite primary key on roles_pems makes every insert an on-conflict-do-nothing - so the command simply runs on every deploy.

Matching is by name, which is the second reason default_role_names stores names rather than ids: the same array that builds Admin, Manager and User for a new tenant locates the equivalent roles in every old one, and the (tenant_id, name) unique constraint makes that lookup exact.

The one hard boundary: never touch a user-defined role. The Course Coordinator a tenant copied from Manager is their artifact, frozen at the moment of copying, and the deploy pipeline has no standing to add powers the admin never chose or remove ones they did. The exception that proves the rule is route removal: when a route disappears the capability genuinely no longer exists, so the pems row goes and every grant referencing it follows through the foreign key, user-defined roles included. That is not revoking anyone's access - it is retiring a verb from the vocabulary, and a grant to a capability that no longer exists is a dangling row, not a permission.

One thing the ERD does not show: nothing in roles marks which rows are the read-only defaults. You can derive it - a role is a default if its name appears in some permission's default_role_names, and the unique constraint keeps that unambiguous - or you can make it explicit with a boolean column and let the database refuse the edit rather than the application layer. The derived version costs no schema change; the explicit one survives someone writing a second code path.

Which Role Does a New Member Get?

A members row appears the moment someone joins a tenant, and until they hold a role they can do precisely nothing. Something has to decide their starting permissions.

The schema-shaped answer is another boolean on roles: mark one row per tenant as the default, and assign it on signup. One column, per-tenant flexible, obvious. It is also the one column I deliberately leave out.

Consider how it fails. A flag is a switch, and switches get flipped - by a support engineer in an admin panel, by a migration that seeds the wrong row, by a helpful script. Flip it onto Admin and nothing breaks. No error, no failed request, no alert. Every person who joins that tenant from then on arrives holding full administrative access, and the system is behaving exactly as configured. You find out weeks later, if you find out at all.

That is what makes it disqualifying. It is not a one-time mistake, it is a standing condition that keeps manufacturing over-privileged accounts until somebody notices, and the blast radius grows with every signup.

So the default role is named in source code instead. User is the role with the fewest permissions, that fact is known while the routes are being written, and it belongs in the same place default_role_names already lives - the codebase. Changing which role new members receive becomes a code change: reviewed, diffed, deployed, attributable to a person. It stops being a checkbox.

The consequence is that privilege only ever increases by explicit human action. A new member who needs to be a Manager asks an admin to upgrade them, and somebody with the authority to grant it does so on purpose. That is a slightly worse user experience and a considerably better security posture: every escalation has a name attached, and the path of least resistance is the least-privileged one.

This is a different kind of flag from the read-only marker discussed above, and the difference is the whole argument. A read-only marker is descriptive - it states what a row is, and getting it wrong lets through an edit that should have been refused. An auto-assign marker is imperative - it causes privilege to be granted, on every future signup, with nobody looking. Those failure modes are not comparable, which is why one is a reasonable column and the other is not.

It costs something, and the cost is worth naming: a tenant cannot decide that its new members start as Managers. Everyone starts at the bottom and gets promoted. Some customers will ask for it, and the flexible version is entirely buildable - the boolean is easy to add and it would work. Choosing not to add it is a judgment that a silent, persistent failure pointing toward more privilege is the wrong risk to accept for the convenience it buys.

How Middleware Turns a Route into an Authorization Check

The two ends now meet in the middle, and they meet on the same pair of strings.

At deploy time, the routing table writes into pems: these are the capabilities that exist.

At request time, the middleware reads the matched route's metadata to learn which (module, method) this request requires, then asks one question: does the current member hold a role that grants it? The check never looks at the URL, the HTTP verb, or the controller name. It compares a pair of strings from the route against a set of pairs belonging to the member.

That is what makes the guarantee from Part 1 real - even if they know the API specs and bypass the UI, they will still be blocked at the API layer. There is no UI-side permission logic to bypass, because the UI was never the thing enforcing it. The route declares what it needs; the middleware decides. A client that calls the endpoint directly hits exactly the same check.

Resolving the member's permission set is a walk across the whole schema: members to members_roles to roles to roles_pems to pems. Four joins to answer one boolean.

Why the Junction Tables Have No ID

Both members_roles and roles_pems use the two foreign keys as their primary key, with no surrogate id in sight. Part 1 gave members its own id and argued hard for it - so why the opposite answer here?

Because these two really are nothing but pairings. roles_pems has no lifecycle: the row means this role grants this permission and it will never mean anything else. No status, no history worth keeping on the row itself, and nothing else in the schema will ever need a foreign key pointing at one specific grant. The pairing is the row's identity, so making the two keys the primary key is the honest shape, and it comes with duplicate protection for free.

members failed both of those tests, which is why it got an id. Look at the U badges on its tenant_id and user_id in the diagram: that composite unique constraint is doing the job the primary key does for roles_pems. One membership per user per tenant, guaranteed either way - the difference is only whether that guarantee also has to serve as the row's identity.

Same schema, same rule, opposite conclusions - which is the point. The shape of a junction table is a consequence of what the row means, not a habit you apply uniformly.

	members	members_roles, roles_pems
Primary key	Surrogate id	The two foreign keys
Pairing enforced by	A composite unique constraint	The primary key itself
Has its own state	Password, login attempts, status	Nothing beyond the pairing
Referenced by other tables	Yes, extensively	Never

And since the shape is a decision, the name is where to record it. The convention is small and worth adopting: pluralize both halves for a pure pairing, only the second half for a pairing that carries its own identity, and give the table a domain name once it is an entity in its own right.

• members_roles, roles_pems - both plural. Composite key, nothing but the pairing.
• member_roles - singular then plural. Surrogate id, the two foreign keys as ordinary fields, a unique constraint over the pair. The form for a junction that spans two bounded contexts and may have to survive them splitting into separate databases - it needs an identity that does not depend on the pairing staying put, but it is still recognizably a pairing.
• members - a name of its own. Mechanical naming would have produced tenant_users, but the table on the left of that comparison is an entity that happens to join two tables, and the domain already has a word for what its rows represent. tenant_users would describe how the table came to exist; members describes what it is.

The payoff is that reading the schema stops requiring a lookup. roles_pems tells you it is a composite-key pairing; member_roles tells you it has an id and a unique constraint, and that somebody thought about why; members tells you to stop thinking of it as a junction at all. A name that disagrees with its own shape becomes an obvious thing to fix in review.

Drawing these is also where an ERD tool earns its keep or gets in the way. In Schemity, dragging an N:N relationship between two entities creates the junction table with both foreign keys and the composite primary key already in place, so the default shape is the correct one for a pure pairing - and when a junction turns out to need its own identity, you add the id and move the uniqueness into a named composite constraint instead of rebuilding the table.

The Constraint This ERD Cannot Express

One honest gap, because it is the classic multi-tenant RBAC bug and the diagram will not warn you about it.

Nothing in the foreign keys stops members_roles from linking a member in tenant A to a role owned by tenant B. Both foreign keys are individually valid - the member exists, the role exists - and the row is a cross-tenant privilege escalation. Referential integrity checks that rows exist, not that they belong to the same tenant.

There are three ways to close it, and the schema should pick one deliberately:

• Carry tenant_id into the junction and use composite foreign keys, so the database itself refuses a mismatched pair. Strongest guarantee, at the cost of a wider key.
• Enforce it in the application layer, where role assignment already knows the acting member's tenant. Cheapest, and correct until someone writes a second code path.
• Enforce it with row-level security, if your database supports it and you are already scoping queries by tenant.

Whichever you choose, notice that the ERD is what surfaced the question. The gap is visible precisely because the auth tables and the account tables sit in different bounded contexts and you can see the lines crossing between them - roles.tenant_id and members_roles.member_id reaching into the account model from opposite directions, with nothing tying them together. When the coupling surface between two contexts is small enough to read in one glance, the missing constraint in it is findable.

Seven tables, one asymmetry, and a routing table doing double duty as the source of truth for what your software can do. That is a complete role-based access control model for a multi-tenant system, and none of it needed a framework's opinion to get there.

Comments

[Mihir kanzariya]

the roles-carry-tenant_id-permissions-dont asymmetry is the right call and underrated. you ship a new permission with a code deploy (it's just a capability) and never touch tenant data, while roles stay fully customer-defined. one gotcha on deriving permissions from the routing table: key the permission on a stable module+action id, not the route path. the moment someone renames or refactors a route, every roles_pems grant pointing at the old string silently orphans and people lose access with nothing thrown. that bug class is brutal because it just quietly denies instead of crashing.