Skip to content

ROLES.md

Latest commit

 

History

History
280 lines (207 loc) · 9.99 KB

ROLES.md

File metadata and controls

280 lines (207 loc) · 9.99 KB

Role Management in Constructive

This document explains the role management system in Constructive, covering the unified dynamic implementation with configurable role names.

Overview: The Role Model

Constructive uses PostgreSQL roles for authorization, following a membership-based model where users (LOGIN roles) are granted membership in base roles (NOLOGIN group roles).

Base Roles (NOLOGIN)

These are group roles that define permission levels. They cannot log in directly but are granted to users:

Role Purpose RLS Behavior
anonymous Unauthenticated access Subject to RLS
authenticated Authenticated user access Subject to RLS
administrator Admin access Bypasses RLS

Users (LOGIN roles)

Users are roles with LOGIN capability that can connect to the database. They are granted membership in base roles to inherit permissions.

Unified Dynamic Implementation

All role management is now handled by a single shared module in @pgpmjs/core/src/roles/index.ts. This provides:

  1. Configurable role names - defaults to canonical names (anonymous, authenticated, administrator) but can be customized
  2. Single source of truth - both PgpmInit (CLI) and DbAdmin (test harness) use the same SQL generators
  3. Concurrency safety - all operations include IF NOT EXISTS pre-checks and exception handling for TOCTOU safety
  4. Optional advisory locking - when useLocks: true, wraps CREATE ROLE operations with pg_advisory_xact_lock

SQL Generators

The shared module exports these SQL generators:

Function Purpose
generateCreateBaseRolesSQL(roles?) Create base roles (anonymous, authenticated, administrator)
generateCreateUserSQL(username, password, roles?, options?) Create a user with grants to base roles
generateCreateTestUsersSQL(roles?) Create test users (app_user, app_admin)
generateCreateUserWithGrantsSQL(username, password, rolesToGrant, options?) Create a user with grants to specified roles
generateGrantRoleSQL(role, user) Grant a single role to a user
generateRemoveUserSQL(username, roles?, options?) Remove a user and revoke grants

Role Mapping

Role names are configurable via the RoleMapping interface from @pgpmjs/types:

interface RoleMapping {
  anonymous?: string;      // default: 'anonymous'
  authenticated?: string;  // default: 'authenticated'
  administrator?: string;  // default: 'administrator'
  default?: string;        // default: 'anonymous'
}

Entry Points and Call Paths

1. Bootstrap Base Roles (Dynamic TypeScript)

Command: pgpm admin-users bootstrap

Call Path:

pgpm admin-users bootstrap
  -> pgpm/pgpm/src/commands/admin-users/bootstrap.ts
    -> new PgpmInit(pgEnv)
      -> init.bootstrapRoles(roles?)
        -> generateCreateBaseRolesSQL(roles?)

What it does:

  • Creates the three base roles: anonymous, authenticated, administrator (or custom names via roles parameter)
  • Sets role attributes (NOLOGIN, NOCREATEDB, etc.)
  • Administrator gets BYPASSRLS, others do not

When to use:

  • One-time setup when initializing a new database
  • Must be run before adding any users

2. Add Test Users (Dynamic TypeScript)

Command: pgpm admin-users add --test

Call Path:

pgpm admin-users add --test
  -> pgpm/pgpm/src/commands/admin-users/add.ts
    -> new PgpmInit(pgEnv)
      -> init.bootstrapTestRoles(roles?, testUsers?)
        -> generateCreateTestUsersSQL(roles?, testUsers?)

What it does:

  • Creates app_user and app_admin with credentials from pgpmDefaults.db.connection (or custom via testUsers parameter)
  • Grants anonymous and authenticated to app_user
  • Grants anonymous, authenticated, and administrator to app_admin

When to use:

  • Local development only
  • Never on production (default passwords)

Prerequisites:

  • Base roles must exist (run pgpm admin-users bootstrap first)

3. Add Custom User (Dynamic TypeScript)

Command: pgpm admin-users add --username X --password Y

Call Path:

pgpm admin-users add --username myuser --password mypass
  -> pgpm/pgpm/src/commands/admin-users/add.ts
    -> new PgpmInit(pgEnv)
      -> init.bootstrapDbRoles(username, password, options?)
        -> Dynamic SQL with parameterized username/password

What it does:

  • Creates a LOGIN role with the provided username and password
  • Grants anonymous and authenticated to the new user

When to use:

  • Production user creation
  • Any time you need a user with custom credentials

Prerequisites:

  • Base roles must exist (run pgpm admin-users bootstrap first)

4. Remove User (Dynamic TypeScript)

Command: pgpm admin-users remove --username X or pgpm admin-users remove --test

Call Path:

