← All schemas
Schema Library

RBAC Database Schema

Role-based access control resolves one question: does this user have this permission right now. The schema answers it with seven tables. Users join organizations through memberships; roles are scoped to an organization; permissions are global resource-plus-action pairs; membership_roles grants a membership a role; role_permissions grants a role its permissions. A permission check is then a three-join path: membership -> membership_roles -> role_permissions -> permissions. The deliberate choice is org-scoped roles: permission names stay tenant-free, so the same role definitions (admin, editor, viewer) can be re-created per organization without any tenant column on the permission table itself.

Written by Varun Krishnan · Founder, dbdiagramr

Published · Updated

Source: Original design by dbdiagramr, following the canonical RBAC model

Tables
7
Check path
Membership -> Role -> Permission
Join tables
2
RBAC database schema diagram showing every table, column, and foreign key

Scroll horizontally to see the full diagram. Click it to open at full size.

Open in DBdiagramr to edit →

Embed this diagram

Paste this HTML into a README, docs page, or blog post. The image links back to the full schema page.

<a href="https://www.dbdiagramr.space/schema/rbac">
  <img src="https://www.dbdiagramr.space/schemas/rbac-er-diagram.svg" alt="RBAC Database Schema: Roles, Permissions &amp; Joins" width="920" height="682" />
</a>
<p><a href="https://www.dbdiagramr.space/schema/rbac">RBAC Database Schema: Roles, Permissions &amp; Joins</a> by dbdiagramr</p>

Tables in the RBAC schema

ColumnTypeNullableKey
organizations
iduuidNoPK
namevarcharNo-
slugvarcharNo-
created_attimestamptzNo-
users
iduuidNoPK
emailvarcharNo-
namevarcharYes-
created_attimestamptzNo-
memberships
iduuidNoPK
organization_iduuidNoFK → organizations.id
user_iduuidNoFK → users.id
joined_attimestamptzNo-
roles
iduuidNoPK
organization_iduuidNoFK → organizations.id
namevarcharNo-
descriptiontextYes-
permissions
iduuidNoPK
resourcevarcharNo-
actionvarcharNo-
descriptiontextYes-
membership_roles
membership_iduuidNoPK, FK → memberships.id
role_iduuidNoPK, FK → roles.id
granted_attimestamptzNo-
role_permissions
role_iduuidNoPK, FK → roles.id
permission_iduuidNoPK, FK → permissions.id

Table relationships

5 relationships connect the tables in this schema. Each entry shows the child columns, the parent columns they reference, and the cardinality.

One-to-many

memberships.organization_id → organizations.id

One organization can have many memberships (memberships.organization_id -> organizations.id, required).

One-to-many

memberships.user_id → users.id

One user can have many memberships (memberships.user_id -> users.id, required).

One-to-many

roles.organization_id → organizations.id

One organization can have many roles (roles.organization_id -> organizations.id, required).

Many-to-many

membership_roles (membership_id -> memberships.id, role_id -> roles.id)

Many memberships can have many roles through membership_roles (membership_roles.membership_id -> memberships.id, membership_roles.role_id -> roles.id).

Many-to-many

role_permissions (role_id -> roles.id, permission_id -> permissions.id)

Many roles can have many permissions through role_permissions (role_permissions.role_id -> roles.id, role_permissions.permission_id -> permissions.id).

Common queries

These PostgreSQL queries run against the schema shown on this page.

Permission check

The three-join path from a membership to a permission.

select 1
from memberships m
join membership_roles mr on mr.membership_id = m.id
join role_permissions rp on rp.role_id = mr.role_id
join permissions p on p.id = rp.permission_id
where m.user_id = '00000000-0000-0000-0000-000000000001'::uuid
  and m.organization_id = '00000000-0000-0000-0000-000000000002'::uuid
  and p.resource = 'articles'
  and p.action = 'delete';

Roles and permission counts

See how many permissions each role grants.

select r.name, count(rp.permission_id) as permission_count
from roles r
left join role_permissions rp on rp.role_id = r.id
group by r.id, r.name
order by permission_count desc, r.name;

Memberships without roles

Find organization members that have no role assigned.

select m.id, u.email, o.name as organization
from memberships m
join users u on u.id = m.user_id
join organizations o on o.id = m.organization_id
left join membership_roles mr on mr.membership_id = m.id
where mr.membership_id is null;

Permission matrix

List every role, permission, and organization pairing.

select o.name as organization, r.name as role,
       p.resource, p.action
from roles r
join organizations o on o.id = r.organization_id
left join role_permissions rp on rp.role_id = r.id
left join permissions p on p.id = rp.permission_id
order by o.name, r.name, p.resource, p.action;

Frequently asked questions

Related schema diagrams

Visualize your own database

Paste your PostgreSQL connection string and get an interactive ER diagram of your own schema in under 10 seconds. No signup required.

Try it free →