From fd05e26618dd0c123ca476b6f5a3d85f1cfe397a Mon Sep 17 00:00:00 2001
From: Ahmad Sherif <me@ahmadsherif.com>
Date: Tue, 11 Oct 2016 14:25:17 +0200
Subject: Precalculate user's authorized projects in database

Closes #23150
---
 .../concerns/select_for_project_authorization.rb   |  9 +++
 app/models/group.rb                                | 13 ++++
 app/models/member.rb                               | 13 ++++
 app/models/project.rb                              | 33 +---------
 app/models/project_authorization.rb                |  8 +++
 app/models/project_group_link.rb                   |  7 ++
 app/models/user.rb                                 | 66 ++++++++++++++-----
 app/services/projects/create_service.rb            |  2 +
 .../user_project_access_changed_service.rb         |  9 +++
 app/workers/authorized_projects_worker.rb          | 15 +++++
 .../feature-precalculate-authorized-projects.yml   |  4 ++
 config/sidekiq_queues.yml                          |  1 +
 db/fixtures/development/04_project.rb              |  1 +
 db/fixtures/development/17_cycle_analytics.rb      |  1 +
 db/fixtures/support/serialized_transaction.rb      |  9 +++
 ...20161010142410_create_project_authorizations.rb | 15 +++++
 ...1_add_authorized_projects_populated_to_users.rb |  9 +++
 db/schema.rb                                       | 11 ++++
 lib/gitlab/database.rb                             |  7 ++
 spec/finders/group_projects_finder_spec.rb         |  5 +-
 spec/models/member_spec.rb                         | 22 +++++++
 spec/models/project_group_link_spec.rb             | 16 +++++
 spec/models/project_spec.rb                        |  6 +-
 spec/models/user_spec.rb                           | 76 +++++++++++++++++++++-
 spec/services/projects/create_service_spec.rb      |  3 +
 spec/support/db_cleaner.rb                         |  4 ++
 spec/workers/authorized_projects_worker_spec.rb    | 22 +++++++
 27 files changed, 336 insertions(+), 51 deletions(-)
 create mode 100644 app/models/concerns/select_for_project_authorization.rb
 create mode 100644 app/models/project_authorization.rb
 create mode 100644 app/services/user_project_access_changed_service.rb
 create mode 100644 app/workers/authorized_projects_worker.rb
 create mode 100644 changelogs/unreleased/feature-precalculate-authorized-projects.yml
 create mode 100644 db/fixtures/support/serialized_transaction.rb
 create mode 100644 db/migrate/20161010142410_create_project_authorizations.rb
 create mode 100644 db/migrate/20161017091941_add_authorized_projects_populated_to_users.rb
 create mode 100644 spec/workers/authorized_projects_worker_spec.rb

diff --git a/app/models/concerns/select_for_project_authorization.rb b/app/models/concerns/select_for_project_authorization.rb
new file mode 100644
index 00000000000..50a1d7fc3e1
--- /dev/null
+++ b/app/models/concerns/select_for_project_authorization.rb
@@ -0,0 +1,9 @@
+module SelectForProjectAuthorization
+  extend ActiveSupport::Concern
+
+  module ClassMethods
+    def select_for_project_authorization
+      select("members.user_id, projects.id AS project_id, members.access_level")
+    end
+  end
+end
diff --git a/app/models/group.rb b/app/models/group.rb
index d9e90cd256a..73b0f1c6572 100644
--- a/app/models/group.rb
+++ b/app/models/group.rb
@@ -5,6 +5,7 @@ class Group < Namespace
   include Gitlab::VisibilityLevel
   include AccessRequestable
   include Referable
+  include SelectForProjectAuthorization
 
   has_many :group_members, -> { where(requested_at: nil) }, dependent: :destroy, as: :source
   alias_method :members, :group_members
@@ -61,6 +62,14 @@ class Group < Namespace
     def visible_to_user(user)
       where(id: user.authorized_groups.select(:id).reorder(nil))
     end
