Mar 20, 2025 · Mar 20, 2025 · Mar 20, 2025
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
 return q.db.UpsertWorkspaceAgentPortShare(ctx, arg)
 }

 func (q *querier) UpsertWorkspaceAppAuditSession(ctx context.Context, arg database.UpsertWorkspaceAppAuditSessionParams) (time.Time, error) {
 func (q *querier) UpsertWorkspaceAppAuditSession(ctx context.Context, arg database.UpsertWorkspaceAppAuditSessionParams) (bool, error) {
 if err := q.authorizeContext(ctx, policy.ActionUpdate, rbac.ResourceSystem); err != nil {
 returntime.Time{}, err
 returnfalse, err
 }
 return q.db.UpsertWorkspaceAppAuditSession(ctx, arg)
 }
diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
 return psl, nil
 }

 func (q *FakeQuerier) UpsertWorkspaceAppAuditSession(_ context.Context, arg database.UpsertWorkspaceAppAuditSessionParams) (time.Time, error) {
 func (q *FakeQuerier) UpsertWorkspaceAppAuditSession(_ context.Context, arg database.UpsertWorkspaceAppAuditSessionParams) (bool, error) {
 err := validateDatabaseType(arg)
 if err != nil {
 returntime.Time{}, err
 returnfalse, err
 }

 q.mutex.Lock()

 q.workspaceAppAuditSessions[i].UpdatedAt = arg.UpdatedAt
 if !fresh {
 q.workspaceAppAuditSessions[i].ID = arg.ID
 q.workspaceAppAuditSessions[i].StartedAt = arg.StartedAt
 returnarg.StartedAt, nil
 returntrue, nil
 }
 returns.StartedAt, nil
 returnfalse, nil
 }

 q.workspaceAppAuditSessions = append(q.workspaceAppAuditSessions, database.WorkspaceAppAuditSession{
 StartedAt:  arg.StartedAt,
 UpdatedAt:  arg.UpdatedAt,
 })
 returnarg.StartedAt, nil
 returntrue, nil
 }

 func (q *FakeQuerier) GetAuthorizedTemplates(ctx context.Context, arg database.GetTemplatesWithFilterParams, prepared rbac.PreparedAuthorized) ([]database.Template, error) {
diff --git a/coderd/database/dbmetrics/querymetrics.go b/coderd/database/dbmetrics/querymetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000302_fix_app_audit_session_race.down.sql b/coderd/database/migrations/000302_fix_app_audit_session_race.down.sql
 ALTER TABLE workspace_app_audit_sessions
 DROP COLUMN id;
diff --git a/coderd/database/migrations/000302_fix_app_audit_session_race.up.sql b/coderd/database/migrations/000302_fix_app_audit_session_race.up.sql
 -- Add column with default to fix existing rows.
 ALTER TABLE workspace_app_audit_sessions
 ADD COLUMN id UUID PRIMARY KEY DEFAULT gen_random_uuid();
 ALTER TABLE workspace_app_audit_sessions
 ALTER COLUMN id DROP DEFAULT;
diff --git a/coderd/database/models.go b/coderd/database/models.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/workspaceappaudit.sql b/coderd/database/queries/workspaceappaudit.sql
 -- name: UpsertWorkspaceAppAuditSession :one
 --
 -- Insert a new workspace app audit session or update an existing one, if
 -- started_at is updated, it means the session has been restarted.
 -- The returned boolean, new_or_stale, can be used to deduce if a new session
 -- was started. This means that a new row was inserted (no previous session) or
 -- the updated_at is older than stale interval.
 INSERT INTO
 workspace_app_audit_sessions (
 id,
 agent_id,
 app_id,
 user_id,
 $6,
 $7,
 $8,
 $9
 $9,
 $10
 )
 ON CONFLICT
 (agent_id, app_id, user_id, ip, user_agent, slug_or_port, status_code)
 DO
 UPDATE
 SET
 -- ID is used to know if session was reset on upsert.
 id = CASE
 WHEN workspace_app_audit_sessions.updated_at > NOW() - (@stale_interval_ms::bigint || ' ms')::interval
 THEN workspace_app_audit_sessions.id
 ELSE EXCLUDED.id
 END,
 started_at = CASE
 WHEN workspace_app_audit_sessions.updated_at > NOW() - (@stale_interval_ms::bigint || ' ms')::interval
 THEN workspace_app_audit_sessions.started_at
 ELSE EXCLUDED.started_at
 END,
 updated_at = EXCLUDED.updated_at
 RETURNING
 started_at;
 id = $1 AS new_or_stale;
diff --git a/coderd/database/unique_constraint.go b/coderd/database/unique_constraint.go
diff --git a/coderd/workspaceapps/db.go b/coderd/workspaceapps/db.go
 slog.F("status_code", statusCode),
 )

 varstartedAt time.Time
 varnewOrStale bool
 err := p.Database.InTx(func(tx database.Store) (err error) {
 // nolint:gocritic // System context is needed to write audit sessions.
 dangerousSystemCtx := dbauthz.AsSystemRestricted(ctx)

 startedAt, err = tx.UpsertWorkspaceAppAuditSession(dangerousSystemCtx, database.UpsertWorkspaceAppAuditSessionParams{
 newOrStale, err = tx.UpsertWorkspaceAppAuditSession(dangerousSystemCtx, database.UpsertWorkspaceAppAuditSessionParams{
 // Config.
 StaleIntervalMS: p.WorkspaceAppAuditSessionTimeout.Milliseconds(),

 // Data.
 ID:         uuid.New(),
 AgentID:    aReq.dbReq.Agent.ID,
 AppID:      aReq.dbReq.App.ID, // Can be unset, in which case uuid.Nil is fine.
 UserID:     userID,            // Can be unset, in which case uuid.Nil is fine.
 return
 }

 if !startedAt.Equal(aReq.time) {
 //If the unique session wasn'trenewed, we don't want to log a new
 //audit event for it.
 if !newOrStale {
 //We either didn'tinsert a new session, or the session
 //didn't timeout due to inactivity.
 return
 }
Original file line number	Diff line number	Diff line change
Expand Up		@@ -4615,9 +4615,9 @@ func (q *querier) UpsertWorkspaceAgentPortShare(ctx context.Context, arg databas
		return q.db.UpsertWorkspaceAgentPortShare(ctx, arg)
		}

		func (q *querier) UpsertWorkspaceAppAuditSession(ctx context.Context, arg database.UpsertWorkspaceAppAuditSessionParams) (time.Time, error) {
		func (q *querier) UpsertWorkspaceAppAuditSession(ctx context.Context, arg database.UpsertWorkspaceAppAuditSessionParams) (bool, error) {
		if err := q.authorizeContext(ctx, policy.ActionUpdate, rbac.ResourceSystem); err != nil {
		returntime.Time{}, err
		returnfalse, err
		}
		return q.db.UpsertWorkspaceAppAuditSession(ctx, arg)
		}
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -12283,10 +12283,10 @@ func (q *FakeQuerier) UpsertWorkspaceAgentPortShare(_ context.Context, arg datab
		return psl, nil
		}

		func (q *FakeQuerier) UpsertWorkspaceAppAuditSession(_ context.Context, arg database.UpsertWorkspaceAppAuditSessionParams) (time.Time, error) {
		func (q *FakeQuerier) UpsertWorkspaceAppAuditSession(_ context.Context, arg database.UpsertWorkspaceAppAuditSessionParams) (bool, error) {
		err := validateDatabaseType(arg)
		if err != nil {
		returntime.Time{}, err
		returnfalse, err
		}

		q.mutex.Lock()
Expand DownExpand Up		@@ -12320,10 +12320,11 @@ func (q *FakeQuerier) UpsertWorkspaceAppAuditSession(_ context.Context, arg data

		q.workspaceAppAuditSessions[i].UpdatedAt = arg.UpdatedAt
		if !fresh {
		q.workspaceAppAuditSessions[i].ID = arg.ID
		q.workspaceAppAuditSessions[i].StartedAt = arg.StartedAt
		returnarg.StartedAt, nil
		returntrue, nil
		}
		returns.StartedAt, nil
		returnfalse, nil
		}

		q.workspaceAppAuditSessions = append(q.workspaceAppAuditSessions, database.WorkspaceAppAuditSession{
Expand All		@@ -12337,7 +12338,7 @@ func (q *FakeQuerier) UpsertWorkspaceAppAuditSession(_ context.Context, arg data
		StartedAt: arg.StartedAt,
		UpdatedAt: arg.UpdatedAt,
		})
		returnarg.StartedAt, nil
		returntrue, nil
		}

		func (q *FakeQuerier) GetAuthorizedTemplates(ctx context.Context, arg database.GetTemplatesWithFilterParams, prepared rbac.PreparedAuthorized) ([]database.Template, error) {
Expand Down
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,2 @@
		ALTER TABLE workspace_app_audit_sessions
		DROP COLUMN id;
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,5 @@
		-- Add column with default to fix existing rows.
		ALTER TABLE workspace_app_audit_sessions
		ADD COLUMN id UUID PRIMARY KEY DEFAULT gen_random_uuid();
mafredri marked this conversation as resolved. Show resolvedHide resolved
		ALTER TABLE workspace_app_audit_sessions
		ALTER COLUMN id DROP DEFAULT;
mafredri marked this conversation as resolved. Show resolvedHide resolved
Original file line number	Diff line number	Diff line change
		@@ -1,9 +1,11 @@
		-- name: UpsertWorkspaceAppAuditSession :one
		--
		-- Insert a new workspace app audit session or update an existing one, if
		-- started_at is updated, it means the session has been restarted.
		-- The returned boolean, new_or_stale, can be used to deduce if a new session
		-- was started. This means that a new row was inserted (no previous session) or
		-- the updated_at is older than stale interval.
		INSERT INTO
		workspace_app_audit_sessions (
		id,
		agent_id,
		app_id,
		user_id,
Expand All		@@ -24,18 +26,25 @@ VALUES
		$6,
		$7,
		$8,
		$9
		$9,
		$10
		)
		ON CONFLICT
		(agent_id, app_id, user_id, ip, user_agent, slug_or_port, status_code)
		DO
		UPDATE
		SET
		-- ID is used to know if session was reset on upsert.
		id = CASE
		WHEN workspace_app_audit_sessions.updated_at > NOW() - (@stale_interval_ms::bigint \|\| ' ms')::interval
		THEN workspace_app_audit_sessions.id
		ELSE EXCLUDED.id
		END,
		started_at = CASE
		WHEN workspace_app_audit_sessions.updated_at > NOW() - (@stale_interval_ms::bigint \|\| ' ms')::interval
		THEN workspace_app_audit_sessions.started_at
		ELSE EXCLUDED.started_at
		END,
		updated_at = EXCLUDED.updated_at
		RETURNING
		started_at;
		id = $1 AS new_or_stale;
Original file line number	Diff line number	Diff line change
Expand Up		@@ -447,16 +447,17 @@ func (p *DBTokenProvider) auditInitRequest(ctx context.Context, w http.ResponseW
		slog.F("status_code", statusCode),
		)

		varstartedAt time.Time
		varnewOrStale bool
		err := p.Database.InTx(func(tx database.Store) (err error) {
		// nolint:gocritic // System context is needed to write audit sessions.
		dangerousSystemCtx := dbauthz.AsSystemRestricted(ctx)

		startedAt, err = tx.UpsertWorkspaceAppAuditSession(dangerousSystemCtx, database.UpsertWorkspaceAppAuditSessionParams{
		newOrStale, err = tx.UpsertWorkspaceAppAuditSession(dangerousSystemCtx, database.UpsertWorkspaceAppAuditSessionParams{
		// Config.
		StaleIntervalMS: p.WorkspaceAppAuditSessionTimeout.Milliseconds(),

		// Data.
		ID: uuid.New(),
		AgentID: aReq.dbReq.Agent.ID,
		AppID: aReq.dbReq.App.ID, // Can be unset, in which case uuid.Nil is fine.
		UserID: userID, // Can be unset, in which case uuid.Nil is fine.
Expand All		@@ -481,9 +482,9 @@ func (p *DBTokenProvider) auditInitRequest(ctx context.Context, w http.ResponseW
		return
		}

		if !startedAt.Equal(aReq.time) {
		//If the unique session wasn'trenewed, we don't want to log a new
		//audit event for it.
		if !newOrStale {
		//We either didn'tinsert a new session, or the session
		//didn't timeout due to inactivity.
		return
		}

Expand Down