Sync identity provider groups to database roles

View as Markdown

PREVIEW Group-to-role mapping is in private preview. It is under active development and may have stability or performance issues. It isn't subject to our backwards compatibility guarantees.
To enable this feature in your Materialize region, contact our team.

As an administrator of a Materialize organization, you can configure SCIM to sync groups from your identity provider (IdP) into Materialize and map them to database roles.

Groups you forward from your IdP are provisioned into your Materialize organization via SCIM, along with their memberships.
When a user authenticates with Materialize, their group memberships are included in the authentication exchange. Materialize uses this information to grant and revoke their membership in existing database roles with the same names.

This means you can manage who belongs to which database role from your IdP. When a team member joins, leaves, or changes teams, updating their group membership in the IdP updates their database role memberships, with no manual GRANT or REVOKE statements required. Roles you grant manually with GRANT are outside the scope of sync and are never revoked.

! Important: Group sync only assigns and unassigns role membership. It never creates or drops database roles. You must create the database role yourself before a group takes effect.

NOTE:

Materialize has two separate permission layers, and a group can affect both:

Organization roles (Organization Admin, Organization Member) control access to Materialize and organization administration. A group can be assigned an organization role, and members of the group receive that organization role automatically. By default, groups are assigned the Organization Member role. Assigning Organization Admin to a group makes its members superusers in Materialize.
Database roles control access to database objects via role-based access control (RBAC). Group sync, the subject of this page, grants and revokes membership in the database role matching each group’s name.

Before you begin

Your Materialize organization must have an SSO connection configured for your identity provider. Group sync builds on SSO, so set that up first. You can confirm your connection under Account > Account Settings > SSO.
You must have an identity provider that supports SCIM 2.0 provisioning (e.g., Okta or Microsoft Entra ID).
Only users assigned the Organization Admin role can manage provisioning and groups.
Group-to-role sync applies on connection, never mid-session. Materialize makes a best effort to apply group changes on the user’s next connection, but it may take several minutes for changes to be reflected.

Step 1. Create a SCIM connection

Log in to the Materialize Console.
Navigate to Account > Account Settings > Provisioning.
Click Add Connection, name the integration, and select your identity provider (Okta, Azure, or Custom SCIM for any other SCIM 2.0-compatible provider).
Follow the in-console guide for your provider. The Console generates a SCIM endpoint URL and an API token, which you enter into your identity provider’s provisioning settings.

Once your identity provider connects successfully, the connection shows as Linked.

Provisioning connections in the Materialize Console

Step 2. Choose which groups to sync

Materialize only syncs the groups you explicitly configure your identity provider to send. Your other IdP groups are not visible to Materialize.

NOTE: Group names that collide with a reserved Materialize role name are skipped and never mapped to a role. Avoid these names when choosing which groups to push. See Limitations for the full list.

In the Okta Admin Console, open the SCIM application you connected in Step 1.
On the Assignments tab, assign the users (or groups) that should be provisioned into Materialize.
On the Push Groups tab, click Push Groups and select the groups to sync, either by name or by rule.

Once pushed, the groups and their memberships appear in the Materialize Console under Account > Account Settings > Groups, marked with a SCIM badge. Groups pushed from your IdP are read-only in Materialize and must be managed from the IdP.

Configure your identity provider’s SCIM provisioning to push the users and groups that should exist in Materialize. Most providers let you scope provisioning to specific groups, so only those groups and their members are synced.

Step 3. Create matching database roles

For each group that should grant access, create a database role with the same name as the group and grant it the privileges the group’s members need. Members of the group get their access through membership in this role:

CREATE ROLE materialize_admins;
GRANT ALL PRIVILEGES ON SCHEMA production TO materialize_admins;

The database role must already exist before the user connects. Group sync only assigns and unassigns membership in existing roles. It never creates roles, so a group with no matching role is skipped during sync. Group names must match role names exactly, including case. The roles you create act as an allowlist for which groups take effect.

To check the mapping status of your groups, navigate to Account > Account Settings > Groups. Each group indicates whether it has a corresponding database role and will work with group mapping.

Groups in the Materialize Console

Step 4. Verify

Have a user in a synced group connect to Materialize (e.g., via the SQL Shell or psql). On their first login via SSO, Materialize auto-provisions the user’s own database role (named from their identity claim). Group sync then grants them membership in the database roles matching their groups. The user’s own role is auto-created. The group roles must already exist.

Role memberships granted by group sync are recorded with the grantor mz_jwt_sync, so you can distinguish them from manual grants:

SELECT r.name AS role, m.name AS member, g.name AS grantor
FROM mz_role_members rm
JOIN mz_roles r ON rm.role_id = r.id
JOIN mz_roles m ON rm.member = m.id
JOIN mz_roles g ON rm.grantor = g.id;

All grants and revokes performed by group sync are also recorded in mz_audit_events.

How sync works

Sync happens at connection time. When a user connects, Materialize compares their current group memberships against their sync-managed role memberships and applies the difference. Materialize makes a best effort to apply changes made in the IdP on the user’s next connection, but it may take several minutes for changes to be reflected. Changes are never applied to a session that is already connected.
Manual grants are never touched. Group sync only manages memberships it granted itself (grantor mz_jwt_sync). A role granted manually with GRANT is never revoked by sync, even if the user leaves the corresponding group. Audit manual grants periodically to avoid users retaining access through stale manual grants.
Groups without a matching role are skipped. The connection proceeds and Materialize sends the client a NOTICE for each unmatched group.

Limitations

Reserved role names are never mapped. Groups whose names collide with a reserved Materialize role name are always skipped during sync, so IdP groups cannot grant system-level privileges. Reserved names are:
- Any name beginning with mz_, pg_, or external_.
- The PUBLIC role.
- The role-specification keywords current_user, current_role, session_user, user, and none.
Matching against reserved names is case-insensitive.
Roles are never created or dropped. Group sync only assigns and unassigns role membership. A group only takes effect once you create a database role with a matching name.
Changes are not applied in real time. Group membership changes are only applied when a user connects, never to a session that is already connected. Materialize makes a best effort to apply changes on the next connection, but it may take several minutes for a change in your identity provider to be reflected.

Manage with Terraform

Instead of the Console, you can manage SCIM connections and groups with the Materialize Terraform provider:

Resource	Description
`materialize_scim_config`	Manage SCIM provisioning connections.
`materialize_scim_group`	Create and manage groups.
`materialize_scim_group_users`	Manage group membership.
`materialize_scim_group_roles`	Assign organization roles to groups.