SCIM: implement Groups #1357

fflorent · 2025-01-03T15:18:26Z

Context

Following #1199, this PR proposes to implement the /Groups Endpoint.

As an IT asset administrator, I can create groups for my users using a centralized solution (an Identity Provider) so they can easily access to the resources. But these groups and memberships are not pushed to Grist as of today. The administrator is either offered to remove access entirely to Grist, or access to Grist and modify the permissions on the resources. Most of all, as Teams are not represented through the UI, it can be a quite painful task.

Proposed solution

SCIM is a standard proposed by the IETF through RFC7644 and RFC7643 which aims to through a simple Rest API provide solution for the above use cases.

Here is the abstract of the RFC7644 which introduces SCIM:

The System for Cross-domain Identity Management (SCIM) specification is an HTTP-based protocol that makes managing identities in multi-domain scenarios easier to support via a standardized service.
Examples include, but are not limited to, enterprise-to-cloud service providers and inter-cloud scenarios. The specification suite seeks to build upon experience with existing schemas and deployments, placing specific emphasis on simplicity of development and integration, while applying existing authentication, authorization, and privacy models. SCIM's intent is to reduce the cost and complexity of user management operations by providing a common user schema, an extension model, and a service protocol defined by this document.

This PR provides a complement to the now existing /scim/v2/Users endpoint (documented here):

SCIM Endpoint	Group Type¹	Usage
`/scim/v2/Groups`	Team	Represents a team of users in your organisation, who are granted accesses toa set of resources through Roles
`/scim/v2/Roles` ²	Role	Grants users and teams a certain access (`owner`, `editor`, `viewer`, ...) to a given resource

Currently only the Roles were represented in the "groups" table. To achieve this distinction, this PR also introduces a "type" column, which only accept team or role values.

This PR provides the implementation of SCIM for Groups (aka Teams), and supports:

All the basic actions for Groups (POST /Groups/, PUT /Groups/:id, GET /Groups/, GET /Groups/:id and DELETE /Groups/:id).
The POST /Bulk endpoint
The POST /Groups/.search by using the Filters (actually to use them, you must have to fetch all the Resources from the DB, the filtering is done in JS, which is probably fine for small projects, I would just be cautious when using big databases + an ORM);
The PATCH /Groups/:id endpoint! It reads a resource using the egress method, applies the asked changes, and calls the ingress method to update the record ;
The pagination

The endpoint for /Roles are nearly the same, though:

Creation (trough POST) and the deletion are not allowed;
We can only update the memberships, which is helpful to assign a role to users or teams;
And the endpoint also return the resource ID associated to the role through the orgId, workspaceId or docId readonly attributes.

As in #1199, I take advantage of these two libraries: scimmy and scimmy-routers.

👀 Known limitations

You cannot add a group as a member of a Resouce Users group, due to the fact that the depth for groups are limited to 4.
The groups are not displayed in the Users Management popup for now. This may be a bit confusing.

⛹️ Play with this endpoint

This is a step-by-step tutorial to play with this new endpoint:
https://gist.github.com/fflorent/dd11a15e424ab4828f679ab038eea764

Related issues

Fixes #870

Has this been tested?

👍 yes, I added tests to the test suite
💭 no, because this PR is a draft and still needs work
🙅 no, because this is not relevant here
🙋 no, because I need help

In the Grist terminology, and as specified in the groups.type column in the database. ↩
This is a custom endpoint as allowed by the SCIM standard. ↩

app/gen-server/lib/homedb/UsersManager.ts

test/gen-server/migrations.ts

fflorent · 2025-01-31T15:09:35Z

test/server/lib/Scim.ts

        assert.equal(res.status, 401);
      });
