Skip to content

Audit Logging

Authentication and audit activity in Hydrolix is logged to the hydro.audit_logs table, accessible through an API or direct SQL queries.

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

Filters⚓︎

Specify filters in the request body. These filters include:

  • user_id string: Includes all Keycloak events 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 retrieves all login errors from May 19, 2024 onward:

Retrieve Login Errors Using API
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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⚓︎

Filter Authentication Events by IP Address
1
2
3
4
5
6
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:

Filter by Timestamp Range
1
2
3
4
5
6
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⚓︎

This feature was introduced in Hydrolix version 5.6.2 where Config API audit logs moved from a PostgreSQL-backed database to the hydro.audit_logs table. Keycloak authentication events moved to the same table in Hydrolix version 5.8. Both changes improve performance and reduce contention on the PostgreSQL database.

hydro.audit_logs table schema⚓︎

The hydro.audit_logs table contains two types of records:

  • Keycloak authentication events: Login attempts, session activity, and lockouts. These moved from a PostgreSQL-backed database to hydro.audit_logs in Hydrolix version 5.8.
  • Config API audit events: Actions performed through the Config API, such as creating or modifying projects, tables, transforms, jobs, users, roles, and policies. These moved from a PostgreSQL-backed database to hydro.audit_logs in Hydrolix version 5.6.2.

Use the source column to distinguish between them: keycloak for authentication events, audit for Config API events.

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

Audit reporting for internal accounts⚓︎

Internal user accounts hdx@hydrolix.net and hdx.readonly@hydrolix.net were deprecated in Hydrolix version 5.11.

Don't depend on these accounts

To avoid interruption of services, switch to service accounts for all external interaction with Hydrolix cluster APIs.

Cluster-internal automation began switching away from the legacy user accounts in v5.11.

Use this or a similar query to collect evidence of account usage using the audit_logs.

Collect Recent Evidence of Internal User Account Authentication
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
SELECT
  user_id,
  username,
  count(*) as login_count
FROM
  hydro.audit_logs
WHERE
  source = 'keycloak'
  AND username IN ('hdx@hydrolix.net', 'hdx.readonly@hydrolix.net')
  AND type = 'LOGIN'
  AND timestamp >= now() - INTERVAL 7 DAY
GROUP BY
  user_id,
  username

Query results don't include the system against which the legacy internal user account authenticated. For example, the client could be using the HTTP Stream API or HTTP Query API, or even the Hydrolix UI.

Use the ip_address columns and other application logs to help identify external systems which use the legacy internal user accounts.

See also example queries.

Example queries⚓︎

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

Find User Actions by Time Range
1
2
3
4
5
6
7
8
9
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:

Find Actions by IP Address
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
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:

Find Users Who Modified a Project
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
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:

Find Authentication Activity for User
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
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