Group Mappings¶

Group Mappings provide the integration layer between external identity providers and the PostgreSQL Permissions Model's group system. They define how provider groups and roles translate into internal group memberships, enabling seamless integration with Active Directory, Azure AD, LDAP, SAML, and other identity systems.

Overview¶

Group mappings connect external identity data to internal group structures through pattern matching and rule-based assignments:

graph TB
    subgraph "Identity Providers"
        AD[Windows AD<br/>- Domain Users<br/>- Engineering-Dept<br/>- Managers]
        AAD[Azure AD<br/>- All Company<br/>- Engineering<br/>- Team-Alpha-Dev]
        LDAP[LDAP<br/>- ou=Engineering<br/>- ou=Managers<br/>- cn=Developers]
        SAML[SAML<br/>- Employee<br/>- Manager<br/>- Developer]
    end

    subgraph "Group Mappings"
        GM[auth.user_group_mapping<br/>Pattern matching rules<br/>Priority ordering<br/>Provider-specific logic]
    end

    subgraph "Internal Groups"
        IG1[EMPLOYEES<br/>External Group]
        IG2[ENGINEERING<br/>External Group]
        IG3[MANAGERS<br/>Hybrid Group]
        IG4[PROJECT_ALPHA<br/>Hybrid Group]
    end

    AD --> GM
    AAD --> GM
    LDAP --> GM
    SAML --> GM

    GM --> IG1
    GM --> IG2
    GM --> IG3
    GM --> IG4

Mapping Table Structure¶

Core Mapping Configuration¶

CREATE TABLE auth.user_group_mapping (
    user_group_mapping_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_group_id uuid REFERENCES auth.user_group,
    provider_id uuid REFERENCES auth.provider,

    -- Pattern matching fields
    provider_group_pattern text,  -- Regex pattern for provider groups
    provider_role_pattern text,   -- Regex pattern for provider roles

    -- Mapping behavior
    mapping_priority integer DEFAULT 100,  -- Lower = higher priority
    is_active boolean DEFAULT true,
    is_inclusive boolean DEFAULT true,     -- true = include, false = exclude

    -- Conditions and metadata
    conditions jsonb,              -- Additional matching conditions
    mapping_metadata jsonb,        -- Extra configuration data

    created_at timestamptz DEFAULT now(),
    created_by uuid REFERENCES auth.user_info(user_id),

    CHECK (provider_group_pattern IS NOT NULL OR provider_role_pattern IS NOT NULL)
);

Essential Indexes¶

-- Performance indexes for mapping resolution
CREATE INDEX idx_user_group_mapping_resolution
    ON auth.user_group_mapping(provider_id, is_active, mapping_priority);

CREATE INDEX idx_user_group_mapping_group_lookup
    ON auth.user_group_mapping(user_group_id) WHERE is_active = true;

CREATE INDEX idx_user_group_mapping_patterns
    ON auth.user_group_mapping(provider_group_pattern, provider_role_pattern)
    WHERE is_active = true;

Pattern Matching Fundamentals¶

Group Pattern Examples¶

-- Exact match patterns
INSERT INTO auth.user_group_mapping (user_group_id, provider_id, provider_group_pattern)
VALUES
    -- Exact group name match
    ('employees-group-uuid', 'azure-ad-uuid', '^Domain Users$'),
    ('engineering-group-uuid', 'azure-ad-uuid', '^Engineering$'),
    ('managers-group-uuid', 'azure-ad-uuid', '^Managers$');

-- Wildcard and prefix patterns
INSERT INTO auth.user_group_mapping (user_group_id, provider_id, provider_group_pattern)
VALUES
    -- All engineering subgroups
    ('engineering-group-uuid', 'azure-ad-uuid', '^Engineering-.*'),
    -- All project groups
    ('project-members-group-uuid', 'azure-ad-uuid', '^Project-.*'),
    -- Department pattern
    ('dept-heads-group-uuid', 'azure-ad-uuid', '.*-Department-Head$');

Role Pattern Examples¶

