Geo: Permission denied trying to refresh FDW
I noticed a couple problems when trying to disable FDW:
-
How come the
postgresql_query
hasaction: :nothing
here? https://gitlab.com/gitlab-org/omnibus-gitlab/blob/ddc1066ef38f80c52c77108e3d471f1e13564e5e/files/gitlab-cookbooks/gitlab-ee/recipes/geo-postgresql.rb#L173 -
When upgrading from 10.4 to 10.5, I saw:
* postgresql_query[create gitlab_secondary schema on geo-postgresql] action nothing (skipped due to action :nothing)
* postgresql_fdw[gitlab_secondary] action create
* postgresql_query[enable postgres_fdw extension on gitlabhq_geo_production] action run (skipped due to not_if)
* postgresql_query[create fdw gitlab_secondary on gitlabhq_geo_production] action run (skipped due to not_if)
* postgresql_query[up fdw gitlab_secondary on gitlabhq_geo_production] action run (skipped due to not_if)
(up to date)
* postgresql_fdw_user_mapping[gitlab_secondary] action create (skipped due to not_if)
* bash[refresh foreign table definition] action run
[execute]
Refreshing foreign tables for FDW: gitlab_secondary ... rake aborted!
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR: permission denied for foreign server 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: permission denied for foreign server 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)
- execute "bash" "/tmp/chef-script20180303-26610-fmk6zl"
* execute[reload geo-postgresql] action nothing (skipped due to action :nothing)
I also don't see any foreign tables here:
gitlabhq_geo_production=# \det
List of foreign tables
Schema | Table | Server
--------+-------+--------
(0 rows)
I don't see anything in the upgrade guide in https://docs.gitlab.com/ee/administration/geo/replication/updating_the_geo_nodes.html#upgrading-to-gitlab-10-5 that suggests we need to do something different.
/cc: @brodock