Implement OAuth access token archival system and retention policy

Shilpa Kundapurrequested to merge
521855-add-oauth-access-tokens-archive-table into master

What does this MR do and why?

  • Adds oauth_access_token_archived_records table and basic model
    • The table has only organization FKs and minimal constraints because it is meant to be a simple dumping ground for data
    • The model has no real functionality since this model won't be used throughout the application, it will just be used in one data migration
  • Records revoked and created more than 1 month ago in oauth_access_token will be copied to oauth_access_token_archived_records and then deleted from oauth_access_token
    • In this way, we soft delete records in the archive.
  • We'll drop the oauth_access_token_archived_records table in a few milestones once we've confirmed the retention policy is being enforced with no issues

This will drastically reduce the number of records in the oauth_access_token and its size (after table swap with a clean table, or pg_repack). A simulation on postgres.ai using the main production DB replica shows that applying cleanup according to the retention policy would reduce table size from 109 GB to ~10 GB (indexes excluded). More details: https://gitlab.com/gitlab-org/gitlab/-/issues/521855#note_2703882221

This is the Part 1 of https://gitlab.com/gitlab-org/gitlab/-/issues/521855.
Part 2 will be handled in: !202767 (merged)

References

Main issue: https://gitlab.com/gitlab-org/gitlab/-/issues/521855
Plan: #555382
Details: https://gitlab.com/gitlab-org/gitlab/-/issues/521855#note_2703882221

Screenshots or screen recordings

Before After

How to set up and validate locally

  • Run migrations, run tests

Queries in batch

SELECT "oauth_access_tokens"."id" FROM "oauth_access_tokens" WHERE "oauth_access_tokens"."id" BETWEEN 1 AND 2080011 AND "oauth_access_tokens"."id" >= 82874 ORDER BY "oauth_access_tokens"."id" ASC LIMIT 1 OFFSET 1000                                                                   

WITH deleted AS (
    DELETE FROM oauth_access_tokens                                                                      
    WHERE id IN (SELECT "oauth_access_tokens"."id" FROM "oauth_access_tokens" WHERE "oauth_access_tokens"."id" BETWEEN 1 AND 2080011 AND "oauth_access_tokens"."id" >= 82874 AND "oauth_access_tokens"."id" < 88886 AND "oauth_access_tokens"."revoked_at" < '2025-08-10 00:00:00' LIMIT 1000)                             
  RETURNING *                                                                                            
)                                                                                                        
INSERT INTO oauth_access_token_archived_records                                                          
  (id, resource_owner_id, application_id, token, refresh_token,                                          
   expires_in, revoked_at, created_at, scopes, organization_id, archived_at)                             
SELECT                                                                                                   
  id, resource_owner_id, application_id, token, refresh_token,                                           
  expires_in, revoked_at, created_at, scopes, organization_id,                                           
  CURRENT_TIMESTAMP as archived_at                                                                       
FROM deleted
Before optimization 
SELECT "oauth_access_tokens"."id" FROM "oauth_access_tokens" WHERE "oauth_access_tokens"."id" BETWEEN 1 AND 2040018 AND "oauth_access_tokens"."revoked_at" IS NOT NULL AND "oauth_access_tokens"."id" >= 2038198 ORDER BY "oauth_access_tokens"."id" ASC LIMIT 1 OFFSET 1000 

SELECT 1 AS one FROM "oauth_access_tokens" WHERE "oauth_access_tokens"."id" BETWEEN 1 AND 2040018 AND "oauth_access_tokens"."revoked_at" IS NOT NULL AND "oauth_access_tokens"."id" >= 2038198 AND "oauth_access_tokens"."id" < 2039355 AND "oauth_access_tokens"."created_at" <= '2025-08-02 00:00:00' LIMIT 1 
                                      
SELECT "oauth_access_tokens".* FROM "oauth_access_tokens" WHERE "oauth_access_tokens"."id" BETWEEN 1 AND 2040018 AND "oauth_access_tokens"."revoked_at" IS NOT NULL AND "oauth_access_tokens"."id" >= 2038198 AND "oauth_access_tokens"."id" < 2039355 AND "oauth_access_tokens"."created_at" <= '2025-08-02 00:00:00' 

BEGIN

