Group Types Overview¶

The PostgreSQL Permissions Model supports three distinct group types to accommodate various authentication and authorization scenarios. This flexible approach allows integration with any identity provider while maintaining internal group management capabilities.

Three Group Models¶

1. Internal Groups¶

Traditional database-managed groups where membership is explicitly stored and managed within the PostgreSQL database.

Use Cases: - Application-specific roles - Custom business groups - Manual user assignment - Groups independent of external systems

2. External Groups¶

Groups where membership is determined entirely by external identity provider mappings. No direct membership is stored in the database.

Use Cases: - Active Directory integration - Azure AD group synchronization - LDAP group mapping - SSO provider groups (Okta, Auth0, KeyCloak)

3. Hybrid Groups¶

Combines both internal and external membership models. Users can be added directly OR inherit membership through external mappings.

Use Cases: - Gradual migration from internal to external - Override external groups with exceptions - Temporary access additions - Mixed authentication environments

Database Structure¶

Core Group Table¶

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 CHECK (group_type IN ('internal', 'external', 'hybrid')),
    is_active boolean DEFAULT true,
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now(),
    UNIQUE (tenant_id, code)
);

Membership Tables¶

-- Internal/Hybrid group membership
CREATE TABLE auth.user_group_member (
    user_group_id uuid REFERENCES auth.user_group,
    user_id uuid REFERENCES auth.user_info,
    added_at timestamptz DEFAULT now(),
    added_by uuid REFERENCES auth.user_info,
    PRIMARY KEY (user_group_id, user_id)
);

-- External group mappings
CREATE TABLE auth.user_group_mapping (
    mapping_id uuid PRIMARY KEY,
    user_group_id uuid REFERENCES auth.user_group,
    provider_id uuid REFERENCES auth.provider,
    external_group_name text,
    external_role_name text,
    is_active boolean DEFAULT true
);

How Group Types Work¶

Internal Groups Flow¶

graph LR
    A[User] --> B[Internal Group]
    B --> C[Direct Membership]
    C --> D[Permissions]

Admin explicitly adds user to group
Membership stored in user_group_member
User inherits group permissions
Membership persists until explicitly removed

External Groups Flow¶

graph LR
    A[User Login] --> B[Identity Provider]
    B --> C[Provider Groups/Roles]
    C --> D[Group Mapping]
    D --> E[Internal Permissions]

User logs in via external provider
Provider returns groups/roles in token
System maps external groups to internal groups
User gets permissions without stored membership

Hybrid Groups Flow¶

graph LR
    A[User] --> B[Hybrid Group]
    B --> C[Direct Member?]
    C -->|Yes| D[Has Access]
    C -->|No| E[Check External]
    E --> F[External Mapping?]
    F -->|Yes| D
    F -->|No| G[No Access]

Creating Different Group Types¶

Create Internal Group¶

-- Create internal group
SELECT auth.create_user_group(
    _tenant_id := 'tenant-uuid',
    _code := 'DEVELOPERS',
    _name := 'Development Team',
    _description := 'Internal development team members',
    _group_type := 'internal'
);

-- Add members directly
SELECT auth.create_user_group_member(
    _user_group_id := 'group-uuid',
    _user_id := 'user-uuid'
);

Create External Group¶

-- Create external group
SELECT auth.create_external_user_group(
    _tenant_id := 'tenant-uuid',
    _code := 'AD_ADMINS',
    _name := 'Active Directory Administrators',
    _description := 'Synced from AD admin group'
);

-- Map external groups
SELECT auth.create_user_group_mapping(
    _user_group_id := 'group-uuid',
    _provider_id := 'azure-ad-provider-uuid',
    _external_group_name := 'Domain Admins',
    _external_role_name := NULL
);

Create Hybrid Group¶

-- Create hybrid group
SELECT auth.create_user_group(
    _tenant_id := 'tenant-uuid',
    _code := 'SUPPORT_TEAM',
    _name := 'Customer Support',
    _group_type := 'hybrid'
);

-- Add direct members
SELECT auth.create_user_group_member(
    _user_group_id := 'group-uuid',
    _user_id := 'user-uuid'
);

-- Also map external groups
SELECT auth.create_user_group_mapping(
    _user_group_id := 'group-uuid',
    _provider_id := 'provider-uuid',
    _external_group_name := 'HelpDesk'
);

Converting Between Group Types¶

Internal to External¶

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

External to Hybrid¶

