Skip to content

Add Personal Access Token API Filters

Andreas Deicharequested to merge
TrueKalix/gitlab:feature/#362248_Add_Filters_PAT_API into master

What does this MR do and why?

Fixes #362248 (closed)

Adds a new set of filters for personal access token (PAT) API.

The filters are:

GET /personal_access_tokens?revoked=true
GET /personal_access_tokens?created_before=2022-01-01T00:00:00
GET /personal_access_tokens?created_after=2022-01-01T00:00:00
GET /personal_access_tokens?scopes=api,read_user
GET /personal_access_tokens?search=name
GET /personal_access_tokens?last_used_before=2022-01-01T00:00:00
GET /personal_access_tokens?last_used_after=2022-01-01T00:00:00
GET /personal_access_tokens?active=false

How to set up and validate locally

  1. Create one or multiple PAT token
  2. Set a value for a token property that matches your filter. E.g. if you are looking for revoked tokens - set one token revoked and another you leave valid
  3. Run the following commands
curl --request GET --header "PRIVATE-TOKEN: <your_access_token>" https://gitlab.example.com/api/v4/personal_access_tokens?revoked=true
curl --request GET --header "PRIVATE-TOKEN: <your_access_token>" https://gitlab.example.com/api/v4/personal_access_tokens?created_before=2022-01-01T00:00:00
curl --request GET --header "PRIVATE-TOKEN: <your_access_token>" https://gitlab.example.com/api/v4/personal_access_tokens?created_after=2022-01-01T00:00:00
curl --request GET --header "PRIVATE-TOKEN: <your_access_token>" https://gitlab.example.com/api/v4/personal_access_tokens?scopes=api,read_user
curl --request GET --header "PRIVATE-TOKEN: <your_access_token>" https://gitlab.example.com/api/v4/personal_access_tokens?search=name
curl --request GET --header "PRIVATE-TOKEN: <your_access_token>" https://gitlab.example.com/api/v4/personal_access_tokens?last_used_before=2022-01-01T00:00:00
curl --request GET --header "PRIVATE-TOKEN: <your_access_token>" https://gitlab.example.com/api/v4/personal_access_tokens?last_used_after=2022-01-01T00:00:00
curl --request GET --header "PRIVATE-TOKEN: <your_access_token>" https://gitlab.example.com/api/v4/personal_access_tokens?active=false
  1. You can also merge the filters
curl --request GET --header "PRIVATE-TOKEN: <your_access_token>" https://gitlab.example.com/api/v4/personal_access_tokens?active=false&last_used_before=2022-01-01T00:00:00

DB Review

Queries

Filter Query
active https://explain.depesz.com/s/arLB
created_after https://explain.depesz.com/s/hxu8
created_before https://explain.depesz.com/s/gbw9
last_used_after https://explain.depesz.com/s/5jdg
last_used_before https://explain.depesz.com/s/7N4d
scopes https://explain.depesz.com/s/HRVt9
search https://explain.depesz.com/s/kdp8
revoked https://explain.depesz.com/s/EAf1
created_at & id https://explain.depesz.com/s/d2PQ
created_before & search https://explain.depesz.com/s/DUXA
created_before & user_id https://explain.depesz.com/s/D3BJ
created_before & search & scopes https://explain.depesz.com/s/yfoR
created_before & search & scopes & last_used_after https://explain.depesz.com/s/CLNP
created_before & search & scopes & revoked https://explain.depesz.com/s/8x8T

🛠 with ❤ at Siemens

Migrations up

> rails db:migrate
main: == 20220530100052 AddPersonalAccessTokensNameGinIndex: migrating ==============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:personal_access_tokens, :name, {:name=>"index_personal_access_tokens_on_name_trigram", :using=>:gin, :opclass=>{:name=>:gin_trgm_ops}, :algorithm=>:concurrently})
main:    -> 0.0400s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0024s
main: -- add_index(:personal_access_tokens, :name, {:name=>"index_personal_access_tokens_on_name_trigram", :using=>:gin, :opclass=>{:name=>:gin_trgm_ops}, :algorithm=>:concurrently})
main:    -> 0.0817s
main: -- execute("RESET statement_timeout")
main:    -> 0.0016s
main: == 20220530100052 AddPersonalAccessTokensNameGinIndex: migrated (0.1562s) =====