DELETE FROM "oauth_access_tokens" WHERE "oauth_access_tokens"."id" BETWEEN 1 AND 2040018 AND "oauth_access_tokens"."revoked_at" IS NOT NULL AND "oauth_access_tokens"."id" >= 2038198 AND "oauth_access_tokens"."id" < 2039355 AND "oauth_access_tokens"."created_at" <= '2025-08-02 00:00:00' 
              
INSERT INTO "oauth_access_token_archived_records" ("resource_owner_id","application_id","token","refresh_token","expires_in","revoked_at","scopes","organization_id","oauth_access_token_id","oauth_access_token_created_at") VALUES (61, 23, 'a0b48d44c4069d8fa6600cf637e345aeb973c3f33fa7e957e9c8916d714bcbcb', '3aa55d42b28c5e8d86ee5f2cdc8f824ce6b77b6b4993e4408b897e5f096c5983', 3600, '2024-03-21 22:37:03', 'write', 1, 2038198, '2024-03-10 23:11:18'), ....

COMMIT

Reversibility and Recovery

  • The migration to queue the batched background migration is reversible.
  • The operations performed by the batched background migration is not automatically reversible since it performs DELETE operations.
    • Migration can be stopped at any point with only processed batches affected
    • The CTE approach ensures atomicity per batch: tokens are only deleted if successfully archived.
    • Unprocessed tokens remain intact

Data recovery options are the following.

Full Recovery (from archive table)

INSERT INTO oauth_access_tokens 
(resource_owner_id, application_id, token, refresh_token,
   expires_in, revoked_at, created_at, scopes, organization_id)
   SELECT id, resource_owner_id, application_id, token, refresh_token,
  expires_in, revoked_at, created_at, scopes, organization_id
   FROM oauth_access_token_archived_records
   WHERE archived_at >= [migration_start_timestamp];

Selective Recovery (specific tokens)

INSERT INTO oauth_access_tokens 
  (resource_owner_id, application_id, token, refresh_token,
   expires_in, revoked_at, created_at, scopes, organization_id)
SELECT 
  id, resource_owner_id, application_id, token, refresh_token,
  expires_in, revoked_at, created_at, scopes, organization_id
FROM oauth_access_token_archived_records
WHERE id BETWEEN 253900000 AND 253901000
ORDER BY id;
Example of deletion and re-insertion

Deletion:


exec WITH deleted AS (
    DELETE FROM oauth_access_tokens                                                                      
    WHERE id IN (SELECT "oauth_access_tokens"."id" FROM "oauth_access_tokens" WHERE "oauth_access_tokens"."id" BETWEEN 1 AND 260000000 AND "oauth_access_tokens"."id" >= 253900000 AND "oauth_access_tokens"."id" < 253901000 AND "oauth_access_tokens"."revoked_at" < '2025-08-10 00:00:00' LIMIT 1000)                             
  RETURNING *                                                                                            
)                                                                                                        
INSERT INTO oauth_access_token_archived_records                                                          
  (id, resource_owner_id, application_id, token, refresh_token,                                          
   expires_in, revoked_at, created_at, scopes, organization_id, archived_at)                             
SELECT                                                                                                   
  id, resource_owner_id, application_id, token, refresh_token,                                           
  expires_in, revoked_at, created_at, scopes, organization_id,                                           
  CURRENT_TIMESTAMP as archived_at                                                                       
FROM deleted ;


