API Key Functions¶
Functions for managing inbound and outbound API keys, including creation, validation, permission assignment, secret rotation, and search. Source: 026_functions_auth_apikey.sql
API keys come in two types:
- Inbound API keys authenticate external callers into your application. Each inbound API key creates a technical user in
auth.user_info(withuser_type_code = 'api'), so that all permission checks work uniformly for both human and service accounts. - Outbound API keys store credentials for calling external services (SendGrid, Slack, Azure, etc.). Encryption and decryption of secrets is handled by the application layer; PostgreSQL stores only pre-encrypted
byteadata.
Utility Functions¶
These helper functions generate keys, secrets, and hashes used by the higher-level API key functions. They are typically not called directly by application code.
auth.generate_api_key_username¶
Generates the deterministic username for an API key's technical user by prefixing api_key_ to the key value.
| Parameter | Type | Default | Description |
|---|---|---|---|
_api_key |
text | -- | The API key value |
Returns: text
Permission required: None
Source: 026_functions_auth_apikey.sql:13
auth.generate_api_key¶
Generates a new random API key value using UUID v4.
No parameters.
Returns: text
Permission required: None
Source: 026_functions_auth_apikey.sql:22
auth.generate_api_secret¶
Generates a new random API secret value using UUID v4.
No parameters.
Returns: text
Permission required: None
Source: 026_functions_auth_apikey.sql:30
auth.generate_api_secret_hash¶
Computes a SHA-256 hash of the given secret. Used to store secret hashes rather than plaintext secrets.
| Parameter | Type | Default | Description |
|---|---|---|---|
_secret |
text | -- | The plaintext secret to hash |
Returns: bytea
Permission required: None
Source: 026_functions_auth_apikey.sql:38
Inbound API Key Management¶
Inbound API keys authenticate external callers (services, integrations, scripts) against your application. Each key is backed by a technical user in auth.user_info, allowing it to participate in the same permission system as human users.
auth.create_api_key¶
Creates a new inbound API key, its associated technical user, and optionally assigns permissions via a permission set and/or individual permission codes. Returns the generated key and secret -- the secret is only available at creation time (it is stored as a hash).
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of who is creating the key |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_title |
text | -- | Human-readable title for the key |
_description |
text | -- | Description of the key's purpose |
_perm_set_code |
text | -- | Permission set code to assign (pass null to skip) |
_permission_codes |
text[] | -- | Array of individual permission codes to assign (pass null or empty array to skip) |
_api_key |
text | null | Custom API key value (auto-generated if null) |
_api_secret |
text | null | Custom API secret value (auto-generated if null) |
_expire_at |
timestamptz | null | Optional expiration timestamp |
_notification_email |
text | null | Optional notification email for key expiry alerts |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__api_key_id integer, __api_key text, __api_secret text)
Permission required: api_keys.create_api_key
Source: 026_functions_auth_apikey.sql:47
Secret visibility
The __api_secret is returned only once at creation time. Store it securely -- it cannot be retrieved later (only the hash is persisted).
auth.update_api_key¶
Updates the metadata (title, description, expiration, notification email) of an existing inbound API key. Does not modify the key value or secret.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text | -- | Identifier of who is updating |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_api_key_id |
integer | -- | ID of the API key to update |
_title |
text | -- | New title |
_description |
text | -- | New description |
_expire_at |
timestamptz | -- | New expiration timestamp |
_notification_email |
text | -- | New notification email |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__api_key_id integer, __title text, __description text, __expire_at timestamptz, __notification_email text)
Permission required: api_keys.update_api_key
Source: 026_functions_auth_apikey.sql:181
auth.delete_api_key¶
Deletes an inbound API key, its permission assignments, and the associated technical user. This is a permanent, cascading deletion.
| 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 |
_api_key_id |
integer | -- | ID of the API key to delete |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__api_key_id integer)
Permission required: api_keys.delete_api_key
Source: 026_functions_auth_apikey.sql:348
auth.search_api_keys¶
Paginated search across inbound API keys. Supports text filtering by title. Results are ordered by title, then API key value.
| 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 | 10 | 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 title) |
Returns: table(__created_by text, __created_at timestamptz, __updated_by text, __updated_at timestamptz, __api_key_id integer, __tenant_id integer, __title text, __description text, __api_key text, __expire_at timestamptz, __notification_email text, __total_items bigint)
Permission required: api_keys.search
Source: 026_functions_auth_apikey.sql:110
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).
auth.get_api_key_permissions¶
Retrieves all permission assignments (permission sets and individual permissions) for a given API key. Internally resolves the key's technical user and delegates to auth.get_user_permissions.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_api_key_id |
integer | -- | ID of the API key |
_tenant_id |
integer | -- | Tenant context |
Returns: table(__assignment_id bigint, __perm_set_code text, __perm_set_title text, __user_group_member_id bigint, __user_group_title text, __permission_inheritance_type text, __permission_code text, __permission_title text, __tenant_id integer, __tenant_code text, __tenant_title text)
The __perm_set_title and __permission_title columns are resolved from the translation system. The three tenant columns (__tenant_id, __tenant_code, __tenant_title) identify which tenant the permission assignment belongs to.
Permission required: Delegates to auth.get_user_permissions (which checks its own permissions)
Source: 026_functions_auth_apikey.sql:164
Inbound Permission Management¶
auth.assign_api_key_permissions¶
Assigns a permission set and/or individual permissions to an API key's technical user. Returns the complete list of assignments after the operation.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of who is assigning |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_api_key_id |
integer | -- | ID of the API key to assign permissions to |
_perm_set_code |
text | -- | Permission set code to assign (pass null to skip) |
_permission_codes |
text[] | -- | Array of individual permission codes to assign (pass null to skip) |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__assignment_id bigint, __tenant_id integer, __perm_set_id integer, __perm_set_code text, __perm_set_title text, __permission_full_code text, __permission_full_title text, __permission_title text)
The __perm_set_title, __permission_full_title, and __permission_title columns are resolved from the translation system (falling back to the code when no translation exists).
Permission required: api_keys.update_permissions
Source: 026_functions_auth_apikey.sql:216
auth.unassign_api_key_permissions¶
Removes a permission set and/or individual permissions from an API key's technical user. Returns the remaining assignments after the operation.
| Parameter | Type | Default | Description |
|---|---|---|---|
_deleted_by |
text | -- | Identifier of who is unassigning |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_api_key_id |
integer | -- | ID of the API key to remove permissions from |
_perm_set_code |
text | -- | Permission set code to remove (pass null to skip) |
_permission_codes |
text[] | -- | Array of individual permission codes to remove (pass null to skip) |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__assignment_id bigint, __perm_set_id integer, __perm_set_code text, __perm_set_title text, __permission_full_code text, __permission_full_title text, __permission_title text)
The __perm_set_title, __permission_full_title, and __permission_title columns are resolved from the translation system (falling back to the code when no translation exists).
Permission required: api_keys.update_permissions
Source: 026_functions_auth_apikey.sql:275
Inbound Secret Management¶
auth.update_api_key_secret¶
Rotates the secret for an inbound API key. Optionally accepts a custom secret; otherwise generates a new one. Returns the new plaintext secret -- this is the only time it is available.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text | -- | Identifier of who is rotating the secret |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_api_key_id |
integer | -- | ID of the API key whose secret to rotate |
_api_secret |
text | null | Custom new secret (auto-generated if null) |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__api_key_id integer, __api_secret text)
Permission required: api_keys.update_api_secret
Source: 026_functions_auth_apikey.sql:383
Secret visibility
The new __api_secret is returned only once. Store it securely -- it cannot be retrieved later.
Inbound Validation¶
auth.validate_api_key¶
Validates an API key and secret combination against the stored hash. On success, returns the technical user's information and all effective permission codes. On failure, logs a failed validation event and raises error 52301.
| Parameter | Type | Default | Description |
|---|---|---|---|
_requested_by |
text | -- | Identifier of who is making the request |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_api_key |
text | -- | The API key to validate |
_api_secret |
text | -- | The API secret to validate |
_request_context |
jsonb | null | Optional request context for audit events |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__user_id bigint, __username text, __user_display_name text, __permission_full_codes text[])
Permission required: api_keys.validate_api_key
Source: 026_functions_auth_apikey.sql:418
Audit trail
Both successful and failed validations are logged via auth.create_user_event with the api_key_validating event type.
Outbound API Key Management¶
Outbound API keys store credentials for calling external services (e.g., SendGrid, Slack, Azure). Unlike inbound keys, outbound keys do not create technical users -- they are purely credential storage.
Encryption
PostgreSQL stores the secret as pre-encrypted bytea data. The application layer is responsible for encrypting before storage and decrypting after retrieval.
auth.create_outbound_api_key¶
Creates a new outbound API key for an external service. Requires a _service_code (e.g., sendgrid, slack) and a pre-encrypted secret. The generated api_key value is prefixed with outbound_{service_code}_.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of who is creating the key |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_title |
text | -- | Human-readable title |
_description |
text | -- | Description of the key's purpose |
_service_code |
text | -- | Service identifier (e.g., sendgrid, slack); stored as lowercase |
_encrypted_secret |
bytea | -- | Pre-encrypted secret data |
_service_url |
text | null | Optional service URL (e.g., API base URL) |
_extra_data |
jsonb | null | Optional extra configuration data |
_expire_at |
timestamptz | null | Optional expiration timestamp |
_notification_email |
text | null | Optional notification email for expiry alerts |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__api_key_id integer, __api_key text, __service_code text)
Permission required: api_keys.create_api_key
Source: 026_functions_auth_apikey.sql:483
auth.get_outbound_api_key¶
Retrieves an outbound API key's metadata by service code. Does not return the encrypted secret -- use auth.get_outbound_api_key_secret for that.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_service_code |
text | -- | Service code to look up |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__api_key_id integer, __api_key text, __title text, __description text, __service_code text, __service_url text, __extra_data jsonb, __expire_at timestamptz, __notification_email text, __created_at timestamptz, __updated_at timestamptz)
Permission required: api_keys.search
Source: 026_functions_auth_apikey.sql:549
auth.get_outbound_api_key_by_id¶
Retrieves an outbound API key's metadata by its ID. Does not return the encrypted secret -- use auth.get_outbound_api_key_secret_by_id for that.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_api_key_id |
integer | -- | ID of the outbound API key |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__api_key_id integer, __api_key text, __title text, __description text, __service_code text, __service_url text, __extra_data jsonb, __expire_at timestamptz, __notification_email text, __created_at timestamptz, __updated_at timestamptz)
Permission required: api_keys.search
Source: 026_functions_auth_apikey.sql:592
auth.update_outbound_api_key¶
Updates the metadata of an outbound API key (title, description, service URL, extra data, expiration, notification email). Does not modify the encrypted secret -- use auth.update_outbound_api_key_secret for that.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text | -- | Identifier of who is updating |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_api_key_id |
integer | -- | ID of the outbound API key to update |
_title |
text | -- | New title |
_description |
text | -- | New description |
_service_url |
text | null | New service URL |
_extra_data |
jsonb | null | New extra data |
_expire_at |
timestamptz | null | New expiration timestamp |
_notification_email |
text | null | New notification email |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__api_key_id integer, __title text, __description text, __service_url text, __extra_data jsonb, __expire_at timestamptz, __notification_email text)
Permission required: api_keys.update_api_key
Source: 026_functions_auth_apikey.sql:715
auth.delete_outbound_api_key¶
Permanently deletes an outbound API key. Raises an exception if the key is not found.
| 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 |
_api_key_id |
integer | -- | ID of the outbound API key to delete |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__api_key_id integer, __service_code text)
Permission required: api_keys.delete_api_key
Source: 026_functions_auth_apikey.sql:889
Outbound Search¶
auth.search_outbound_api_keys¶
Paginated search across outbound API keys. Supports text filtering by title and service code, and optional exact-match filtering by service code. Results are ordered by service code, then title.
| 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 | 10 | 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 title and service code) |
service_code |
text | Exact-match filter by service code |
Returns: table(__api_key_id integer, __api_key text, __title text, __description text, __service_code text, __service_url text, __extra_data jsonb, __expire_at timestamptz, __notification_email text, __created_at timestamptz, __updated_at timestamptz, __total_items bigint)
Permission required: api_keys.search
Source: 026_functions_auth_apikey.sql:825
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).
Outbound Secret Management¶
auth.get_outbound_api_key_secret¶
Retrieves the encrypted secret for an outbound API key by service code. Only returns keys that have not expired. Decryption is the application layer's responsibility.
| Parameter | Type | Default | Description |
|---|---|---|---|
_requested_by |
text | -- | Identifier of who is requesting the secret |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_service_code |
text | -- | Service code to look up |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__api_key_id integer, __service_code text, __service_url text, __encrypted_secret bytea, __extra_data jsonb)
Permission required: api_keys.read_outbound_secret
Source: 026_functions_auth_apikey.sql:638
Security note
This function returns encrypted secrets. Access requires the elevated api_keys.read_outbound_secret permission, which should be granted sparingly.
auth.get_outbound_api_key_secret_by_id¶
Retrieves the encrypted secret for an outbound API key by its ID. Only returns keys that have not expired. Decryption is the application layer's responsibility.
| Parameter | Type | Default | Description |
|---|---|---|---|
_requested_by |
text | -- | Identifier of who is requesting the secret |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_api_key_id |
integer | -- | ID of the outbound API key |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__api_key_id integer, __service_code text, __service_url text, __encrypted_secret bytea, __extra_data jsonb)
Permission required: api_keys.read_outbound_secret
Source: 026_functions_auth_apikey.sql:678
auth.update_outbound_api_key_secret¶
Rotates the encrypted secret for an outbound API key. The application layer must encrypt the new secret before calling this function. Raises an exception if the key is not found.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text | -- | Identifier of who is rotating the secret |
_user_id |
bigint | -- | User ID of the caller (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_api_key_id |
integer | -- | ID of the outbound API key whose secret to rotate |
_encrypted_secret |
bytea | -- | New pre-encrypted secret data |
_tenant_id |
integer | 1 | Tenant context |
Returns: table(__api_key_id integer, __service_code text)
Permission required: api_keys.update_api_secret
Source: 026_functions_auth_apikey.sql:776
Permission Summary¶
All API key functions in the auth schema require permissions under the api_keys hierarchy:
| Permission Code | Used By |
|---|---|
api_keys.create_api_key |
create_api_key, create_outbound_api_key |
api_keys.update_api_key |
update_api_key, update_outbound_api_key |
api_keys.delete_api_key |
delete_api_key, delete_outbound_api_key |
api_keys.search |
search_api_keys, get_outbound_api_key, get_outbound_api_key_by_id, search_outbound_api_keys |
api_keys.update_permissions |
assign_api_key_permissions, unassign_api_key_permissions |
api_keys.update_api_secret |
update_api_key_secret, update_outbound_api_key_secret |
api_keys.validate_api_key |
validate_api_key |
api_keys.read_outbound_secret |
get_outbound_api_key_secret, get_outbound_api_key_secret_by_id |
Journal Event Codes¶
API key operations use these event codes for audit logging:
| Code | Event | Functions |
|---|---|---|
14001 |
apikey_created |
create_api_key, create_outbound_api_key |
14002 |
apikey_updated |
update_api_key, assign_api_key_permissions, unassign_api_key_permissions, update_api_key_secret, update_outbound_api_key, update_outbound_api_key_secret |
14003 |
apikey_deleted |
delete_api_key, delete_outbound_api_key |