Audit Logging

Your Hydrolix cluster provides you with an API to access authentication activity.

Overview

Hydrolix uses Keycloak for authentication. Retrieve authentication events from the HTTP API at https://{myhost}.hydrolix.live/config/v1/auth_logs using the GET action. The endpoint returns a JSON-formatted list of all Keycloak events.

This feature was introduced in Hydrolix version v5.8 where Keycloak authentication events moved from a PostgreSQL-backed database to the Hydrolix hydro.audit_logs table. This change improves performance of audit log reporting for large clusters and decreases contention on the PostgreSQL database system.

Filters

Filters can be specified in the request body. These filters include:

• user_id string: Includes all Keycloak events that are created by the given user. To return all Keycloak events regardless of user, exclude the field.

• event_types array(string): Specifies all the event types to be included in the response. The most useful event types are:

• LOGIN: A user has successfully logged in and has begun a Keycloak session. The sessionId is included in the Keycloak event object.

• LOGIN_ERROR: Someone has tried logging in as the user and failed. The reason for this failure is included in the error field in the event object (invalid_user_credentials, timed_out, and others).

• USER_DISABLED_BY_TEMPORARY_LOCKOUT: A user has been locked out temporarily, with the reason included in the event object.

• USER_DISABLED_BY_PERMANENT_LOCKOUT: A user has been locked out permanently, with the reason included in the event object.

• from_date string: Excludes all events that occur before the provided time. Should be in the format YYYY-MM-DD.

• to_date string: Excludes all events that occur after the provided time. Should be in the format YYYY-MM-DD.

• from_timestamp int: Excludes all events that occur before the provided epoch timestamp in milliseconds. More precise than from_date.

• to_timestamp int: Excludes all events that occur after the provided epoch timestamp in milliseconds. More precise than to_date.

• ip_address string: Filters events by the IP address from which the authentication attempt originated.

• username string: Filters events by username.

• target_user string: Filters events by target user UUID (for actions performed on other users).

• target_username string: Filters events by target username (for actions performed on other users).

• direction string: Controls the sort order of results by timestamp. Valid values are ASC (ascending, oldest first) or DESC (descending, newest first).

• limit int: Limits the number of events returned in the response. The default limit is 100 events.

RBAC permissions

API endpoint permissions

The /config/v1/auth_logs endpoint is accessible to users with the view_audit permission. By default, only the super_admin role has this permission on cluster startup. Other roles (user_admin, operator, read_only) don't have access by default and must be explicitly granted the view_audit or view_auth_logs_user permission.

Direct table query permissions

Users with the select_sql permission for the hydro.audit_logs table can query authentication and audit events directly using SQL. During cluster initialization, users and roles with global view_audit or view_auth_logs_user permissions automatically receive select_sql permission for the hydro.audit_logs table.

Example API call

After setting $HDX_TOKEN and $HDX_HOSTNAME, this command will retrieve all the login errors from May 19, 2024 and onward:

curl -s --header "authorization: Bearer $HDX_TOKEN" \
  --header "Content-Type: application/json" \
  --request GET \
  --url https://${HDX_HOSTNAME}/config/v1/auth_logs/ \
  --data '{"event_types": ["LOGIN_ERROR"],
           "from_date": "2024-05-19"}' | jq
[
  {
    "time": 1716238444160,
    "type": "LOGIN_ERROR",
    "realmId": "hydrolix-users",
    "clientId": "config-api",
    "userId": "03345f81-7bb2-4a03-8f1f-b248fc2c9efb",
    "ipAddress": "10.8.0.21",
    "error": "invalid_user_credentials",
    "details": {
      "auth_method": "openid-connect",
      "grant_type": "password",
      "client_auth_method": "client-secret",
      "username": "test@hydrolix.io"
    }
  }
]

Filter by IP address

curl -s --header "authorization: Bearer $HDX_TOKEN" \
  --header "Content-Type: application/json" \
  --request GET \
  --url https://${HDX_HOSTNAME}/config/v1/auth_logs/ \
  --data '{"ip_address": "10.8.0.21",
           "from_date": "2024-05-19"}' | jq

Filter by timestamp range

Use epoch timestamps in milliseconds for more precise time filtering:

curl -s --header "authorization: Bearer $HDX_TOKEN" \
  --header "Content-Type: application/json" \
  --request GET \
  --url https://${HDX_HOSTNAME}/config/v1/auth_logs/ \
  --data '{"from_timestamp": 1759104000000,
           "to_timestamp": 1759190399000}' | jq

Query the hydro.audit_logs table directly

hydro.audit_logs table schema

The hydro.audit_logs table contains both Keycloak authentication events (migrated in v5.8) and audit activity records (migrated in v5.6). Key columns include:

• timestamp (datetime): When the event occurred
• source (string): Event source (keycloak or audit)
• source_id (string): Original event ID from the source system
• type (string): Event type (for example, LOGIN, LOGIN_ERROR, create:job)
• user_id (string): UUID of the user who performed the action
• username (string): Username of the user who performed the action
• session_id (string): Session identifier
• ip_address (string): IP address from which the action originated
• resource_id (string): UUID of the affected resource (for audit events)
• resource_type (string): Type of affected resource (for audit events). Examples: project, table, transform, job, user, role, policy
• target_user_id (string): UUID of user affected by the action
• target_username (string): Username of user affected by the action
• error (string): Error message (for failed authentication attempts)
• event_details (string): JSON string with additional event details

Example queries

Find all actions by a specific user within a time range:

SELECT
  toStartOfMinute(timestamp) AS minute,
  type,
  event_details
FROM hydro.audit_logs
WHERE timestamp BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 04:00:00'
  AND username = 'user@example.com'
GROUP BY minute, type, event_details
ORDER BY minute

Find all actions from a specific IP address:

SELECT
  user_id,
  username,
  resource_id,
  resource_type,
  count(*) as num_changes
FROM hydro.audit_logs
WHERE timestamp BETWEEN '2024-01-01 00:00:00' AND '2024-12-01 04:00:00'
  AND ip_address = '10.8.0.21'
GROUP BY user_id, username, resource_id, resource_type
ORDER BY num_changes DESC

Find all users who made changes to a project:

SELECT
  user_id,
  username,
  ip_address,
  session_id,
  count(*) as num_changes
FROM hydro.audit_logs
WHERE timestamp BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 04:00:00'
  AND resource_id = '<project-uuid>'
  AND resource_type = 'project'
GROUP BY user_id, username, ip_address, session_id
ORDER BY num_changes DESC

Find authentication activity for a specific user:

SELECT
  timestamp,
  type,
  ip_address,
  session_id,
  error
FROM hydro.audit_logs
WHERE source = 'keycloak'
  AND username = 'user@example.com'
  AND timestamp >= now() - INTERVAL 7 DAY
ORDER BY timestamp DESC