-
-      it.skip('should allow operation like PATCH for kiwi', async function () {


Removed it, it looks like this won't be fixed (for good reasons I think):
scimmyjs/scimmy-routers#27

app/gen-server/entity/Group.ts

app/server/lib/scim/v2/roles/SCIMMYRoleSchema.ts

hexaltation

Thank you @fflorent for this huge piece of work.

Two small changes asked.

And one concern about the stickyness of the code with SCIMMY.
How much of of work could it need:

To add a layer of abstraction to not use SCIMMY object in higher levels of grist,
To replace this lib contributed by only one guy the day he won't maintained it anymore.

But no doubt that it's the best solution for now.

test/gen-server/migrations.ts

This change also introduce a "Team" and a "Role" type for Groups. "Role" type is the historical type, it is the role granted to the user for a resource. The "Team" type is a new type, it is meant to gather people and grant them a role together for a resource. The SCIM /Groups endpoints gives access to the "Teams" groups. The SCIM /Roles endpoints gives a (limited) access to the "Roles" groups. For more information, please take a look at this Pr description: gristlabs#1357 (comment) Co-authored-by: Grégoire Cutzach <[email protected]>

paulfitz

(just a quick skim (scim?) of part of the code)

app/gen-server/lib/homedb/UsersManager.ts

paulfitz · 2025-07-24T20:51:35Z

app/gen-server/lib/homedb/UsersManager.ts

+  }
+
+  /**
+   * Returns a Promise for an array of User entites for the given userIds.


app/gen-server/migration/1568238234987-TeamMembers.ts

app/gen-server/lib/homedb/GroupsManager.ts

This change also introduce a "Team" and a "Role" type for Groups. "Role" type is the historical type, it is the role granted to the user for a resource. The "Team" type is a new type, it is meant to gather people and grant them a role together for a resource. The SCIM /Groups endpoints gives access to the "Teams" groups. The SCIM /Roles endpoints gives a (limited) access to the "Roles" groups. For more information, please take a look at this Pr description: gristlabs#1357 (comment) Co-authored-by: Grégoire Cutzach <[email protected]>

paulfitz · 2025-08-28T17:08:53Z

app/gen-server/migration/1753088213255-GroupTypes.ts

+    const newColumnNonNull = newColumn.clone();
+    newColumnNonNull.isNullable = false;
+
+    await queryRunner.changeColumn('groups', newColumn, newColumnNonNull);


Hmm this all feels ok and will probably run alright on large installations. At some point it is more practical to just use nullable columns and take the hit in type-checking, but we're not at that scale yet.

app/server/lib/scim/v2/BaseController.ts

app/server/lib/scim/v2/ScimGroupController.ts

app/gen-server/lib/homedb/UsersManager.ts

paulfitz · 2025-08-28T17:21:25Z

app/gen-server/lib/homedb/UsersManager.ts

+  }
+
+  /**
+   * Returns a Promise for an array of User entites for the given userIds.


app/gen-server/migration/1568238234987-TeamMembers.ts

app/server/lib/scim/v2/ScimUtils.ts

This change also introduce a "Team" and a "Role" type for Groups. "Role" type is the historical type, it is the role granted to the user for a resource. The "Team" type is a new type, it is meant to gather people and grant them a role together for a resource. The SCIM /Groups endpoints gives access to the "Teams" groups. The SCIM /Roles endpoints gives a (limited) access to the "Roles" groups. For more information, please take a look at this Pr description: gristlabs#1357 (comment) Co-authored-by: Grégoire Cutzach <[email protected]>

After having introduced the "type" column in the "Groups" table, the test for migrating TeamMembers failed because the Group entity had a property that does not exist yet as a column in the table. Instead execute a plain INSERT INTO statement to run the migration so it does not involve the non-existing yet "type" column.

Co-authored-by: Copilot <[email protected]>

After the upgrade of SCIMMY, it looks like SCIMMY has a better support of typing. Let's take advantage of it!

app/server/lib/scim/v2/roles/SCIMMYRoleResource.ts

Co-authored-by: Copilot <[email protected]>

Copilot

Pull Request Overview

This PR implements SCIM support for Groups by adding a new /Groups endpoint and related functionality. It introduces a type distinction for groups (Team vs Role), adds extensive test coverage, and upgrades SCIM library dependencies.

Add Groups endpoints supporting CRUD operations for Teams
Introduce group type system distinguishing Teams from Roles
Add comprehensive test coverage for Groups and Roles functionality

Reviewed Changes

Copilot reviewed 23 out of 24 changed files in this pull request and generated 9 comments.

Show a summary per file

File	Description
test/server/lib/Scim.ts	Adds comprehensive test suite for Groups and Roles SCIM endpoints
test/gen-server/seed.ts	Updates group creation to use new Group.create() method with type
test/gen-server/migrations.ts	Adds GroupTypes migration and foreign key utility usage
test/gen-server/lib/homedb/UsersManager.ts	Updates group creation to use new typed Group.create() method
test/gen-server/lib/homedb/GroupsManager.ts	New comprehensive test suite for GroupsManager functionality
package.json	Updates SCIM library versions to 1.3.5 and 1.3.2
app/server/lib/scim/v2/roles/SCIMMYRoleSchema.ts	New schema definition for Role resources
app/server/lib/scim/v2/roles/SCIMMYRoleResource.ts	New SCIM resource implementation for Roles
app/server/lib/scim/v2/ScimV2Api.ts	Integrates Group and Role resources into SCIM API
app/server/lib/scim/v2/ScimUtils.ts	Common utilities for SCIM transformations
app/server/lib/scim/v2/ScimUserUtils.ts	Removed, functionality moved to ScimUtils
app/server/lib/scim/v2/ScimUserController.ts	Refactored to extend BaseController
app/server/lib/scim/v2/ScimRoleController.ts	New controller for Role SCIM operations
app/server/lib/scim/v2/ScimGroupController.ts	New controller for Group SCIM operations
app/server/lib/scim/v2/BaseController.ts	New base class for SCIM controllers
app/server/lib/dbUtils.ts	Adds utility for SQLite foreign key constraint management
app/gen-server/migration/1753088213255-GroupTypes.ts	New migration adding type column to groups table
app/gen-server/migration/1568238234987-TeamMembers.ts	Updates to handle new Group entity structure
app/gen-server/lib/homedb/UsersManager.ts	Adds getUsersByIdsStrict method
app/gen-server/lib/homedb/Interfaces.ts	Adds GroupWithMembersDescriptor and GroupTypes
app/gen-server/lib/homedb/HomeDBManager.ts	Adds group management methods
app/gen-server/lib/homedb/GroupsManager.ts	Extensive additions for group CRUD operations
app/gen-server/entity/Group.ts	Adds type column and validation logic

_{Tip: Customize your code reviews with copilot-instructions.md. Create the file or learn how to get started.}

test/server/lib/Scim.ts

app/server/lib/scim/v2/ScimV2Api.ts

app/gen-server/migration/1568238234987-TeamMembers.ts

app/gen-server/entity/Group.ts

paulfitz

Thanks for persisting @fflorent ! Looks good. The main focus of my reviewing has been to watch out for any security issues. Everything looks well organized from that perspective. The SCIM functionality looks plausible to me but I'm no expert. Responding to related feature requests and bug reports may be like for the OIDC work, where the original contributor bears the brunt of maintenance just due to the knowledge they've accumulated.

fflorent force-pushed the scim-groups-endpoints branch 3 times, most recently from fbce58e to 2c32360 Compare January 3, 2025 19:08

fflorent force-pushed the scim-groups-endpoints branch 4 times, most recently from 8ecb5ce to 7f924fe Compare January 17, 2025 12:21

fflorent force-pushed the scim-groups-endpoints branch from 0f8575a to 2e91be2 Compare January 31, 2025 12:50

fflorent commented Jan 31, 2025

View reviewed changes

fflorent commented Feb 1, 2025

View reviewed changes

app/gen-server/entity/Group.ts Outdated Show resolved Hide resolved

fflorent marked this pull request as ready for review February 2, 2025 21:19

fflorent requested a review from hexaltation February 2, 2025 21:19

hexaltation reviewed Feb 3, 2025

View reviewed changes

app/server/lib/scim/v2/roles/SCIMMYRoleSchema.ts Outdated Show resolved Hide resolved

hexaltation suggested changes Feb 3, 2025

View reviewed changes

test/gen-server/migrations.ts Outdated Show resolved Hide resolved

fflorent moved this to Needs feedback in French administration Board Mar 12, 2025

fflorent added this to French administration Board Mar 12, 2025

fflorent moved this from Needs feedback to In Progress in French administration Board Jul 16, 2025

fflorent force-pushed the scim-groups-endpoints branch from ef96456 to 52aeaec Compare July 21, 2025 08:55

fflorent force-pushed the scim-groups-endpoints branch from 52aeaec to 1f3e416 Compare July 21, 2025 09:02

fflorent moved this from In Progress to Needs feedback in French administration Board Jul 21, 2025

fflorent force-pushed the scim-groups-endpoints branch from 1f3e416 to d532cfc Compare July 21, 2025 09:06

fflorent force-pushed the scim-groups-endpoints branch from d532cfc to 2e3c081 Compare July 21, 2025 09:11

fflorent force-pushed the scim-groups-endpoints branch 2 times, most recently from b404afd to b2ac191 Compare July 22, 2025 08:44

fflorent force-pushed the scim-groups-endpoints branch from b2ac191 to 88455b6 Compare July 22, 2025 09:05

fflorent force-pushed the scim-groups-endpoints branch from 88455b6 to e304fec Compare July 22, 2025 09:16

fflorent force-pushed the scim-groups-endpoints branch from e304fec to 9c96d90 Compare July 22, 2025 10:10

paulfitz requested a review from Copilot July 23, 2025 17:03

This comment was marked as outdated.

Sign in to view

paulfitz reviewed Jul 24, 2025

View reviewed changes

fflorent force-pushed the scim-groups-endpoints branch from c4982e4 to 653f631 Compare July 25, 2025 08:18

fflorent requested a review from paulfitz July 28, 2025 11:52

fflorent force-pushed the scim-groups-endpoints branch from 653f631 to a7ecfb4 Compare August 13, 2025 15:13

paulfitz reviewed Aug 28, 2025

View reviewed changes

fflorent and others added 4 commits September 2, 2025 11:33

Apply suggestions from code review

1dc9317

Co-authored-by: Copilot <[email protected]>

Bump scimmy and scimmy-routers

44c7a3e

fflorent force-pushed the scim-groups-endpoints branch from d03c648 to 44c7a3e Compare September 2, 2025 09:47

fflorent added 2 commits September 2, 2025 12:12

Fix code style

90ac5e7

Improve types of SCIM related files

279388d

After the upgrade of SCIMMY, it looks like SCIMMY has a better support of typing. Let's take advantage of it!

fflorent force-pushed the scim-groups-endpoints branch from 167bf46 to 279388d Compare September 2, 2025 15:55

fflorent commented Sep 2, 2025

View reviewed changes

app/server/lib/scim/v2/roles/SCIMMYRoleResource.ts Show resolved Hide resolved

fflorent and others added 2 commits September 2, 2025 18:49

Revert change to getUsersByIds

4146167

little typo in test description

d2dcba6

Co-authored-by: Copilot <[email protected]>

fflorent requested a review from Copilot September 2, 2025 16:56

Copilot AI reviewed Sep 2, 2025

View reviewed changes

paulfitz approved these changes Sep 2, 2025

View reviewed changes

paulfitz merged commit c2b53f4 into gristlabs:main Sep 2, 2025
14 checks passed

github-project-automation bot moved this from Needs feedback to Done in French administration Board Sep 2, 2025

fflorent deleted the scim-groups-endpoints branch September 30, 2025 06:52

Uh oh!

SCIM: implement Groups #1357

SCIM: implement Groups #1357

Uh oh!

Conversation

fflorent commented Jan 3, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Context

Proposed solution

👀 Known limitations

⛹️ Play with this endpoint

Related issues

Has this been tested?

Footnotes

Uh oh!

Uh oh!

Uh oh!

fflorent Jan 31, 2025

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

hexaltation left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

This comment was marked as outdated.

Uh oh!

paulfitz left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

paulfitz Jul 24, 2025

Choose a reason for hiding this comment

Uh oh!

paulfitz Aug 28, 2025

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

paulfitz Aug 28, 2025

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

paulfitz Aug 28, 2025

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull Request Overview

Reviewed Changes

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

paulfitz left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

fflorent commented Jan 3, 2025 •

edited

Loading