Multi-Factor Authentication (MFA)¶

Complete MFA implementation with TOTP enrollment, recovery codes, challenge/verification, and policy-based enforcement. Source: 036_tables_mfa.sql, 038_functions_mfa.sql, 039_mfa_policy.sql, 040_functions_mfa_policy.sql

Overview¶

The MFA subsystem has two parts:

MFA Enrollment & Verification -- two-step enrollment (enroll → confirm), TOTP challenge/verify, one-time recovery codes, and recovery code reset.
MFA Policy -- scope-based rules that signal whether MFA is required for a user, with resolution: user > group > tenant > global.

Key design principles:

The DB never sees raw TOTP secrets -- the app encrypts the secret before passing it to enroll_mfa. The DB stores secret_encrypted as an opaque blob.
Recovery codes are returned as plaintext exactly once (at enrollment or reset). The DB stores only SHA-256 hashes.
Challenge/verify is token-based -- each challenge creates a short-lived token (5 min default). The app verifies the TOTP code externally and passes the boolean result.
Policy is advisory -- is_mfa_required returns a boolean signal. Enforcement (blocking login, redirecting to setup) is the app's responsibility.

MFA applies to email/password logins only. External providers (AzureAD, Google, etc.) handle their own MFA -- by the time the app receives an OAuth token, the provider has already enforced it. See Authentication Process for the full picture.

sequenceDiagram
    participant App
    participant DB

    App->>DB: auth.get_user_by_email_for_authentication(email)
    DB-->>App: user_id, password_hash

    App->>App: Verify password hash

    alt Password correct
        App->>DB: auth.get_mfa_status(user_id, ..., target_user_id)
        DB-->>App: is_confirmed, is_enabled

        alt MFA not enrolled
            App->>DB: unsecure.is_mfa_required(target_user_id, tenant_id)
            DB-->>App: true/false
            alt MFA required but not enrolled
                App-->>App: Redirect to MFA setup
            else MFA not required
                App-->>App: Login complete
            end
        else MFA enrolled and confirmed
            App->>DB: auth.create_mfa_challenge(...)
            DB-->>App: token_uid, expires_at
            App-->>App: Show TOTP input

            App->>App: User enters TOTP code
            App->>App: Verify TOTP code against secret

            App->>DB: auth.verify_mfa_challenge(..., code_is_valid)
            DB-->>App: void (success) or exception

            App-->>App: Login complete
        end
    else Password wrong
        App->>DB: auth.record_login_failure(...)
        DB-->>App: raises 52103 or 33004 (auto-locked)
    end

MFA Policy (Enforcement Rules)¶

Policy rules determine whether MFA is required for a given user. Without any rules, MFA is optional (default: false).

Resolution Order¶

When checking whether MFA is required, the system evaluates rules in this order (most specific wins):

User-level -- a rule targeting the specific user (tenant-specific user rule wins over global user rule)
Group-level -- bool_or(mfa_required) across all groups the user belongs to (uses cached group IDs)
Tenant-level -- a rule for the tenant with no user/group scope
Global -- a rule with all scope columns null
No match -- returns false (MFA not required)

Scope Examples¶

-- Require MFA for everyone (global rule)
select * from auth.create_mfa_policy('admin', 1, 'corr-001');

-- Require MFA for a specific tenant
select * from auth.create_mfa_policy('admin', 1, 'corr-002', _tenant_id := 1);

-- Require MFA for members of a group
select * from auth.create_mfa_policy('admin', 1, 'corr-003', _user_group_id := 5);

-- Exempt a specific user from MFA (overrides group/tenant/global rules)
select * from auth.create_mfa_policy('admin', 1, 'corr-004',
    _target_user_id := 1001, _mfa_required := false);

Permissions¶

All MFA permissions are hierarchical under the mfa parent:

Permission Code	Assignable	Description
`mfa`	No (parent)	MFA parent permission
`mfa.enroll_mfa`	Yes	Initiate MFA enrollment
`mfa.confirm_mfa_enrollment`	Yes	Confirm MFA enrollment
`mfa.disable_mfa`	Yes	Remove MFA enrollment
`mfa.get_mfa_status`	Yes	Query MFA status and `is_mfa_required`
`mfa.create_mfa_challenge`	Yes	Create MFA challenge token
`mfa.verify_mfa_challenge`	Yes	Verify MFA challenge
`mfa.reset_mfa`	Yes	Regenerate recovery codes
`mfa.mfa_policy`	No (parent)	MFA policy parent permission
`mfa.mfa_policy.create_mfa_policy`	Yes	Create MFA enforcement rules
`mfa.mfa_policy.delete_mfa_policy`	Yes	Delete MFA enforcement rules
`mfa.mfa_policy.get_mfa_policies`	Yes	List MFA enforcement rules