-- Role-based mappings
INSERT INTO auth.user_group_mapping (user_group_id, provider_id, provider_role_pattern)
VALUES
    -- Management roles
    ('managers-group-uuid', 'azure-ad-uuid', '^(Manager|Director|VP|Executive)$'),
    -- Technical roles
    ('developers-group-uuid', 'azure-ad-uuid', '.*(Developer|Engineer|Architect).*'),
    -- Administrative roles
    ('admins-group-uuid', 'azure-ad-uuid', '.*(Admin|Administrator).*');

Combined Pattern Mappings¶

-- Complex mapping combining groups and roles
INSERT INTO auth.user_group_mapping
    (user_group_id, provider_id, provider_group_pattern, provider_role_pattern, mapping_priority)
VALUES
    -- Senior engineering: Must be in Engineering group AND have Senior role
    ('senior-engineering-uuid', 'azure-ad-uuid', '^Engineering$', '.*Senior.*', 50),
    -- Project leads: Any project group with Manager role
    ('project-leads-uuid', 'azure-ad-uuid', '^Project-.*', '.*Manager.*', 60),
    -- Department managers: Department group with Manager role
    ('dept-managers-uuid', 'azure-ad-uuid', '.*-Department$', 'Manager', 40);

Provider-Specific Mapping Patterns¶

Windows Active Directory¶

-- Windows AD typically uses DN-style group names
DO $$
DECLARE
    v_ad_provider_id uuid := (
        SELECT provider_id FROM auth.provider WHERE code = 'WINDOWS_AD'
    );
BEGIN
    -- Domain groups
    PERFORM auth.create_user_group_mapping(
        (SELECT user_group_id FROM auth.user_group WHERE code = 'EMPLOYEES'),
        v_ad_provider_id,
        '^CN=Domain Users,CN=Users,DC=company,DC=com$',  -- Full DN
        NULL,
        10  -- High priority
    );

    -- Organizational units
    PERFORM auth.create_user_group_mapping(
        (SELECT user_group_id FROM auth.user_group WHERE code = 'ENGINEERING'),
        v_ad_provider_id,
        'CN=Engineering,OU=Departments,DC=company,DC=com',
        NULL,
        20
    );

    -- Nested group patterns
    PERFORM auth.create_user_group_mapping(
        (SELECT user_group_id FROM auth.user_group WHERE code = 'MANAGERS'),
        v_ad_provider_id,
        'CN=.*,OU=Management,DC=company,DC=com',
        NULL,
        30
    );
END $$;

Azure Active Directory¶

-- Azure AD uses display names and object IDs
DO $$
DECLARE
    v_aad_provider_id uuid := (
        SELECT provider_id FROM auth.provider WHERE code = 'AZURE_AD'
    );
BEGIN
    -- Security groups by name
    PERFORM auth.create_user_group_mapping(
        (SELECT user_group_id FROM auth.user_group WHERE code = 'ALL_EMPLOYEES'),
        v_aad_provider_id,
        '^All Company$',
        NULL,
        10
    );

    -- Dynamic groups with patterns
    PERFORM auth.create_user_group_mapping(
        (SELECT user_group_id FROM auth.user_group WHERE code = 'ENGINEERING'),
        v_aad_provider_id,
        '^(Engineering|Development|DevOps).*',
        NULL,
        20
    );

    -- Role-based assignments
    PERFORM auth.create_user_group_mapping(
        (SELECT user_group_id FROM auth.user_group WHERE code = 'GLOBAL_ADMINS'),
        v_aad_provider_id,
        NULL,
        '^Global Administrator$',
        5  -- Highest priority
    );

    -- Application roles
    PERFORM auth.create_user_group_mapping(
        (SELECT user_group_id FROM auth.user_group WHERE code = 'APP_USERS'),
        v_aad_provider_id,
        NULL,
        'Application\\.User',  -- Escaped dot
        50
    );
END $$;

LDAP Directory Services¶

-- LDAP uses DN-based group membership
DO $$
DECLARE
    v_ldap_provider_id uuid := (
        SELECT provider_id FROM auth.provider WHERE code = 'COMPANY_LDAP'
    );
