feat(coderd): add oauth2 scope columns and single-use delete queries

Migration 000567 adds a nullable `scope text` to oauth2_provider_app_codes and oauth2_provider_app_tokens so the scope negotiated at /oauth2/authorize can travel from a code to the token it is exchanged for. No backfill, and every insert writes NULL for now, which reads as unrestricted access, so behavior is unchanged. DeleteOAuth2ProviderAppCodeByIDReturningID and DeleteAPIKeyByIDReturningID return sql.ErrNoRows when the row is already gone, letting the grant paths enforce single use without a read-then-write race. The existing blind deletes and their call sites are unchanged. Refs PLAT-478
BobbyHo · Aug 13, 2026 · Aug 10, 2026 · Aug 11, 2026 · Aug 11, 2026 · Aug 11, 2026
commit 7efa327698ab828dd86ae88dd5ba4b0c061ca880
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -2067,6 +2067,17 @@ func (q *querier) DeleteAPIKeyByID(ctx context.Context, id string) error {
 	return deleteQ(q.log, q.auth, q.db.GetAPIKeyByID, q.db.DeleteAPIKeyByID)(ctx, id)
 }
 
+func (q *querier) DeleteAPIKeyByIDReturningID(ctx context.Context, id string) (string, error) {
+	key, err := q.db.GetAPIKeyByID(ctx, id)
+	if err != nil {
+		return "", err
+	}
+	if err := q.authorizeContext(ctx, policy.ActionDelete, key); err != nil {
+		return "", err
+	}
+	return q.db.DeleteAPIKeyByIDReturningID(ctx, id)
+}
+
 func (q *querier) DeleteAPIKeysByUserID(ctx context.Context, userID uuid.UUID) error {
 	// TODO: This is not 100% correct because it omits apikey IDs.
 	err := q.authorizeContext(ctx, policy.ActionDelete,
@@ -2314,6 +2325,17 @@ func (q *querier) DeleteOAuth2ProviderAppCodeByID(ctx context.Context, id uuid.U
 	return q.db.DeleteOAuth2ProviderAppCodeByID(ctx, id)
 }
 
+func (q *querier) DeleteOAuth2ProviderAppCodeByIDReturningID(ctx context.Context, id uuid.UUID) (uuid.UUID, error) {
+	code, err := q.db.GetOAuth2ProviderAppCodeByID(ctx, id)
+	if err != nil {
+		return uuid.Nil, err
+	}
+	if err := q.authorizeContext(ctx, policy.ActionDelete, code); err != nil {
+		return uuid.Nil, err
+	}
+	return q.db.DeleteOAuth2ProviderAppCodeByIDReturningID(ctx, id)
+}
+
 func (q *querier) DeleteOAuth2ProviderAppCodesByAppAndUserID(ctx context.Context, arg database.DeleteOAuth2ProviderAppCodesByAppAndUserIDParams) error {
 	if err := q.authorizeContext(ctx, policy.ActionDelete,
 		rbac.ResourceOauth2AppCodeToken.WithOwner(arg.UserID.String())); err != nil {

diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
@@ -360,6 +360,12 @@ func (s *MethodTestSuite) TestAPIKey() {
 		dbm.EXPECT().DeleteAPIKeyByID(gomock.Any(), key.ID).Return(nil).AnyTimes()
 		check.Args(key.ID).Asserts(key, policy.ActionDelete).Returns()
 	}))
+	s.Run("DeleteAPIKeyByIDReturningID", s.Mocked(func(dbm *dbmock.MockStore, faker *gofakeit.Faker, check *expects) {
+		key := testutil.Fake(s.T(), faker, database.APIKey{})
+		dbm.EXPECT().GetAPIKeyByID(gomock.Any(), key.ID).Return(key, nil).AnyTimes()
+		dbm.EXPECT().DeleteAPIKeyByIDReturningID(gomock.Any(), key.ID).Return(key.ID, nil).AnyTimes()
+		check.Args(key.ID).Asserts(key, policy.ActionDelete).Returns(key.ID)
+	}))
 	s.Run("DeleteExpiredAPIKeys", s.Mocked(func(dbm *dbmock.MockStore, faker *gofakeit.Faker, check *expects) {
 		args := database.DeleteExpiredAPIKeysParams{
 			Before:     time.Date(2025, 11, 21, 0, 0, 0, 0, time.UTC),
@@ -6001,6 +6007,15 @@ func (s *MethodTestSuite) TestOAuth2ProviderAppCodes() {
 		})
 		check.Args(code.ID).Asserts(code, policy.ActionDelete)
 	}))
+	s.Run("DeleteOAuth2ProviderAppCodeByIDReturningID", s.Subtest(func(db database.Store, check *expects) {
+		user := dbgen.User(s.T(), db, database.User{})
+		app := dbgen.OAuth2ProviderApp(s.T(), db, database.OAuth2ProviderApp{})
+		code := dbgen.OAuth2ProviderAppCode(s.T(), db, database.OAuth2ProviderAppCode{
+			AppID:  app.ID,
+			UserID: user.ID,
+		})
+		check.Args(code.ID).Asserts(code, policy.ActionDelete).Returns(code.ID)
+	}))
 	s.Run("DeleteOAuth2ProviderAppCodesByAppAndUserID", s.Subtest(func(db database.Store, check *expects) {
 		dbtestutil.DisableForeignKeysAndTriggers(s.T(), db)
 		user := dbgen.User(s.T(), db, database.User{})

diff --git a/coderd/database/dbgen/dbgen.go b/coderd/database/dbgen/dbgen.go
@@ -1784,6 +1784,7 @@ func OAuth2ProviderAppCode(t testing.TB, db database.Store, seed database.OAuth2
 		CodeChallengeMethod: seed.CodeChallengeMethod,
 		StateHash:           seed.StateHash,
 		RedirectUri:         seed.RedirectUri,
+		Scope:               seed.Scope,
 	})
 	require.NoError(t, err, "insert oauth2 app code")
 	return code
@@ -1805,6 +1806,7 @@ func OAuth2ProviderAppToken(t testing.TB, db database.Store, seed database.OAuth
 		APIKeyID:    takeFirst(seed.APIKeyID, uuid.New().String()),
 		UserID:      takeFirst(seed.UserID, uuid.New()),
 		Audience:    seed.Audience,
+		Scope:       seed.Scope,
 	})
 	require.NoError(t, err, "insert oauth2 app token")
 	return token

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/000567_oauth2_scope_enforcement.down.sql b/coderd/database/migrations/000567_oauth2_scope_enforcement.down.sql
@@ -0,0 +1,3 @@
+ALTER TABLE oauth2_provider_app_codes DROP COLUMN scope;
+
+ALTER TABLE oauth2_provider_app_tokens DROP COLUMN scope;
diff --git a/coderd/database/migrations/000567_oauth2_scope_enforcement.up.sql b/coderd/database/migrations/000567_oauth2_scope_enforcement.up.sql
@@ -0,0 +1,16 @@
+-- The scope negotiated at /oauth2/authorize travels with the grant itself:
+-- recorded on the code when it is issued, then carried onto the token it is
+-- exchanged for so a refresh can narrow against what was actually granted
+-- rather than against the app's current allowlist.
+--
+-- Both columns are nullable with no backfill. A NULL means "no scope was
+-- recorded for this grant", which the token endpoint reads as unrestricted
+-- access, so codes and tokens issued before this migration keep working.
+
+ALTER TABLE oauth2_provider_app_codes ADD COLUMN scope text;
+
+ALTER TABLE oauth2_provider_app_tokens ADD COLUMN scope text;
+
+COMMENT ON COLUMN oauth2_provider_app_codes.scope IS 'Space-separated scope negotiated at authorization time. NULL means no scope was recorded and the exchanged token is unrestricted.';
+
+COMMENT ON COLUMN oauth2_provider_app_tokens.scope IS 'Space-separated scope granted to this token. A refresh may narrow this but never widen it. NULL means no scope was recorded and the token is unrestricted.';
diff --git a/coderd/database/models.go b/coderd/database/models.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,3 @@
		ALTER TABLE oauth2_provider_app_codes DROP COLUMN scope;

		ALTER TABLE oauth2_provider_app_tokens DROP COLUMN scope;