pgpm admin-users remove --username myuser
  -> pgpm/pgpm/src/commands/admin-users/remove.ts
    -> new PgpmInit(pgEnv)
      -> init.removeDbRoles(username, options?)
        -> Dynamic SQL with parameterized username

What it does:

  • Revokes anonymous and authenticated memberships
  • Drops the role

5. Test Harness User Creation (Dynamic TypeScript)

Entry Point: getConnections() in postgres/pgsql-test/src/connect.ts

Call Path:

getConnections(options, seedAdapters)
  -> getPgRootAdmin(config, connOpts)
    -> new DbAdmin(opts, false, connOpts)
  -> root.createUserRole(user, password, dbName, options?)
    -> Dynamic SQL with configurable role names via getRoleName()

What it does:

  • Creates a test user for connecting to the test database
  • Grants anonymous, authenticated, and administrator roles
  • Uses getRoleName() to support configurable role names via PgTestConnectionOptions.roles

When to use:

  • Automated test setup (Jest, etc.)
  • CI/CD pipelines
  • Any programmatic test database creation

Key Difference from CLI:

  • Role names are configurable via PgTestConnectionOptions.roles
  • Grants administrator role (for testing purposes only)

6. Grant Single Role (Dynamic TypeScript)

Entry Point: DbAdmin.grantRole() in postgres/pgsql-test/src/admin.ts

Call Path:

admin.grantRole(role, user, dbName, options?)
  -> Dynamic SQL with pre-check and exception handling

What it does:

  • Grants a single role to a user
  • Pre-checks membership to avoid unnecessary operations
  • Handles concurrent grant attempts gracefully

When to use:

  • Flexible role granting in tests
  • When you need to grant a specific role outside the standard patterns

Where Base Roles Come From in Tests

When running tests via getConnections(), base roles must already exist. They are typically created by:

  1. PGPM deployment via seed adapter: The default seed.pgpm() adapter deploys the PGPM module which may include role creation in migrations
  2. Container initialization: Docker images may have roles pre-created
  3. Manual bootstrap: Running pgpm admin-users bootstrap before tests

The getConnections() function assumes base roles exist when it calls createUserRole() to grant them.

Role Name Configuration

Static SQL (Hardcoded)

The SQL files use hardcoded role names:

  • anonymous
  • authenticated
  • administrator

Dynamic TypeScript (Configurable in pgsql-test)

The pgsql-test package supports configurable role names via PgTestConnectionOptions.roles:

const connections = await getConnections({
  db: {
    roles: {
      anonymous: 'custom_anon',
      authenticated: 'custom_auth',
      administrator: 'custom_admin',
      default: 'custom_anon'
    }
  }
});

This is useful when:

  • Testing with non-standard role names
  • Running tests against databases with different role configurations

Concurrency Safety

All role management operations are designed to be idempotent and safe under concurrent execution:

  1. IF NOT EXISTS pre-checks: Avoid unnecessary CREATE/GRANT attempts
  2. Exception handling: Catch and ignore race conditions (duplicate_object, unique_violation)
  3. Optional advisory locking: When useLocks: true, wraps CREATE ROLE operations with pg_advisory_xact_lock(42, hashtext(username))

Error Handling by Operation

Operation Ignored Errors Surfaced Errors
CREATE ROLE 42710 (duplicate_object), 23505 (unique_violation) 42501 (insufficient_privilege)
GRANT 23505 (unique_violation), 42704 (undefined_object) 42501 (insufficient_privilege), 0LP01 (invalid_grant_operation)
REVOKE/DROP 42704 (undefined_object) 2BP01 (dependent_objects_still_exist), 55006 (object_in_use), 42501 (insufficient_privilege)

Summary: When to Use What

Scenario Mechanism Entry Point
Initialize base roles Static SQL pgpm admin-users bootstrap
Add test users (dev only) Static SQL pgpm admin-users add --test
Add production user Dynamic TS pgpm admin-users add --username X --password Y
Remove user Dynamic TS pgpm admin-users remove
Automated test setup Dynamic TS getConnections() -> createUserRole()
Grant specific role Dynamic TS admin.grantRole()

File Locations

File Purpose
pgpm/core/src/roles/index.ts Shared SQL generators (single source of truth)
pgpm/core/src/init/client.ts PgpmInit class (CLI operations, uses shared module)
postgres/pgsql-test/src/admin.ts DbAdmin class (test operations, uses shared module)
postgres/pgsql-test/src/roles.ts Role name configuration helpers
postgres/pgsql-test/src/connect.ts Test connection setup
pgpm/types/src/pgpm.ts RoleMapping interface and defaults

Defaults

All role and test user defaults are sourced from pgpmDefaults in @pgpmjs/types:

Default Source
Role names pgpmDefaults.db.roles (anonymous, authenticated, administrator)
Test user credentials pgpmDefaults.db.connection (app_user/app_password)
Test admin credentials Hardcoded (app_admin/admin_password)