`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 itrds_superuser
permissions withGRANT 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 callscheck_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']