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

Commitcefe07d

Browse files
authored
feat: purge expired api keys in dbpurge (#20863)
closes#19889This is in response to a migration in v2.27 that takes very long on deployments with large `api_key` tables.
1 parentc6631e1 commitcefe07d

File tree

10 files changed

+197
-4
lines changed

10 files changed

+197
-4
lines changed

‎coderd/database/dbauthz/dbauthz.go‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1641,6 +1641,15 @@ func (q *querier) DeleteCustomRole(ctx context.Context, arg database.DeleteCusto
16411641
returnq.db.DeleteCustomRole(ctx,arg)
16421642
}
16431643

1644+
func (q*querier)DeleteExpiredAPIKeys(ctx context.Context,arg database.DeleteExpiredAPIKeysParams) (int64,error) {
1645+
// Requires DELETE across all API keys.
1646+
iferr:=q.authorizeContext(ctx,policy.ActionDelete,rbac.ResourceApiKey);err!=nil {
1647+
return0,err
1648+
}
1649+
1650+
returnq.db.DeleteExpiredAPIKeys(ctx,arg)
1651+
}
1652+
16441653
func (q*querier)DeleteExternalAuthLink(ctx context.Context,arg database.DeleteExternalAuthLinkParams)error {
16451654
returnfetchAndExec(q.log,q.auth,policy.ActionUpdatePersonal,func(ctx context.Context,arg database.DeleteExternalAuthLinkParams) (database.ExternalAuthLink,error) {
16461655
//nolint:gosimple

‎coderd/database/dbauthz/dbauthz_test.go‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -216,6 +216,14 @@ func (s *MethodTestSuite) TestAPIKey() {
216216
dbm.EXPECT().DeleteAPIKeyByID(gomock.Any(),key.ID).Return(nil).AnyTimes()
217217
check.Args(key.ID).Asserts(key,policy.ActionDelete).Returns()
218218
}))
219+
s.Run("DeleteExpiredAPIKeys",s.Mocked(func(dbm*dbmock.MockStore,faker*gofakeit.Faker,check*expects) {
220+
args:= database.DeleteExpiredAPIKeysParams{
221+
Before:time.Date(2025,11,21,0,0,0,0,time.UTC),
222+
LimitCount:1000,
223+
}
224+
dbm.EXPECT().DeleteExpiredAPIKeys(gomock.Any(),args).Return(int64(0),nil).AnyTimes()
225+
check.Args(args).Asserts(rbac.ResourceApiKey,policy.ActionDelete).Returns(int64(0))
226+
}))
219227
s.Run("GetAPIKeyByID",s.Mocked(func(dbm*dbmock.MockStore,faker*gofakeit.Faker,check*expects) {
220228
key:=testutil.Fake(s.T(),faker, database.APIKey{})
221229
dbm.EXPECT().GetAPIKeyByID(gomock.Any(),key.ID).Return(key,nil).AnyTimes()

‎coderd/database/dbgen/dbgen.go‎

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -175,6 +175,13 @@ func APIKey(t testing.TB, db database.Store, seed database.APIKey, munge ...func
175175
}
176176
}
177177

178+
// It does not make sense for the created_at to be after the expires_at.
179+
// So if expires is set, change the default created_at to be 24 hours before.
180+
varcreatedAt time.Time
181+
if!seed.ExpiresAt.IsZero()&&seed.CreatedAt.IsZero() {
182+
createdAt=seed.ExpiresAt.Add(-24*time.Hour)
183+
}
184+
178185
params:= database.InsertAPIKeyParams{
179186
ID:takeFirst(seed.ID,id),
180187
// 0 defaults to 86400 at the db layer
@@ -184,7 +191,7 @@ func APIKey(t testing.TB, db database.Store, seed database.APIKey, munge ...func
184191
UserID:takeFirst(seed.UserID,uuid.New()),
185192
LastUsed:takeFirst(seed.LastUsed,dbtime.Now()),
186193
ExpiresAt:takeFirst(seed.ExpiresAt,dbtime.Now().Add(time.Hour)),
187-
CreatedAt:takeFirst(seed.CreatedAt,dbtime.Now()),
194+
CreatedAt:takeFirst(seed.CreatedAt,createdAt,dbtime.Now()),
188195
UpdatedAt:takeFirst(seed.UpdatedAt,dbtime.Now()),
189196
LoginType:takeFirst(seed.LoginType,database.LoginTypePassword),
190197
Scopes:takeFirstSlice([]database.APIKeyScope(seed.Scopes), []database.APIKeyScope{database.ApiKeyScopeCoderAll}),

‎coderd/database/dbmetrics/querymetrics.go‎

Lines changed: 7 additions & 0 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

‎coderd/database/dbmock/dbmock.go‎

Lines changed: 15 additions & 0 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

‎coderd/database/dbpurge/dbpurge.go‎

Lines changed: 19 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,19 @@ func New(ctx context.Context, logger slog.Logger, db database.Store, vals *coder
7878
iferr:=tx.ExpirePrebuildsAPIKeys(ctx,dbtime.Time(start));err!=nil {
7979
returnxerrors.Errorf("failed to expire prebuilds user api keys: %w",err)
8080
}
81+
expiredAPIKeys,err:=tx.DeleteExpiredAPIKeys(ctx, database.DeleteExpiredAPIKeysParams{
82+
// Leave expired keys for a week to allow the backend to know the difference
83+
// between a 404 and an expired key. This purge code is just to bound the size of
84+
// the table to something more reasonable.
85+
Before:dbtime.Time(start.Add(time.Hour*24*7*-1)),
86+
// There could be a lot of expired keys here, so set a limit to prevent this
87+
// taking too long.
88+
// This runs every 10 minutes, so it deletes ~1.5m keys per day at most.
89+
LimitCount:10000,
90+
})
91+
iferr!=nil {
92+
returnxerrors.Errorf("failed to delete expired api keys: %w",err)
93+
}
8194
deleteOldTelemetryLocksBefore:=start.Add(-maxTelemetryHeartbeatAge)
8295
iferr:=tx.DeleteOldTelemetryLocks(ctx,deleteOldTelemetryLocksBefore);err!=nil {
8396
returnxerrors.Errorf("failed to delete old telemetry locks: %w",err)
@@ -93,13 +106,16 @@ func New(ctx context.Context, logger slog.Logger, db database.Store, vals *coder
93106

94107
deleteAIBridgeRecordsBefore:=start.Add(-vals.AI.BridgeConfig.Retention.Value())
95108
// nolint:gocritic // Needs to run as aibridge context.
96-
count,err:=tx.DeleteOldAIBridgeRecords(dbauthz.AsAIBridged(ctx),deleteAIBridgeRecordsBefore)
109+
purgedAIBridgeRecords,err:=tx.DeleteOldAIBridgeRecords(dbauthz.AsAIBridged(ctx),deleteAIBridgeRecordsBefore)
97110
iferr!=nil {
98111
returnxerrors.Errorf("failed to delete old aibridge records: %w",err)
99112
}
100-
logger.Debug(ctx,"purged aibridge entries",slog.F("count",count),slog.F("since",deleteAIBridgeRecordsBefore.Format(time.RFC3339)))
101113

102-
logger.Debug(ctx,"purged old database entries",slog.F("duration",clk.Since(start)))
114+
logger.Debug(ctx,"purged old database entries",
115+
slog.F("expired_api_keys",expiredAPIKeys),
116+
slog.F("aibridge_records",purgedAIBridgeRecords),
117+
slog.F("duration",clk.Since(start)),
118+
)
103119

104120
returnnil
105121
},database.DefaultTXOptions().WithID("db_purge"));err!=nil {

‎coderd/database/querier.go‎

Lines changed: 1 addition & 0 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

‎coderd/database/querier_test.go‎

Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7835,3 +7835,81 @@ func TestUpdateAIBridgeInterceptionEnded(t *testing.T) {
78357835
}
78367836
})
78377837
}
7838+
7839+
funcTestDeleteExpiredAPIKeys(t*testing.T) {
7840+
t.Parallel()
7841+
db,_:=dbtestutil.NewDB(t)
7842+
7843+
// Constant time for testing
7844+
now:=time.Date(2025,11,20,12,0,0,0,time.UTC)
7845+
expiredBefore:=now.Add(-time.Hour)// Anything before this is expired
7846+
7847+
ctx:=testutil.Context(t,testutil.WaitLong)
7848+
7849+
user:=dbgen.User(t,db, database.User{})
7850+
7851+
expiredTimes:= []time.Time{
7852+
expiredBefore.Add(-time.Hour*24*365),
7853+
expiredBefore.Add(-time.Hour*24),
7854+
expiredBefore.Add(-time.Hour),
7855+
expiredBefore.Add(-time.Minute),
7856+
expiredBefore.Add(-time.Second),
7857+
}
7858+
for_,exp:=rangeexpiredTimes {
7859+
// Expired api keys
7860+
dbgen.APIKey(t,db, database.APIKey{UserID:user.ID,ExpiresAt:exp})
7861+
}
7862+
7863+
unexpiredTimes:= []time.Time{
7864+
expiredBefore.Add(time.Hour*24*365),
7865+
expiredBefore.Add(time.Hour*24),
7866+
expiredBefore.Add(time.Hour),
7867+
expiredBefore.Add(time.Minute),
7868+
expiredBefore.Add(time.Second),
7869+
}
7870+
for_,unexp:=rangeunexpiredTimes {
7871+
// Unexpired api keys
7872+
dbgen.APIKey(t,db, database.APIKey{UserID:user.ID,ExpiresAt:unexp})
7873+
}
7874+
7875+
// All keys are present before deletion
7876+
keys,err:=db.GetAPIKeysByUserID(ctx, database.GetAPIKeysByUserIDParams{
7877+
LoginType:user.LoginType,
7878+
UserID:user.ID,
7879+
})
7880+
require.NoError(t,err)
7881+
require.Len(t,keys,len(expiredTimes)+len(unexpiredTimes))
7882+
7883+
// Delete expired keys
7884+
// First verify the limit works by deleting one at a time
7885+
deletedCount,err:=db.DeleteExpiredAPIKeys(ctx, database.DeleteExpiredAPIKeysParams{
7886+
Before:expiredBefore,
7887+
LimitCount:1,
7888+
})
7889+
require.NoError(t,err)
7890+
require.Equal(t,int64(1),deletedCount)
7891+
7892+
// Ensure it was deleted
7893+
remaining,err:=db.GetAPIKeysByUserID(ctx, database.GetAPIKeysByUserIDParams{
7894+
LoginType:user.LoginType,
7895+
UserID:user.ID,
7896+
})
7897+
require.NoError(t,err)
7898+
require.Len(t,remaining,len(expiredTimes)+len(unexpiredTimes)-1)
7899+
7900+
// Delete the rest of the expired keys
7901+
deletedCount,err=db.DeleteExpiredAPIKeys(ctx, database.DeleteExpiredAPIKeysParams{
7902+
Before:expiredBefore,
7903+
LimitCount:100,
7904+
})
7905+
require.NoError(t,err)
7906+
require.Equal(t,int64(len(expiredTimes)-1),deletedCount)
7907+
7908+
// Ensure only unexpired keys remain
7909+
remaining,err=db.GetAPIKeysByUserID(ctx, database.GetAPIKeysByUserIDParams{
7910+
LoginType:user.LoginType,
7911+
UserID:user.ID,
7912+
})
7913+
require.NoError(t,err)
7914+
require.Len(t,remaining,len(unexpiredTimes))
7915+
}

‎coderd/database/queries.sql.go‎

Lines changed: 32 additions & 0 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

‎coderd/database/queries/apikeys.sql‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -85,6 +85,26 @@ DELETE FROM
8585
WHERE
8686
user_id= $1;
8787

88+
-- name: DeleteExpiredAPIKeys :one
89+
WITH expired_keysAS (
90+
SELECT id
91+
FROM api_keys
92+
-- expired keys only
93+
WHERE expires_at< @before::timestamptz
94+
LIMIT @limit_count
95+
),
96+
deleted_rowsAS (
97+
DELETEFROM
98+
api_keys
99+
USING
100+
expired_keys
101+
WHERE
102+
api_keys.id=expired_keys.id
103+
RETURNINGapi_keys.id
104+
)
105+
SELECTCOUNT(deleted_rows.id)AS deleted_countFROM deleted_rows;
106+
;
107+
88108
-- name: ExpirePrebuildsAPIKeys :exec
89109
-- Firstly, collect api_keys owned by the prebuilds user that correlate
90110
-- to workspaces no longer owned by the prebuilds user.

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp