Internal Groups¶

Internal Groups are the traditional database-managed group model where membership is explicitly stored and managed within the PostgreSQL database. This approach provides complete control over group membership and is ideal for application-specific roles and business groups.

Overview¶

Internal Groups use direct database storage for membership management:

graph LR
    subgraph "Internal Group Model"
        IG[Internal Group<br/>auth.user_group<br/>group_type = 'internal']
        GM[Group Members<br/>auth.user_group_member<br/>Direct membership storage]

        U1[User 1]
        U2[User 2]
        U3[User 3]
    end

    GM --> U1
    GM --> U2
    GM --> U3
    IG --> GM

Key Characteristics: - Membership stored in auth.user_group_member table - Complete administrative control - No dependency on external systems - Immediate membership changes - Perfect for application-specific roles

Database Structure¶

Core Tables¶

-- Internal group definition
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 'internal',
    is_active boolean DEFAULT true,
    created_at timestamptz DEFAULT now(),
    UNIQUE (tenant_id, code)
);

-- Direct membership storage
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,
    is_active boolean DEFAULT true,
    PRIMARY KEY (user_group_id, user_id)
);

Creating Internal Groups¶

Basic Group Creation¶

-- Create internal group
SELECT auth.create_user_group(
    _tenant_id := 'tenant-uuid',
    _code := 'DEVELOPERS',
    _name := 'Development Team',
    _description := 'Software developers and engineers',
    _group_type := 'internal'
);
-- Returns: user_group_id (uuid)

-- Simpler creation (defaults to internal)
SELECT auth.create_user_group(
    'tenant-uuid',
    'MANAGERS',
    'Management Team',
    'Department managers and team leads'
);

Common Internal Group Examples¶

-- Department-based groups
SELECT auth.create_user_group(
    'tenant-uuid', 'ENGINEERING', 'Engineering Department',
    'All engineering staff'
);

SELECT auth.create_user_group(
    'tenant-uuid', 'MARKETING', 'Marketing Department',
    'Marketing and communications team'
);

SELECT auth.create_user_group(
    'tenant-uuid', 'FINANCE', 'Finance Department',
    'Accounting and financial analysts'
);

-- Role-based groups
SELECT auth.create_user_group(
    'tenant-uuid', 'TEAM_LEADS', 'Team Leaders',
    'Team leads across all departments'
);

SELECT auth.create_user_group(
    'tenant-uuid', 'SENIOR_STAFF', 'Senior Staff',
    'Senior level employees'
);

-- Function-based groups
SELECT auth.create_user_group(
    'tenant-uuid', 'APPROVERS', 'Expense Approvers',
    'Users who can approve expenses'
);

Managing Group Membership¶

Adding Members¶

-- Add single member to group
SELECT auth.create_user_group_member(
    _user_group_id := 'group-uuid',
    _user_id := 'user-uuid',
    _added_by := 'admin-user-uuid'  -- Who added them
);

-- Add multiple members
DO $$
DECLARE
    v_group_id uuid := 'developers-group-uuid';
    v_admin_id uuid := 'admin-user-uuid';
    v_user_ids uuid[] := ARRAY[
        'john-uuid', 'jane-uuid', 'bob-uuid'
    ];
    v_user_id uuid;
BEGIN
    FOREACH v_user_id IN ARRAY v_user_ids
    LOOP
        PERFORM auth.create_user_group_member(v_group_id, v_user_id, v_admin_id);
    END LOOP;
END $$;

Removing Members¶

-- Remove member from group (soft delete)
SELECT auth.deactivate_user_group_member(
    _user_group_id := 'group-uuid',
    _user_id := 'user-uuid'
);

-- Hard delete member
SELECT auth.delete_user_group_member(
    _user_group_id := 'group-uuid',
    _user_id := 'user-uuid'
);

Bulk Operations¶

