User Management Functions¶
Functions for user registration, authentication, identity management, status control, preferences, and search. Source: 020_functions_auth_user.sql
Registration & Provisioning¶
auth.register_user¶
Registers a new user with email/password authentication. Creates user record, email identity, and user data. Validates that the email provider is active and the email is not already registered.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of who is creating the user |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_email |
text | -- | Email address (used as username and provider UID) |
_password_hash |
text | -- | Pre-hashed password |
_display_name |
text | -- | User's display name |
_user_data |
jsonb | null | Optional custom user data |
_request_context |
jsonb | null | Optional request context for audit events |
Returns: table(__user_id bigint, __code text, __uuid text, __username text, __email text, __display_name text)
Permission required: users.register_user
Source: 020_functions_auth_user.sql:290
auth.ensure_user_from_provider¶
Provisions or updates a user from an external identity provider (OAuth, SAML, etc.). If the user does not exist, creates them; if they do exist, updates their basic data and validates status. Checks the user blacklist before creating new users. Cannot be used with the email provider -- use auth.register_user instead.
This is the primary login/provisioning function for external providers. See Identity Providers for the full flow.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of who is creating/updating |
_user_id |
bigint | -- | User ID of the caller |
_correlation_id |
text | -- | Correlation ID for audit trail |
_provider_code |
text | -- | Provider code (e.g., azuread, google) |
_provider_uid |
text | -- | User's unique ID from the provider |
_provider_oid |
text | -- | Provider's object ID (e.g., Azure AD OID) |
_username |
text | -- | Username from the provider |
_display_name |
text | -- | Display name from the provider |
_email |
text | null | Email from the provider |
_user_data |
jsonb | null | Optional custom user data |
_request_context |
jsonb | null | Optional request context for audit events |
Returns: table(__user_id bigint, __code text, __uuid text, __username text, __email text, __display_name text)
Permission required: None (called during provider login flow)
Source: 020_functions_auth_user.sql:647
auth.ensure_user_info¶
Ensures a user record exists by username. If a user with the given username already exists, returns their data; otherwise creates a new user. This is a simpler idempotent provisioning function compared to auth.ensure_user_from_provider.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of who is creating |
_user_id |
bigint | -- | User ID of the caller |
_correlation_id |
text | -- | Correlation ID for audit trail |
_username |
text | -- | Username to ensure exists |
_display_name |
text | -- | Display name for new users |
_provider_code |
text | null | Optional provider code |
_email |
text | null | Optional email address |
_user_data |
jsonb | null | Optional custom user data |
Returns: table(__user_id bigint, __code text, __uuid text, __username text, __email text, __display_name text)
Permission required: None
Source: 020_functions_auth_user.sql:539
auth.create_service_user_info¶
Creates a service (technical) user. Service users are non-human accounts used for background processes, integrations, or system tasks. See also API key management which also creates technical users.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of who is creating |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_username |
text | -- | Username for the service user |
_email |
text | null | Optional email address |
_display_name |
text | null | Optional display name |
_custom_service_user_id |
bigint | null | Optional specific user ID to assign |
Returns: table(__user_id bigint, __username text, __email text, __display_name text)
Permission required: users.create_service_user
Source: 020_functions_auth_user.sql:249
auth.assign_user_default_groups¶
Adds a user to all groups marked as default for a given tenant. Typically called after registration to give new users baseline permissions. See Group Types for more on default groups.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of who is performing the action |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | User ID to add to default groups |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__user_id bigint, __user_group_id integer, __user_group_code text, __user_group_title text)
Permission required: users.add_to_default_groups
Source: 020_functions_auth_user.sql:346
User Status Management¶
auth.enable_user¶
Re-enables a previously disabled user account. Does not affect system users.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text | -- | Identifier of who is performing the action |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | User ID to enable |
_request_context |
jsonb | null | Optional request context for audit events |
Returns: table(__user_id bigint, __is_active boolean, __is_locked boolean)
Permission required: users.enable_user
Source: 020_functions_auth_user.sql:13
auth.disable_user¶
Disables a user account, preventing login. Clears the user's permission cache across all tenants to ensure immediate effect. Does not affect system users.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text | -- | Identifier of who is performing the action |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | User ID to disable |
_request_context |
jsonb | null | Optional request context for audit events |
Returns: table(__user_id bigint, __is_active boolean, __is_locked boolean)
Permission required: users.disable_user
Source: 020_functions_auth_user.sql:47
auth.unlock_user¶
Unlocks a previously locked user account.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text | -- | Identifier of who is performing the action |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | User ID to unlock |
_request_context |
jsonb | null | Optional request context for audit events |
Returns: table(__user_id bigint, __is_active boolean, __is_locked boolean)
Permission required: users.unlock_user
Source: 020_functions_auth_user.sql:84
auth.lock_user¶
Locks a user account, preventing login. Clears the user's permission cache across all tenants to ensure immediate effect. Locked accounts differ from disabled accounts in intent -- locking is typically a security response (e.g., too many failed login attempts).
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text | -- | Identifier of who is performing the action |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | User ID to lock |
_request_context |
jsonb | null | Optional request context for audit events |
Returns: table(__user_id bigint, __is_active boolean, __is_locked boolean)
Permission required: users.lock_user
Source: 020_functions_auth_user.sql:118
Identity Management¶
auth.enable_user_identity¶
Re-enables a previously disabled identity for a user on a specific provider. Raises error 52111 if the user/provider combination does not exist.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text | -- | Identifier of who is performing the action |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | User ID whose identity to enable |
_provider_code |
text | -- | Provider code (e.g., email, azuread) |
_request_context |
jsonb | null | Optional request context for audit events |
Returns: table(__user_identity_id bigint, __is_active boolean)
Permission required: users.enable_user_identity
Source: 020_functions_auth_user.sql:155
auth.disable_user_identity¶
Disables a user's identity on a specific provider, preventing login through that provider while leaving other identities functional. Raises error 52111 if the user/provider combination does not exist.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text | -- | Identifier of who is performing the action |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | User ID whose identity to disable |
_provider_code |
text | -- | Provider code (e.g., email, azuread) |
_request_context |
jsonb | null | Optional request context for audit events |
Returns: table(__user_identity_id bigint, __is_active boolean)
Permission required: users.disable_user_identity
Source: 020_functions_auth_user.sql:202
Authentication¶
auth.get_user_by_email_for_authentication¶
Retrieves user credentials for email/password authentication. Performs comprehensive validation: checks that the email provider is active, the user exists, login is allowed (can_login), the user is active and not locked, and the identity is active. Logs appropriate events for each failure case. Updates the user's last used provider to email on success.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_email |
text | -- | Email address to look up |
_request_context |
jsonb | null | Optional request context for audit events |
Returns: table(__user_id bigint, __code text, __uuid text, __username text, __email text, __display_name text, __provider text, __password_hash text, __password_salt text)
Permission required: authentication.get_data
Source: 020_functions_auth_user.sql:437
Security note
This function returns password hashes. It should only be called from secure application contexts that handle credential verification.
auth.verify_user_by_email¶
Single-call email/password login that combines user lookup, status validation, password hash comparison, failure recording, and auto-lockout into one DB call. The application hashes the password on its side and passes the hash to this function. On success, returns user data and logs user_logged_in. On hash mismatch, logs user_login_failed (reason: wrong_password), checks auto-lockout threshold, and raises the appropriate error.
Unlike get_user_by_email_for_authentication, this function only logs user_logged_in after a confirmed hash match (cleaner audit trail) and does not return password hashes.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_email |
text | -- | Email address to look up |
_password_hash |
text | -- | Password hash computed by the application |
_request_context |
jsonb | null | Optional request context for audit events |
Returns: table(__user_id bigint, __code text, __uuid text, __username text, __email text, __display_name text)
Permission required: authentication.get_data
Source: 037_functions_auto_lockout.sql:177
Error behavior:
| Condition | Event logged | Error raised |
|---|---|---|
| User not found | user_login_failed (reason: user_not_found) |
52103 |
can_login = false |
user_login_failed (reason: login_disabled) |
52112 |
is_active = false |
user_login_failed (reason: user_disabled) |
52105 |
| Identity disabled | user_login_failed (reason: identity_disabled) |
52110 |
| User locked | user_login_failed (reason: user_locked) |
52106 |
| Wrong password hash | user_login_failed (reason: wrong_password) |
33004 (if auto-locked) or 52103 |
See Authentication Process for the full login flow.
auth.record_login_failure¶
Called by the application after password hash mismatch in the two-step login flow. Logs a user_login_failed event (reason: wrong_password), then checks auto-lockout. Always raises an error: 33004 if the user was auto-locked by this call, or 52103 (invalid credentials) otherwise.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | User ID who failed to authenticate |
_email |
text | -- | Email address (for error messages) |
_request_context |
jsonb | null | Optional request context for audit events |
Returns: void (always raises an exception)
Permission required: authentication.get_data
Source: 037_functions_auto_lockout.sql:123
Not needed with single-call flow
If you use auth.verify_user_by_email, failure recording and auto-lockout are handled automatically. This function is only needed in the two-step flow with get_user_by_email_for_authentication.
Password Management¶
auth.update_user_password¶
Updates a user's password hash (and optionally salt). If the caller is changing their own password (_user_id = _target_user_id), no permission check is performed. Changing another user's password requires permission.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text | -- | Identifier of who is performing the action |
_user_id |
bigint | -- | User ID of the caller |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | User ID whose password to update |
_password_hash |
text | -- | New password hash |
_request_context |
jsonb | -- | Request context for audit events |
_password_salt |
text | null | Optional password salt |
Returns: table(__user_id bigint, __provider_code text, __provider_uid text)
Permission required: users.change_password (only when changing another user's password)
Source: 020_functions_auth_user.sql:266
User Data & Preferences¶
The auth.user_data table stores per-user profile data and three jsonb columns for extensible configuration:
| Column | Purpose | Example keys |
|---|---|---|
settings |
App-level user configuration | locale, timezone, notification_email, default_tenant |
preferences |
UI/UX preferences | theme, sidebar_collapsed, default_view, items_per_page |
custom_data |
App-specific extensible fields | employee_number, department, cost_center |
All three default to '{}'::jsonb and are never null. Name columns (first_name, middle_name, last_name) remain as plain text.
auth.update_user_data¶
Partial-merge update for user data. Each jsonb parameter is shallow-merged with the existing value — pass only the keys you want to change. To remove a key, pass it with a null value (removed via jsonb_strip_nulls after merge). Name columns use coalesce semantics — pass null to leave unchanged. Auto-creates the user_data row on first update.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text | -- | Identifier of who is performing the action |
_user_id |
bigint | -- | User ID of the caller |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | User ID whose data to update |
_first_name |
text | null |
First name (null = no change) |
_middle_name |
text | null |
Middle name (null = no change) |
_last_name |
text | null |
Last name (null = no change) |
_settings |
jsonb | null |
Keys to merge into settings (null = no change) |
_preferences |
jsonb | null |
Keys to merge into preferences (null = no change) |
_custom_data |
jsonb | null |
Keys to merge into custom_data (null = no change) |
_tenant_id |
integer | 1 |
Tenant scope for permission check |
Returns: setof auth.user_data -- the updated row.
Permission required: users.update_user_data (only when updating another user; self-update is free)
Journal event: 10002 (user_updated) -- journals changed key names (not values) for audit without leaking data.
Example
-- Change locale setting and collapse sidebar preference
select * from auth.update_user_data('app', _user_id, _corr_id, _target_user_id,
_settings := '{"locale": "cs"}'::jsonb,
_preferences := '{"sidebar_collapsed": true}'::jsonb);
-- Remove a setting key
select * from auth.update_user_data('app', _user_id, _corr_id, _target_user_id,
_settings := '{"old_key": null}'::jsonb);
-- Update name + custom data in one call
select * from auth.update_user_data('app', _user_id, _corr_id, _target_user_id,
_first_name := 'Jane',
_custom_data := '{"department": "engineering", "employee_number": "E-1234"}'::jsonb);
Source: 020_functions_auth_user.sql
auth.get_user_data¶
Retrieves user data for a given user. If the caller is querying their own data, no permission check is performed. Querying another user's data requires permission.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | User ID of the caller |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | User ID whose data to retrieve |
_tenant_id |
integer | 1 |
Tenant scope |
Returns: setof auth.user_data -- includes first_name, middle_name, last_name, settings, preferences, custom_data.
Permission required: users.get_data (only when querying another user's data)
Source: 020_functions_auth_user.sql
User Lookup¶
auth.get_user_by_id¶
Retrieves basic user information by user ID. Raises error 52103 if the user does not exist.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | User ID to look up |
_correlation_id |
text | -- | Correlation ID for audit trail |
Returns: table(__user_id bigint, __code text, __uuid text, __username text, __email text, __display_name text)
Permission required: None
Source: 020_functions_auth_user.sql:363
auth.get_user_by_provider_oid¶
Retrieves basic user information by provider object ID (e.g., Azure AD OID).
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | User ID of the caller |
_correlation_id |
text | -- | Correlation ID for audit trail |
_provider_oid |
text | -- | Provider object ID to search for |
Returns: table(__user_id bigint, __code text, __uuid text, __username text, __email text, __display_name text)
Permission required: None
Source: 020_functions_auth_user.sql:817
auth.get_user_identity¶
Retrieves a user's identity record for a specific provider, including provider groups and roles.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | User ID whose identity to retrieve |
_provider_code |
text | -- | Provider code (e.g., email, azuread) |
Returns: table(__user_identity_id bigint, __provider_code text, __uid text, __user_id bigint, __provider_groups text[], __provider_roles text[], __user_data jsonb)
Permission required: users.get_user_identity
Source: 020_functions_auth_user.sql:390
auth.get_user_identity_by_email¶
Retrieves a user's identity record by matching on the user's email address and a specific provider.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_email |
text | -- | Email address to match against user_info.email |
_provider_code |
text | -- | Provider code to filter by |
Returns: table(__user_identity_id bigint, __provider_code text, __uid text, __user_id bigint, __provider_groups text[], __provider_roles text[], __user_data jsonb)
Permission required: users.get_user_identity
Source: 020_functions_auth_user.sql:413
User Deletion¶
auth.delete_user_info¶
Permanently deletes a user and all associated data. Cannot delete system users. Optionally adds the user to the blacklist to prevent re-registration. Raises error 33001 if the user does not exist, or 33002 if the user is a system user.
| Parameter | Type | Default | Description |
|---|---|---|---|
_deleted_by |
text | -- | Identifier of who is deleting |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | User ID to delete |
_tenant_id |
integer | 1 | Tenant context |
_blacklist |
boolean | false | Whether to add the user to the blacklist after deletion |
Returns: table(__user_id bigint, __username text)
Permission required: users.delete_user_info
Source: 020_functions_auth_user.sql:610
Search¶
auth.search_users¶
Paginated search across all users with filtering by user type, active/locked status, and free-text search. Text search uses normalized data (nrm_search_data) for accent-insensitive matching. Results are ordered by display name.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | null | Correlation ID for audit trail |
_search_criteria |
jsonb | null | Search filters (see criteria keys below) |
_page |
integer | 1 | Page number (1-based) |
_page_size |
integer | 30 | Page size (capped at 100) |
_tenant_id |
integer | 1 | Tenant context |
_target_tenant_id |
integer | null | Cross-tenant: query a specific tenant's data (admin only) |
Criteria keys:
| Key | Type | Description |
|---|---|---|
search_text |
text | Free-text search (matches against normalized user data) |
user_type_code |
text | Filter by user type code |
is_active |
boolean | Filter by active status |
is_locked |
boolean | Filter by locked status |
Returns: table(__user_id bigint, __code text, __uuid text, __username text, __email text, __display_name text, __user_type_code text, __is_active boolean, __is_locked boolean, __total_items bigint)
Permission required: users.read_users
Source: 020_functions_auth_user.sql:865
Pagination
The __total_items column is included in every row and contains the total count of matching records (before pagination). Use it to calculate total pages: ceil(__total_items / _page_size).
Blacklist Management¶
The blacklist prevents specific users or provider identities from registering or logging in. Blacklist entries can match on username, provider code, provider UID, and/or provider OID. The blacklist is checked automatically during auth.ensure_user_from_provider for new user creation.
auth.is_blacklisted¶
Checks whether a user or identity matches any blacklist entry. At least one parameter should be provided.
| Parameter | Type | Default | Description |
|---|---|---|---|
_username |
text | null | Username to check |
_provider_code |
text | null | Provider code to check |
_provider_uid |
text | null | Provider UID to check |
_provider_oid |
text | null | Provider OID to check |
Returns: boolean
Permission required: None
Source: 020_functions_auth_user.sql:904
auth.create_blacklist_user¶
Adds an entry to the user blacklist. Entries can match on any combination of username, provider code, provider UID, and provider OID. Prevents blacklisting system users (raises error 33002).
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of who is creating the entry |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_username |
text | null | Username to blacklist |
_provider_code |
text | null | Provider code to blacklist |
_provider_uid |
text | null | Provider UID to blacklist |
_provider_oid |
text | null | Provider OID to blacklist |
_reason |
text | 'manual' |
Reason for blacklisting |
_notes |
text | null | Additional notes |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__blacklist_id bigint)
Permission required: users.manage_blacklist
Source: 020_functions_auth_user.sql:917
auth.delete_blacklist_user¶
Removes an entry from the user blacklist by its ID. Raises error 33018 if the blacklist entry does not exist.
| Parameter | Type | Default | Description |
|---|---|---|---|
_deleted_by |
text | -- | Identifier of who is removing the entry |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_blacklist_id |
bigint | -- | ID of the blacklist entry to remove |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__removed_blacklist_id bigint, __username text, __provider_code text)
Permission required: users.manage_blacklist
Source: 020_functions_auth_user.sql:956
auth.search_blacklist¶
Paginated search across blacklist entries with filtering by reason and free-text search. Text search matches against username, provider UID, provider OID, and notes. Results are ordered by creation date (newest first). The blacklist is app-wide (not tenant-scoped), so this function does not support cross-tenant queries.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | null | Correlation ID for audit trail |
_search_criteria |
jsonb | null | Search filters (see criteria keys below) |
_page |
integer | 1 | Page number (1-based) |
_page_size |
integer | 30 | Page size (capped at 100) |
_tenant_id |
integer | 1 | Tenant context |
Criteria keys:
| Key | Type | Description |
|---|---|---|
search_text |
text | Free-text search |
reason |
text | Filter by reason |
Returns: table(__blacklist_id bigint, __username text, __provider_code text, __provider_uid text, __provider_oid text, __original_user_id bigint, __reason text, __notes text, __created_at timestamptz, __created_by text, __total_items bigint)
Permission required: users.search_blacklist
Source: 020_functions_auth_user.sql:1058