3 files changed, 88 insertions, 4 deletions
diff --git a/app/models/ci/variable.rb b/app/models/ci/variable.rb
index a77bbef0fca..760872d3e6b 100644
--- a/app/models/ci/variable.rb
+++ b/app/models/ci/variable.rb
@@ -6,6 +6,7 @@ module Ci
     include HasVariable
     include Presentable
     include Maskable
+    prepend HasEnvironmentScope
 
     belongs_to :project
 
diff --git a/app/models/concerns/has_environment_scope.rb b/app/models/concerns/has_environment_scope.rb
new file mode 100644
index 00000000000..9553abe4dd3
--- /dev/null
+++ b/app/models/concerns/has_environment_scope.rb
@@ -0,0 +1,78 @@
+# frozen_string_literal: true
+
+module HasEnvironmentScope
+  extend ActiveSupport::Concern
+
+  prepended do
+    validates(
+      :environment_scope,
+      presence: true,
+      format: { with: ::Gitlab::Regex.environment_scope_regex,
+                message: ::Gitlab::Regex.environment_scope_regex_message }
+    )
+
+    ##
+    # Select rows which have a scope that matches the given environment name.
+    # Rows are ordered by relevance, by default. The most relevant row is
+    # placed at the end of a list.
+    #
+    # options:
+    #   - relevant_only: (boolean)
+    #     You can get the most relevant row only. Other rows are not be
+    #     selected even if its scope matches the environment name.
+    #     This is equivalent to using `#last` from SQL standpoint.
+    #
+    scope :on_environment, -> (environment_name, relevant_only: false) do
+      order_direction = relevant_only ? 'DESC' : 'ASC'
+
+      where = <<~SQL
+        environment_scope IN (:wildcard, :environment_name) OR
+          :environment_name LIKE
+            #{::Gitlab::SQL::Glob.to_like('environment_scope')}
+      SQL
+
+      order = <<~SQL
+        CASE environment_scope
+          WHEN :wildcard THEN 0
+          WHEN :environment_name THEN 2
+          ELSE 1
+        END #{order_direction}
+      SQL
+
+      values = {
+        wildcard: '*',
+        environment_name: environment_name
+      }
+
+      sanitized_order_sql = sanitize_sql_array([order, values])
+
+      # The query is trying to find variables with scopes matching the
+      # current environment name. Suppose the environment name is
+      # 'review/app', and we have variables with environment scopes like:
+      # * variable A: review
+      # * variable B: review/app
+      # * variable C: review/*
+      # * variable D: *
+      # And the query should find variable B, C, and D, because it would
+      # try to convert the scope into a LIKE pattern for each variable:
+      # * A: review
+      # * B: review/app
+      # * C: review/%
+      # * D: %
+      # Note that we'll match % and _ literally therefore we'll escape them.
+      # In this case, B, C, and D would match. We also want to prioritize
+      # the exact matched name, and put * last, and everything else in the
+      # middle. So the order should be: D < C < B
+      relation = where(where, values)
+        .order(Arel.sql(sanitized_order_sql)) # `order` cannot escape for us!
+
+      relation = relation.limit(1) if relevant_only
+
+      relation
+    end
+  end
+
+  def environment_scope=(new_environment_scope)
+    super(new_environment_scope.to_s.strip)
+  end
+end
diff --git a/app/models/project.rb b/app/models/project.rb
index 960795b73cb..7a5e980b783 100644
--- a/app/models/project.rb
+++ b/app/models/project.rb
@@ -1828,11 +1828,16 @@ class Project < ApplicationRecord
   end
 
   def ci_variables_for(ref:, environment: nil)
-    # EE would use the environment
-    if protected_for?(ref)
-      variables
+    result = if protected_for?(ref)
+               variables
+             else
+               variables.unprotected
+             end
+
+    if environment
+      result.on_environment(environment)
     else
-      variables.unprotected
+      result.where(environment_scope: '*')
     end
   end