External Groups

External Groups are groups where membership is determined entirely by external identity provider mappings. No direct membership is stored in the database - instead, users inherit group membership based on their provider groups/roles from their last-used identity.

Overview

External Groups rely on identity provider data for membership:

graph TB
    subgraph "External Identity Provider"
        P["Azure AD / Windows AD / LDAP<br/>Provider Groups:<br/>- Domain Users<br/>- Engineering<br/>- Managers"]
    end

    subgraph "User Identity"
        UI["User Identity<br/>provider_groups: Domain Users, Engineering<br/>provider_roles: Developer<br/>is_last_used: true"]
    end

    subgraph "External Group System"
        EG["External Group<br/>auth.user_group<br/>group_type = external"]
        GM["Group Mappings<br/>auth.user_group_mapping<br/>Maps provider groups to internal groups"]
    end

    P --> UI
    EG --> GM
    GM -.-> UI

Key Characteristics: - No stored membership: No records in auth.user_group_member - Provider-driven: Membership determined by external systems - Dynamic: Changes when provider groups change - Last-used identity: Uses most recent login provider data

Database Structure

Core Tables

-- External group definition (no member table needed)
CREATE TABLE auth.user_group (
    user_group_id uuid PRIMARY KEY,
    tenant_id uuid REFERENCES auth.tenant,
    code text NOT NULL,
    name text NOT NULL,
    description text,
    group_type text DEFAULT 'external',
    is_active boolean DEFAULT true,
    created_at timestamptz DEFAULT now(),
    UNIQUE (tenant_id, code)
);

-- Mapping from external provider groups/roles to internal groups
CREATE TABLE auth.user_group_mapping (
    mapping_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_group_id uuid REFERENCES auth.user_group,
    provider_id uuid REFERENCES auth.provider,
    external_group_name text,     -- Maps to provider_groups
    external_role_name text,      -- Maps to provider_roles
    is_active boolean DEFAULT true,
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now()
);

Creating External Groups

Basic External Group

-- Create external group
SELECT auth.create_external_user_group(
    _tenant_id := 'tenant-uuid',
    _code := 'AD_DEVELOPERS',
    _name := 'Active Directory Developers',
    _description := 'Users mapped from AD Developer groups'
);
-- Returns: user_group_id (uuid)

-- Alternative: Create normal group and set as external
SELECT auth.create_user_group(
    'tenant-uuid',
    'AZURE_MANAGERS',
    'Azure AD Managers',
    'Mapped from Azure AD management roles'
);

-- Convert to external type
SELECT auth.set_user_group_as_external(
    _user_group_id := 'group-uuid'
);
-- This removes any existing direct memberships

Common External Group Examples

-- Windows Active Directory groups
SELECT auth.create_external_user_group(
    'tenant-uuid',
    'DOMAIN_ADMINS',
    'Domain Administrators',
    'Windows domain admin group'
);

-- Azure AD security groups
SELECT auth.create_external_user_group(
    'tenant-uuid',
    'AZURE_ENGINEERING',
    'Azure Engineering Team',
    'Engineering security group from Azure AD'
);

-- LDAP organizational units
SELECT auth.create_external_user_group(
    'tenant-uuid',
    'LDAP_MANAGERS',
    'LDAP Management',
    'Management users from LDAP directory'
);

Creating Group Mappings

Map Provider Groups

-- Map Azure AD group to internal group
SELECT auth.create_user_group_mapping(
    _user_group_id := 'azure-engineering-group-uuid',
    _provider_id := 'azure-ad-provider-uuid',
    _external_group_name := 'Engineering',        -- Azure AD group name
    _external_role_name := NULL                    -- Not mapping roles
);

-- Map Windows AD group
SELECT auth.create_user_group_mapping(
    _user_group_id := 'domain-admins-group-uuid',
    _provider_id := 'windows-ad-provider-uuid',
    _external_group_name := 'Domain Admins',      -- Windows group
    _external_role_name := NULL
);

-- Map SAML role attribute
SELECT auth.create_user_group_mapping(
    _user_group_id := 'saml-managers-group-uuid',
    _provider_id := 'okta-saml-provider-uuid',
    _external_group_name := NULL,
    _external_role_name := 'Manager'              -- SAML role claim
);

Multiple Provider Mappings

-- Map the same internal group to multiple providers
DO $$
DECLARE
    v_group_id uuid := 'developers-group-uuid';
BEGIN
    -- Azure AD mapping
    PERFORM auth.create_user_group_mapping(
        v_group_id,
        'azure-ad-provider-uuid',
        'Developers',
        NULL
    );

    -- Windows AD mapping
    PERFORM auth.create_user_group_mapping(
        v_group_id,
        'windows-ad-provider-uuid',
        'COMPANY\Developers',
        NULL
    );

    -- LDAP mapping
    PERFORM auth.create_user_group_mapping(
        v_group_id,
        'ldap-provider-uuid',
        'cn=developers,ou=groups,dc=company,dc=com',
        NULL
    );
