Skip to content

Backend for Service Account MVC

Drew Blessing requested to merge dblessing_service_account_backend_mvc into master

What does this MR do and why?

Describe in detail what your merge request does and why.

Part of #387073 (closed). Backend changes for Service Accounts MVC.

This MR represents the first set of backend changes for service accounts. It's a minimal change that adds the user type, and ensures some basic permissions are present or not (for example, to ensure this user type cannot sign in). It also ensures a service account user is not a billable seat.

This is a low risk change as there is currently no mechanism to create a service account except via the Rails console. Subsequent MRs will adjust permissions further, and ensure the user type can have access tokens created by admins/owners/maintainers.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Database

Thanks to specs, I was alerted that I needed to adjust the index for the query User.active.without_bots. The migration alters the index accordingly and updates the spec.

Migrate output 
main: == 20230131184319 UpdateBillableUsersIndexForServiceAccounts: migrating =======
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.3172s
main: -- index_exists?(:users, :id, {:where=>"state = 'active' AND (user_type IS NULL OR user_type IN (6, 4, 13)) AND (user_type IS NULL OR user_type IN (4, 5))\n", :name=>"index_users_for_active_billable_users", :algorithm=>:concurrently})
main:    -> 0.3393s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0071s
main: -- add_index(:users, :id, {:where=>"state = 'active' AND (user_type IS NULL OR user_type IN (6, 4, 13)) AND (user_type IS NULL OR user_type IN (4, 5))\n", :name=>"index_users_for_active_billable_users", :algorithm=>:concurrently})
main:    -> 0.0083s
main: -- execute("RESET statement_timeout")
main:    -> 0.0026s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0211s
main: -- indexes(:users)
main:    -> 0.0878s
main: -- remove_index(:users, {:algorithm=>:concurrently, :name=>"index_users_for_billable_users"})
main:    -> 0.0037s
main: == 20230131184319 UpdateBillableUsersIndexForServiceAccounts: migrated (1.0950s)

ci: == 20230131184319 UpdateBillableUsersIndexForServiceAccounts: migrating =======
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0092s
ci: -- index_exists?(:users, :id, {:where=>"state = 'active' AND (user_type IS NULL OR user_type IN (6, 4, 13)) AND (user_type IS NULL OR user_type IN (4, 5))\n", :name=>"index_users_for_active_billable_users", :algorithm=>:concurrently})
ci:    -> 0.0238s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:users, :id, {:where=>"state = 'active' AND (user_type IS NULL OR user_type IN (6, 4, 13)) AND (user_type IS NULL OR user_type IN (4, 5))\n", :name=>"index_users_for_active_billable_users", :algorithm=>:concurrently})
ci:    -> 0.0096s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0006s
ci: -- indexes(:users)
ci:    -> 0.0170s
ci: -- remove_index(:users, {:algorithm=>:concurrently, :name=>"index_users_for_billable_users"})
ci:    -> 0.0029s
ci: == 20230131184319 UpdateBillableUsersIndexForServiceAccounts: migrated (0.0970s)
Rollback output 
main: == 20230131184319 UpdateBillableUsersIndexForServiceAccounts: reverting =======
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0637s
main: -- index_exists?(:users, :id, {:where=>"((state)::text = 'active'::text) AND ((user_type IS NULL)\nOR (user_type = ANY (ARRAY[6, 4]))) AND\n((user_type IS NULL) OR (user_type = ANY (ARRAY[4, 5])))\n", :name=>"index_users_for_billable_users", :algorithm=>:concurrently})
main:    -> 0.0092s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:users, :id, {:where=>"((state)::text = 'active'::text) AND ((user_type IS NULL)\nOR (user_type = ANY (ARRAY[6, 4]))) AND\n((user_type IS NULL) OR (user_type = ANY (ARRAY[4, 5])))\n", :name=>"index_users_for_billable_users", :algorithm=>:concurrently})
main:    -> 0.0030s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0005s
main: -- indexes(:users)
main:    -> 0.0081s
main: -- remove_index(:users, {:algorithm=>:concurrently, :name=>"index_users_for_active_billable_users"})
main:    -> 0.0014s
main: == 20230131184319 UpdateBillableUsersIndexForServiceAccounts: reverted (0.0993s)

ci: == 20230131184319 UpdateBillableUsersIndexForServiceAccounts: reverting =======
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0940s
ci: -- index_exists?(:users, :id, {:where=>"((state)::text = 'active'::text) AND ((user_type IS NULL)\nOR (user_type = ANY (ARRAY[6, 4]))) AND\n((user_type IS NULL) OR (user_type = ANY (ARRAY[4, 5])))\n", :name=>"index_users_for_billable_users", :algorithm=>:concurrently})
ci:    -> 0.0166s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:users, :id, {:where=>"((state)::text = 'active'::text) AND ((user_type IS NULL)\nOR (user_type = ANY (ARRAY[6, 4]))) AND\n((user_type IS NULL) OR (user_type = ANY (ARRAY[4, 5])))\n", :name=>"index_users_for_billable_users", :algorithm=>:concurrently})
ci:    -> 0.0031s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0010s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0011s
ci: -- indexes(:users)
ci:    -> 0.0259s
ci: -- remove_index(:users, {:algorithm=>:concurrently, :name=>"index_users_for_active_billable_users"})
ci:    -> 0.0035s
ci: == 20230131184319 UpdateBillableUsersIndexForServiceAccounts: reverted (0.1721s)
`User.billable.count` query 
SELECT COUNT(*) 
FROM
    users
WHERE
    users.state IN ( 'active' ) AND
    (
        users.user_type IS NULL OR
        users.user_type IN ( 6, 4, 13 )
    ) AND
    (
        users.user_type IS NULL OR
        users.user_type IN ( 4, 5 )
    );

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

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 Drew Blessing

Merge request reports