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.
Last updated: 2026-09-27
Source: Original design by dbdiagramr, following the canonical RBAC model
Tables in the RBAC schema
| Column | Type | Nullable | Key |
|---|---|---|---|
| organizations | |||
| id | uuid | No | PK |
| name | varchar | No | - |
| slug | varchar | No | - |
| created_at | timestamptz | No | - |
| users | |||
| id | uuid | No | PK |
| varchar | No | - | |
| name | varchar | Yes | - |
| created_at | timestamptz | No | - |
| memberships | |||
| id | uuid | No | PK |
| organization_id | uuid | No | - |
| user_id | uuid | No | - |
| joined_at | timestamptz | No | - |
| roles | |||
| id | uuid | No | PK |
| organization_id | uuid | No | - |
| name | varchar | No | - |
| description | text | Yes | - |
| permissions | |||
| id | uuid | No | PK |
| resource | varchar | No | - |
| action | varchar | No | - |
| description | text | Yes | - |
| membership_roles | |||
| membership_id | uuid | No | - |
| role_id | uuid | No | - |
| granted_at | timestamptz | No | - |
| role_permissions | |||
| role_id | uuid | No | - |
| permission_id | uuid | No | - |
Frequently asked questions
What tables does an RBAC database schema need?
An org-scoped RBAC schema needs organizations, users, memberships, roles, permissions, membership_roles, and role_permissions - 7 tables. The two join tables carry the many-to-many edges between memberships and roles, and between roles and permissions.
How do you check a permission in SQL with this schema?
Join from the user's membership through membership_roles to the role, then through role_permissions to permissions, filtering on resource and action: 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 = ? AND m.organization_id = ? AND p.resource = 'articles' AND p.action = 'delete'.
Should roles or permissions be scoped to the organization?
Scope roles to the organization and keep permissions global. Permissions are vocabulary (resource + action pairs like articles:delete); roles are the org's policy (which permissions admin means here). This keeps permission names stable while each tenant defines its own roles.
Explore more schemas
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 →