END $$;

Complex Mapping Scenarios

-- Map both groups AND roles to same internal group
DO $$
DECLARE
    v_senior_dev_group uuid := 'senior-developers-group-uuid';
    v_azure_provider uuid := 'azure-ad-provider-uuid';
BEGIN
    -- Senior developers can come from either:
    -- 1. Azure AD "Senior Developers" group
    PERFORM auth.create_user_group_mapping(
        v_senior_dev_group, v_azure_provider, 'Senior Developers', NULL
    );

    -- 2. Users with "TechLead" role
    PERFORM auth.create_user_group_mapping(
        v_senior_dev_group, v_azure_provider, NULL, 'TechLead'
    );

    -- 3. Users with "Architect" role
    PERFORM auth.create_user_group_mapping(
        v_senior_dev_group, v_azure_provider, NULL, 'Architect'
    );
END $$;

How External Groups Work

Membership Resolution

External group membership is resolved dynamically:

-- Function to get user's external group memberships
CREATE OR REPLACE FUNCTION get_user_external_groups(
    _user_id uuid,
    _tenant_id uuid
) RETURNS TABLE (
    user_group_id uuid,
    group_code text,
    group_name text,
    mapping_source text
) AS $$
BEGIN
    RETURN QUERY
    WITH user_last_identity AS (
        -- Get user's current identity and provider groups/roles
        SELECT
            ui.provider_id,
            ui.provider_groups,
            ui.provider_roles
        FROM auth.user_identity ui
        WHERE ui.user_id = _user_id
          AND ui.is_last_used = true
          AND ui.is_active = true
    )
    SELECT DISTINCT
        ug.user_group_id,
        ug.code,
        ug.name,
        CASE
            WHEN ugm.external_group_name IS NOT NULL
            THEN 'group:' || ugm.external_group_name
            ELSE 'role:' || ugm.external_role_name
        END as mapping_source
    FROM user_last_identity uli
    JOIN auth.user_group_mapping ugm ON uli.provider_id = ugm.provider_id
    JOIN auth.user_group ug ON ugm.user_group_id = ug.user_group_id
    WHERE ugm.is_active = true
      AND ug.is_active = true
      AND ug.tenant_id = _tenant_id
      AND ug.group_type IN ('external', 'hybrid')
      AND (
          ugm.external_group_name = ANY(uli.provider_groups) OR
          ugm.external_role_name = ANY(uli.provider_roles)
      );
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT * FROM get_user_external_groups('user-uuid', 'tenant-uuid');

Permission Resolution

When checking permissions for external groups:

