Merge branch 'fix-postgresql-table-grant' into 'master'

Use has_table_privilege for TRIGGER on PostgreSQL

Closes #38634

See merge request gitlab-org/gitlab-ce!16618

2 files changed, 35 insertions, 20 deletions

diff --git a/changelogs/unreleased/fix-postgresql-table-grant.yml b/changelogs/unreleased/fix-postgresql-table-grant.yml
new file mode 100644
index 00000000000..1c6559f6f73
--- /dev/null
+++ b/changelogs/unreleased/fix-postgresql-table-grant.yml
@@ -0,0 +1,5 @@
+---
+title: Use has_table_privilege for TRIGGER on PostgreSQL
+merge_request:
+author:
+type: fixed
diff --git a/lib/gitlab/database/grant.rb b/lib/gitlab/database/grant.rb
index 9f76967fc77..d32837f5793 100644
--- a/lib/gitlab/database/grant.rb
+++ b/lib/gitlab/database/grant.rb
@@ -12,30 +12,40 @@ module Gitlab
       # Returns true if the current user can create and execute triggers on the
       # given table.
       def self.create_and_execute_trigger?(table)
-        priv =
-          if Database.postgresql?
-            where(privilege_type: 'TRIGGER', table_name: table)
-              .where('grantee = user')
-          else
-            queries = [
-              Grant.select(1)
-                .from('information_schema.user_privileges')
-                .where("PRIVILEGE_TYPE = 'SUPER'")
-                .where("GRANTEE = CONCAT('\\'', REPLACE(CURRENT_USER(), '@', '\\'@\\''), '\\'')"),
+        if Database.postgresql?
+          # We _must not_ use quote_table_name as this will produce double
+          # quotes on PostgreSQL and for "has_table_privilege" we need single
+          # quotes.
+          quoted_table = connection.quote(table)
 
-              Grant.select(1)
-                .from('information_schema.schema_privileges')
-                .where("PRIVILEGE_TYPE = 'TRIGGER'")
-                .where('TABLE_SCHEMA = ?', Gitlab::Database.database_name)
-                .where("GRANTEE = CONCAT('\\'', REPLACE(CURRENT_USER(), '@', '\\'@\\''), '\\'')")
-            ]
+          begin
+            from(nil)
+              .pluck("has_table_privilege(#{quoted_table}, 'TRIGGER')")
+              .first
+          rescue ActiveRecord::StatementInvalid
+            # This error is raised when using a non-existing table name. In this
+            # case we just want to return false as a user technically can't
+            # create triggers for such a table.
+            false
+          end
+        else
+          queries = [
+            Grant.select(1)
+              .from('information_schema.user_privileges')
+              .where("PRIVILEGE_TYPE = 'SUPER'")
+              .where("GRANTEE = CONCAT('\\'', REPLACE(CURRENT_USER(), '@', '\\'@\\''), '\\'')"),
 
-            union = SQL::Union.new(queries).to_sql
+            Grant.select(1)
+              .from('information_schema.schema_privileges')
+              .where("PRIVILEGE_TYPE = 'TRIGGER'")
+              .where('TABLE_SCHEMA = ?', Gitlab::Database.database_name)
+              .where("GRANTEE = CONCAT('\\'', REPLACE(CURRENT_USER(), '@', '\\'@\\''), '\\'')")
+          ]
 
-            Grant.from("(#{union}) privs")
-          end
+          union = SQL::Union.new(queries).to_sql
 
-        priv.any?
+          Grant.from("(#{union}) privs").any?
+        end
       end
     end
   end