BEGIN
    -- Department-based groups
    PERFORM auth.create_user_group_mapping(
        (SELECT user_group_id FROM auth.user_group WHERE code = 'ENGINEERING'),
        v_ldap_provider_id,
        'cn=engineering,ou=departments,dc=company,dc=com',
        NULL,
        20
    );

    -- Functional groups
    PERFORM auth.create_user_group_mapping(
        (SELECT user_group_id FROM auth.user_group WHERE code = 'DEVELOPERS'),
        v_ldap_provider_id,
        'cn=developers,ou=roles,dc=company,dc=com',
        NULL,
        30
    );

    -- Manager group with role
    PERFORM auth.create_user_group_mapping(
        (SELECT user_group_id FROM auth.user_group WHERE code = 'MANAGERS'),
        v_ldap_provider_id,
        'cn=.*,ou=management,dc=company,dc=com',
        'manager',
        15
    );
END $$;

SAML Identity Providers¶

-- SAML uses attribute-based roles and groups
DO $$
DECLARE
    v_saml_provider_id uuid := (
        SELECT provider_id FROM auth.provider WHERE code = 'COMPANY_SAML'
    );
BEGIN
    -- Role attribute mappings
    PERFORM auth.create_user_group_mapping(
        (SELECT user_group_id FROM auth.user_group WHERE code = 'EMPLOYEES'),
        v_saml_provider_id,
        NULL,
        '^Employee$',
        10
    );

    PERFORM auth.create_user_group_mapping(
        (SELECT user_group_id FROM auth.user_group WHERE code = 'MANAGERS'),
        v_saml_provider_id,
        NULL,
        '^Manager$',
        20
    );

    PERFORM auth.create_user_group_mapping(
        (SELECT user_group_id FROM auth.user_group WHERE code = 'CONTRACTORS'),
        v_saml_provider_id,
        NULL,
        '^Contractor$',
        30
    );

    -- Group attribute mappings (if provider sends group info)
    PERFORM auth.create_user_group_mapping(
        (SELECT user_group_id FROM auth.user_group WHERE code = 'ENGINEERING'),
        v_saml_provider_id,
        '^Engineering.*',
        NULL,
        25
    );
END $$;

Advanced Mapping Scenarios¶

Priority-Based Resolution¶

-- Complex priority mapping for overlapping patterns
CREATE TABLE mapping_priorities_example AS
SELECT
    mapping_priority,
    provider_group_pattern,
    provider_role_pattern,
    user_group_code,
    'Higher number = lower priority' as note
FROM (
    VALUES
        -- Highest priority: Specific executive roles
        (1, NULL, '^(CEO|CTO|CFO)$', 'EXECUTIVES'),

        -- High priority: Department heads
        (10, '^.*-Department$', 'Head', 'DEPT_HEADS'),

        -- Medium priority: Managers
        (20, NULL, '.*Manager.*', 'MANAGERS'),

        -- Lower priority: Department members
        (50, '^Engineering-.*', NULL, 'ENGINEERING'),
        (50, '^Marketing-.*', NULL, 'MARKETING'),
        (50, '^Sales-.*', NULL, 'SALES'),

        -- Lowest priority: All employees
        (100, 'Domain Users', NULL, 'EMPLOYEES')
) AS priority_examples(mapping_priority, provider_group_pattern, provider_role_pattern, user_group_code);

Conditional Mappings with JSON¶

-- Advanced mappings with JSON conditions
INSERT INTO auth.user_group_mapping
    (user_group_id, provider_id, provider_group_pattern, conditions, mapping_priority)
