`grant.rb` check for `TRIGGER` privilege does not work with AWS RDS Postgres 10.10 because of permissions

Summary

grant.rb check for TRIGGER privilege does not work with AWS RDS because the current query requires information_schema.pg_authid which is disallowed there.

Steps to reproduce

Install GitLab 12.7.2-ee using RDS Postgres 10.10

We used the Omnibus installer plus RDS Postgres related instructions here https://docs.gitlab.com/ee/install/aws/
We created a postgres user gitlab and granted it rds_superuser permissions with GRANT rds_superuser TO gitlab;

Show that Gitlab::Database::Grant.create_and_execute_trigger? is not returning the correct value:

# Rails console returns false because of AR exception
irb(main):011:0> Gitlab::Database::Grant.create_and_execute_trigger?('software_license_policies')
=> false

# Postgres console shows our user has this privilege
gitlabhq_production=> select has_table_privilege('software_license_policies', 'TRIGGER');
 has_table_privilege
---------------------
 t
(1 row)

Manually call the check_trigger_permissions! migration helper. You can also run a migration that uses a DB migration helper that calls check_trigger_permissions!. This shows that the discrepancy in 2) is causing the error message.

irb(main):009:0> class Foo; extend Gitlab::Database::MigrationHelpers; end
=> Foo
irb(main):010:0> Foo.check_trigger_permissions! 'foo'

Traceback (most recent call last):
        2: from (irb):10
        1: from lib/gitlab/database/migration_helpers.rb:934:in `check_trigger_permissions!'
RuntimeError (Your database user is not allowed to create, drop, or execute triggers on the)
table foo.

If you are using PostgreSQL you can solve this by logging in to the GitLab
database (gitlabhq_production) using a super user and running:

    ALTER gitlab WITH SUPERUSER

This query will grant the user super user permissions, ensuring you don't run
into similar problems in the future (e.g. when new tables are created).

Show that the query does not work with FROM "information_schema"."role_table_grants" but does work without it:

gitlabhq_production=> SELECT has_table_privilege('software_license_policies', 'TRIGGER') FROM "information_schema"."role_table_grants";
ERROR:  permission denied for relation pg_authid

gitlabhq_production=> SELECT has_table_privilege('software_license_policies', 'TRIGGER') FROM "information_schema"."role_table_grants";
ERROR:  permission denied for relation pg_authid
gitlabhq_production=> SELECT has_table_privilege('software_license_policies', 'TRIGGER');
 has_table_privilege
---------------------
 t
(1 row)

Example Project

This works on a clean install of 12.7.2-ee from the Rails/Postgres console

What is the current bug behavior?

If we run a migration that uses a helper that calls Gitlab::Database::Grant.create_and_execute_trigger?, the following error message is printed and migrations do not proceed. This causes Gitlab to be down until fixed since migrations are pending.

What is the expected correct behavior?

Migrations should proceed and we should not see this error message on RDS Postgres instances.

Relevant logs and/or screenshots

See Steps to Reproduce. Also, we saw this full error migrating from 12.5.1 to 12.7.0

Caused by:
Your database user is not allowed to create, drop, or execute triggers on the
table software_license_policies.

If you are using PostgreSQL you can solve this by logging in to the GitLab
database (gitlabhq_production) using a super user and running:

ALTER gitlab WITH SUPERUSER

This query will grant the user super user permissions, ensuring you don't run
into similar problems in the future (e.g. when new tables are created).
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:934:in `check_trigger_permissions!'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:471:in `rename_column_concurrently'
/opt/gitlab/embedded/service/gitlab-rails/db/migrate/20191119220425_rename_software_license_policies_approval_status_to_classification.rb:11:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:49:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
== 20191119220425 RenameSoftwareLicensePoliciesApprovalStatusToClassification: migrating
-- transaction_open?()
-> 0.0000s

Output of checks

This bug happens on an Omnibus self-managed install.

Results of GitLab environment info

System information
System:   Ubuntu 18.04
Proxy:    no
Current User: git
Using RVM:  no
Ruby Version: 2.6.5p114
Gem Version:  2.7.10
Bundler Version:1.17.3
Rake Version: 12.3.3
Redis Version:  5.0.7
Git Version:  2.24.1
Sidekiq Version:5.2.7
Go Version: unknown

GitLab information
Version:  12.7.2-ee
Revision: be1bc017799
Directory:  /opt/gitlab/embedded/service/gitlab-rails
DB Adapter: PostgreSQL
DB Version: 10.10
URL:    https://gitlab.housecalldev.com
HTTP Clone URL: https://gitlab.housecalldev.com/some-group/some-project.git
SSH Clone URL:  git@gitlab.housecalldev.com:some-group/some-project.git
Elasticsearch:  no
Geo:    no
Using LDAP: no
Using Omniauth: yes
Omniauth Providers:

GitLab Shell
Version:  11.0.0
Repository storage paths:
- default:  /data/gitlab/git/repositories
GitLab Shell path:    /opt/gitlab/embedded/service/gitlab-shell
Git:    /opt/gitlab/embedded/bin/git

Results of GitLab application Check

Checking GitLab subtasks ...

Checking GitLab Shell ...

GitLab Shell: ... GitLab Shell version >= 11.0.0 ? ... OK (11.0.0)
Running /opt/gitlab/embedded/service/gitlab-shell/bin/check
Internal API available: OK
Redis available via internal API: OK
gitlab-shell self-check successful

Checking GitLab Shell ... Finished

Checking Gitaly ...

Gitaly: ... default ... OK

Checking Gitaly ... Finished

Checking Sidekiq ...

Sidekiq: ... Running? ... yes
Number of Sidekiq processes ... 1

Checking Sidekiq ... Finished

Checking Incoming Email ...

Incoming Email: ... Reply by email is disabled in config/gitlab.yml

Checking Incoming Email ... Finished

Checking LDAP ...

LDAP: ... LDAP is disabled in config/gitlab.yml

Checking LDAP ... Finished

Checking GitLab App ...

Git configured correctly? ... yes
Database config exists? ... yes
All migrations up? ... yes
Database contains orphaned GroupMembers? ... no
GitLab config exists? ... yes
GitLab config up to date? ... yes
Log directory writable? ... yes
Tmp directory writable? ... yes
Uploads directory exists? ... yes
Uploads directory has correct permissions? ... yes
Uploads directory tmp has correct permissions? ... yes
Init script exists? ... skipped (omnibus-gitlab has no init script)
Init script up-to-date? ... skipped (omnibus-gitlab has no init script)
Projects have namespace: ...
4/2 ... yes
4/3 ... yes
4/5 ... yes
Redis version >= 2.8.0? ... yes
Ruby version >= 2.5.3 ? ... yes (2.6.5)
Git version >= 2.22.0 ? ... yes (2.24.1)
Git user has default SSH configuration? ... yes
Active users: ... 4
Is authorized keys file accessible? ... yes
Elasticsearch version 5.6 - 6.x? ... skipped (elasticsearch is disabled)

Checking GitLab App ... Finished


Checking GitLab subtasks ... Finished

Possible fixes

This model is including information_schema.role_table_grants in the query even though it isn't required: https://gitlab.com/gitlab-org/gitlab/blob/v12.7.2-ee/lib/gitlab/database/grant.rb#L9

The query would work if it looked something closer to: select_all("select has_table_privilege('software_license_policies', 'TRIGGER');").first['has_table_privilege']

Edited Jan 29, 2020 by Eric Johnson