Add new scopes to the deploy_tokens table (!29383) · Merge requests · GitLab.org / GitLab

Steve Abrams requested to merge 213566-package-deploy-tokens into master Apr 11, 2020

What does this MR do?

This adds two new scopes to deploy tokens: read_package_registry for pulling/downloading packages and write_package_registry for pushing/uploading packages to the deploy_tokens table.

This is the first step in adding deploy token access to the package registry in #213566 (closed)

This MR will be followed by an MR that implements the logic for the package registry API to allow access based on these new scopes, and then an MR to add the new scopes to the frontend deploy token form and deploy token API.

Database

deploy_tokens has less than 160000 records.

Up Migration

== 20200411125656 AddPackageScopesToDeployTokens: migrating ===================
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- transaction()
-- add_column(:deploy_tokens, :read_package_registry, :boolean, {:default=>nil})
   -> 0.0039s
-- change_column_default(:deploy_tokens, :read_package_registry, false)
   -> 0.0051s
   -> 0.0100s
-- columns(:deploy_tokens)
   -> 0.0011s
-- transaction_open?()
   -> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"deploy_tokens\"")
   -> 0.0034s
-- exec_query("SELECT \"deploy_tokens\".\"id\" FROM \"deploy_tokens\" ORDER BY \"deploy_tokens\".\"id\" ASC LIMIT 1")
   -> 0.0006s