+
+    def select_for_project_authorization
+      if current_scope.joins_values.include?(:shared_projects)
+        select("members.user_id, projects.id AS project_id, project_group_links.group_access")
+      else
+        super
+      end
+    end
   end
 
   def to_reference(_from_project = nil)
@@ -176,4 +185,8 @@ class Group < Namespace
   def system_hook_service
     SystemHooksService.new
   end
+
+  def refresh_members_authorized_projects
+    UserProjectAccessChangedService.new(users.pluck(:id)).execute
+  end
 end
diff --git a/app/models/member.rb b/app/models/member.rb
index b89ba8ecbb8..7be2665bf48 100644
--- a/app/models/member.rb
+++ b/app/models/member.rb
@@ -113,6 +113,8 @@ class Member < ActiveRecord::Base
         member.save
       end
 
+      UserProjectAccessChangedService.new(user.id).execute if user.is_a?(User)
+
       member
     end
 
@@ -239,6 +241,7 @@ class Member < ActiveRecord::Base
   end
 
   def post_create_hook
+    UserProjectAccessChangedService.new(user.id).execute
     system_hook_service.execute_hooks_for(self, :create)
   end
 
@@ -247,9 +250,19 @@ class Member < ActiveRecord::Base
   end
 
   def post_destroy_hook
+    refresh_member_authorized_projects
     system_hook_service.execute_hooks_for(self, :destroy)
   end
 
+  def refresh_member_authorized_projects
+    # If user/source is being destroyed, project access are gonna be destroyed eventually
+    # because of DB foreign keys, so we shouldn't bother with refreshing after each
+    # member is destroyed through association
+    return if destroyed_by_association.present?
+
+    UserProjectAccessChangedService.new(user_id).execute
+  end
+
   def after_accept_invite
     post_create_hook
   end
diff --git a/app/models/project.rb b/app/models/project.rb
index f9bcc547c36..50ca7fbcedf 100644
--- a/app/models/project.rb
+++ b/app/models/project.rb
@@ -13,6 +13,7 @@ class Project < ActiveRecord::Base
   include CaseSensitivity
   include TokenAuthenticatable
   include ProjectFeaturesCompatibility
+  include SelectForProjectAuthorization
 
   extend Gitlab::ConfigHelper
 
@@ -1289,16 +1290,10 @@ class Project < ActiveRecord::Base
 
   # Checks if `user` is authorized for this project, with at least the
   # `min_access_level` (if given).
-  #
-  # If you change the logic of this method, please also update `User#authorized_projects`
   def authorized_for_user?(user, min_access_level = nil)
     return false unless user
 
-    return true if personal? && namespace_id == user.namespace_id
-
-    authorized_for_user_by_group?(user, min_access_level) ||
-      authorized_for_user_by_members?(user, min_access_level) ||
-      authorized_for_user_by_shared_projects?(user, min_access_level)
+    user.authorized_project?(self, min_access_level)
   end
 
   def append_or_update_attribute(name, value)
@@ -1358,30 +1353,6 @@ class Project < ActiveRecord::Base
       current_application_settings.default_branch_protection == Gitlab::Access::PROTECTION_DEV_CAN_MERGE
   end
 
-  def authorized_for_user_by_group?(user, min_access_level)
-    member = user.group_members.find_by(source_id: group)
-
-    member && (!min_access_level || member.access_level >= min_access_level)
-  end
-
-  def authorized_for_user_by_members?(user, min_access_level)
-    member = members.find_by(user_id: user)
-
-    member && (!min_access_level || member.access_level >= min_access_level)
-  end
-
-  def authorized_for_user_by_shared_projects?(user, min_access_level)
-    shared_projects = user.group_members.joins(group: :shared_projects).
-      where(project_group_links: { project_id: self })
-
-    if min_access_level
-      members_scope = { access_level: Gitlab::Access.values.select { |access| access >= min_access_level } }
-      shared_projects = shared_projects.where(members: members_scope)
-    end
-
-    shared_projects.any?
-  end
-
   # Similar to the normal callbacks that hook into the life cycle of an
   # Active Record object, you can also define callbacks that get triggered
   # when you add an object to an association collection. If any of these