-- Add all users from one group to another
INSERT INTO auth.user_group_member (user_group_id, user_id, added_by)
SELECT
    'target-group-uuid',
    ugm.user_id,
    'admin-user-uuid'
FROM auth.user_group_member ugm
WHERE ugm.user_group_id = 'source-group-uuid'
  AND ugm.is_active = true
ON CONFLICT (user_group_id, user_id) DO NOTHING;

-- Move all members from one group to another
UPDATE auth.user_group_member
SET user_group_id = 'new-group-uuid'
WHERE user_group_id = 'old-group-uuid';

Group Administration¶

Update Group Information¶

-- Update group details
SELECT auth.update_user_group(
    _user_group_id := 'group-uuid',
    _name := 'Updated Group Name',
    _description := 'Updated description with more details'
);

-- Activate/deactivate group
UPDATE auth.user_group
SET is_active = false
WHERE user_group_id = 'group-uuid';

Group Hierarchy¶

Internal groups can reference other groups for hierarchical structures:

-- Create parent-child group relationships
CREATE TABLE auth.user_group_hierarchy (
    parent_group_id uuid REFERENCES auth.user_group(user_group_id),
    child_group_id uuid REFERENCES auth.user_group(user_group_id),
    created_at timestamptz DEFAULT now(),
    PRIMARY KEY (parent_group_id, child_group_id)
);

-- Example: Managers group contains Team Leads
INSERT INTO auth.user_group_hierarchy (parent_group_id, child_group_id)
VALUES (
    (SELECT user_group_id FROM auth.user_group WHERE code = 'MANAGERS'),
    (SELECT user_group_id FROM auth.user_group WHERE code = 'TEAM_LEADS')
);

Permission Assignment to Internal Groups¶

Assign Permission Sets¶

-- Assign permission set to internal group
SELECT auth.assign_permission(
    _tenant_id := 'tenant-uuid',
    _user_id := NULL,
    _user_group_id := 'developers-group-uuid',
    _perm_set_code := 'DEVELOPER_PERMISSIONS',
    _perm_code := NULL
);

-- All group members inherit these permissions

Assign Individual Permissions¶

-- Assign specific permission to group
SELECT auth.assign_permission(
    _tenant_id := 'tenant-uuid',
    _user_id := NULL,
    _user_group_id := 'approvers-group-uuid',
    _perm_set_code := NULL,
    _perm_code := 'expenses.approve'
);

Multiple Permission Assignment¶

-- Assign multiple permission sets to group
DO $$
DECLARE
    v_group_id uuid := (
        SELECT user_group_id FROM auth.user_group
        WHERE code = 'SENIOR_STAFF' AND tenant_id = 'tenant-uuid'
    );
    v_permission_sets text[] := ARRAY['BASE_USER', 'ADVANCED_FEATURES', 'REPORTING'];
    v_perm_set text;
BEGIN
    FOREACH v_perm_set IN ARRAY v_permission_sets
    LOOP
        PERFORM auth.assign_permission(
            'tenant-uuid', NULL, v_group_id, v_perm_set, NULL
        );
    END LOOP;
END $$;

Querying Internal Groups¶

Group Membership¶

-- Get all members of a group
SELECT
    u.user_id,
    u.username,
    u.display_name,
    ugm.added_at,
    ugm.added_by,
    adder.display_name as added_by_name
FROM auth.user_group_member ugm
JOIN auth.user_info u ON ugm.user_id = u.user_id
LEFT JOIN auth.user_info adder ON ugm.added_by = adder.user_id
WHERE ugm.user_group_id = 'group-uuid'
  AND ugm.is_active = true
ORDER BY ugm.added_at;

User's Group Memberships¶

-- Get all groups a user belongs to
SELECT
    ug.code,
    ug.name,
    ug.description,
    ugm.added_at
FROM auth.user_group_member ugm
JOIN auth.user_group ug ON ugm.user_group_id = ug.user_group_id
WHERE ugm.user_id = 'user-uuid'
  AND ugm.is_active = true
  AND ug.is_active = true
