Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

chore!: delete old connection events from audit log#18735

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.

Already on GitHub?Sign in to your account

Merged
ethanndickson merged 1 commit intomainfromethan/delete-old-connection-events
Jul 15, 2025
Merged
Show file tree
Hide file tree
Changes fromall commits
Commits
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
10 changes: 10 additions & 0 deletionscoderd/database/dbauthz/dbauthz.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -1552,6 +1552,16 @@ func (q *querier) DeleteOAuth2ProviderAppTokensByAppAndUserID(ctx context.Contex
return q.db.DeleteOAuth2ProviderAppTokensByAppAndUserID(ctx, arg)
}

func (q *querier) DeleteOldAuditLogConnectionEvents(ctx context.Context, threshold database.DeleteOldAuditLogConnectionEventsParams) error {
// `ResourceSystem` is deprecated, but it doesn't make sense to add
// `policy.ActionDelete` to `ResourceAuditLog`, since this is the one and
// only time we'll be deleting from the audit log.
if err := q.authorizeContext(ctx, policy.ActionDelete, rbac.ResourceSystem); err != nil {
return err
}
return q.db.DeleteOldAuditLogConnectionEvents(ctx, threshold)
}

func (q *querier) DeleteOldNotificationMessages(ctx context.Context) error {
if err := q.authorizeContext(ctx, policy.ActionDelete, rbac.ResourceNotificationMessage); err != nil {
return err
Expand Down
4 changes: 4 additions & 0 deletionscoderd/database/dbauthz/dbauthz_test.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -337,6 +337,10 @@ func (s *MethodTestSuite) TestAuditLogs() {
_ = dbgen.AuditLog(s.T(), db, database.AuditLog{})
check.Args(database.CountAuditLogsParams{}, emptyPreparedAuthorized{}).Asserts(rbac.ResourceAuditLog, policy.ActionRead)
}))
s.Run("DeleteOldAuditLogConnectionEvents", s.Subtest(func(db database.Store, check *expects) {
_ = dbgen.AuditLog(s.T(), db, database.AuditLog{})
check.Args(database.DeleteOldAuditLogConnectionEventsParams{}).Asserts(rbac.ResourceSystem, policy.ActionDelete)
}))
}

func (s *MethodTestSuite) TestConnectionLogs() {
Expand Down
7 changes: 7 additions & 0 deletionscoderd/database/dbmetrics/querymetrics.go
View file
Open in desktop

Some generated files are not rendered by default. Learn more abouthow customized files appear on GitHub.

14 changes: 14 additions & 0 deletionscoderd/database/dbmock/dbmock.go
View file
Open in desktop

Some generated files are not rendered by default. Learn more abouthow customized files appear on GitHub.

13 changes: 13 additions & 0 deletionscoderd/database/dbpurge/dbpurge.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -18,6 +18,11 @@ import (
const (
delay = 10 * time.Minute
maxAgentLogAge = 7 * 24 * time.Hour
// Connection events are now inserted into the `connection_logs` table.
// We'll slowly remove old connection events from the `audit_logs` table,
// but we won't touch the `connection_logs` table.
maxAuditLogConnectionEventAge = 90 * 24 * time.Hour // 90 days
auditLogConnectionEventBatchSize = 1000
)

// New creates a new periodically purging database instance.
Expand DownExpand Up@@ -63,6 +68,14 @@ func New(ctx context.Context, logger slog.Logger, db database.Store, clk quartz.
return xerrors.Errorf("failed to delete old notification messages: %w", err)
}

deleteOldAuditLogConnectionEventsBefore := start.Add(-maxAuditLogConnectionEventAge)
if err := tx.DeleteOldAuditLogConnectionEvents(ctx, database.DeleteOldAuditLogConnectionEventsParams{
BeforeTime: deleteOldAuditLogConnectionEventsBefore,
LimitCount: auditLogConnectionEventBatchSize,
}); err != nil {
return xerrors.Errorf("failed to delete old audit log connection events: %w", err)
}

logger.Debug(ctx, "purged old database entries", slog.F("duration", clk.Since(start)))

return nil
Expand Down
145 changes: 145 additions & 0 deletionscoderd/database/dbpurge/dbpurge_test.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -490,3 +490,148 @@ func containsProvisionerDaemon(daemons []database.ProvisionerDaemon, name string
return d.Name == name
})
}

