Add oauth_consents table and model (!228884) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Adds the oauth_consents table and Authn::OauthConsent model to store user consent decisions for the IAM OAuth consent flow.

When a user authorizes (or denies) an OAuth application through the IAM service consent screen, a record is persisted capturing the client details, requested/granted scopes, and the consent decision timestamp. This table is a prerequisite for the consent controller and services being built in the follow-up integration MR.

Key design decisions

Sharding key: user_id (in gitlab_main_user schema); consent records are fundamentally user-scoped (a user's decision to authorize/deny an app), so they belong with the user, not the organization.
status (smallint, default 0): Lifecycle enum: granted (0) → revoked (1).
Separate FK migrations per the two-FK creation pattern: required because the table references two different tables.
client_id references oauth_applications.uid via a FK.
Three indexes: consent_challenge (unique, primary lookup), client_id (FK cascade performance), [user_id, client_id] (FK cascade on user_id + future re-consent queries).

Notes

This table will be subject to a retention period similarly to oauth_access_tokens, oauth_access_grants, to periodically cleanup records.

References

Main issue: GitLab Rails: Consent Integration with IAM Service (#589572)

Database

Migration up

20260410153740_create_oauth_consents.rb

>>> Executing: bin/rails db:migrate:up:main VERSION=20260410153740
main: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 64366
main: == 20260410153740 CreateOauthConsents: migrating ==============================
main: -- create_table(:oauth_consents, {:if_not_exists=>true})
main:    -> 0.0046s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE oauth_consents\nADD CONSTRAINT check_1191e7b1bf\nCHECK ( char_length(client_id) <= 100 )\nNOT VALID;\n")
main:    -> 0.0006s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT check_1191e7b1bf;")
main:    -> 0.0005s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE oauth_consents\nADD CONSTRAINT check_aeae00cea2\nCHECK ( char_length(client_name) <= 255 )\nNOT VALID;\n")
main:    -> 0.0004s
main: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT check_aeae00cea2;")
main:    -> 0.0006s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE oauth_consents\nADD CONSTRAINT check_3fd9d8434f\nCHECK ( char_length(consent_challenge) <= 100 )\nNOT VALID;\n")
main:    -> 0.0006s
main: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT check_3fd9d8434f;")
main:    -> 0.0004s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE oauth_consents\nADD CONSTRAINT check_oauth_consents_requested_scopes_size\nCHECK ( CARDINALITY(requested_scopes) <= 50 )\nNOT VALID;\n")
main:    -> 0.0011s
main: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT check_oauth_consents_requested_scopes_size;")
main:    -> 0.0005s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE oauth_consents\nADD CONSTRAINT check_oauth_consents_granted_scopes_size\nCHECK ( CARDINALITY(granted_scopes) <= 50 )\nNOT VALID;\n")
main:    -> 0.0006s
main: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT check_oauth_consents_granted_scopes_size;")
main:    -> 0.0008s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE oauth_consents\nADD CONSTRAINT check_oauth_consents_status_timestamp_consistency\nCHECK ( (status = 0 AND authorized_at IS NULL AND rejected_at IS NULL AND revoked_at IS NULL) OR\n      (status = 1 AND authorized_at IS NOT NULL AND rejected_at IS NULL AND revoked_at IS NULL) OR\n      (status = 2 AND rejected_at IS NOT NULL AND authorized_at IS NULL AND revoked_at IS NULL) OR\n      (status = 3 AND revoked_at IS NOT NULL AND rejected_at IS NULL) )\nNOT VALID;\n")
main:    -> 0.0006s
main: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT check_oauth_consents_status_timestamp_consistency;")
main:    -> 0.0006s
main: -- add_index(:oauth_consents, :consent_challenge, {:unique=>true, :name=>"index_oauth_consents_on_consent_challenge"})
main:    -> 0.0012s
main: -- add_index(:oauth_consents, :client_id, {:name=>"index_oauth_consents_on_client_id"})
main:    -> 0.0008s
main: -- add_index(:oauth_consents, [:user_id, :client_id], {:name=>"index_oauth_consents_on_user_id_and_client_id"})
main:    -> 0.0007s
main: == 20260410153740 CreateOauthConsents: migrated (0.1105s) =====================

main: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 64366
>>> Executing: bin/rails db:migrate:up:ci VERSION=20260410153740
ci: == [advisory_lock_connection] object_id: 139620, pg_backend_pid: 64450
ci: == 20260410153740 CreateOauthConsents: migrating ==============================
ci: -- create_table(:oauth_consents, {:if_not_exists=>true})
ci:    -> 0.0084s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE oauth_consents\nADD CONSTRAINT check_1191e7b1bf\nCHECK ( char_length(client_id) <= 100 )\nNOT VALID;\n")
ci:    -> 0.0006s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0036s
ci: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT check_1191e7b1bf;")
ci:    -> 0.0006s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE oauth_consents\nADD CONSTRAINT check_aeae00cea2\nCHECK ( char_length(client_name) <= 255 )\nNOT VALID;\n")
ci:    -> 0.0006s
ci: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT check_aeae00cea2;")
ci:    -> 0.0004s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE oauth_consents\nADD CONSTRAINT check_3fd9d8434f\nCHECK ( char_length(consent_challenge) <= 100 )\nNOT VALID;\n")
ci:    -> 0.0005s
ci: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT check_3fd9d8434f;")
ci:    -> 0.0004s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE oauth_consents\nADD CONSTRAINT check_oauth_consents_requested_scopes_size\nCHECK ( CARDINALITY(requested_scopes) <= 50 )\nNOT VALID;\n")
ci:    -> 0.0008s
ci: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT check_oauth_consents_requested_scopes_size;")
ci:    -> 0.0006s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE oauth_consents\nADD CONSTRAINT check_oauth_consents_granted_scopes_size\nCHECK ( CARDINALITY(granted_scopes) <= 50 )\nNOT VALID;\n")
ci:    -> 0.0007s
ci: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT check_oauth_consents_granted_scopes_size;")
ci:    -> 0.0011s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE oauth_consents\nADD CONSTRAINT check_oauth_consents_status_timestamp_consistency\nCHECK ( (status = 0 AND authorized_at IS NULL AND rejected_at IS NULL AND revoked_at IS NULL) OR\n      (status = 1 AND authorized_at IS NOT NULL AND rejected_at IS NULL AND revoked_at IS NULL) OR\n      (status = 2 AND rejected_at IS NOT NULL AND authorized_at IS NULL AND revoked_at IS NULL) OR\n      (status = 3 AND revoked_at IS NOT NULL AND rejected_at IS NULL) )\nNOT VALID;\n")
ci:    -> 0.0013s
ci: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT check_oauth_consents_status_timestamp_consistency;")
ci:    -> 0.0006s
ci: -- add_index(:oauth_consents, :consent_challenge, {:unique=>true, :name=>"index_oauth_consents_on_consent_challenge"})
ci:    -> 0.0013s
ci: -- add_index(:oauth_consents, :client_id, {:name=>"index_oauth_consents_on_client_id"})
ci:    -> 0.0011s
ci: -- add_index(:oauth_consents, [:user_id, :client_id], {:name=>"index_oauth_consents_on_user_id_and_client_id"})
ci:    -> 0.0011s
I, [2026-04-15T17:07:27.070402 #64395]  INFO -- : Database: 'ci', Table: 'oauth_consents': Lock Writes
I, [2026-04-15T17:07:27.071129 #64395]  INFO -- : {:method=>"with_lock_retries", :class=>"gitlab:db:lock_writes", :message=>"Lock timeout is set", :current_iteration=>1, :lock_timeout_in_ms=>100}
I, [2026-04-15T17:07:27.072589 #64395]  INFO -- : {:method=>"with_lock_retries", :class=>"gitlab:db:lock_writes", :message=>"Migration finished", :current_iteration=>1, :lock_timeout_in_ms=>100}
ci: == 20260410153740 CreateOauthConsents: migrated (0.1381s) =====================

ci: == [advisory_lock_connection] object_id: 139620, pg_backend_pid: 64450

20260410153750_add_user_foreign_key_to_oauth_consents

>>> Executing: bin/rails db:migrate:up:main VERSION=20260410153750
main: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 36094
main: == 20260410153750 AddUserForeignKeyToOauthConsents: migrating =================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE oauth_consents ADD CONSTRAINT fk_8233ea86aa FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0025s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT fk_8233ea86aa;")
main:    -> 0.0033s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20260410153750 AddUserForeignKeyToOauthConsents: migrated (0.0560s) ========

main: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 36094
>>> Executing: bin/rails db:migrate:up:ci VERSION=20260410153750
ci: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 36208
ci: == 20260410153750 AddUserForeignKeyToOauthConsents: migrating =================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE oauth_consents ADD CONSTRAINT fk_8233ea86aa FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0022s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT fk_8233ea86aa;")
ci:    -> 0.0080s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0007s
ci: == 20260410153750 AddUserForeignKeyToOauthConsents: migrated (0.0812s) ========

ci: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 36208

20260410153800_add_oauth_application_foreign_key_to_oauth_consents.rb

>>> Executing: bin/rails db:migrate:up:main VERSION=20260410153800
main: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 36447
main: == 20260410153800 AddOauthApplicationForeignKeyToOauthConsents: migrating =====
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE oauth_consents ADD CONSTRAINT fk_c5f142ff4b FOREIGN KEY (client_id) REFERENCES oauth_applications (uid) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0019s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT fk_c5f142ff4b;")
main:    -> 0.0020s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: == 20260410153800 AddOauthApplicationForeignKeyToOauthConsents: migrated (0.0566s) 

main: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 36447
>>> Executing: bin/rails db:migrate:up:ci VERSION=20260410153800
ci: == [advisory_lock_connection] object_id: 139620, pg_backend_pid: 36554
ci: == 20260410153800 AddOauthApplicationForeignKeyToOauthConsents: migrating =====
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE oauth_consents ADD CONSTRAINT fk_c5f142ff4b FOREIGN KEY (client_id) REFERENCES oauth_applications (uid) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0050s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE oauth_consents VALIDATE CONSTRAINT fk_c5f142ff4b;")
ci:    -> 0.0035s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20260410153800 AddOauthApplicationForeignKeyToOauthConsents: migrated (0.0715s) 

ci: == [advisory_lock_connection] object_id: 139620, pg_backend_pid: 36554

Migration down

20260410153800_add_oauth_application_foreign_key_to_oauth_consents.rb

>>> Executing: bin/rails db:migrate:down:main VERSION=20260410153800
main: == [advisory_lock_connection] object_id: 139620, pg_backend_pid: 34667
main: == 20260410153800 AddOauthApplicationForeignKeyToOauthConsents: reverting =====
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:oauth_consents, {:column=>:client_id})
main:    -> 0.0279s
main: == 20260410153800 AddOauthApplicationForeignKeyToOauthConsents: reverted (0.0451s) 

main: == [advisory_lock_connection] object_id: 139620, pg_backend_pid: 34667
>>> Executing: bin/rails db:migrate:down:ci VERSION=20260410153800
ci: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 34769
ci: == 20260410153800 AddOauthApplicationForeignKeyToOauthConsents: reverting =====
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- remove_foreign_key(:oauth_consents, {:column=>:client_id})
ci:    -> 0.0242s
ci: == 20260410153800 AddOauthApplicationForeignKeyToOauthConsents: reverted (0.0467s) 

ci: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 34769

20260410153750_add_user_foreign_key_to_oauth_consents.rb

>>> Executing: bin/rails db:migrate:down:main VERSION=20260410153750
main: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 34929
main: == 20260410153750 AddUserForeignKeyToOauthConsents: reverting =================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:oauth_consents, {:column=>:user_id})
main:    -> 0.0169s
main: == 20260410153750 AddUserForeignKeyToOauthConsents: reverted (0.0332s) ========

main: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 34929
>>> Executing: bin/rails db:migrate:down:ci VERSION=20260410153750
ci: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 35005
ci: == 20260410153750 AddUserForeignKeyToOauthConsents: reverting =================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- remove_foreign_key(:oauth_consents, {:column=>:user_id})
ci:    -> 0.0170s
ci: == 20260410153750 AddUserForeignKeyToOauthConsents: reverted (0.0379s) ========

ci: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 35005

20260410153740_create_oauth_consents.rb

>>> Executing: bin/rails db:migrate:down:main VERSION=20260410153740
main: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 35207
main: == 20260410153740 CreateOauthConsents: reverting ==============================
main: -- drop_table(:oauth_consents)
main:    -> 0.0086s
main: == 20260410153740 CreateOauthConsents: reverted (0.0168s) =====================

main: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 35207
>>> Executing: bin/rails db:migrate:down:ci VERSION=20260410153740
ci: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 35310
ci: == 20260410153740 CreateOauthConsents: reverting ==============================
ci: -- drop_table(:oauth_consents)
ci:    -> 0.0097s
ci: == 20260410153740 CreateOauthConsents: reverted (0.0234s) =====================

ci: == [advisory_lock_connection] object_id: 139640, pg_backend_pid: 35310

How to set up and validate locally

Run migration
Verify table

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.

Edited Apr 17, 2026 by Daniele Bracciani

Add oauth_consents table and model