Error Codes¶

Code	Function	Description
38001	`error.raise_38001`	MFA is already enrolled and confirmed for this type
38002	`error.raise_38002`	MFA is not enrolled for this type
38003	`error.raise_38003`	MFA enrollment is not confirmed
38004	`error.raise_38004`	The provided MFA code is not valid
38005	`error.raise_38005`	MFA verification is required
38006	`error.raise_38006`	MFA type does not exist or is inactive
38007	`error.raise_38007`	MFA policy does not exist

Tables¶

`auth.user_mfa`¶

Stores MFA enrollment state per user per MFA type.

Column	Type	Description
`user_mfa_id`	bigint	Auto-generated primary key
`user_id`	bigint	FK to `auth.user_info` (cascade delete)
`mfa_type_code`	text	FK to `const.mfa_type` (e.g. `totp`)
`secret_encrypted`	text	App-encrypted TOTP secret
`is_enabled`	boolean	Whether MFA is active
`is_confirmed`	boolean	Whether enrollment has been confirmed with a valid code
`recovery_codes`	text[]	SHA-256 hashes of one-time recovery codes
`enrolled_at`	timestamptz	When enrollment was initiated
`confirmed_at`	timestamptz	When enrollment was confirmed (null if pending)

Unique constraint on (user_id, mfa_type_code) -- one enrollment per MFA type per user.

Source: 036_tables_mfa.sql:41

`auth.mfa_policy`¶

Scope-based rules for MFA enforcement.

Column	Type	Description
`mfa_policy_id`	bigint	Auto-generated primary key
`tenant_id`	integer	FK to `auth.tenant` (nullable -- null = all tenants)
`user_group_id`	integer	FK to `auth.user_group` (nullable -- null = all groups)
`user_id`	bigint	FK to `auth.user_info` (nullable -- null = all users)
`mfa_required`	boolean	Whether MFA is required for this scope

Unique index on coalesce(tenant_id, -1), coalesce(user_group_id, -1), coalesce(user_id, -1) prevents duplicate rules for the same scope.

Source: 039_mfa_policy.sql:18

`const.mfa_type`¶

MFA type lookup table.

Column	Type	Description
`code`	text	Primary key (e.g. `totp`)
`title`	text	Human-readable name
`is_active`	boolean	Whether this type is currently available

Source: 036_tables_mfa.sql:28

Enrollment & Lifecycle Functions¶

`auth.enroll_mfa`¶

Initiates MFA enrollment. The application provides the encrypted TOTP secret (the DB never sees the raw secret). Generates 10 recovery codes -- returned as plaintext once, stored as SHA-256 hashes.

If the user already has a confirmed enrollment for this MFA type, raises 38001. If there is an unconfirmed pending enrollment, it is replaced.

Parameter	Type	Default	Description
`_created_by`	text	--	Identifier of who is enrolling
`_user_id`	bigint	--	Caller user ID (for permission check)
`_correlation_id`	text	--	Correlation ID for audit trail
`_target_user_id`	bigint	--	User to enroll MFA for
`_mfa_type_code`	text	--	MFA type (e.g. `totp`)
`_secret_encrypted`	text	--	App-encrypted TOTP secret
`_request_context`	jsonb	null	Optional request context for audit

Returns: table(__user_mfa_id bigint, __mfa_type_code text, __recovery_codes text[], __enrolled_at timestamptz) Permission required: mfa.enroll_mfa Events: mfa_enrolled (10090) Errors: 38001 (already enrolled), 38006 (MFA type not found) Source: 038_functions_mfa.sql:23

-- Enroll TOTP MFA for a user
select * from auth.enroll_mfa('admin', 1, 'corr-123', 1001, 'totp', 'encrypted_secret_here');
-- Returns: user_mfa_id, mfa_type_code, recovery_codes (10 plaintext codes), enrolled_at
-- IMPORTANT: Display recovery codes to the user NOW -- they are never returned again

`auth.confirm_mfa_enrollment`¶

Confirms a pending MFA enrollment. The application verifies the TOTP code externally and passes the result. Sets is_enabled=true and is_confirmed=true.