-- Convert external group to hybrid
SELECT auth.set_user_group_as_hybrid(
    _user_group_id := 'group-uuid'
);
-- Now can add direct members while keeping mappings

Any to Internal¶

-- Convert to internal only
UPDATE auth.user_group
SET group_type = 'internal'
WHERE user_group_id = 'group-uuid';

-- Remove external mappings
DELETE FROM auth.user_group_mapping
WHERE user_group_id = 'group-uuid';

Permission Resolution by Group Type¶

Check User's Effective Groups¶

-- Get all groups for a user (internal + external)
WITH user_groups AS (
    -- Internal/Hybrid direct membership
    SELECT
        ug.user_group_id,
        ug.code,
        ug.name,
        'direct' as membership_source
    FROM auth.user_group ug
    JOIN auth.user_group_member ugm ON ug.user_group_id = ugm.user_group_id
    WHERE ugm.user_id = 'user-uuid'
      AND ug.group_type IN ('internal', 'hybrid')

    UNION

    -- External/Hybrid mapped membership
    SELECT
        ug.user_group_id,
        ug.code,
        ug.name,
        'mapped' as membership_source
    FROM auth.user_group ug
    JOIN auth.user_group_mapping ugma ON ug.user_group_id = ugma.user_group_id
    JOIN auth.user_identity ui ON ugma.provider_id = ui.provider_id
    WHERE ui.user_id = 'user-uuid'
      AND ui.is_last_used = true
      AND ug.group_type IN ('external', 'hybrid')
      AND (
          ugma.external_group_name = ANY(ui.provider_groups) OR
          ugma.external_role_name = ANY(ui.provider_roles)
      )
)
SELECT * FROM user_groups;

Choosing the Right Group Type¶

Decision Matrix¶

Scenario	Recommended Type	Reason
Pure database application	Internal	Full control, no external dependencies
Enterprise with AD/LDAP	External	Leverage existing infrastructure
SaaS with multiple auth methods	Hybrid	Flexibility for different customers
Migration from legacy system	Hybrid	Gradual transition capability
API-only services	Internal	Simple programmatic management
SSO-only authentication	External	No duplicate user management

Best Practices by Type¶

Internal Groups: - Document group purposes clearly - Implement approval workflows - Regular membership audits - Clear naming conventions

External Groups: - Map at appropriate granularity - Handle provider outages gracefully - Cache mappings for performance - Regular sync validation

Hybrid Groups: - Clear documentation of membership sources - Precedence rules for conflicts - Audit both membership types - Monitor for orphaned members

Performance Considerations¶

Internal Groups¶

Direct database lookups
Best performance for permission checks
No external dependencies

External Groups¶

Requires provider group resolution
May need caching strategy
Network latency considerations

Hybrid Groups¶

Most complex resolution logic
Balance flexibility vs performance
Consider materialized views for heavy usage

Migration Strategies¶

Moving from Internal to External¶

-- Prepare migration
CREATE TEMP TABLE migration_map AS
SELECT
    ugm.user_group_id,
    ugm.user_id,
    ui.provider_groups,
    ui.provider_roles
FROM auth.user_group_member ugm
JOIN auth.user_identity ui ON ugm.user_id = ui.user_id
WHERE ui.is_last_used = true;

-- Create mappings based on analysis
INSERT INTO auth.user_group_mapping
    (user_group_id, provider_id, external_group_name)
SELECT DISTINCT
    user_group_id,
    'provider-uuid',
    unnest(provider_groups)
FROM migration_map;

-- Convert groups to external
UPDATE auth.user_group
SET group_type = 'external'
WHERE user_group_id IN (SELECT DISTINCT user_group_id FROM migration_map);

Common Patterns¶

Department Groups (Internal)¶

CREATE_INTERNAL_GROUP('FINANCE', 'Finance Department');
CREATE_INTERNAL_GROUP('MARKETING', 'Marketing Department');

Provider Groups (External)¶

CREATE_EXTERNAL_GROUP('AZURE_USERS', 'Azure AD Users');
MAP_EXTERNAL_GROUP('AZURE_USERS', 'azure-ad', 'Domain Users');

Override Groups (Hybrid)¶

CREATE_HYBRID_GROUP('SPECIAL_ACCESS', 'Special Access Users');
-- Mostly external, but can add exceptions

What's Next¶

Deep dive into Internal Groups
Explore External Groups configuration
Learn about Hybrid Groups patterns
Understand Group Mappings in detail