ORDER BY ug.name;

Group Statistics¶

-- Group membership statistics
SELECT
    ug.code,
    ug.name,
    COUNT(ugm.user_id) as member_count,
    COUNT(ugm.user_id) FILTER (WHERE ugm.added_at > now() - interval '30 days') as recent_additions,
    MIN(ugm.added_at) as first_member_added,
    MAX(ugm.added_at) as last_member_added
FROM auth.user_group ug
LEFT JOIN auth.user_group_member ugm ON ug.user_group_id = ugm.user_group_id
    AND ugm.is_active = true
WHERE ug.tenant_id = 'tenant-uuid'
  AND ug.group_type = 'internal'
  AND ug.is_active = true
GROUP BY ug.user_group_id
ORDER BY member_count DESC;

Common Internal Group Patterns¶

Department-Based Access Control¶

-- Create department groups with appropriate permissions
DO $$
DECLARE
    v_tenant_id uuid := 'tenant-uuid';
    v_dept_groups record;
BEGIN
    -- Create department groups
    FOR v_dept_groups IN
        SELECT * FROM (VALUES
            ('HR', 'Human Resources', 'HR_PERMISSIONS'),
            ('IT', 'Information Technology', 'IT_ADMIN'),
            ('SALES', 'Sales Team', 'SALES_PERMISSIONS'),
            ('SUPPORT', 'Customer Support', 'SUPPORT_PERMISSIONS')
        ) AS depts(code, name, perm_set)
    LOOP
        -- Create group
        PERFORM auth.create_user_group(
            v_tenant_id,
            v_dept_groups.code,
            v_dept_groups.name,
            v_dept_groups.name || ' department members'
        );

        -- Assign permission set
        PERFORM auth.assign_permission(
            v_tenant_id,
            NULL,
            (SELECT user_group_id FROM auth.user_group
             WHERE code = v_dept_groups.code AND tenant_id = v_tenant_id),
            v_dept_groups.perm_set,
            NULL
        );
    END LOOP;
END $$;

Role-Based Groups¶

-- Create role-based groups with hierarchical permissions
DO $$
DECLARE
    v_tenant_id uuid := 'tenant-uuid';
BEGIN
    -- Admin group (highest level)
    PERFORM auth.create_user_group(
        v_tenant_id, 'ADMINS', 'Administrators', 'System administrators'
    );
    PERFORM auth.assign_permission(
        v_tenant_id, NULL,
        (SELECT user_group_id FROM auth.user_group WHERE code = 'ADMINS'),
        'FULL_ADMIN', NULL
    );

    -- Manager group (mid level)
    PERFORM auth.create_user_group(
        v_tenant_id, 'MANAGERS', 'Managers', 'Department managers'
    );
    PERFORM auth.assign_permission(
        v_tenant_id, NULL,
        (SELECT user_group_id FROM auth.user_group WHERE code = 'MANAGERS'),
        'MANAGER_PERMISSIONS', NULL
    );

    -- Employee group (base level)
    PERFORM auth.create_user_group(
        v_tenant_id, 'EMPLOYEES', 'Employees', 'All employees'
    );
    PERFORM auth.assign_permission(
        v_tenant_id, NULL,
        (SELECT user_group_id FROM auth.user_group WHERE code = 'EMPLOYEES'),
        'BASIC_USER', NULL
    );
END $$;

Project-Based Groups¶

-- Dynamic project groups
CREATE OR REPLACE FUNCTION create_project_group(
    _tenant_id uuid,
    _project_code text,
    _project_name text,
    _project_lead_user_id uuid
) RETURNS uuid AS $$
DECLARE
    v_group_id uuid;
