coder · geokat · Dec 15, 2025 · Dec 18, 2025 · Dec 18, 2025 · Dec 18, 2025
diff --git a/coderd/coderd.go b/coderd/coderd.go
@@ -573,6 +573,15 @@ 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 all system role permissions are current.
+	//nolint:gocritic // We need to manage system roles
+	err = rolestore.ReconcileSystemRoles(dbauthz.AsSystemRestricted(ctx), options.Logger, options.Database)
+	if err != nil {
+		// Not ideal, but not using Fatal here and just continuing
+		// after logging the error would be a potential security hole.
+		options.Logger.Fatal(ctx, "failed to reconcile system 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
+	LockIDReconcileSystemRoles
 )
 
 // GenLockID generates a unique and consistent lock ID from a given string.

diff --git a/coderd/database/migrations/000405_add_system_role_support.down.sql b/coderd/database/migrations/000405_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/000405_add_system_role_support.up.sql b/coderd/database/migrations/000405_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/000406_add_workspace_sharing_disabled.down.sql b/coderd/database/migrations/000406_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/000406_add_workspace_sharing_disabled.up.sql b/coderd/database/migrations/000406_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/000407_create_org_member_system_roles.down.sql b/coderd/database/migrations/000407_create_org_member_system_roles.down.sql
@@ -0,0 +1,6 @@
+-- Drop the trigger and function created by the up migration.
+DROP TRIGGER IF EXISTS trigger_insert_org_member_system_role ON organizations;
+DROP FUNCTION IF EXISTS insert_org_member_system_role;
+
+-- Remove organization-member system roles created by the up migration.
+DELETE FROM custom_roles WHERE name = 'organization-member' AND is_system = true;
diff --git a/coderd/database/migrations/000407_create_org_member_system_roles.up.sql b/coderd/database/migrations/000407_create_org_member_system_roles.up.sql
@@ -0,0 +1,81 @@
+-- Create placeholder organization-member system roles for existing
+-- organizations. Also add a trigger that creates the placeholder role
+-- when an organization is created. Permissions will be empty until
+-- populated by the reconciliation routine.
+--
+-- Note: why do all this in the database (as opposed to coderd)? Less
+-- room for race conditions. If the role doesn't exist when coderd
+-- expects it, the only correct option is to panic. On the other hand,
+-- a placeholder role with empty permissions is harmless and the
+-- reconciliation process is idempotent.
+
+-- Create roles for the existing organizations.
+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
+    NOT EXISTS (
+        SELECT 1
+        FROM custom_roles
+        WHERE
+            custom_roles.name = 'organization-member'
+            AND custom_roles.organization_id = organizations.id
+    );
+
+-- When we insert a new organization, we also want to create a
+-- placeholder org-member system role for it.
+CREATE OR REPLACE FUNCTION insert_org_member_system_role() RETURNS trigger AS $$
+BEGIN
+    INSERT INTO custom_roles (
+        name,
+        display_name,
+        organization_id,
+        site_permissions,
+        org_permissions,
+        user_permissions,
+        member_permissions,
+        is_system,
+        created_at,
+        updated_at
+    ) VALUES (
+        'organization-member',
+        '',
+        NEW.id,
+        '[]'::jsonb,
+        '[]'::jsonb,
+        '[]'::jsonb,
+        '[]'::jsonb,
+        true,
+        NOW(),
+        NOW()
+    );
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trigger_insert_org_member_system_role
+    AFTER INSERT ON organizations
+    FOR EACH ROW
+    EXECUTE FUNCTION insert_org_member_system_role();
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;