mirror of
https://github.com/coder/coder.git
synced 2026-06-02 20:48:20 +00:00
George K
86ca61d6ca
Audit and connection log pages were timing out due to expensive COUNT(*) queries over large tables. This commit adds opt-in count capping: requests can return a `count_cap` field signaling that the count was truncated at a threshold, avoiding full table scans that caused page timeouts. Text-cast UUID comparisons in regosql-generated authorization queries also contributed to the slowdown by preventing index usage for connection and audit log queries. These now emit native UUID operators. Frontend changes handle the capped state in usePaginatedQuery and PaginationWidget, optionally displaying a capped count in the pagination UI (e.g. "Showing 2,076 to 2,100 of 2,000+ logs") Related to: https://linear.app/codercom/issue/PLAT-31/connectionaudit-log-performance-issue
284 lines
8.6 KiB
SQL
284 lines
8.6 KiB
SQL
-- GetAuditLogsBefore retrieves `row_limit` number of audit logs before the provided
|
|
-- ID.
|
|
-- name: GetAuditLogsOffset :many
|
|
SELECT sqlc.embed(audit_logs),
|
|
-- sqlc.embed(users) would be nice but it does not seem to play well with
|
|
-- left joins.
|
|
users.username AS user_username,
|
|
users.name AS user_name,
|
|
users.email AS user_email,
|
|
users.created_at AS user_created_at,
|
|
users.updated_at AS user_updated_at,
|
|
users.last_seen_at AS user_last_seen_at,
|
|
users.status AS user_status,
|
|
users.login_type AS user_login_type,
|
|
users.rbac_roles AS user_roles,
|
|
users.avatar_url AS user_avatar_url,
|
|
users.deleted AS user_deleted,
|
|
users.quiet_hours_schedule AS user_quiet_hours_schedule,
|
|
COALESCE(organizations.name, '') AS organization_name,
|
|
COALESCE(organizations.display_name, '') AS organization_display_name,
|
|
COALESCE(organizations.icon, '') AS organization_icon
|
|
FROM audit_logs
|
|
LEFT JOIN users ON audit_logs.user_id = users.id
|
|
LEFT JOIN organizations ON audit_logs.organization_id = organizations.id
|
|
-- First join on workspaces to get the initial workspace create
|
|
-- to workspace build 1 id. This is because the first create is
|
|
-- is a different audit log than subsequent starts.
|
|
LEFT JOIN workspaces ON audit_logs.resource_type = 'workspace'
|
|
AND audit_logs.resource_id = workspaces.id
|
|
-- Get the reason from the build if the resource type
|
|
-- is a workspace_build
|
|
LEFT JOIN workspace_builds wb_build ON audit_logs.resource_type = 'workspace_build'
|
|
AND audit_logs.resource_id = wb_build.id
|
|
-- Get the reason from the build #1 if this is the first
|
|
-- workspace create.
|
|
LEFT JOIN workspace_builds wb_workspace ON audit_logs.resource_type = 'workspace'
|
|
AND audit_logs.action = 'create'
|
|
AND workspaces.id = wb_workspace.workspace_id
|
|
AND wb_workspace.build_number = 1
|
|
WHERE
|
|
-- Filter resource_type
|
|
CASE
|
|
WHEN @resource_type::text != '' THEN resource_type = @resource_type::resource_type
|
|
ELSE true
|
|
END
|
|
-- Filter resource_id
|
|
AND CASE
|
|
WHEN @resource_id::uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN resource_id = @resource_id
|
|
ELSE true
|
|
END
|
|
-- Filter organization_id
|
|
AND CASE
|
|
WHEN @organization_id::uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN audit_logs.organization_id = @organization_id
|
|
ELSE true
|
|
END
|
|
-- Filter by resource_target
|
|
AND CASE
|
|
WHEN @resource_target::text != '' THEN resource_target = @resource_target
|
|
ELSE true
|
|
END
|
|
-- Filter action
|
|
AND CASE
|
|
WHEN @action::text != '' THEN action = @action::audit_action
|
|
ELSE true
|
|
END
|
|
-- Filter by user_id
|
|
AND CASE
|
|
WHEN @user_id::uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN user_id = @user_id
|
|
ELSE true
|
|
END
|
|
-- Filter by username
|
|
AND CASE
|
|
WHEN @username::text != '' THEN user_id = (
|
|
SELECT id
|
|
FROM users
|
|
WHERE lower(username) = lower(@username)
|
|
AND deleted = false
|
|
)
|
|
ELSE true
|
|
END
|
|
-- Filter by user_email
|
|
AND CASE
|
|
WHEN @email::text != '' THEN users.email = @email
|
|
ELSE true
|
|
END
|
|
-- Filter by date_from
|
|
AND CASE
|
|
WHEN @date_from::timestamp with time zone != '0001-01-01 00:00:00Z' THEN "time" >= @date_from
|
|
ELSE true
|
|
END
|
|
-- Filter by date_to
|
|
AND CASE
|
|
WHEN @date_to::timestamp with time zone != '0001-01-01 00:00:00Z' THEN "time" <= @date_to
|
|
ELSE true
|
|
END
|
|
-- Filter by build_reason
|
|
AND CASE
|
|
WHEN @build_reason::text != '' THEN COALESCE(wb_build.reason::text, wb_workspace.reason::text) = @build_reason
|
|
ELSE true
|
|
END
|
|
-- Filter request_id
|
|
AND CASE
|
|
WHEN @request_id::uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN audit_logs.request_id = @request_id
|
|
ELSE true
|
|
END
|
|
-- Authorize Filter clause will be injected below in GetAuthorizedAuditLogsOffset
|
|
-- @authorize_filter
|
|
ORDER BY "time" DESC
|
|
LIMIT -- a limit of 0 means "no limit". The audit log table is unbounded
|
|
-- in size, and is expected to be quite large. Implement a default
|
|
-- limit of 100 to prevent accidental excessively large queries.
|
|
COALESCE(NULLIF(@limit_opt::int, 0), 100) OFFSET @offset_opt;
|
|
|
|
-- name: InsertAuditLog :one
|
|
INSERT INTO audit_logs (
|
|
id,
|
|
"time",
|
|
user_id,
|
|
organization_id,
|
|
ip,
|
|
user_agent,
|
|
resource_type,
|
|
resource_id,
|
|
resource_target,
|
|
action,
|
|
diff,
|
|
status_code,
|
|
additional_fields,
|
|
request_id,
|
|
resource_icon
|
|
)
|
|
VALUES (
|
|
$1,
|
|
$2,
|
|
$3,
|
|
$4,
|
|
$5,
|
|
$6,
|
|
$7,
|
|
$8,
|
|
$9,
|
|
$10,
|
|
$11,
|
|
$12,
|
|
$13,
|
|
$14,
|
|
$15
|
|
)
|
|
RETURNING *;
|
|
|
|
-- name: CountAuditLogs :one
|
|
SELECT COUNT(*) FROM (
|
|
SELECT 1
|
|
FROM audit_logs
|
|
LEFT JOIN users ON audit_logs.user_id = users.id
|
|
LEFT JOIN organizations ON audit_logs.organization_id = organizations.id
|
|
-- First join on workspaces to get the initial workspace create
|
|
-- to workspace build 1 id. This is because the first create is
|
|
-- is a different audit log than subsequent starts.
|
|
LEFT JOIN workspaces ON audit_logs.resource_type = 'workspace'
|
|
AND audit_logs.resource_id = workspaces.id
|
|
-- Get the reason from the build if the resource type
|
|
-- is a workspace_build
|
|
LEFT JOIN workspace_builds wb_build ON audit_logs.resource_type = 'workspace_build'
|
|
AND audit_logs.resource_id = wb_build.id
|
|
-- Get the reason from the build #1 if this is the first
|
|
-- workspace create.
|
|
LEFT JOIN workspace_builds wb_workspace ON audit_logs.resource_type = 'workspace'
|
|
AND audit_logs.action = 'create'
|
|
AND workspaces.id = wb_workspace.workspace_id
|
|
AND wb_workspace.build_number = 1
|
|
WHERE
|
|
-- Filter resource_type
|
|
CASE
|
|
WHEN @resource_type::text != '' THEN resource_type = @resource_type::resource_type
|
|
ELSE true
|
|
END
|
|
-- Filter resource_id
|
|
AND CASE
|
|
WHEN @resource_id::uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN resource_id = @resource_id
|
|
ELSE true
|
|
END
|
|
-- Filter organization_id
|
|
AND CASE
|
|
WHEN @organization_id::uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN audit_logs.organization_id = @organization_id
|
|
ELSE true
|
|
END
|
|
-- Filter by resource_target
|
|
AND CASE
|
|
WHEN @resource_target::text != '' THEN resource_target = @resource_target
|
|
ELSE true
|
|
END
|
|
-- Filter action
|
|
AND CASE
|
|
WHEN @action::text != '' THEN action = @action::audit_action
|
|
ELSE true
|
|
END
|
|
-- Filter by user_id
|
|
AND CASE
|
|
WHEN @user_id::uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN user_id = @user_id
|
|
ELSE true
|
|
END
|
|
-- Filter by username
|
|
AND CASE
|
|
WHEN @username::text != '' THEN user_id = (
|
|
SELECT id
|
|
FROM users
|
|
WHERE lower(username) = lower(@username)
|
|
AND deleted = false
|
|
)
|
|
ELSE true
|
|
END
|
|
-- Filter by user_email
|
|
AND CASE
|
|
WHEN @email::text != '' THEN users.email = @email
|
|
ELSE true
|
|
END
|
|
-- Filter by date_from
|
|
AND CASE
|
|
WHEN @date_from::timestamp with time zone != '0001-01-01 00:00:00Z' THEN "time" >= @date_from
|
|
ELSE true
|
|
END
|
|
-- Filter by date_to
|
|
AND CASE
|
|
WHEN @date_to::timestamp with time zone != '0001-01-01 00:00:00Z' THEN "time" <= @date_to
|
|
ELSE true
|
|
END
|
|
-- Filter by build_reason
|
|
AND CASE
|
|
WHEN @build_reason::text != '' THEN COALESCE(wb_build.reason::text, wb_workspace.reason::text) = @build_reason
|
|
ELSE true
|
|
END
|
|
-- Filter request_id
|
|
AND CASE
|
|
WHEN @request_id::uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN audit_logs.request_id = @request_id
|
|
ELSE true
|
|
END
|
|
-- Authorize Filter clause will be injected below in CountAuthorizedAuditLogs
|
|
-- @authorize_filter
|
|
-- Avoid a slow scan on a large table with joins. The caller
|
|
-- passes the count cap and we add 1 so the frontend can detect
|
|
-- capping and show "... of N+". A cap of 0 means no limit (NULLIF
|
|
-- -> NULL + 1 = NULL).
|
|
-- NOTE: Parameterizing this so that we can easily change from,
|
|
-- e.g., 2000 to 5000. However, use literal NULL (or no LIMIT)
|
|
-- here if disabling the capping on a large table permanently.
|
|
-- This way the PG planner can plan parallel execution for
|
|
-- potential large wins.
|
|
LIMIT NULLIF(@count_cap::int, 0) + 1
|
|
) AS limited_count;
|
|
|
|
-- name: DeleteOldAuditLogConnectionEvents :exec
|
|
DELETE FROM audit_logs
|
|
WHERE id IN (
|
|
SELECT id FROM audit_logs
|
|
WHERE
|
|
(
|
|
action = 'connect'
|
|
OR action = 'disconnect'
|
|
OR action = 'open'
|
|
OR action = 'close'
|
|
)
|
|
AND "time" < @before_time::timestamp with time zone
|
|
ORDER BY "time" ASC
|
|
LIMIT @limit_count
|
|
);
|
|
|
|
-- name: DeleteOldAuditLogs :execrows
|
|
-- Deletes old audit logs based on retention policy, excluding deprecated
|
|
-- connection events (connect, disconnect, open, close) which are handled
|
|
-- separately by DeleteOldAuditLogConnectionEvents.
|
|
WITH old_logs AS (
|
|
SELECT id
|
|
FROM audit_logs
|
|
WHERE
|
|
"time" < @before_time::timestamp with time zone
|
|
AND action NOT IN ('connect', 'disconnect', 'open', 'close')
|
|
ORDER BY "time" ASC
|
|
LIMIT @limit_count
|
|
)
|
|
DELETE FROM audit_logs
|
|
USING old_logs
|
|
WHERE audit_logs.id = old_logs.id;
|