Skip to main content

User Roles

The Cosailor Template implements a flexible role-based access control (RBAC) system with predefined roles that can be adapted to various organizational structures.

Predefined Roles​

Admin Role​

Role Name: admin

Permissions:

  • Full access to all users within their tenant
  • Can read all user data in their organization
  • Management capabilities over team structure
  • Access to administrative functions and reporting

Assignment:

  • Typically assigned to organization owners, executives, or IT administrators
  • Usually tenant-scoped but can be global
  • Can only be granted by existing admins

Limitations:

  • Cannot write/edit other users' personal data (follows write-self-only rule)
  • Must respect tenant boundaries unless explicitly configured otherwise

Manager Role​

Role Name: manager

Permissions:

  • Access to assigned team members (reps) only
  • Can read data for their assigned reps
  • View team performance metrics and dashboards
  • Team management capabilities

Assignment:

  • Assigned to people who supervise other users
  • Created through user_assignments table linking manager to reps
  • Can be tenant-scoped

Access Pattern:

  • Uses managed_user_ids from JWT token
  • Accesses only users explicitly assigned to them
  • Hierarchical access within organizational structure

Representative Role​

Role Name: rep

Permissions:

  • Access to their own data only
  • Can read and write their own profile information
  • Access to personal dashboard and metrics
  • Limited to resources they create or own

Assignment:

  • Default role for regular users
  • Assigned to employees without management responsibilities
  • Usually tenant-scoped

Access Pattern:

  • Self-access only to their own data
  • Cannot access other users' information
  • Limited to resources within their tenant scope

Role Assignment Examples​

Admin User Setup​

-- Assign admin role to a user
INSERT INTO user_roles (user_id, role_id, tenant_id)
SELECT
    u.id,
    r.id,
    t.id
FROM users u, roles r, tenants t
WHERE u.email = 'admin@company.com'
  AND r.name = 'admin'
  AND t.name = 'Acme Corp';

Manager with Team Assignment​

-- Assign manager role
INSERT INTO user_roles (user_id, role_id, tenant_id)
SELECT
    u.id,
    r.id,
    t.id
FROM users u, roles r, tenants t
WHERE u.email = 'manager@company.com'
  AND r.name = 'manager'
  AND t.name = 'Acme Corp';

-- Assign team members to manager
INSERT INTO user_assignments (user_id, assigned_user_id, tenant_id)
SELECT
    manager.id,
    rep.id,
    t.id
FROM users manager, users rep, tenants t
WHERE manager.email = 'manager@company.com'
  AND rep.email IN ('rep1@company.com', 'rep2@company.com')
  AND t.name = 'Acme Corp';

Regular Rep User​

-- Assign rep role
INSERT INTO user_roles (user_id, role_id, tenant_id)
SELECT
    u.id,
    r.id,
    t.id
FROM users u, roles r, tenants t
WHERE u.email = 'rep@company.com'
  AND r.name = 'rep'
  AND t.name = 'Acme Corp';

Authorization Patterns by Role​

Admin Access Pattern​

@router.get("/users")
def list_all_users(auth: ServiceAuthContext = Depends(get_auth_context)):
    if "admin" not in auth.roles:
        raise HTTPException(403, detail="Admin access required")
    
    # Admins can see all users in their tenant
    return get_users_by_tenant(conn, auth.tenant_id)

Manager Access Pattern​

@router.get("/my-team")
def get_my_team(auth: ServiceAuthContext = Depends(get_auth_context)):
    # Managers can only see their assigned reps
    if not auth.managed_user_ids:
        return []
    
    stmt = select(User).where(User.id.in_(auth.managed_user_ids))
    return conn.execute(stmt).scalars().all()

Rep Access Pattern​

@router.get("/my-profile")
def get_my_profile(auth: ServiceAuthContext = Depends(get_auth_context)):
    # Reps can only see their own data
    return get_user_by_id(conn, auth.user_id)

Role Management Best Practices​

Security Principles​

  1. Principal of Least Privilege - Users should have minimum required permissions
  2. Role Separation - Clear distinction between different role types
  3. Regular Auditing - Periodic review of role assignments
  4. Just-in-Time Access - Temporary elevation of permissions when needed

Data Access Patterns​

  1. Hierarchical Access - Respect organizational structure
  2. Tenant Isolation - Never cross tenant boundaries without explicit permission
  3. Self-Service - Users can manage their own basic information
  4. Manager Override - Managers can access team members' data within limits

Operational Considerations​

  1. Role Onboarding - Clear process for assigning roles to new users
  2. Role Transitions - Smooth process for role changes
  3. Delegation - Mechanisms for temporary role assignment
  4. Emergency Access - Break-glass procedures for critical situations

Troubleshooting Role Issues​

Common Problems​

User cannot access expected data:

  • Check role assignments in user_roles table
  • Verify tenant association in user_roles.tenant_id
  • Confirm managed user relationships in user_assignments

JWT token missing roles:

  • Verify token generation includes user roles
  • Check token expiration
  • Regenerate token after role changes

API endpoint returning 403:

  • Verify API checks account for all required roles
  • Check hierarchical permission logic
  • Confirm resource-level authorization

Debug Queries​

-- Check user's role assignments
SELECT u.email, r.name as role, t.name as tenant
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
LEFT JOIN tenants t ON ur.tenant_id = t.id
WHERE u.email = 'user@example.com';

-- Check manager-rep assignments
SELECT
    manager.email as manager,
    rep.email as rep,
    t.name as tenant
FROM user_assignments ua
JOIN users manager ON ua.user_id = manager.id
JOIN users rep ON ua.assigned_user_id = rep.id
JOIN tenants t ON ua.tenant_id = t.id
WHERE manager.email = 'manager@example.com';

This role system provides a solid foundation for access control that can be extended and customized based on specific organizational requirements.