Parameter	Type	Default	Description
`_updated_by`	text	--	Identifier of who is confirming
`_user_id`	bigint	--	Caller user ID
`_correlation_id`	text	--	Correlation ID for audit trail
`_target_user_id`	bigint	--	User whose enrollment to confirm
`_mfa_type_code`	text	--	MFA type
`_code_is_valid`	boolean	--	Whether the app verified the TOTP code as valid
`_request_context`	jsonb	null	Optional request context

Returns: void Permission required: mfa.confirm_mfa_enrollment Events: mfa_enrollment_confirmed (10091) Errors: 38002 (not enrolled), 38004 (invalid code) Source: 038_functions_mfa.sql:108

`auth.disable_mfa`¶

Deletes an MFA enrollment record entirely.

Parameter	Type	Default	Description
`_updated_by`	text	--	Identifier of who is disabling
`_user_id`	bigint	--	Caller user ID
`_correlation_id`	text	--	Correlation ID
`_target_user_id`	bigint	--	User whose MFA to disable
`_mfa_type_code`	text	--	MFA type
`_request_context`	jsonb	null	Optional request context

Returns: void Permission required: mfa.disable_mfa Events: mfa_disabled (10051) Errors: 38002 (not enrolled) Source: 038_functions_mfa.sql:167

`auth.get_mfa_status`¶

Returns MFA enrollment status for a user. Returns an empty set if the user has no MFA enrollment.

Parameter	Type	Default	Description
`_user_id`	bigint	--	Caller user ID
`_correlation_id`	text	--	Correlation ID
`_target_user_id`	bigint	--	User to check

Returns: table(__user_mfa_id bigint, __mfa_type_code text, __is_enabled boolean, __is_confirmed boolean, __enrolled_at timestamptz, __confirmed_at timestamptz, __recovery_codes_remaining int) Permission required: mfa.get_mfa_status Source: 038_functions_mfa.sql:214

`auth.reset_mfa`¶

Regenerates 10 new recovery codes for a confirmed MFA enrollment. The old codes are replaced. Returns new plaintext codes (shown once, stored as SHA-256 hashes).

Use this when a user has lost their recovery codes but still has their TOTP authenticator. No need to disable and re-enroll.

Parameter	Type	Default	Description
`_updated_by`	text	--	Identifier of who is resetting
`_user_id`	bigint	--	Caller user ID
`_correlation_id`	text	--	Correlation ID
`_target_user_id`	bigint	--	User whose recovery codes to reset
`_mfa_type_code`	text	--	MFA type
`_request_context`	jsonb	null	Optional request context

Returns: table(__user_mfa_id bigint, __recovery_codes text[]) Permission required: mfa.reset_mfa Events: mfa_recovery_reset (10097) Errors: 38002 (not enrolled), 38003 (not confirmed) Source: 040_functions_mfa_policy.sql:22

-- Reset recovery codes for a user's TOTP enrollment
select * from auth.reset_mfa('admin', 1, 'corr-456', 1001, 'totp');
-- Returns: user_mfa_id, recovery_codes (10 new plaintext codes)

Challenge & Verification Functions¶

`auth.create_mfa_challenge`¶

Creates a time-limited MFA challenge token (5 minutes by default). Invalidates any previous valid MFA tokens for the user. Validates that MFA is enrolled, confirmed, and enabled.

Parameter	Type	Default	Description
`_created_by`	text	--	Identifier of who is creating the challenge
`_user_id`	bigint	--	Caller user ID
`_correlation_id`	text	--	Correlation ID
`_target_user_id`	bigint	--	User to challenge
`_mfa_type_code`	text	--	MFA type
`_request_context`	jsonb	null	Optional request context

Returns: table(__token_uid text, __expires_at timestamptz) Permission required: mfa.create_mfa_challenge Events: mfa_challenge_created (10092) Errors: 38002 (not enrolled / not enabled), 38003 (not confirmed) Source: 038_functions_mfa.sql:253

`auth.verify_mfa_challenge`¶

Verifies an MFA challenge. Three-way verification:

_code_is_valid = true -- TOTP code verified by app, challenge passes
_code_is_valid = false + _recovery_code provided -- hash-matched against stored recovery codes (consumed on use)
Neither valid -- token marked as failed, raises 38004