VALUES
    -- Map only if user has specific attribute
    ((SELECT user_group_id FROM auth.user_group WHERE code = 'SENIOR_STAFF'),
     'azure-ad-provider-uuid',
     '^Engineering$',
     '{"required_attributes": {"employeeType": "FTE", "jobLevel": "Senior"}}',
     30),

    -- Map only if user is in multiple groups
    ((SELECT user_group_id FROM auth.user_group WHERE code = 'CROSS_FUNCTIONAL'),
     'azure-ad-provider-uuid',
     NULL,
     '{"required_groups": ["Engineering", "Product"], "match_type": "all"}',
     40),

    -- Time-based mapping
    ((SELECT user_group_id FROM auth.user_group WHERE code = 'TEMP_PROJECT'),
     'azure-ad-provider-uuid',
     '^Project-Alpha$',
     '{"valid_until": "2024-12-31", "valid_from": "2024-01-01"}',
     20);

Exclusion Patterns¶

-- Negative mappings (exclusions)
INSERT INTO auth.user_group_mapping
    (user_group_id, provider_id, provider_group_pattern, is_inclusive, mapping_priority)
VALUES
    -- Exclude contractors from full employee group
    ((SELECT user_group_id FROM auth.user_group WHERE code = 'EMPLOYEES'),
     'azure-ad-provider-uuid',
     '^Contractors$',
     false,  -- Exclusion mapping
     5),     -- High priority exclusion

    -- Exclude service accounts from user groups
    ((SELECT user_group_id FROM auth.user_group WHERE code = 'INTERACTIVE_USERS'),
     'azure-ad-provider-uuid',
     '^Service-Accounts$',
     false,
     1);     -- Highest priority exclusion

Mapping Resolution Logic¶

Resolution Algorithm¶

-- Comprehensive mapping resolution function
CREATE OR REPLACE FUNCTION auth.resolve_user_group_mappings(
    _user_id uuid,
    _provider_id uuid DEFAULT NULL  -- NULL = use last-used identity
) RETURNS TABLE (
    user_group_id uuid,
    group_code text,
    group_name text,
    mapping_source text,
    mapping_priority integer
) AS $$
DECLARE
    v_provider_id uuid;
    v_provider_groups text[];
    v_provider_roles text[];
BEGIN
    -- Get user's provider identity (last used if not specified)
    SELECT
        COALESCE(_provider_id, ui.provider_id),
        ui.provider_groups,
        ui.provider_roles
    INTO v_provider_id, v_provider_groups, v_provider_roles
    FROM auth.user_identity ui
    WHERE ui.user_id = _user_id
      AND (_provider_id IS NULL AND ui.is_last_used = true OR ui.provider_id = _provider_id)
      AND ui.is_active = true
    LIMIT 1;

    IF v_provider_id IS NULL THEN
        RETURN; -- No matching identity found
    END IF;

    -- Return mapped groups ordered by priority
    RETURN QUERY
    WITH mapping_matches AS (
        SELECT DISTINCT
            ugm.user_group_id,
            ugm.mapping_priority,
            ugm.is_inclusive,
            CASE
                WHEN ugm.provider_group_pattern IS NOT NULL AND ugm.provider_role_pattern IS NOT NULL
                    THEN 'group_and_role'
                WHEN ugm.provider_group_pattern IS NOT NULL
                    THEN 'group_pattern'
                WHEN ugm.provider_role_pattern IS NOT NULL
                    THEN 'role_pattern'
                ELSE 'unknown'
            END as mapping_type
        FROM auth.user_group_mapping ugm
        WHERE ugm.provider_id = v_provider_id
          AND ugm.is_active = true
          AND (
              -- Group pattern match
              (ugm.provider_group_pattern IS NOT NULL
               AND EXISTS (
                   SELECT 1 FROM unnest(v_provider_groups) AS pg(group_name)
                   WHERE pg.group_name ~ ugm.provider_group_pattern
               ))
              OR
              -- Role pattern match
              (ugm.provider_role_pattern IS NOT NULL
               AND EXISTS (
                   SELECT 1 FROM unnest(v_provider_roles) AS pr(role_name)
                   WHERE pr.role_name ~ ugm.provider_role_pattern
               ))
          )
    ),
    resolved_groups AS (
        SELECT
            mm.user_group_id,
            mm.mapping_priority,
            mm.mapping_type,
            -- Apply inclusion/exclusion logic
            CASE
                WHEN NOT mm.is_inclusive THEN false
                ELSE NOT EXISTS (
                    SELECT 1 FROM mapping_matches excl
                    WHERE excl.user_group_id = mm.user_group_id
                      AND excl.is_inclusive = false
                      AND excl.mapping_priority <= mm.mapping_priority
                )
            END as include_group
        FROM mapping_matches mm
        WHERE mm.is_inclusive = true
    )
    SELECT
        rg.user_group_id,
        ug.code,
        ug.name,
        rg.mapping_type,
        rg.mapping_priority
    FROM resolved_groups rg
    JOIN auth.user_group ug ON rg.user_group_id = ug.user_group_id
    WHERE rg.include_group = true
      AND ug.is_active = true
    ORDER BY rg.mapping_priority, ug.code;
