PostgreSQL

Role-based access control (RBAC) database schema

A canonical RBAC schema: users, roles, permissions, resources, and per-resource grants. Supports both role-level (workspace-wide) and resource-level (per-project) permissions.

Start for free

Database schema previewRole-based access control (RBAC) database schema

Recreate this in Raltey

Copy this prompt

Paste this into Raltey's AI agent to generate the same diagram, then tweak it on the canvas.

Prompt

Design a PostgreSQL database schema for: Role-based access control (RBAC) database schema.

A canonical RBAC schema: users, roles, permissions, resources, and per-resource grants. Supports both role-level (workspace-wide) and resource-level (per-project) permissions.

Include these tables: role, permission, role_permission, user_role, resource_grant.

Use proper primary keys, foreign keys, and indexes. Export SQL DDL for PostgreSQL.

Context

- Permissions are strings (e.g., "project:edit", "billing:read").
- Roles bundle permissions.
- Resource-level grants override role grants for specific objects.
- Effective permission resolution is left to the application layer.

Patterns

- Role bundles permissions; user_role assigns roles per workspace.
- Resource grants act as overrides on top of role grants.
- is_system flag protects built-in roles from deletion.

Tables

The schema, table by table

role

DBML - role

Table role {
  id           uuid pk
  workspace_id uuid [ref: > workspace.id]
  name         text not null
  is_system    boolean default false
}

permission

DBML - permission

Table permission {
  id          uuid pk
  key         text unique not null   // project:edit, billing:read
  description text
}

role_permission

DBML - role_permission

Table role_permission {
  role_id       uuid [ref: > role.id]
  permission_id uuid [ref: > permission.id]
  pk (role_id, permission_id)
}

user_role

DBML - user_role

Table user_role {
  user_id      uuid [ref: > user_account.id]
  role_id      uuid [ref: > role.id]
  workspace_id uuid [ref: > workspace.id]
  pk (user_id, role_id, workspace_id)
}

resource_grant

Per-resource override (e.g., grant edit on one specific project).

DBML - resource_grant

Table resource_grant {
  id            uuid pk
  user_id       uuid [ref: > user_account.id]
  permission_id uuid [ref: > permission.id]
  resource_type text not null
  resource_id   uuid not null
  granted_at    timestamptz default `now()`
}

Relationships

From	To	Type
role	role_permission	1:N
permission	role_permission	1:N
user_account	user_role	1:N
role	user_role	1:N
user_account	resource_grant	1:N

Notes from the field

Cache effective permissions in Redis with a TTL or invalidation on grant change.
Index resource_grant(user_id, resource_type, resource_id) for fast lookups.
Prefer string keys for permissions over numeric IDs - migrations are simpler.

Frequently asked

Should I model permissions as strings or as a flag table?

Strings ("project:edit"). They are migration-friendly and read well in code. Numeric IDs save bytes but cost developer time.

Do I need both roles and resource grants?

For most apps yes. Roles cover broad defaults (admin, member). Resource grants handle the "this user can edit just this project" case without spawning a new role.

More templates

FlowchartSaaS onboarding email flow FlowchartCustomer support runbook flow FlowchartPayment webhook retry and error flow FlowchartGitHub PR review and merge flow FlowchartLogin with magic link flow ArchitectureThree-tier SaaS architecture on AWS ArchitectureMicroservices architecture on Kubernetes ArchitectureReal-time data pipeline on GCP

Open this template in Raltey

Edit it on the canvas, ask the AI agent to extend it, then share or export.