Part of a series (2 stacked PRs) that closes coder/internal#1073

• PR 1/2 (this one)
• PR 2/2

Migrating Org-Member Role to Database

Problem

The organization-member role’s permissions are generated in code (the built-in orgMember role). This keeps it easy to evolve alongside RBAC resource changes, but it prevents per-organization variation (e.g. introducing and respecting an organization’s workspace_sharing_disabled setting).

Solution: Database-Backed System Roles (per organization)

Store one organization-member role per organization in custom_roles with is_system=true. Create placeholder rows in the database (migration + trigger) and reconcile them at startup so the stored permissions stay in sync with the code-generated source of truth.

1. Schema Changes

• Added custom_roles.is_system (boolean) to mark Coder-managed roles.
• Added custom_roles.member_permissions (jsonb) to store member-scoped permissions (resources owned by the user).
• Added organizations.workspace_sharing_disabled (boolean) as a per-org setting.

2. Migration + DB Trigger

• Migration creates placeholder organization-member system roles for all existing organizations (with empty permissions).
• A database trigger creates the placeholder system role for every newly inserted organization.
• Permissions are populated using a source-of-truth function.

3. Permission Generation (code is source of truth)

• rbac.OrgMemberPermissions(workspaceSharingDisabled bool) in coderd/rbac/roles.go is the source of truth.
• It generates:
  • Org-scoped permissions (e.g. org reads, role listing, and sharing-related reads when sharing is enabled).
  • Member-scoped permissions via member_permissions.
• When workspace_sharing_disabled=true, it includes a negated permission for workspace:share to disable sharing.

4. Org Creation (enterprise)

• On org creation, the DB trigger inserts an empty placeholder system role row.
• enterprise/coderd/organizations.go calls rolestore.ReconcileOrgMemberRole() (with a system-restricted auth context) to populate the role’s permissions immediately.

5. Startup Reconciliation (system role maintenance)

• At startup, coderd/coderd.go calls rolestore.ReconcileSystemRoles().
• ReconcileSystemRoles:
  • Acquires a PostgreSQL advisory lock (LockIDReconcileSystemRoles) to prevent concurrent reconciles across instances.
  • Iterates organizations and ensures the organization-member system role exists.
  • Compares stored vs expected permissions using set-based comparison (rbac.PermissionsEqual) and updates only when needed.
  • Treats missing roles as a critical condition (logs loudly and attempts to recreate as a last resort).

Key Design Decisions