main: == 20220530130446 AddPersonalAccessTokensCreatedAtIndex: migrating ============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:personal_access_tokens, [:user_id, :created_at], {:name=>"index_personal_access_tokens_on_created_at", :algorithm=>:concurrently})
main:    -> 0.0114s
main: -- add_index(:personal_access_tokens, [:user_id, :created_at], {:name=>"index_personal_access_tokens_on_created_at", :algorithm=>:concurrently})
main:    -> 0.0107s
main: == 20220530130446 AddPersonalAccessTokensCreatedAtIndex: migrated (0.0315s) ===

main: == 20220530131122 AddPersonalAccessTokensLastUsedAtIndex: migrating ===========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:personal_access_tokens, [:user_id, :last_used_at], {:name=>"index_personal_access_tokens_on_last_used_at", :algorithm=>:concurrently})
main:    -> 0.0058s
main: -- add_index(:personal_access_tokens, [:user_id, :last_used_at], {:name=>"index_personal_access_tokens_on_last_used_at", :algorithm=>:concurrently})
main:    -> 0.0024s
main: == 20220530131122 AddPersonalAccessTokensLastUsedAtIndex: migrated (0.0146s) ==

main: == 20220531180051 AddPersonalAccessTokensScopesGinIndex: migrating ============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:personal_access_tokens, :scopes, {:name=>"index_personal_access_tokens_on_scopes_trigram", :using=>:gin, :opclass=>{:scopes=>:gin_trgm_ops}, :algorithm=>:concurrently})
main:    -> 0.0250s
main: -- add_index(:personal_access_tokens, :scopes, {:name=>"index_personal_access_tokens_on_scopes_trigram", :using=>:gin, :opclass=>{:scopes=>:gin_trgm_ops}, :algorithm=>:concurrently})
main:    -> 0.0120s
main: == 20220531180051 AddPersonalAccessTokensScopesGinIndex: migrated (0.0428s) ===

ci: == 20220530100052 AddPersonalAccessTokensNameGinIndex: migrating ==============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:personal_access_tokens, :name, {:name=>"index_personal_access_tokens_on_name_trigram", :using=>:gin, :opclass=>{:name=>:gin_trgm_ops}, :algorithm=>:concurrently})
ci:    -> 0.0420s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0018s
ci: -- add_index(:personal_access_tokens, :name, {:name=>"index_personal_access_tokens_on_name_trigram", :using=>:gin, :opclass=>{:name=>:gin_trgm_ops}, :algorithm=>:concurrently})
ci:    -> 0.0248s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0039s
ci: == 20220530100052 AddPersonalAccessTokensNameGinIndex: migrated (0.0869s) =====

ci: == 20220530130446 AddPersonalAccessTokensCreatedAtIndex: migrating ============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:personal_access_tokens, [:user_id, :created_at], {:name=>"index_personal_access_tokens_on_created_at", :algorithm=>:concurrently})
ci:    -> 0.0126s
ci: -- add_index(:personal_access_tokens, [:user_id, :created_at], {:name=>"index_personal_access_tokens_on_created_at", :algorithm=>:concurrently})
ci:    -> 0.0050s
ci: == 20220530130446 AddPersonalAccessTokensCreatedAtIndex: migrated (0.0323s) ===

ci: == 20220530131122 AddPersonalAccessTokensLastUsedAtIndex: migrating ===========
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:personal_access_tokens, [:user_id, :last_used_at], {:name=>"index_personal_access_tokens_on_last_used_at", :algorithm=>:concurrently})
ci:    -> 0.0911s
ci: -- add_index(:personal_access_tokens, [:user_id, :last_used_at], {:name=>"index_personal_access_tokens_on_last_used_at", :algorithm=>:concurrently})
ci:    -> 0.0070s
ci: == 20220530131122 AddPersonalAccessTokensLastUsedAtIndex: migrated (0.1266s) ==

