mirror of
https://github.com/coder/coder.git
synced 2026-06-03 04:58:23 +00:00
Ethan
08e17a07fc
### Breaking Change (changelog note): > User connections to workspaces, and the opening of workspace apps or ports will no longer create entries in the audit log. Those events will now be included in the 'Connection Log'. Please see the 'Connection Log' page in the dashboard, and the Connection Log [documentation](https://coder.com/docs/admin/monitoring/connection-logs) for details. Those with permission to view the Audit Log will also be able to view the Connection Log. The new Connection Log has the same licensing restrictions as the Audit Log, and requires a Premium Coder deployment. ### Context This is the first PR of a few for moving connection events out of the audit log, and into a new database table and web UI page called the 'Connection Log'. This PR: - Creates the new table - Adds and tests queries for inserting and reading, including reading with an RBAC filter. - Implements the corresponding RBAC changes, such that anyone who can view the audit log can read from the table - Implements, under the enterprise package, a `ConnectionLogger` abstraction to replace the `Auditor` abstraction for these logs. (No-op'd in AGPL, like the `Auditor`) - Routes SSH connection and Workspace App events into the new `ConnectionLogger` - Updates all existing tests to check the values of the `ConnectionLogger` instead of the `Auditor`. Future PRs: - Add filtering to the query - Add an enterprise endpoint to query the new table - Write a query to delete old events from the audit log, call it from dbpurge. - Implement a table in the Web UI for viewing connection logs. > [!NOTE] > The PRs in this stack obviously won't be (completely) atomic. Whilst they'll each pass CI, the stack is designed to be merged all at once. I'm splitting them up for the sake of those reviewing, and so changes can be reviewed as early as possible. Despite this, it's really hard to make this PR any smaller than it already is. I'll be keeping it in draft until it's actually ready to merge.
69 lines
3.4 KiB
SQL
69 lines
3.4 KiB
SQL
CREATE TYPE connection_status AS ENUM (
|
|
'connected',
|
|
'disconnected'
|
|
);
|
|
|
|
CREATE TYPE connection_type AS ENUM (
|
|
-- SSH events
|
|
'ssh',
|
|
'vscode',
|
|
'jetbrains',
|
|
'reconnecting_pty',
|
|
-- Web events
|
|
'workspace_app',
|
|
'port_forwarding'
|
|
);
|
|
|
|
CREATE TABLE connection_logs (
|
|
id uuid NOT NULL,
|
|
connect_time timestamp with time zone NOT NULL,
|
|
organization_id uuid NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,
|
|
workspace_owner_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
|
|
workspace_id uuid NOT NULL REFERENCES workspaces (id) ON DELETE CASCADE,
|
|
workspace_name text NOT NULL,
|
|
agent_name text NOT NULL,
|
|
type connection_type NOT NULL,
|
|
ip inet NOT NULL,
|
|
code integer,
|
|
|
|
-- Only set for web events
|
|
user_agent text,
|
|
user_id uuid,
|
|
slug_or_port text,
|
|
|
|
-- Null for web events
|
|
connection_id uuid,
|
|
disconnect_time timestamp with time zone, -- Null until we upsert a disconnect log for the same connection_id.
|
|
disconnect_reason text,
|
|
|
|
PRIMARY KEY (id)
|
|
);
|
|
|
|
|
|
COMMENT ON COLUMN connection_logs.code IS 'Either the HTTP status code of the web request, or the exit code of an SSH connection. For non-web connections, this is Null until we receive a disconnect event for the same connection_id.';
|
|
|
|
COMMENT ON COLUMN connection_logs.user_agent IS 'Null for SSH events. For web connections, this is the User-Agent header from the request.';
|
|
|
|
COMMENT ON COLUMN connection_logs.user_id IS 'Null for SSH events. For web connections, this is the ID of the user that made the request.';
|
|
|
|
COMMENT ON COLUMN connection_logs.slug_or_port IS 'Null for SSH events. For web connections, this is the slug of the app or the port number being forwarded.';
|
|
|
|
COMMENT ON COLUMN connection_logs.connection_id IS 'The SSH connection ID. Used to correlate connections and disconnections. As it originates from the agent, it is not guaranteed to be unique.';
|
|
|
|
COMMENT ON COLUMN connection_logs.disconnect_time IS 'The time the connection was closed. Null for web connections. For other connections, this is null until we receive a disconnect event for the same connection_id.';
|
|
|
|
COMMENT ON COLUMN connection_logs.disconnect_reason IS 'The reason the connection was closed. Null for web connections. For other connections, this is null until we receive a disconnect event for the same connection_id.';
|
|
|
|
COMMENT ON TYPE audit_action IS 'NOTE: `connect`, `disconnect`, `open`, and `close` are deprecated and no longer used - these events are now tracked in the connection_logs table.';
|
|
|
|
-- To associate connection closure events with the connection start events.
|
|
CREATE UNIQUE INDEX idx_connection_logs_connection_id_workspace_id_agent_name
|
|
ON connection_logs (connection_id, workspace_id, agent_name);
|
|
|
|
COMMENT ON INDEX idx_connection_logs_connection_id_workspace_id_agent_name IS 'Connection ID is NULL for web events, but present for SSH events. Therefore, this index allows multiple web events for the same workspace & agent. For SSH events, the upsertion query handles duplicates on this index by upserting the disconnect_time and disconnect_reason for the same connection_id when the connection is closed.';
|
|
|
|
CREATE INDEX idx_connection_logs_connect_time_desc ON connection_logs USING btree (connect_time DESC);
|
|
CREATE INDEX idx_connection_logs_organization_id ON connection_logs USING btree (organization_id);
|
|
CREATE INDEX idx_connection_logs_workspace_owner_id ON connection_logs USING btree (workspace_owner_id);
|
|
CREATE INDEX idx_connection_logs_workspace_id ON connection_logs USING btree (workspace_id);
|