BEGIN
    -- Create project group
    v_group_id := auth.create_user_group(
        _tenant_id,
        'PROJECT_' || upper(_project_code),
        'Project: ' || _project_name,
        'Members of project ' || _project_name
    );

    -- Add project lead
    PERFORM auth.create_user_group_member(
        v_group_id, _project_lead_user_id, _project_lead_user_id
    );

    -- Assign project permissions
    PERFORM auth.assign_permission(
        _tenant_id, NULL, v_group_id, 'PROJECT_MEMBER', NULL
    );

    RETURN v_group_id;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT create_project_group(
    'tenant-uuid',
    'ALPHA',
    'Project Alpha',
    'project-lead-user-uuid'
);

Best Practices for Internal Groups¶

Group Design Principles¶

Clear Purpose: Each group should have a specific, well-defined purpose
Meaningful Names: Use descriptive codes and names
Appropriate Granularity: Not too broad, not too narrow
Document Membership Criteria: Clear rules for who belongs

Membership Management¶

-- Audit group membership changes
CREATE TABLE auth.user_group_member_audit (
    audit_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_group_id uuid,
    user_id uuid,
    action text, -- 'ADDED', 'REMOVED', 'ACTIVATED', 'DEACTIVATED'
    performed_by uuid REFERENCES auth.user_info(user_id),
    performed_at timestamptz DEFAULT now(),
    reason text
);

-- Function to audit membership changes
CREATE OR REPLACE FUNCTION audit_group_membership_change(
    _user_group_id uuid,
    _user_id uuid,
    _action text,
    _performed_by uuid,
    _reason text DEFAULT NULL
) RETURNS void AS $$
BEGIN
    INSERT INTO auth.user_group_member_audit
        (user_group_id, user_id, action, performed_by, reason)
    VALUES
        (_user_group_id, _user_id, _action, _performed_by, _reason);
END;
$$ LANGUAGE plpgsql;

Performance Optimization¶

-- Essential indexes for internal groups
CREATE INDEX idx_user_group_member_user_lookup
    ON auth.user_group_member(user_id) WHERE is_active = true;

CREATE INDEX idx_user_group_member_group_lookup
    ON auth.user_group_member(user_group_id) WHERE is_active = true;

CREATE INDEX idx_user_group_tenant_type
    ON auth.user_group(tenant_id, group_type) WHERE is_active = true;

Security Considerations¶

Access Control: Limit who can modify group membership
Audit Trail: Log all membership changes
Regular Reviews: Periodic membership audits
Principle of Least Privilege: Only assign necessary permissions

Troubleshooting Internal Groups¶

Common Issues¶

Duplicate Membership:

-- Find and fix duplicate memberships
SELECT user_group_id, user_id, COUNT(*)
FROM auth.user_group_member
GROUP BY user_group_id, user_id
HAVING COUNT(*) > 1;

-- Clean up duplicates (keep most recent)
DELETE FROM auth.user_group_member ugm1
WHERE ugm1.ctid NOT IN (
    SELECT DISTINCT ON (user_group_id, user_id) ctid
    FROM auth.user_group_member ugm2
    WHERE ugm2.user_group_id = ugm1.user_group_id
      AND ugm2.user_id = ugm1.user_id
    ORDER BY user_group_id, user_id, added_at DESC
);

Orphaned Memberships:

-- Find memberships for inactive users or groups
SELECT
    ugm.user_group_id,
    ugm.user_id,
    'Inactive user' as issue
FROM auth.user_group_member ugm
JOIN auth.user_info u ON ugm.user_id = u.user_id
WHERE u.is_active = false

UNION ALL

SELECT
    ugm.user_group_id,
    ugm.user_id,
    'Inactive group' as issue
FROM auth.user_group_member ugm
JOIN auth.user_group ug ON ugm.user_group_id = ug.user_group_id
WHERE ug.is_active = false;

What's Next¶

Learn about External Groups for provider-based membership
Explore Hybrid Groups combining internal and external models
Understand Group Mappings for provider integration
Review Permission Assignment patterns