Add worker to delete expired and revoked OAuth Access Grants
What does this MR do and why?
Add worker to delete expired and revoked OAuth Access Grants
The oauth_access_grants table has grown significantly due to accumulated expired access grants and lacks a proper cleanup mechanism. This commit introduces a worker to handle the cleanup process. After the table size is managed, a follow-up change will be needed to implement a cron job for periodic deletion of expired access grants.
Currently we have oauth_access_grants >43M and >14M are expired.
Changelog: changed
References
Some useful queries:
- Total number of oauth_access_grants
- Number of expired oauth_access_grants
- https://console.postgres.ai/gitlab/gitlab-production-main/sessions/40903/commands/125688 to load the max id and
- https://console.postgres.ai/gitlab/gitlab-production-main/sessions/40903/commands/125690 to remove the sub batch
Table definition
Table "public.oauth_access_grants"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------------------+-----------------------------+-----------+----------+-------------------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('oauth_access_grants_id_seq'::regclass) | plain | | |
resource_owner_id | integer | | not null | | plain | | |
application_id | integer | | not null | | plain | | |
token | character varying(255) | | not null | | extended | | |
expires_in | integer | | not null | | plain | | |
redirect_uri | text | | not null | | extended | | |
created_at | timestamp without time zone | | not null | | plain | | |
revoked_at | timestamp without time zone | | | | plain | | |
scopes | character varying(255) | | | | extended | | |
code_challenge | text | | | | extended | | |
code_challenge_method | text | | | | extended | | |
organization_id | bigint | | not null | | plain | | |
Indexes:
"oauth_access_grants_pkey" PRIMARY KEY, btree (id)
"idx_oauth_access_grants_on_organization_id" btree (organization_id)
"index_oauth_access_grants_on_application_id" btree (application_id)
"index_oauth_access_grants_on_resource_owner_id" btree (resource_owner_id, application_id, created_at)
"index_oauth_access_grants_on_token" UNIQUE, btree (token)
Check constraints:
"oauth_access_grants_code_challenge" CHECK (char_length(code_challenge) <= 128)
"oauth_access_grants_code_challenge_method" CHECK (char_length(code_challenge_method) <= 5)
Foreign-key constraints:
"fk_59cdb2323c" FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
Referenced by:
TABLE "oauth_openid_requests" CONSTRAINT "fk_77114b3b09" FOREIGN KEY (access_grant_id) REFERENCES oauth_access_grants(id) ON DELETE CASCADE
Access method: heap
Options: autovacuum_enabled=true, toast.autovacuum_enabled=trueTest locally
- Create an Oauth application under http://localhost:3000/-/user_settings/applications
- Activate a grant
- Activate the related feature flag with
Feature.enable(cleanup_oauth_access_grants)
Populate DB
def create_oauth_access_grants
batch_size = 10_000
expired_count = 20_000
live_count = 1_000
application = Doorkeeper::Application.first
user = User.first
organization = user.organizations.first
# Different expiration periods for variety
expiration_periods = [
30.minutes.to_i, # 30 minutes
1.hour.to_i, # 1 hour
2.hours.to_i, # 2 hours
6.hours.to_i, # 6 hours
12.hours.to_i, # 12 hours
1.day.to_i, # 1 day
3.days.to_i, # 3 days
1.week.to_i, # 1 week
2.weeks.to_i, # 2 weeks
1.month.to_i # 1 month
]
puts "Creating #{expired_count} expired OAuth access grants..."
# Create expired grants
(0...expired_count).each_slice(batch_size) do |batch|
grants = batch.map do |i|
expires_in = expiration_periods.sample
# Create grants that are already expired by setting old created_at
created_time = (expires_in + rand(1.hour..7.days)).seconds.ago
{
application_id: application.id,
resource_owner_id: user.id,
organization_id: organization.id,
token: SecureRandom.hex(32),
expires_in: expires_in,
redirect_uri: application.redirect_uri,
scopes: application.scopes.to_s,
created_at: created_time
}
end
OauthAccessGrant.insert_all(grants)
puts "Created #{batch.last + 1} expired grants..." if (batch.last + 1) % 50_000 == 0
end
puts "Creating #{live_count} live OAuth access grants..."
# Create live grants (not expired)
(0...live_count).each_slice(1000) do |batch|
grants = batch.map do |i|
expires_in = expiration_periods.sample
# Create grants that won't expire for a while
created_time = 1.hour.ago
{
application_id: application.id,
resource_owner_id: user.id,
organization_id: organization.id,
token: SecureRandom.hex(32),
expires_in: expires_in,
redirect_uri: application.redirect_uri,
scopes: application.scopes.to_s,
created_at: created_time,
}
end
OauthAccessGrant.insert_all(grants)
puts "Created #{batch.last + 1} live grants..." if (batch.last + 1) % 1000 == 0
end
puts "Successfully created #{expired_count} expired and #{live_count} live OAuth access grants!"
puts "Total grants in database: #{OauthAccessGrant.count}"
end
# Run the creation
create_oauth_access_grantsRun worker and verify
Trigger the worker from rails console:
Authn::OauthAccessGrantCleanupWorker.perform_in(1.seconds)
Verify that only the expired tokens are deleted:
OauthAccessGrant.where("created_at + \(expires_in * INTERVAL'1 SECOND'\) < NOW()").count -> returns 0
OauthAccessGrant.where("created_at + \(expires_in * INTERVAL'1 SECOND'\) > NOW()").count -> returns 800MR 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.
Edited by Daniele Bracciani