diff --git a/app/models/project_authorization.rb b/app/models/project_authorization.rb
new file mode 100644
index 00000000000..a00d43773d9
--- /dev/null
+++ b/app/models/project_authorization.rb
@@ -0,0 +1,8 @@
+class ProjectAuthorization < ActiveRecord::Base
+  belongs_to :user
+  belongs_to :project
+
+  validates :project, presence: true
+  validates :access_level, inclusion: { in: Gitlab::Access.all_values }, presence: true
+  validates :user, uniqueness: { scope: [:project, :access_level] }, presence: true
+end
diff --git a/app/models/project_group_link.rb b/app/models/project_group_link.rb
index db46def11eb..6149c35cc61 100644
--- a/app/models/project_group_link.rb
+++ b/app/models/project_group_link.rb
@@ -16,6 +16,9 @@ class ProjectGroupLink < ActiveRecord::Base
   validates :group_access, inclusion: { in: Gitlab::Access.values }, presence: true
   validate :different_group
 
+  after_create :refresh_group_members_authorized_projects
+  after_destroy :refresh_group_members_authorized_projects
+
   def self.access_options
     Gitlab::Access.options
   end
@@ -35,4 +38,8 @@ class ProjectGroupLink < ActiveRecord::Base
       errors.add(:base, "Project cannot be shared with the project it is in.")
     end
   end
+
+  def refresh_group_members_authorized_projects
+    group.refresh_members_authorized_projects
+  end
 end
diff --git a/app/models/user.rb b/app/models/user.rb
index 5a2b232c4ed..c405321127b 100644
--- a/app/models/user.rb
+++ b/app/models/user.rb
@@ -72,6 +72,8 @@ class User < ActiveRecord::Base
   has_many :created_projects,         foreign_key: :creator_id, class_name: 'Project'
   has_many :users_star_projects, dependent: :destroy
   has_many :starred_projects, through: :users_star_projects, source: :project
+  has_many :project_authorizations, dependent: :destroy
+  has_many :authorized_projects, through: :project_authorizations, source: :project
 
   has_many :snippets,                 dependent: :destroy, foreign_key: :author_id
   has_many :issues,                   dependent: :destroy, foreign_key: :author_id
@@ -438,11 +440,44 @@ class User < ActiveRecord::Base
     Group.where("namespaces.id IN (#{union.to_sql})")
   end
 