//nolint:paralleltest // It uses LockIDDBPurge.
func TestDeleteOldAuditLogConnectionEvents(t *testing.T) {
ctx, cancel := context.WithTimeout(context.Background(), testutil.WaitShort)
defer cancel()

clk := quartz.NewMock(t)
now := dbtime.Now()
afterThreshold := now.Add(-91 * 24 * time.Hour) // 91 days ago (older than 90 day threshold)
beforeThreshold := now.Add(-30 * 24 * time.Hour) // 30 days ago (newer than 90 day threshold)
closeBeforeThreshold := now.Add(-89 * 24 * time.Hour) // 89 days ago
clk.Set(now).MustWait(ctx)

db, _ := dbtestutil.NewDB(t, dbtestutil.WithDumpOnFailure())
logger := slogtest.Make(t, &slogtest.Options{IgnoreErrors: true})
user := dbgen.User(t, db, database.User{})
org := dbgen.Organization(t, db, database.Organization{})

oldConnectLog := dbgen.AuditLog(t, db, database.AuditLog{
UserID: user.ID,
OrganizationID: org.ID,
Time: afterThreshold,
Action: database.AuditActionConnect,
ResourceType: database.ResourceTypeWorkspace,
})

oldDisconnectLog := dbgen.AuditLog(t, db, database.AuditLog{
UserID: user.ID,
OrganizationID: org.ID,
Time: afterThreshold,
Action: database.AuditActionDisconnect,
ResourceType: database.ResourceTypeWorkspace,
})

oldOpenLog := dbgen.AuditLog(t, db, database.AuditLog{
UserID: user.ID,
OrganizationID: org.ID,
Time: afterThreshold,
Action: database.AuditActionOpen,
ResourceType: database.ResourceTypeWorkspace,
})

oldCloseLog := dbgen.AuditLog(t, db, database.AuditLog{
UserID: user.ID,
OrganizationID: org.ID,
Time: afterThreshold,
Action: database.AuditActionClose,
ResourceType: database.ResourceTypeWorkspace,
})

recentConnectLog := dbgen.AuditLog(t, db, database.AuditLog{
UserID: user.ID,
OrganizationID: org.ID,
Time: beforeThreshold,
Action: database.AuditActionConnect,
ResourceType: database.ResourceTypeWorkspace,
})

oldNonConnectionLog := dbgen.AuditLog(t, db, database.AuditLog{
UserID: user.ID,
OrganizationID: org.ID,
Time: afterThreshold,
Action: database.AuditActionCreate,
ResourceType: database.ResourceTypeWorkspace,
})

nearThresholdConnectLog := dbgen.AuditLog(t, db, database.AuditLog{
UserID: user.ID,
OrganizationID: org.ID,
Time: closeBeforeThreshold,
Action: database.AuditActionConnect,
ResourceType: database.ResourceTypeWorkspace,
})

// Run the purge
done := awaitDoTick(ctx, t, clk)
closer := dbpurge.New(ctx, logger, db, clk)
defer closer.Close()
// Wait for tick
testutil.TryReceive(ctx, t, done)

// Verify results by querying all audit logs
logs, err := db.GetAuditLogsOffset(ctx, database.GetAuditLogsOffsetParams{})
require.NoError(t, err)

// Extract log IDs for comparison
logIDs := make([]uuid.UUID, len(logs))
for i, log := range logs {
logIDs[i] = log.AuditLog.ID
}

require.NotContains(t, logIDs, oldConnectLog.ID, "old connect log should be deleted")
require.NotContains(t, logIDs, oldDisconnectLog.ID, "old disconnect log should be deleted")
require.NotContains(t, logIDs, oldOpenLog.ID, "old open log should be deleted")
require.NotContains(t, logIDs, oldCloseLog.ID, "old close log should be deleted")
require.Contains(t, logIDs, recentConnectLog.ID, "recent connect log should be kept")
require.Contains(t, logIDs, nearThresholdConnectLog.ID, "near threshold connect log should be kept")
require.Contains(t, logIDs, oldNonConnectionLog.ID, "old non-connection log should be kept")
}

func TestDeleteOldAuditLogConnectionEventsLimit(t *testing.T) {
t.Parallel()

ctx, cancel := context.WithTimeout(context.Background(), testutil.WaitShort)
defer cancel()

db, _ := dbtestutil.NewDB(t, dbtestutil.WithDumpOnFailure())
user := dbgen.User(t, db, database.User{})
org := dbgen.Organization(t, db, database.Organization{})

now := dbtime.Now()
threshold := now.Add(-90 * 24 * time.Hour)

for i := 0; i < 5; i++ {
dbgen.AuditLog(t, db, database.AuditLog{
UserID: user.ID,
OrganizationID: org.ID,
Time: threshold.Add(-time.Duration(i+1) * time.Hour),
Action: database.AuditActionConnect,
ResourceType: database.ResourceTypeWorkspace,
})
}

err := db.DeleteOldAuditLogConnectionEvents(ctx, database.DeleteOldAuditLogConnectionEventsParams{
BeforeTime: threshold,
LimitCount: 1,
})
require.NoError(t, err)

logs, err := db.GetAuditLogsOffset(ctx, database.GetAuditLogsOffsetParams{})
require.NoError(t, err)

require.Len(t, logs, 4)

err = db.DeleteOldAuditLogConnectionEvents(ctx, database.DeleteOldAuditLogConnectionEventsParams{
BeforeTime: threshold,
LimitCount: 100,
})
require.NoError(t, err)

logs, err = db.GetAuditLogsOffset(ctx, database.GetAuditLogsOffsetParams{})
require.NoError(t, err)

require.Len(t, logs, 0)
}
1 change: 1 addition & 0 deletionscoderd/database/querier.go
View file
Open in desktop

Some generated files are not rendered by default. Learn more abouthow customized files appear on GitHub.

27 changes: 27 additions & 0 deletionscoderd/database/queries.sql.go
View file
Open in desktop

Some generated files are not rendered by default. Learn more abouthow customized files appear on GitHub.

16 changes: 16 additions & 0 deletionscoderd/database/queries/auditlogs.sql
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -237,3 +237,19 @@ WHERE
-- Authorize Filter clause will be injected below in CountAuthorizedAuditLogs
-- @authorize_filter
;

-- 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
);
Loading
[8]ページ先頭
©2009-2025 Movatter.jp