exec WITH deleted AS (     DELETE FROM oauth_access_tokens                                                                           WHERE id IN (SELECT "oauth_access_tokens"."id" FROM "oauth_access_tokens" WHERE "oauth_access_tokens"."id" BETWEEN 1 AND 260000000 AND "oauth_access_tokens"."id" >= 253900000 AND "oauth_access_tokens"."id" < 253901000 AND "oauth_access_tokens"."revoked_at" <  
[...SKIP...]  
  
The query has been executed. Duration: 20.121 ms

Re-insertion:

exec INSERT INTO oauth_access_tokens 
  (resource_owner_id, application_id, token, refresh_token,
   expires_in, revoked_at, created_at, scopes, organization_id)
SELECT 
  resource_owner_id, application_id, token, refresh_token,
  expires_in, revoked_at, created_at, scopes, organization_id
FROM oauth_access_token_archived_records
WHERE id BETWEEN 253900000 AND 253901000
ORDER BY id;
​

exec INSERT INTO oauth_access_tokens (resource_owner_id, application_id, token, refresh_token, expires_in, revoked_at, created_at, scopes, organization_id) SELECT resource_owner_id, application_id, token, refresh_token, expires_in, revoked_at, created_at, scopes, organization_id FROM oauth_access_token_archived_records WHERE id BETWEEN 253900000 AND 253901000 ORDER BY id;


The query has been executed. Duration: 213.721 ms

Important: Since the archive table preserves original id values, records can be restored with their original IDs, maintaining referential integrity.

User Experience Impact if Error Occurs

  • Minimal authentication impact: Since these tokens are already revoked, they cannot be used for authentication. No active user sessions would be affected.
  • Token management operations: Users attempting to view or manage their revoked tokens might encounter errors if the UI expects these records to exist.
  • Token revocation operations: If application logic attempts to re-revoke or update already-revoked tokens without proper existence checks, operations might fail with record not found errors.

To support it: code audit and tests

Migrations

Migrate up

migrate:up VERSION=20250826100517 
$ bin/rails db:migrate:up:main VERSION=20250826100517  
main: == [advisory_lock_connection] object_id: 153960, pg_backend_pid: 93087
main: == 20250826100517 CreateOauthAccessTokenArchivedRecords: migrating ============
main: -- create_table(:oauth_access_token_archived_records, {:id=>false})
main: -- quote_column_name(:token)
main:    -> 0.0000s
main: -- quote_column_name(:refresh_token)
main:    -> 0.0000s
main: -- quote_column_name(:scopes)
main:    -> 0.0000s
main:    -> 0.0260s
main: -- execute("ALTER TABLE oauth_access_token_archived_records ADD PRIMARY KEY (id)")
main:    -> 0.0013s
main: == 20250826100517 CreateOauthAccessTokenArchivedRecords: migrated (0.0317s) ===

main: == [advisory_lock_connection] object_id: 153960, pg_backend_pid: 93087

$ bin/rails db:migrate:up:ci VERSION=20250826100517  
ci: == [advisory_lock_connection] object_id: 153960, pg_backend_pid: 93279
ci: == 20250826100517 CreateOauthAccessTokenArchivedRecords: migrating ============
ci: -- create_table(:oauth_access_token_archived_records, {:id=>false})
ci: -- quote_column_name(:token)
ci:    -> 0.0000s
ci: -- quote_column_name(:refresh_token)
ci:    -> 0.0000s
ci: -- quote_column_name(:scopes)
ci:    -> 0.0000s
ci:    -> 0.0276s
ci: -- execute("ALTER TABLE oauth_access_token_archived_records ADD PRIMARY KEY (id)")
ci:    -> 0.0016s
I, [2025-09-09T13:40:54.725068 #93221]  INFO -- : Database: 'ci', Table: 'oauth_access_token_archived_records': Lock Writes
ci: == 20250826100517 CreateOauthAccessTokenArchivedRecords: migrated (0.0391s) ===

ci: == [advisory_lock_connection] object_id: 153960, pg_backend_pid: 93279

Migrate down

migrate:down VERSION=20250826100517 
$ bin/rails db:migrate:down:main VERSION=20250826100517  
main: == [advisory_lock_connection] object_id: 153960, pg_backend_pid: 93898
main: == 20250826100517 CreateOauthAccessTokenArchivedRecords: reverting ============
main: -- drop_table(:oauth_access_token_archived_records)
main:    -> 0.0167s
main: == 20250826100517 CreateOauthAccessTokenArchivedRecords: reverted (0.0216s) ===

main: == [advisory_lock_connection] object_id: 153960, pg_backend_pid: 93898


$ bin/rails db:migrate:down:ci VERSION=20250826100517  
ci: == [advisory_lock_connection] object_id: 153960, pg_backend_pid: 94065
ci: == 20250826100517 CreateOauthAccessTokenArchivedRecords: reverting ============
ci: -- drop_table(:oauth_access_token_archived_records)
ci:    -> 0.0162s
ci: == 20250826100517 CreateOauthAccessTokenArchivedRecords: reverted (0.0242s) ===

ci: == [advisory_lock_connection] object_id: 153960, pg_backend_pid: 94065

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #521855

Edited by Daniele Bracciani

Merge request reports