-  # Returns projects user is authorized to access.
-  #
-  # If you change the logic of this method, please also update `Project#authorized_for_user`
+  def refresh_authorized_projects
+    loop do
+      begin
+        Gitlab::Database.serialized_transaction do
+          project_authorizations.delete_all
+
+          # project_authorizations_union can return multiple records for the same project/user with
+          # different access_level so we take row with the maximum access_level
+          project_authorizations.connection.execute <<-SQL
+            INSERT INTO project_authorizations (user_id, project_id, access_level)
+            SELECT user_id, project_id, MAX(access_level) AS access_level
+            FROM (#{project_authorizations_union.to_sql}) sub
+            GROUP BY user_id, project_id
+          SQL
+
+          update_column(:authorized_projects_populated, true) unless authorized_projects_populated
+        end
+
+        break
+      # In the event of a concurrent modification Rails raises StatementInvalid.
+      # In this case we want to keep retrying until the transaction succeeds
+      rescue ActiveRecord::StatementInvalid
+      end
+    end
+  end
+
   def authorized_projects(min_access_level = nil)
-    Project.where("projects.id IN (#{projects_union(min_access_level).to_sql})")
+    refresh_authorized_projects unless authorized_projects_populated
+
+    # We're overriding an association, so explicitly call super with no arguments or it would be passed as `force_reload` to the association
+    projects = super()
+    projects = projects.where('project_authorizations.access_level >= ?', min_access_level) if min_access_level
+
+    projects
+  end
+
+  def authorized_project?(project, min_access_level = nil)
+    authorized_projects(min_access_level).exists?({ id: project.id })
   end
 
   # Returns the projects this user has reporter (or greater) access to, limited
@@ -456,8 +491,9 @@ class User < ActiveRecord::Base
   end
 
   def viewable_starred_projects
-    starred_projects.where("projects.visibility_level IN (?) OR projects.id IN (#{projects_union.to_sql})",
-                           [Project::PUBLIC, Project::INTERNAL])
+    starred_projects.where("projects.visibility_level IN (?) OR projects.id IN (?)",
+                           [Project::PUBLIC, Project::INTERNAL],
+                           authorized_projects.select(:project_id))
   end
 
   def owned_projects
@@ -887,16 +923,14 @@ class User < ActiveRecord::Base
 
   private
 
-  def projects_union(min_access_level = nil)
-    relations = [personal_projects.select(:id),
-                 groups_projects.select(:id),
-                 projects.select(:id),
-                 groups.joins(:shared_projects).select(:project_id)]
-
-    if min_access_level
-      scope = { access_level: Gitlab::Access.all_values.select { |access| access >= min_access_level } }
-      relations = [relations.shift] + relations.map { |relation| relation.where(members: scope) }
-    end
+  # Returns a union query of projects that the user is authorized to access
+  def project_authorizations_union
+    relations = [
+      personal_projects.select("#{id} AS user_id, projects.id AS project_id, #{Gitlab::Access::OWNER} AS access_level"),
+      groups_projects.select_for_project_authorization,
+      projects.select_for_project_authorization,
+      groups.joins(:shared_projects).select_for_project_authorization
+    ]
 
     Gitlab::SQL::Union.new(relations)
   end
diff --git a/app/services/projects/create_service.rb b/app/services/projects/create_service.rb
index 28db145a1f4..159f46cd465 100644
--- a/app/services/projects/create_service.rb
+++ b/app/services/projects/create_service.rb
@@ -106,6 +106,8 @@ module Projects
       unless @project.group || @project.gitlab_project_import?
         @project.team << [current_user, :master, current_user]
       end
+
+      @project.group.refresh_members_authorized_projects if @project.group
     end
 
     def skip_wiki?
diff --git a/app/services/user_project_access_changed_service.rb b/app/services/user_project_access_changed_service.rb
new file mode 100644
index 00000000000..2469b4f0d7c
--- /dev/null
+++ b/app/services/user_project_access_changed_service.rb
@@ -0,0 +1,9 @@
+class UserProjectAccessChangedService
+  def initialize(user_ids)
+    @user_ids = Array.wrap(user_ids)
+  end
+
+  def execute
+    AuthorizedProjectsWorker.bulk_perform_async(@user_ids.map { |id| [id] })
+  end
+end
diff --git a/app/workers/authorized_projects_worker.rb b/app/workers/authorized_projects_worker.rb
new file mode 100644
index 00000000000..331727ba9d8
--- /dev/null
+++ b/app/workers/authorized_projects_worker.rb
@@ -0,0 +1,15 @@
+class AuthorizedProjectsWorker
+  include Sidekiq::Worker
+  include DedicatedSidekiqQueue
+
+  def self.bulk_perform_async(args_list)
+    Sidekiq::Client.push_bulk('class' => self, 'args' => args_list)
+  end
+
+  def perform(user_id)
+    user = User.find_by(id: user_id)
+    return unless user
+
+    user.refresh_authorized_projects
+  end
+end
diff --git a/changelogs/unreleased/feature-precalculate-authorized-projects.yml b/changelogs/unreleased/feature-precalculate-authorized-projects.yml
new file mode 100644
index 00000000000..6e48b710fec
--- /dev/null
+++ b/changelogs/unreleased/feature-precalculate-authorized-projects.yml
@@ -0,0 +1,4 @@
+---
+title: Precalculate user's authorized projects in database
+merge_request: 6839
+author:
diff --git a/config/sidekiq_queues.yml b/config/sidekiq_queues.yml
index f3531dd30a5..69136b73946 100644
--- a/config/sidekiq_queues.yml
+++ b/config/sidekiq_queues.yml
@@ -35,6 +35,7 @@
   - [clear_database_cache, 1]
   - [delete_user, 1]
   - [delete_merged_branches, 1]
+  - [authorized_projects, 1]
   - [expire_build_instance_artifacts, 1]
   - [group_destroy, 1]
   - [irker, 1]
diff --git a/db/fixtures/development/04_project.rb b/db/fixtures/development/04_project.rb
index a984eda5ab5..18a2df7c059 100644
--- a/db/fixtures/development/04_project.rb
+++ b/db/fixtures/development/04_project.rb
@@ -1,4 +1,5 @@
 require 'sidekiq/testing'
+require './db/fixtures/support/serialized_transaction'
 
 Sidekiq::Testing.inline! do
   Gitlab::Seeder.quiet do
diff --git a/db/fixtures/development/17_cycle_analytics.rb b/db/fixtures/development/17_cycle_analytics.rb
index e882a492757..0e72067e6ad 100644
--- a/db/fixtures/development/17_cycle_analytics.rb
+++ b/db/fixtures/development/17_cycle_analytics.rb
@@ -1,5 +1,6 @@
 require 'sidekiq/testing'
 require './spec/support/test_env'
+require './db/fixtures/support/serialized_transaction'
 
 class Gitlab::Seeder::CycleAnalytics
   def initialize(project, perf: false)
diff --git a/db/fixtures/support/serialized_transaction.rb b/db/fixtures/support/serialized_transaction.rb
new file mode 100644
index 00000000000..d3305b661e5
--- /dev/null
+++ b/db/fixtures/support/serialized_transaction.rb
@@ -0,0 +1,9 @@
+require 'gitlab/database'
+
+module Gitlab
+  module Database
+    def self.serialized_transaction
+      connection.transaction { yield }
+    end
+  end
+end
diff --git a/db/migrate/20161010142410_create_project_authorizations.rb b/db/migrate/20161010142410_create_project_authorizations.rb
new file mode 100644
index 00000000000..e095ab969f8
--- /dev/null
+++ b/db/migrate/20161010142410_create_project_authorizations.rb
@@ -0,0 +1,15 @@
+class CreateProjectAuthorizations < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  def change
+    create_table :project_authorizations do |t|
+      t.references :user, foreign_key: { on_delete: :cascade }
+      t.references :project, foreign_key: { on_delete: :cascade }
+      t.integer :access_level
+
+      t.index [:user_id, :project_id, :access_level], unique: true, name: 'index_project_authorizations_on_user_id_project_id_access_level'
+    end
+  end
+end
diff --git a/db/migrate/20161017091941_add_authorized_projects_populated_to_users.rb b/db/migrate/20161017091941_add_authorized_projects_populated_to_users.rb
new file mode 100644
index 00000000000..8f6be9dd677
--- /dev/null
+++ b/db/migrate/20161017091941_add_authorized_projects_populated_to_users.rb
@@ -0,0 +1,9 @@
+class AddAuthorizedProjectsPopulatedToUsers < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  def change
+    add_column :users, :authorized_projects_populated, :boolean
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index ed4dfc786f6..83846877ef6 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -844,6 +844,14 @@ ActiveRecord::Schema.define(version: 20161109150329) do
   add_index "personal_access_tokens", ["token"], name: "index_personal_access_tokens_on_token", unique: true, using: :btree
   add_index "personal_access_tokens", ["user_id"], name: "index_personal_access_tokens_on_user_id", using: :btree
 
+  create_table "project_authorizations", force: :cascade do |t|
+    t.integer "user_id"
+    t.integer "project_id"
+    t.integer "access_level"
+  end
+
+  add_index "project_authorizations", ["user_id", "project_id", "access_level"], name: "index_project_authorizations_on_user_id_project_id_access_level", unique: true, using: :btree
+
   create_table "project_features", force: :cascade do |t|
     t.integer "project_id"
     t.integer "merge_requests_access_level"
@@ -1187,6 +1195,7 @@ ActiveRecord::Schema.define(version: 20161109150329) do
     t.boolean "external", default: false
     t.string "organization"
     t.string "incoming_email_token"
+    t.boolean "authorized_projects_populated"
   end
 
   add_index "users", ["admin"], name: "index_users_on_admin", using: :btree
@@ -1248,6 +1257,8 @@ ActiveRecord::Schema.define(version: 20161109150329) do
   add_foreign_key "merge_requests_closing_issues", "issues", on_delete: :cascade
   add_foreign_key "merge_requests_closing_issues", "merge_requests", on_delete: :cascade
   add_foreign_key "personal_access_tokens", "users"
+  add_foreign_key "project_authorizations", "projects", on_delete: :cascade
+  add_foreign_key "project_authorizations", "users", on_delete: :cascade
   add_foreign_key "protected_branch_merge_access_levels", "protected_branches"
   add_foreign_key "protected_branch_push_access_levels", "protected_branches"
   add_foreign_key "trending_projects", "projects", on_delete: :cascade
diff --git a/lib/gitlab/database.rb b/lib/gitlab/database.rb
index 55b8f888d53..2d5c9232425 100644
--- a/lib/gitlab/database.rb
+++ b/lib/gitlab/database.rb
@@ -35,6 +35,13 @@ module Gitlab
       order
     end
 
+    def self.serialized_transaction
+      opts = {}
+      opts[:isolation] = :serializable unless Rails.env.test? && connection.transaction_open?
+
+      connection.transaction(opts) { yield }
+    end
+
     def self.random
       Gitlab::Database.postgresql? ? "RANDOM()" : "RAND()"
     end
diff --git a/spec/finders/group_projects_finder_spec.rb b/spec/finders/group_projects_finder_spec.rb
index fbe09b28b3c..00eec3f3f4c 100644
--- a/spec/finders/group_projects_finder_spec.rb
+++ b/spec/finders/group_projects_finder_spec.rb
@@ -38,7 +38,10 @@ describe GroupProjectsFinder do
   end
 
   describe 'without group member current_user' do
-    before { shared_project_2.team << [current_user, Gitlab::Access::MASTER] }
+    before do
+      shared_project_2.team << [current_user, Gitlab::Access::MASTER]
+      current_user.reload
+    end
 
     context "only shared" do
       context "without external user" do
diff --git a/spec/models/member_spec.rb b/spec/models/member_spec.rb
index 12419d6fd5a..4f7c8a36cb5 100644
--- a/spec/models/member_spec.rb
+++ b/spec/models/member_spec.rb
@@ -443,6 +443,16 @@ describe Member, models: true do
 
       member.accept_invite!(user)
     end
+
+    it "refreshes user's authorized projects", truncate: true do
+      project = member.source
+
+      expect(user.authorized_projects).not_to include(project)
+
+      member.accept_invite!(user)
+
+      expect(user.authorized_projects.reload).to include(project)
+    end
   end
 
   describe "#decline_invite!" do
@@ -468,4 +478,16 @@ describe Member, models: true do
       expect { member.generate_invite_token }.to change { member.invite_token}
     end
   end
+
+  describe "destroying a record", truncate: true do
+    it "refreshes user's authorized projects" do
+      project = create(:project, :private)
+      user    = create(:user)
+      member  = project.team << [user, :reporter]
+
+      member.destroy
+
+      expect(user.authorized_projects).not_to include(project)
+    end
+  end
 end
diff --git a/spec/models/project_group_link_spec.rb b/spec/models/project_group_link_spec.rb
index c5ff1941378..47397a822c1 100644
--- a/spec/models/project_group_link_spec.rb
+++ b/spec/models/project_group_link_spec.rb
@@ -14,4 +14,20 @@ describe ProjectGroupLink do
     it { should validate_presence_of(:group) }
     it { should validate_presence_of(:group_access) }
   end
+
+  describe "destroying a record", truncate: true do
+    it "refreshes group users' authorized projects" do
+      project     = create(:project, :private)
+      group       = create(:group)
+      reporter    = create(:user)
+      group_users = group.users
+
+      group.add_reporter(reporter)
+      project.project_group_links.create(group: group)
+      group_users.each { |user| expect(user.authorized_projects).to include(project) }
+
+      project.project_group_links.destroy_all
+      group_users.each { |user| expect(user.authorized_projects).not_to include(project) }
+    end
+  end
 end
diff --git a/spec/models/project_spec.rb b/spec/models/project_spec.rb
index 46fa00a79c4..e637c464b18 100644
--- a/spec/models/project_spec.rb
+++ b/spec/models/project_spec.rb
@@ -1514,7 +1514,7 @@ describe Project, models: true do
       members_project.team << [developer, :developer]
       members_project.team << [master, :master]
 
-      create(:project_group_link, project: shared_project, group: group)
+      create(:project_group_link, project: shared_project, group: group, group_access: Gitlab::Access::DEVELOPER)
     end
 
     it 'returns false for no user' do
@@ -1543,7 +1543,9 @@ describe Project, models: true do
       expect(members_project.authorized_for_user?(developer, Gitlab::Access::MASTER)).to be(false)
       expect(members_project.authorized_for_user?(master, Gitlab::Access::MASTER)).to be(true)
       expect(shared_project.authorized_for_user?(developer, Gitlab::Access::MASTER)).to be(false)
-      expect(shared_project.authorized_for_user?(master, Gitlab::Access::MASTER)).to be(true)
+      expect(shared_project.authorized_for_user?(master, Gitlab::Access::MASTER)).to be(false)
+      expect(shared_project.authorized_for_user?(developer, Gitlab::Access::DEVELOPER)).to be(true)
+      expect(shared_project.authorized_for_user?(master, Gitlab::Access::DEVELOPER)).to be(true)
     end
   end
 
diff --git a/spec/models/user_spec.rb b/spec/models/user_spec.rb
index 580ce4a9e0a..10528693024 100644
--- a/spec/models/user_spec.rb
+++ b/spec/models/user_spec.rb
@@ -1072,7 +1072,7 @@ describe User, models: true do
     it { is_expected.to eq([private_group]) }
   end
 
-  describe '#authorized_projects' do
+  describe '#authorized_projects', truncate: true do
     context 'with a minimum access level' do
       it 'includes projects for which the user is an owner' do
         user = create(:user)
@@ -1092,6 +1092,80 @@ describe User, models: true do
           .to contain_exactly(project)
       end
     end
+
+    it "includes user's personal projects" do
+      user    = create(:user)
+      project = create(:project, :private, namespace: user.namespace)
+
+      expect(user.authorized_projects).to include(project)
+    end
+
+    it "includes personal projects user has been given access to" do
+      user1   = create(:user)
+      user2   = create(:user)
+      project = create(:project, :private, namespace: user1.namespace)
+
+      project.team << [user2, Gitlab::Access::DEVELOPER]
+
+      expect(user2.authorized_projects).to include(project)
+    end
+
+    it "includes projects of groups user has been added to" do
+      group   = create(:group)
+      project = create(:project, group: group)
+      user    = create(:user)
+
+      group.add_developer(user)
+
+      expect(user.authorized_projects).to include(project)
+    end
+
+    it "does not include projects of groups user has been removed from" do
+      group   = create(:group)
+      project = create(:project, group: group)
+      user    = create(:user)
+
+      member = group.add_developer(user)
+      expect(user.authorized_projects).to include(project)
+
+      member.destroy
+      expect(user.authorized_projects).not_to include(project)
+    end
+
+    it "includes projects shared with user's group" do
+      user    = create(:user)
+      project = create(:project, :private)
+      group   = create(:group)
+
+      group.add_reporter(user)
+      project.project_group_links.create(group: group)
+
+      expect(user.authorized_projects).to include(project)
+    end
+
+    it "does not include destroyed projects user had access to" do
+      user1   = create(:user)
+      user2   = create(:user)
+      project = create(:project, :private, namespace: user1.namespace)
+
+      project.team << [user2, Gitlab::Access::DEVELOPER]
+      expect(user2.authorized_projects).to include(project)
+
+      project.destroy
+      expect(user2.authorized_projects).not_to include(project)
+    end
+
+    it "does not include projects of destroyed groups user had access to" do
+      group   = create(:group)
+      project = create(:project, namespace: group)
+      user    = create(:user)
+
+      group.add_developer(user)
+      expect(user.authorized_projects).to include(project)
+
+      group.destroy
+      expect(user.authorized_projects).not_to include(project)
+    end
   end
 
   describe '#projects_where_can_admin_issues' do
diff --git a/spec/services/projects/create_service_spec.rb b/spec/services/projects/create_service_spec.rb
index 2cf9883113c..fbd22560d6e 100644
--- a/spec/services/projects/create_service_spec.rb
+++ b/spec/services/projects/create_service_spec.rb
@@ -34,6 +34,8 @@ describe Projects::CreateService, services: true do
         @group = create :group
         @group.add_owner(@user)
 
+        @user.refresh_authorized_projects # Ensure cache is warm
+
         @opts.merge!(namespace_id: @group.id)
         @project = create_project(@user, @opts)
       end
@@ -41,6 +43,7 @@ describe Projects::CreateService, services: true do
       it { expect(@project).to be_valid }
       it { expect(@project.owner).to eq(@group) }
       it { expect(@project.namespace).to eq(@group) }
+      it { expect(@user.authorized_projects).to include(@project) }
     end
 
     context 'error handling' do
diff --git a/spec/support/db_cleaner.rb b/spec/support/db_cleaner.rb
index ac38e31b77e..247f0954221 100644
--- a/spec/support/db_cleaner.rb
+++ b/spec/support/db_cleaner.rb
@@ -11,6 +11,10 @@ RSpec.configure do |config|
     DatabaseCleaner.strategy = :truncation
   end
 
+  config.before(:each, truncate: true) do
+    DatabaseCleaner.strategy = :truncation
+  end
+
   config.before(:each) do
     DatabaseCleaner.start
   end
diff --git a/spec/workers/authorized_projects_worker_spec.rb b/spec/workers/authorized_projects_worker_spec.rb
new file mode 100644
index 00000000000..18a1aab766c
--- /dev/null
+++ b/spec/workers/authorized_projects_worker_spec.rb
@@ -0,0 +1,22 @@
+require 'spec_helper'
+
+describe AuthorizedProjectsWorker do
+  describe '#perform' do
+    it "refreshes user's authorized projects" do
+      user = create(:user)
+
+      expect(User).to receive(:find_by).with(id: user.id).and_return(user)
+      expect(user).to receive(:refresh_authorized_projects)
+
+      described_class.new.perform(user.id)
+    end
+
+    context "when user is not found" do
+      it "does nothing" do
+        expect_any_instance_of(User).not_to receive(:refresh_authorized_projects)
+
+        described_class.new.perform(999_999)
+      end
+    end
+  end
+end
-- 
cgit v1.2.1