END;
$$ LANGUAGE plpgsql;

-- Usage examples
SELECT * FROM auth.resolve_user_group_mappings('user-uuid');
SELECT * FROM auth.resolve_user_group_mappings('user-uuid', 'azure-ad-provider-uuid');

Mapping Management Functions¶

Create Mapping with Validation¶

CREATE OR REPLACE FUNCTION auth.create_user_group_mapping_validated(
    _user_group_id uuid,
    _provider_id uuid,
    _provider_group_pattern text DEFAULT NULL,
    _provider_role_pattern text DEFAULT NULL,
    _mapping_priority integer DEFAULT 100,
    _conditions jsonb DEFAULT NULL
) RETURNS uuid AS $$
DECLARE
    v_mapping_id uuid;
    v_group_type text;
BEGIN
    -- Validate group type supports mappings
    SELECT group_type INTO v_group_type
    FROM auth.user_group
    WHERE user_group_id = _user_group_id;

    IF v_group_type NOT IN ('external', 'hybrid') THEN
        RAISE EXCEPTION 'Group mappings only supported for external and hybrid groups, not %', v_group_type;
    END IF;

    -- Validate pattern requirements
    IF _provider_group_pattern IS NULL AND _provider_role_pattern IS NULL THEN
        RAISE EXCEPTION 'Either provider_group_pattern or provider_role_pattern must be specified';
    END IF;

    -- Create mapping
    INSERT INTO auth.user_group_mapping
        (user_group_id, provider_id, provider_group_pattern, provider_role_pattern,
         mapping_priority, conditions)
    VALUES
        (_user_group_id, _provider_id, _provider_group_pattern, _provider_role_pattern,
         _mapping_priority, _conditions)
    RETURNING user_group_mapping_id INTO v_mapping_id;

    -- Log creation
    PERFORM auth.create_auth_event(
        NULL, -- System event
        NULL,
        '50050', -- MAPPING_CREATED
        'Group mapping created',
        jsonb_build_object(
            'mapping_id', v_mapping_id,
            'group_id', _user_group_id,
            'provider_id', _provider_id
        )
    );

    RETURN v_mapping_id;
END;
$$ LANGUAGE plpgsql;

Bulk Mapping Operations¶

-- Create multiple mappings from configuration
CREATE OR REPLACE FUNCTION auth.create_mappings_from_config(
    _mapping_config jsonb
) RETURNS integer AS $$
DECLARE
    v_mapping jsonb;
    v_count integer := 0;
    v_group_id uuid;
    v_provider_id uuid;
BEGIN
    FOR v_mapping IN SELECT jsonb_array_elements(_mapping_config)
    LOOP
        -- Get group and provider IDs
        SELECT user_group_id INTO v_group_id
        FROM auth.user_group
        WHERE code = v_mapping->>'group_code'
          AND tenant_id = (v_mapping->>'tenant_id')::uuid;

        SELECT provider_id INTO v_provider_id
        FROM auth.provider
        WHERE code = v_mapping->>'provider_code';

        IF v_group_id IS NOT NULL AND v_provider_id IS NOT NULL THEN
            PERFORM auth.create_user_group_mapping_validated(
                v_group_id,
                v_provider_id,
                v_mapping->>'group_pattern',
                v_mapping->>'role_pattern',
                COALESCE((v_mapping->>'priority')::integer, 100),
                v_mapping->'conditions'
            );
            v_count := v_count + 1;
        END IF;
    END LOOP;

    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

