Geo : PG::InsufficientPrivilege: ERROR: must be owner of schema gitlab_secondary
Previously upgraded machine may fail to refresh FDW with postgres permission issue:
* bash[refresh foreign table definition] action run
[execute] rake aborted!
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR: must be owner of schema gitlab_secondary
: DROP SCHEMA IF EXISTS gitlab_secondary CASCADE;
CREATE SCHEMA gitlab_secondary;
IMPORT FOREIGN SCHEMA public
FROM SERVER gitlab_secondary
INTO gitlab_secondary;
/opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/geo/geo_tasks.rb:47:in `block (2 levels) in refresh_foreign_tables!'
/opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/geo/geo_tasks.rb:46:in `block in refresh_foreign_tables!'
/opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/geo/database_tasks.rb:169:in `with_geo_db'
/opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/geo/geo_tasks.rb:45:in `refresh_foreign_tables!'
/opt/gitlab/embedded/service/gitlab-rails/ee/lib/tasks/geo.rake:62:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Caused by:
PG::InsufficientPrivilege: ERROR: must be owner of schema gitlab_secondary
/opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/geo/geo_tasks.rb:47:in `block (2 levels) in refresh_foreign_tables!'
/opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/geo/geo_tasks.rb:46:in `block in refresh_foreign_tables!'
/opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/geo/database_tasks.rb:169:in `with_geo_db'
/opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/geo/geo_tasks.rb:45:in `refresh_foreign_tables!'
/opt/gitlab/embedded/service/gitlab-rails/ee/lib/tasks/geo.rake:62: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 => geo:db:refresh_foreign_tables
(See full trace by running task with --trace)
Refreshing foreign tables for FDW: gitlab_secondary ...
- execute "bash" "/tmp/chef-script20180315-31778-1d5a4dj"When checking permissions:
# /opt/gitlab/bin/gitlab-geo-psql -d gitlabhq_geo_production
could not change directory to "/root": Permission denied
psql (9.6.8)
Type "help" for help.
gitlabhq_geo_production=# \dn
List of schemas
Name | Owner
------------------+-------------
gitlab_secondary | gitlab-psql
public | gitlab-psql
(2 rows)In a machine where it is working correctly:
# /opt/gitlab/bin/gitlab-geo-psql -d gitlabhq_geo_production
could not change directory to "/root": Permission denied
psql (9.6.8)
Type "help" for help.
gitlabhq_geo_production=# \dn
List of schemas
Name | Owner
------------------+-------------
gitlab_secondary | gitlab_geo
public | gitlab-psql
(2 rows)So for some reason, the gitlab_secondary schema was with the wrong owner on one of my test boxes (could be issue with testing previous versions while in development).
In a different note, we should check that value as well and make sure we use the correct owner, like we do for all FDW related parameters.