coder · geokat · Dec 15, 2025
diff --git a/coderd/coderd.go b/coderd/coderd.go
@@ -573,6 +573,17 @@ func New(options *Options) *API {
  // bugs that may only occur when a key isn't precached in tests and the latency cost is minimal.
  cryptokeys.StartRotator(ctx, options.Logger, options.Database)
 
+ // Ensure system role permissions are current for all organizations.
+ // This backfills permissions for roles created by migration and
+ // updates permissions when new RBAC resources are added, while
+ // using advisory lock for multi-replica safety.
+ err = ReconcileOrgMemberRoles(ctx, options.Logger, options.Database)
+ if err != nil {
+ // TODO:(geokat) Not using fatal here and just continuing after
+ // logging the error would be a potential security hole, right?
+ options.Logger.Fatal(ctx, "failed to reconcile orgMember role permissions", slog.Error(err))
+	}
+
  // AGPL uses a no-op build usage checker as there are no license
  // entitlements to enforce. This is swapped out in
  // enterprise/coderd/coderd.go.

diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -4110,6 +4110,8 @@ func (q *querier) InsertCustomRole(ctx context.Context, arg database.InsertCusto
  return database.CustomRole{}, err
 	}
 
+ // TODO(geokat): should we add MemberPermissions validation too
+ // now that we have them in system roles?
  if err := q.customRoleCheck(ctx, database.CustomRole{
  Name:            arg.Name,
  DisplayName:     arg.DisplayName,
@@ -4864,6 +4866,8 @@ func (q *querier) UpdateCustomRole(ctx context.Context, arg database.UpdateCusto
  return database.CustomRole{}, err
 	}
 
+ // TODO(geokat): should we add MemberPermissions validation too
+ // now that we have them in system roles?
  if err := q.customRoleCheck(ctx, database.CustomRole{
  Name:            arg.Name,
  DisplayName:     arg.DisplayName,

diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/lock.go b/coderd/database/lock.go
@@ -13,6 +13,7 @@ const (
  LockIDNotificationsReportGenerator
  LockIDCryptoKeyRotation
  LockIDReconcilePrebuilds
+ LockIDReconcileOrgMemberRoles
 )
 
 // GenLockID generates a unique and consistent lock ID from a given string.

diff --git a/coderd/database/migrations/000404_add_system_role_support.down.sql b/coderd/database/migrations/000404_add_system_role_support.down.sql
@@ -0,0 +1,3 @@
+ALTER TABLE custom_roles DROP COLUMN IF EXISTS member_permissions;
+
+ALTER TABLE custom_roles DROP COLUMN IF EXISTS is_system;
diff --git a/coderd/database/migrations/000404_add_system_role_support.up.sql b/coderd/database/migrations/000404_add_system_role_support.up.sql
@@ -0,0 +1,10 @@
+-- Add is_system column to identify system-managed roles.
+ALTER TABLE custom_roles
+    ADD COLUMN is_system boolean NOT NULL DEFAULT false;
+
+-- Add member_permissions column for member-scoped permissions within an organization.
+ALTER TABLE custom_roles
+    ADD COLUMN member_permissions jsonb NOT NULL DEFAULT '[]'::jsonb;
+
+COMMENT ON COLUMN custom_roles.is_system IS
+ 'System roles are managed by Coder and cannot be modified or deleted by users.';
diff --git a/coderd/database/migrations/000405_add_workspace_sharing_disabled.down.sql b/coderd/database/migrations/000405_add_workspace_sharing_disabled.down.sql
@@ -0,0 +1 @@
+ALTER TABLE organizations DROP COLUMN IF EXISTS workspace_sharing_disabled;
diff --git a/coderd/database/migrations/000405_add_workspace_sharing_disabled.up.sql b/coderd/database/migrations/000405_add_workspace_sharing_disabled.up.sql
@@ -0,0 +1,2 @@
+ALTER TABLE organizations
+    ADD COLUMN workspace_sharing_disabled boolean NOT NULL DEFAULT false;
diff --git a/coderd/database/migrations/000406_create_org_member_system_roles.down.sql b/coderd/database/migrations/000406_create_org_member_system_roles.down.sql
@@ -0,0 +1,11 @@
+-- Remove organization-member system roles created by the up migration.
+DELETE FROM
+    custom_roles
+WHERE
+    name = 'organization-member'
+ AND is_system = true;
+
+-- Restore the original unique constraint (name only, no organization_id).
+DROP INDEX IF EXISTS idx_custom_roles_name_lower_organization_id;
+
+CREATE UNIQUE INDEX idx_custom_roles_name_lower ON custom_roles USING btree (lower(name));
diff --git a/coderd/database/migrations/000406_create_org_member_system_roles.up.sql b/coderd/database/migrations/000406_create_org_member_system_roles.up.sql
@@ -0,0 +1,51 @@
+-- Fix the unique constraint on custom_roles to allow the same role
+-- name in different organizations. The original constraint only
+-- covered name, but roles should be unique per (name,
+-- organization_id).
+DROP INDEX IF EXISTS idx_custom_roles_name_lower;
+
+-- Use COALESCE to handle NULL organization_id. Site-wide custom roles
+-- are currently not used, but that can change in the future and this
+-- will be necessary. And there are no performance implications.
+CREATE UNIQUE INDEX idx_custom_roles_name_lower_organization_id ON custom_roles USING btree (
+ lower(name),
+    COALESCE(organization_id, '00000000-0000-0000-0000-000000000000'::uuid)
+);
+
+-- Create placeholder organization-member system roles for existing
+-- organizations.  Permissions are empty here and will be populated by
+-- the startup hook.
+INSERT INTO custom_roles (
+    name,
+    display_name,
+    organization_id,
+    site_permissions,
+    org_permissions,
+    user_permissions,
+    member_permissions,
+    is_system,
+    created_at,
+    updated_at
+)
+SELECT
+ 'organization-member', -- reserved role name, so it doesn't exist in DB yet
+ '',
+    id,
+ '[]'::jsonb,
+ '[]'::jsonb,
+ '[]'::jsonb,
+ '[]'::jsonb,
+    true,
+    NOW(),
+    NOW()
+FROM
+    organizations
+WHERE
+    deleted = false
+ AND NOT EXISTS (
+ SELECT 1
+ FROM custom_roles
+ WHERE
+ custom_roles.name = 'organization-member'
+ AND custom_roles.organization_id = organizations.id
+    );
diff --git a/coderd/database/models.go b/coderd/database/models.go
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,3 @@
		ALTER TABLE custom_roles DROP COLUMN IF EXISTS member_permissions;

		ALTER TABLE custom_roles DROP COLUMN IF EXISTS is_system;
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1 @@
		ALTER TABLE organizations DROP COLUMN IF EXISTS workspace_sharing_disabled;
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,2 @@
		ALTER TABLE organizations
		ADD COLUMN workspace_sharing_disabled boolean NOT NULL DEFAULT false;