flowchart TD
    A[Permission Check Request] --> B[Get User's Last Used Identity]
    B --> C[Extract provider_groups & provider_roles]
    C --> D[Find Matching Group Mappings]
    D --> E[Get Internal Groups]
    E --> F[Collect Group Permissions]
    F --> G[Check Required Permission]
    G -->|Has Permission| H[Allow Access]
    G -->|Lacks Permission| I[Deny Access]

Managing External Groups

Update Group Mappings

-- Add new mapping to existing external group
SELECT auth.create_user_group_mapping(
    _user_group_id := 'existing-group-uuid',
    _provider_id := 'new-provider-uuid',
    _external_group_name := 'NewExternalGroup',
    _external_role_name := NULL
);

-- Disable mapping (don't delete for audit trail)
UPDATE auth.user_group_mapping
SET is_active = false, updated_at = now()
WHERE mapping_id = 'mapping-uuid';

-- Update mapping details
UPDATE auth.user_group_mapping
SET
    external_group_name = 'UpdatedGroupName',
    updated_at = now()
WHERE mapping_id = 'mapping-uuid';

Delete Mappings

-- Remove mapping (affects membership immediately)
SELECT auth.delete_user_group_mapping(
    _mapping_id := 'mapping-uuid'
);

-- Remove all mappings for a provider (when decommissioning)
DELETE FROM auth.user_group_mapping
WHERE provider_id = 'old-provider-uuid';

Querying External Groups

View Group Mappings

-- See all mappings for external groups
SELECT
    ug.code as group_code,
    ug.name as group_name,
    p.name as provider_name,
    ugm.external_group_name,
    ugm.external_role_name,
    ugm.is_active,
    ugm.created_at
FROM auth.user_group ug
JOIN auth.user_group_mapping ugm ON ug.user_group_id = ugm.user_group_id
JOIN auth.provider p ON ugm.provider_id = p.provider_id
WHERE ug.tenant_id = 'tenant-uuid'
  AND ug.group_type = 'external'
ORDER BY ug.code, p.name;

Find Users in External Groups

-- Get all users who would be in an external group
WITH group_mappings AS (
    SELECT
        ugm.external_group_name,
        ugm.external_role_name,
        ugm.provider_id
    FROM auth.user_group_mapping ugm
    WHERE ugm.user_group_id = 'external-group-uuid'
      AND ugm.is_active = true
)
SELECT DISTINCT
    u.user_id,
    u.username,
    u.display_name,
    ui.provider_groups,
    ui.provider_roles,
    p.name as provider_name
FROM auth.user_identity ui
JOIN auth.user_info u ON ui.user_id = u.user_id
JOIN auth.provider p ON ui.provider_id = p.provider_id
JOIN group_mappings gm ON ui.provider_id = gm.provider_id
WHERE ui.is_last_used = true
  AND ui.is_active = true
  AND (
      gm.external_group_name = ANY(ui.provider_groups) OR
      gm.external_role_name = ANY(ui.provider_roles)
  );

External Group Statistics

-- Statistics for external groups
SELECT
    ug.code,
    ug.name,
    COUNT(DISTINCT ugm.mapping_id) as mapping_count,
    array_agg(DISTINCT p.name) as mapped_providers,
    COUNT(DISTINCT ui.user_id) as potential_members
FROM auth.user_group ug
LEFT JOIN auth.user_group_mapping ugm ON ug.user_group_id = ugm.user_group_id
    AND ugm.is_active = true
LEFT JOIN auth.provider p ON ugm.provider_id = p.provider_id
LEFT JOIN auth.user_identity ui ON (
    ui.provider_id = ugm.provider_id
    AND ui.is_last_used = true
    AND ui.is_active = true
    AND (
        ugm.external_group_name = ANY(ui.provider_groups) OR
        ugm.external_role_name = ANY(ui.provider_roles)
    )
)
WHERE ug.tenant_id = 'tenant-uuid'
  AND ug.group_type = 'external'
  AND ug.is_active = true
GROUP BY ug.user_group_id
ORDER BY potential_members DESC;

Common External Group Patterns

Enterprise Active Directory

-- Map common AD groups
DO $$
DECLARE
    v_tenant_id uuid := 'tenant-uuid';
    v_ad_provider uuid := 'windows-ad-provider-uuid';
    v_group_id uuid;
BEGIN
    -- Domain Admins -> System Administrators
    v_group_id := auth.create_external_user_group(
        v_tenant_id, 'DOMAIN_ADMINS', 'Domain Administrators',
        'Windows domain administrators'
    );
    PERFORM auth.create_user_group_mapping(
        v_group_id, v_ad_provider, 'Domain Admins', NULL
    );

    -- Engineering OU -> Engineering Team
    v_group_id := auth.create_external_user_group(
        v_tenant_id, 'ENGINEERING', 'Engineering Team',
        'Engineering department from AD'
    );
    PERFORM auth.create_user_group_mapping(
        v_group_id, v_ad_provider, 'CN=Engineering,OU=Departments,DC=company,DC=com', NULL
    );

    -- Multiple IT groups -> IT Support
    v_group_id := auth.create_external_user_group(
        v_tenant_id, 'IT_SUPPORT', 'IT Support Team',
        'IT support staff from various AD groups'
    );
    PERFORM auth.create_user_group_mapping(
        v_group_id, v_ad_provider, 'IT Support', NULL
    );
    PERFORM auth.create_user_group_mapping(
        v_group_id, v_ad_provider, 'Help Desk', NULL
    );
    PERFORM auth.create_user_group_mapping(
        v_group_id, v_ad_provider, 'Network Admins', NULL
    );
END $$;

Azure AD with Roles

-- Azure AD groups and application roles
DO $$
DECLARE
    v_tenant_id uuid := 'tenant-uuid';
    v_azure_provider uuid := 'azure-ad-provider-uuid';
    v_group_id uuid;
BEGIN
    -- Power Users (combination of group and role)
    v_group_id := auth.create_external_user_group(
        v_tenant_id, 'POWER_USERS', 'Power Users',
        'Users with elevated privileges from Azure AD'
    );

    -- Map from security group
    PERFORM auth.create_user_group_mapping(
        v_group_id, v_azure_provider, 'Power Users', NULL
    );

    -- Map from application role
    PERFORM auth.create_user_group_mapping(
        v_group_id, v_azure_provider, NULL, 'PowerUser'
    );

    -- Map from directory role
    PERFORM auth.create_user_group_mapping(
        v_group_id, v_azure_provider, NULL, 'Application Administrator'
    );
END $$;

Multi-Provider Scenario

-- Same logical group from multiple providers
DO $$
DECLARE
    v_tenant_id uuid := 'tenant-uuid';
    v_managers_group uuid;
BEGIN
    -- Create single internal group for managers
    v_managers_group := auth.create_external_user_group(
        v_tenant_id, 'ALL_MANAGERS', 'All Managers',
        'Managers from all identity providers'
    );

    -- Map from Azure AD
    PERFORM auth.create_user_group_mapping(
        v_managers_group, 'azure-ad-provider-uuid', 'Managers', NULL
    );

    -- Map from Windows AD
    PERFORM auth.create_user_group_mapping(
        v_managers_group, 'windows-ad-provider-uuid', 'COMPANY\Managers', NULL
    );

    -- Map from LDAP
    PERFORM auth.create_user_group_mapping(
        v_managers_group, 'ldap-provider-uuid', 'cn=managers,ou=groups,dc=company,dc=com', NULL
    );

    -- Map from SAML role
    PERFORM auth.create_user_group_mapping(
        v_managers_group, 'okta-saml-provider-uuid', NULL, 'Manager'
    );
END $$;

Benefits of External Groups

Advantages

1. No Synchronization Required: Membership updates automatically with provider changes
2. Real-time Updates: Changes reflect immediately on next login
3. Single Source of Truth: Provider remains authoritative for group membership
4. Reduced Administration: No manual membership management needed
5. Audit Trail: Provider systems handle membership audit

Use Cases

• Enterprise Integration: Leverage existing AD/LDAP groups
• SSO Environments: Natural fit for single sign-on workflows
• Dynamic Teams: Groups that change frequently in external systems
• Compliance: Use provider's group management for compliance
• Large Organizations: Reduce administrative overhead

Limitations and Considerations

Limitations

1. Provider Dependency: Requires external system availability
2. Last-Used Identity: Only considers most recent login provider
3. No Direct Control: Cannot manually add/remove members
4. Provider Outages: Group resolution fails if provider unavailable

Performance Considerations

-- Cache external group membership for performance
CREATE TABLE auth.external_group_membership_cache (
    user_id uuid REFERENCES auth.user_info(user_id),
    user_group_id uuid REFERENCES auth.user_group(user_group_id),
    cached_at timestamptz DEFAULT now(),
    expires_at timestamptz DEFAULT now() + interval '1 hour',
    PRIMARY KEY (user_id, user_group_id)
);

-- Refresh cache function
CREATE OR REPLACE FUNCTION refresh_external_group_cache(
    _user_id uuid
) RETURNS void AS $$
BEGIN
    -- Clear expired cache
    DELETE FROM auth.external_group_membership_cache
    WHERE user_id = _user_id AND expires_at < now();

    -- Rebuild cache
    INSERT INTO auth.external_group_membership_cache (user_id, user_group_id)
    SELECT _user_id, user_group_id
    FROM get_user_external_groups(_user_id,
        (SELECT tenant_id FROM auth.tenant_user WHERE user_id = _user_id LIMIT 1)
    )
    ON CONFLICT (user_id, user_group_id) DO UPDATE SET
        cached_at = now(),
        expires_at = now() + interval '1 hour';
END;
$$ LANGUAGE plpgsql;

Troubleshooting External Groups

Common Issues

User not appearing in external group:

-- Debug external group membership
SELECT
    u.username,
    ui.provider_groups,
    ui.provider_roles,
    p.name as provider,
    ugm.external_group_name,
    ugm.external_role_name,
    CASE
        WHEN ugm.external_group_name = ANY(ui.provider_groups) THEN 'GROUP_MATCH'
        WHEN ugm.external_role_name = ANY(ui.provider_roles) THEN 'ROLE_MATCH'
        ELSE 'NO_MATCH'
    END as match_status
FROM auth.user_info u
JOIN auth.user_identity ui ON u.user_id = ui.user_id AND ui.is_last_used = true
JOIN auth.provider p ON ui.provider_id = p.provider_id
CROSS JOIN auth.user_group_mapping ugm
WHERE u.username = 'problematic.user'
  AND ugm.user_group_id = 'external-group-uuid';

Mapping not working:

-- Check mapping configuration
SELECT
    ug.code,
    ug.name,
    ug.group_type,
    ug.is_active as group_active,
    p.name as provider,
    p.is_active as provider_active,
    ugm.external_group_name,
    ugm.external_role_name,
    ugm.is_active as mapping_active
FROM auth.user_group ug
JOIN auth.user_group_mapping ugm ON ug.user_group_id = ugm.user_group_id
JOIN auth.provider p ON ugm.provider_id = p.provider_id
WHERE ug.user_group_id = 'external-group-uuid';

[{"content": "Create external groups documentation", "status": "completed", "activeForm": "Creating external groups documentation"}, {"content": "Create hybrid groups documentation", "status": "in_progress", "activeForm": "Creating hybrid groups documentation"}, {"content": "Create group mappings documentation", "status": "pending", "activeForm": "Creating group mappings documentation"}]