-- exec_query("SELECT \"deploy_tokens\".\"id\" FROM \"deploy_tokens\" WHERE \"deploy_tokens\".\"id\" >= 1 ORDER BY \"deploy_tokens\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0003s
-- execute("UPDATE \"deploy_tokens\" SET \"read_package_registry\" = FALSE WHERE \"deploy_tokens\".\"id\" >= 1 AND \"deploy_tokens\".\"id\" < 2")
   -> 0.0004s
-- exec_query("SELECT \"deploy_tokens\".\"id\" FROM \"deploy_tokens\" WHERE \"deploy_tokens\".\"id\" >= 2 ORDER BY \"deploy_tokens\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0002s
-- execute("UPDATE \"deploy_tokens\" SET \"read_package_registry\" = FALSE WHERE \"deploy_tokens\".\"id\" >= 2 AND \"deploy_tokens\".\"id\" < 3")
   -> 0.0002s
-- exec_query("SELECT \"deploy_tokens\".\"id\" FROM \"deploy_tokens\" WHERE \"deploy_tokens\".\"id\" >= 3 ORDER BY \"deploy_tokens\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0002s
-- execute("UPDATE \"deploy_tokens\" SET \"read_package_registry\" = FALSE WHERE \"deploy_tokens\".\"id\" >= 3 AND \"deploy_tokens\".\"id\" < 4")
   -> 0.0003s
-- exec_query("SELECT \"deploy_tokens\".\"id\" FROM \"deploy_tokens\" WHERE \"deploy_tokens\".\"id\" >= 4 ORDER BY \"deploy_tokens\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0003s
-- execute("UPDATE \"deploy_tokens\" SET \"read_package_registry\" = FALSE WHERE \"deploy_tokens\".\"id\" >= 4 AND \"deploy_tokens\".\"id\" < 5")
   -> 0.0006s
-- exec_query("SELECT \"deploy_tokens\".\"id\" FROM \"deploy_tokens\" WHERE \"deploy_tokens\".\"id\" >= 5 ORDER BY \"deploy_tokens\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0003s
-- execute("UPDATE \"deploy_tokens\" SET \"read_package_registry\" = FALSE WHERE \"deploy_tokens\".\"id\" >= 5 AND \"deploy_tokens\".\"id\" < 6")
   -> 0.0003s
-- exec_query("SELECT \"deploy_tokens\".\"id\" FROM \"deploy_tokens\" WHERE \"deploy_tokens\".\"id\" >= 6 ORDER BY \"deploy_tokens\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0003s
-- execute("UPDATE \"deploy_tokens\" SET \"read_package_registry\" = FALSE WHERE \"deploy_tokens\".\"id\" >= 6")
   -> 0.0003s
-- change_column_null(:deploy_tokens, :read_package_registry, false)
   -> 0.0004s
-- execute("RESET ALL")
   -> 0.0001s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- transaction()
-- add_column(:deploy_tokens, :write_package_registry, :boolean, {:default=>nil})
   -> 0.0006s
-- change_column_default(:deploy_tokens, :write_package_registry, false)
   -> 0.0016s
   -> 0.0027s
-- columns(:deploy_tokens)
   -> 0.0013s
-- transaction_open?()
   -> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"deploy_tokens\"")
   -> 0.0004s
-- exec_query("SELECT \"deploy_tokens\".\"id\" FROM \"deploy_tokens\" ORDER BY \"deploy_tokens\".\"id\" ASC LIMIT 1")
   -> 0.0003s
-- exec_query("SELECT \"deploy_tokens\".\"id\" FROM \"deploy_tokens\" WHERE \"deploy_tokens\".\"id\" >= 1 ORDER BY \"deploy_tokens\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0003s
-- execute("UPDATE \"deploy_tokens\" SET \"write_package_registry\" = FALSE WHERE \"deploy_tokens\".\"id\" >= 1 AND \"deploy_tokens\".\"id\" < 2")
   -> 0.0004s
-- exec_query("SELECT \"deploy_tokens\".\"id\" FROM \"deploy_tokens\" WHERE \"deploy_tokens\".\"id\" >= 2 ORDER BY \"deploy_tokens\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0003s
-- execute("UPDATE \"deploy_tokens\" SET \"write_package_registry\" = FALSE WHERE \"deploy_tokens\".\"id\" >= 2 AND \"deploy_tokens\".\"id\" < 3")
   -> 0.0004s
-- exec_query("SELECT \"deploy_tokens\".\"id\" FROM \"deploy_tokens\" WHERE \"deploy_tokens\".\"id\" >= 3 ORDER BY \"deploy_tokens\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0003s
-- execute("UPDATE \"deploy_tokens\" SET \"write_package_registry\" = FALSE WHERE \"deploy_tokens\".\"id\" >= 3 AND \"deploy_tokens\".\"id\" < 4")
   -> 0.0003s
-- exec_query("SELECT \"deploy_tokens\".\"id\" FROM \"deploy_tokens\" WHERE \"deploy_tokens\".\"id\" >= 4 ORDER BY \"deploy_tokens\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0003s
-- execute("UPDATE \"deploy_tokens\" SET \"write_package_registry\" = FALSE WHERE \"deploy_tokens\".\"id\" >= 4 AND \"deploy_tokens\".\"id\" < 5")
   -> 0.0004s
-- exec_query("SELECT \"deploy_tokens\".\"id\" FROM \"deploy_tokens\" WHERE \"deploy_tokens\".\"id\" >= 5 ORDER BY \"deploy_tokens\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0003s
-- execute("UPDATE \"deploy_tokens\" SET \"write_package_registry\" = FALSE WHERE \"deploy_tokens\".\"id\" >= 5 AND \"deploy_tokens\".\"id\" < 6")
   -> 0.0004s
-- exec_query("SELECT \"deploy_tokens\".\"id\" FROM \"deploy_tokens\" WHERE \"deploy_tokens\".\"id\" >= 6 ORDER BY \"deploy_tokens\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0003s
-- execute("UPDATE \"deploy_tokens\" SET \"write_package_registry\" = FALSE WHERE \"deploy_tokens\".\"id\" >= 6")
   -> 0.0003s
-- change_column_null(:deploy_tokens, :write_package_registry, false)
   -> 0.0004s
-- execute("RESET ALL")
   -> 0.0001s
== 20200411125656 AddPackageScopesToDeployTokens: migrated (0.0320s) ==========

Down Migration

== 20200411125656 AddPackageScopesToDeployTokens: reverting ===================
-- remove_column(:deploy_tokens, :read_package_registry)
   -> 0.0030s
-- remove_column(:deploy_tokens, :write_package_registry)
   -> 0.0011s
== 20200411125656 AddPackageScopesToDeployTokens: reverted (0.0044s) ==========

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
[-] Tested in all supported browsers
[-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

[-] Label as security and @ mention @gitlab-com/gl-security/appsec
[-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
[-] Security reports checked/validated by a reviewer from the AppSec team

Related #213566 (closed)

Edited Apr 16, 2020 by Steve Abrams

Add new scopes to the deploy_tokens table