ci: == 20220531180051 AddPersonalAccessTokensScopesGinIndex: migrating ============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:personal_access_tokens, :scopes, {:name=>"index_personal_access_tokens_on_scopes_trigram", :using=>:gin, :opclass=>{:scopes=>:gin_trgm_ops}, :algorithm=>:concurrently})
ci:    -> 0.0123s
ci: -- add_index(:personal_access_tokens, :scopes, {:name=>"index_personal_access_tokens_on_scopes_trigram", :using=>:gin, :opclass=>{:scopes=>:gin_trgm_ops}, :algorithm=>:concurrently})
ci:    -> 0.0035s
ci: == 20220531180051 AddPersonalAccessTokensScopesGinIndex: migrated (0.1074s) ===

Migration down

> rails db:migrate:down:main VERSION=20220531180051 
main: == 20220531180051 AddPersonalAccessTokensScopesGinIndex: reverting ============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0312s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0017s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_personal_access_tokens_on_scopes_trigram"})
main:    -> 0.0359s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: == 20220531180051 AddPersonalAccessTokensScopesGinIndex: reverted (0.0886s) ===

> rails db:migrate:down:main VERSION=20220530131122 
main: == 20220530131122 AddPersonalAccessTokensLastUsedAtIndex: reverting ===========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0892s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0022s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_personal_access_tokens_on_last_used_at"})
main:    -> 0.0081s
main: -- execute("RESET statement_timeout")
main:    -> 0.0024s
main: == 20220530131122 AddPersonalAccessTokensLastUsedAtIndex: reverted (0.1297s) ==

> rails db:migrate:down:main VERSION=20220530130446  
main: == 20220530130446 AddPersonalAccessTokensCreatedAtIndex: reverting ============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0144s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_personal_access_tokens_on_created_at"})
main:    -> 0.0057s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20220530130446 AddPersonalAccessTokensCreatedAtIndex: reverted (0.0312s) ===

> rails db:migrate:down:main VERSION=20220530100052 
main: == 20220530100052 AddPersonalAccessTokensNameGinIndex: reverting ==============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0183s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0008s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_personal_access_tokens_on_name_trigram"})
main:    -> 0.0068s
main: -- execute("RESET statement_timeout")
main:    -> 0.0013s
main: == 20220530100052 AddPersonalAccessTokensNameGinIndex: reverted (0.0437s) =====

> rails db:migrate:down:ci VERSION=20220531180051 
ci: == 20220531180051 AddPersonalAccessTokensScopesGinIndex: reverting ============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:personal_access_tokens)
ci:    -> 0.0100s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_personal_access_tokens_on_scopes_trigram"})
ci:    -> 0.0068s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0006s
ci: == 20220531180051 AddPersonalAccessTokensScopesGinIndex: reverted (0.0278s) ===

> rails db:migrate:down:ci VERSION=20220530131122 
ci: == 20220530131122 AddPersonalAccessTokensLastUsedAtIndex: reverting ===========
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:personal_access_tokens)
ci:    -> 0.0116s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_personal_access_tokens_on_last_used_at"})
ci:    -> 0.0054s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0007s
ci: == 20220530131122 AddPersonalAccessTokensLastUsedAtIndex: reverted (0.0265s) ==

> rails db:migrate:down:ci VERSION=20220530130446 
ci: == 20220530130446 AddPersonalAccessTokensCreatedAtIndex: reverting ============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:personal_access_tokens)
ci:    -> 0.0116s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0005s
ci: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_personal_access_tokens_on_created_at"})
ci:    -> 0.0032s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0007s
ci: == 20220530130446 AddPersonalAccessTokensCreatedAtIndex: reverted (0.0256s) ===

> rails db:migrate:down:ci VERSION=20220530100052 
ci: == 20220530100052 AddPersonalAccessTokensNameGinIndex: reverting ==============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:personal_access_tokens)
ci:    -> 0.0104s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_personal_access_tokens_on_name_trigram"})
ci:    -> 0.0040s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0005s
ci: == 20220530100052 AddPersonalAccessTokensNameGinIndex: reverted (0.0242s) =====

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Andreas Deicha

Merge request reports