Parameter	Type	Default	Description
`_updated_by`	text	--	Identifier of who is verifying
`_user_id`	bigint	--	Caller user ID
`_correlation_id`	text	--	Correlation ID
`_target_user_id`	bigint	--	User being verified
`_token_uid`	text	--	Challenge token UID from `create_mfa_challenge`
`_code_is_valid`	boolean	--	Whether the app verified the TOTP code
`_recovery_code`	text	null	Recovery code to try (if TOTP failed)
`_request_context`	jsonb	null	Optional request context

Returns: void Permission required: mfa.verify_mfa_challenge Events: mfa_challenge_passed (10093), mfa_recovery_used (10094) Errors: 38004 (invalid code), 30002 (token expired/invalid), 30003 (token wrong user), 30005 (token not found) Source: 038_functions_mfa.sql:337

MFA Policy Functions¶

`auth.create_mfa_policy`¶

Creates an MFA enforcement rule. The scope is determined by which parameters are provided:

All null = global rule
_tenant_id only = tenant-level
_user_group_id only = group-level
_target_user_id only = user-level
Combinations are also supported (e.g. tenant + user = user in specific tenant)

Parameter	Type	Default	Description
`_created_by`	text	--	Identifier of who is creating
`_user_id`	bigint	--	Caller user ID
`_correlation_id`	text	--	Correlation ID
`_tenant_id`	integer	null	Tenant scope (null = all tenants)
`_user_group_id`	integer	null	Group scope (null = all groups)
`_target_user_id`	bigint	null	User scope (null = all users)
`_mfa_required`	boolean	true	Whether MFA is required for this scope
`_request_context`	jsonb	null	Optional request context

Returns: table(__mfa_policy_id bigint) Permission required: mfa.mfa_policy.create_mfa_policy Events: mfa_policy_created (10095) Source: 040_functions_mfa_policy.sql:95

`auth.delete_mfa_policy`¶

Deletes an MFA policy rule.

Parameter	Type	Default	Description
`_deleted_by`	text	--	Identifier of who is deleting
`_user_id`	bigint	--	Caller user ID
`_correlation_id`	text	--	Correlation ID
`_mfa_policy_id`	bigint	--	Policy to delete
`_request_context`	jsonb	null	Optional request context

Returns: void Permission required: mfa.mfa_policy.delete_mfa_policy Events: mfa_policy_deleted (10096) Errors: 38007 (policy not found) Source: 040_functions_mfa_policy.sql:127

`auth.get_mfa_policies`¶

Lists MFA policy rules, optionally filtered by scope parameters. All params null = return all policies.

Parameter	Type	Default	Description
`_user_id`	bigint	--	Caller user ID
`_correlation_id`	text	--	Correlation ID
`_tenant_id`	integer	null	Filter by tenant
`_user_group_id`	integer	null	Filter by group
`_target_user_id`	bigint	null	Filter by user

Returns: table(__mfa_policy_id bigint, __tenant_id integer, __user_group_id integer, __user_id bigint, __mfa_required boolean, __created_at timestamptz, __created_by text) Permission required: mfa.mfa_policy.get_mfa_policies Source: 040_functions_mfa_policy.sql:163

`unsecure.is_mfa_required`¶

Resolves whether MFA is required for a user. No permission check -- designed for login flows where the caller is the authentication system itself.

Parameter	Type	Default	Description
`_target_user_id`	bigint	--	User to check
`_tenant_id`	integer	1	Tenant context

Returns: boolean Permission required: None (unsecure schema) Source: 040_functions_mfa_policy.sql:201

`auth.is_mfa_required`¶

Permission-checked wrapper around unsecure.is_mfa_required.

Parameter	Type	Default	Description
`_user_id`	bigint	--	Caller user ID
`_correlation_id`	text	--	Correlation ID
`_target_user_id`	bigint	--	User to check
`_tenant_id`	integer	1	Tenant context

Returns: boolean Permission required: mfa.get_mfa_status Source: 040_functions_mfa_policy.sql:258

Multi-Factor Authentication (MFA)¶

Overview¶

Typical Email/Password Login Flow with MFA¶

MFA Policy (Enforcement Rules)¶

Resolution Order¶

Scope Examples¶

Permissions¶

Error Codes¶

Tables¶

auth.user_mfa¶

auth.mfa_policy¶

const.mfa_type¶

Enrollment & Lifecycle Functions¶

auth.enroll_mfa¶

auth.confirm_mfa_enrollment¶

auth.disable_mfa¶

auth.get_mfa_status¶

auth.reset_mfa¶