-- Usage example
SELECT auth.create_mappings_from_config('[
    {
        "tenant_id": "tenant-uuid",
        "group_code": "ENGINEERING",
        "provider_code": "AZURE_AD",
        "group_pattern": "^Engineering.*",
        "priority": 50
    },
    {
        "tenant_id": "tenant-uuid",
        "group_code": "MANAGERS",
        "provider_code": "AZURE_AD",
        "role_pattern": ".*Manager.*",
        "priority": 30
    }
]'::jsonb);

Mapping Validation and Testing¶

Test Mapping Patterns¶

-- Test mapping patterns against sample data
CREATE OR REPLACE FUNCTION auth.test_group_mappings(
    _provider_id uuid,
    _test_groups text[],
    _test_roles text[]
) RETURNS TABLE (
    user_group_code text,
    user_group_name text,
    matched_by text,
    mapping_priority integer
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        ug.code,
        ug.name,
        CASE
            WHEN ugm.provider_group_pattern IS NOT NULL AND ugm.provider_role_pattern IS NOT NULL
                THEN 'Group: ' || ugm.provider_group_pattern || ' + Role: ' || ugm.provider_role_pattern
            WHEN ugm.provider_group_pattern IS NOT NULL
                THEN 'Group: ' || ugm.provider_group_pattern
            WHEN ugm.provider_role_pattern IS NOT NULL
                THEN 'Role: ' || ugm.provider_role_pattern
        END as matched_by,
        ugm.mapping_priority
    FROM auth.user_group_mapping ugm
    JOIN auth.user_group ug ON ugm.user_group_id = ug.user_group_id
    WHERE ugm.provider_id = _provider_id
      AND ugm.is_active = true
      AND (
          (ugm.provider_group_pattern IS NOT NULL
           AND EXISTS (
               SELECT 1 FROM unnest(_test_groups) AS tg(group_name)
               WHERE tg.group_name ~ ugm.provider_group_pattern
           ))
          OR
          (ugm.provider_role_pattern IS NOT NULL
           AND EXISTS (
               SELECT 1 FROM unnest(_test_roles) AS tr(role_name)
               WHERE tr.role_name ~ ugm.provider_role_pattern
           ))
      )
    ORDER BY ugm.mapping_priority, ug.code;
END;
$$ LANGUAGE plpgsql;

-- Test with sample data
SELECT * FROM auth.test_group_mappings(
    'azure-ad-provider-uuid',
    ARRAY['Engineering', 'Engineering-Frontend', 'Project-Alpha'],
    ARRAY['Senior Developer', 'Team Manager']
);

Mapping Coverage Analysis¶

-- Analyze mapping coverage across providers
WITH mapping_stats AS (
    SELECT
        p.code as provider_code,
        p.name as provider_name,
        COUNT(ugm.user_group_mapping_id) as total_mappings,
        COUNT(ugm.user_group_mapping_id) FILTER (
            WHERE ugm.provider_group_pattern IS NOT NULL
        ) as group_pattern_mappings,
        COUNT(ugm.user_group_mapping_id) FILTER (
            WHERE ugm.provider_role_pattern IS NOT NULL
        ) as role_pattern_mappings,
        COUNT(DISTINCT ugm.user_group_id) as mapped_groups
    FROM auth.provider p
    LEFT JOIN auth.user_group_mapping ugm ON p.provider_id = ugm.provider_id
        AND ugm.is_active = true
    GROUP BY p.provider_id
),
group_coverage AS (
    SELECT
        ug.group_type,
        COUNT(*) as total_groups,
        COUNT(ugm.user_group_id) as mapped_groups
    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
    WHERE ug.is_active = true
      AND ug.group_type IN ('external', 'hybrid')
    GROUP BY ug.group_type
)
SELECT
    'Provider Analysis' as analysis_type,
    provider_code as detail,
    total_mappings::text as value1,
    mapped_groups::text as value2
FROM mapping_stats

UNION ALL

SELECT
    'Group Coverage' as analysis_type,
    group_type as detail,
    (mapped_groups::float / total_groups * 100)::integer::text || '%' as value1,
    mapped_groups::text || '/' || total_groups::text as value2
FROM group_coverage

ORDER BY analysis_type, detail;

Best Practices for Group Mappings¶

Design Principles¶

Clear Patterns: Use specific, unambiguous regex patterns
Priority Management: Lower numbers = higher priority, use gaps (10, 20, 30)
Documentation: Comment complex patterns and their business logic
Testing: Always test patterns with sample data before deployment

Mapping Strategy Templates¶

-- Template: Department-based mapping
DO $$
DECLARE
    v_tenant_id uuid := 'tenant-uuid';
    v_provider_id uuid := 'provider-uuid';
    v_dept_groups text[] := ARRAY['Engineering', 'Marketing', 'Sales', 'HR', 'Finance'];
    v_dept text;
    v_group_id uuid;
BEGIN
    FOREACH v_dept IN ARRAY v_dept_groups
    LOOP
        -- Create department group if not exists
        INSERT INTO auth.user_group (tenant_id, code, name, group_type)
        VALUES (v_tenant_id, upper(v_dept), v_dept || ' Department', 'external')
        ON CONFLICT (tenant_id, code) DO NOTHING
        RETURNING user_group_id INTO v_group_id;

        IF v_group_id IS NULL THEN
            SELECT user_group_id INTO v_group_id
            FROM auth.user_group
            WHERE tenant_id = v_tenant_id AND code = upper(v_dept);
        END IF;

        -- Create mapping
        PERFORM auth.create_user_group_mapping_validated(
            v_group_id,
            v_provider_id,
            '^' || v_dept || '.*',  -- Group pattern
            NULL,
            50  -- Standard priority
        );
    END LOOP;
END $$;

Security Considerations¶

-- Secure mapping validation
CREATE OR REPLACE FUNCTION auth.validate_mapping_security(
    _user_group_mapping_id uuid
) RETURNS boolean AS $$
DECLARE
    v_mapping record;
    v_issues text[] := ARRAY[]::text[];
BEGIN
    SELECT * INTO v_mapping
    FROM auth.user_group_mapping ugm
    JOIN auth.user_group ug ON ugm.user_group_id = ug.user_group_id
    JOIN auth.provider p ON ugm.provider_id = p.provider_id
    WHERE ugm.user_group_mapping_id = _user_group_mapping_id;

    -- Check for overly broad patterns
    IF v_mapping.provider_group_pattern ~ '^\.\*' THEN
        v_issues := v_issues || 'Pattern too broad - starts with .*';
    END IF;

    IF v_mapping.provider_role_pattern ~ '^\.\*' THEN
        v_issues := v_issues || 'Role pattern too broad - starts with .*';
    END IF;

    -- Check for dangerous regex
    IF v_mapping.provider_group_pattern ~ '.*\(\.\*\).*' THEN
        v_issues := v_issues || 'Potentially dangerous regex capturing';
    END IF;

    -- Log issues if found
    IF array_length(v_issues, 1) > 0 THEN
        PERFORM auth.create_auth_event(
            NULL, NULL, '50051', 'Mapping security validation failed',
            jsonb_build_object('mapping_id', _user_group_mapping_id, 'issues', v_issues)
        );
        RETURN false;
    END IF;

    RETURN true;
END;
$$ LANGUAGE plpgsql;

Troubleshooting Group Mappings¶

Common Issues and Solutions¶

-- Diagnostic queries for mapping issues

-- 1. Users not getting expected group membership
WITH user_mapping_debug AS (
    SELECT
        u.username,
        u.display_name,
        ui.provider_groups,
        ui.provider_roles,
        p.name as provider_name,
        ugm.provider_group_pattern,
        ugm.provider_role_pattern,
        ug.code as group_code,
        CASE
            WHEN ugm.provider_group_pattern IS NOT NULL THEN
                EXISTS (
                    SELECT 1 FROM unnest(ui.provider_groups) AS pg(name)
                    WHERE pg.name ~ ugm.provider_group_pattern
                )
            ELSE NULL
        END as group_pattern_matches,
        CASE
            WHEN ugm.provider_role_pattern IS NOT NULL THEN
                EXISTS (
                    SELECT 1 FROM unnest(ui.provider_roles) AS pr(name)
                    WHERE pr.name ~ ugm.provider_role_pattern
                )
            ELSE NULL
        END as role_pattern_matches
    FROM auth.user_info u
    JOIN auth.user_identity ui ON u.user_id = ui.user_id
    JOIN auth.provider p ON ui.provider_id = p.provider_id
    CROSS JOIN auth.user_group_mapping ugm
    JOIN auth.user_group ug ON ugm.user_group_id = ug.user_group_id
    WHERE u.username = 'debug-username'  -- Specify user to debug
      AND ui.is_last_used = true
      AND ui.is_active = true
      AND ugm.is_active = true
)
SELECT
    username,
    provider_name,
    group_code,
    provider_group_pattern,
    group_pattern_matches,
    provider_role_pattern,
    role_pattern_matches,
    CASE
        WHEN provider_group_pattern IS NOT NULL AND provider_role_pattern IS NOT NULL
            THEN group_pattern_matches AND role_pattern_matches
        WHEN provider_group_pattern IS NOT NULL
            THEN group_pattern_matches
        WHEN provider_role_pattern IS NOT NULL
            THEN role_pattern_matches
        ELSE false
    END as should_match
FROM user_mapping_debug
ORDER BY group_code;

-- 2. Find unused mappings
SELECT
    ug.code as group_code,
    ugm.provider_group_pattern,
    ugm.provider_role_pattern,
    COUNT(ui.user_id) as matching_users
FROM auth.user_group_mapping ugm
JOIN auth.user_group ug ON ugm.user_group_id = ug.user_group_id
LEFT JOIN auth.user_identity ui ON ugm.provider_id = ui.provider_id
    AND ui.is_last_used = true
    AND ui.is_active = true
    AND (
        (ugm.provider_group_pattern IS NOT NULL
         AND EXISTS (
             SELECT 1 FROM unnest(ui.provider_groups) AS pg(name)
             WHERE pg.name ~ ugm.provider_group_pattern
         ))
        OR
        (ugm.provider_role_pattern IS NOT NULL
         AND EXISTS (
             SELECT 1 FROM unnest(ui.provider_roles) AS pr(name)
             WHERE pr.name ~ ugm.provider_role_pattern
         ))
    )
WHERE ugm.is_active = true
GROUP BY ugm.user_group_mapping_id, ug.code, ugm.provider_group_pattern, ugm.provider_role_pattern
HAVING COUNT(ui.user_id) = 0
ORDER BY ug.code;

-- 3. Conflicting mappings (same priority)
SELECT
    ug1.code as group1,
    ug2.code as group2,
    ugm1.mapping_priority,
    ugm1.provider_group_pattern,
    ugm1.provider_role_pattern,
    'Same priority conflict' as issue
FROM auth.user_group_mapping ugm1
JOIN auth.user_group_mapping ugm2 ON ugm1.provider_id = ugm2.provider_id
    AND ugm1.mapping_priority = ugm2.mapping_priority
    AND ugm1.user_group_mapping_id < ugm2.user_group_mapping_id
JOIN auth.user_group ug1 ON ugm1.user_group_id = ug1.user_group_id
JOIN auth.user_group ug2 ON ugm2.user_group_id = ug2.user_group_id
WHERE ugm1.is_active = true
  AND ugm2.is_active = true
ORDER BY ugm1.mapping_priority;

What's Next¶

Review Internal Groups for direct membership management
Learn about External Groups for provider-only membership
Explore Hybrid Groups for combined membership models
Understand Permission Assignment patterns