Skip to content

Add FDW user mapping password if needed

Michael Kozono requested to merge mk/fix-fdw-user-mapping-for-no-password into master

What does this MR do?

When the FDW user mapping exists, but a password is not set, then the SET option errors.

This change uses the ADD option in that case.

Checklist

See Definition of done.

  • Change added to CHANGELOG.md. Not applicable for Documentation changes and minor changes.
  • Documentation created/updated
  • Tests added
  • Integration tests added to GitLab QA, if applicable
  • MR targeting master branch
  • MR has a green pipeline on GitLab.com

Reviewer Checklist

In addition to above, reviewer must:

  • Pipeline is green on dev.gitlab.org if the change is not touching documentation or internal cookbooks

References

Resolves https://gitlab.com/gitlab-org/gitlab-ee/issues/7688

Click me to collapse/fold Proof of fix on an v11.4 Omnibus + Geo installation.

Before

gitlabhq_geo_production=# alter user mapping for gitlab_geo server gitlab_secondary OPTIONS (DROP password);
ALTER USER MAPPING
gitlabhq_geo_production=# \deu+
               List of user mappings
      Server      | User name  |    FDW Options    
------------------+------------+-------------------
 gitlab_secondary | gitlab_geo | ("user" 'gitlab')
(1 row)

gitlabhq_geo_production=# \q
root@mike-test-geo2:~# gitlab-ctl reconfigure
Starting Chef Client, version 13.6.4
resolving cookbooks for run list: ["gitlab-ee"]
Synchronizing Cookbooks:
  - repmgr (0.1.0)
  - postgresql (0.1.0)
  - gitlab-ee (0.0.1)

...snip...

  * postgresql_fdw_user_mapping[gitlab_secondary] action create
    * postgresql_query[create mapping for gitlab_geo at gitlab_secondary] action run (skipped due to not_if)
    * postgresql_query[update mapping for gitlab_geo at gitlab_secondary] action run
      * execute[update mapping for gitlab_geo at gitlab_secondary (geo-postgresql)] action run
        [execute] ERROR:  option "password" not found
        [execute] ERROR:  option "password" not found
        [execute] ERROR:  option "password" not found

After

gitlabhq_geo_production=# alter user mapping for gitlab_geo server gitlab_secondary OPTIONS (DROP password);
ALTER USER MAPPING
gitlabhq_geo_production=# \deu+
               List of user mappings
      Server      | User name  |    FDW Options    
------------------+------------+-------------------
 gitlab_secondary | gitlab_geo | ("user" 'gitlab')
(1 row)

gitlabhq_geo_production=# \q
root@mike-test-geo2:~# gitlab-ctl reconfigure
Starting Chef Client, version 13.6.4
resolving cookbooks for run list: ["gitlab-ee"]
Synchronizing Cookbooks:
  - package (0.1.0)
  - consul (0.0.0)
  - postgresql (0.1.0)
  - registry (0.1.0)

...snip...

  * postgresql_fdw_user_mapping[gitlab_secondary] action create
    * postgresql_query[create mapping for gitlab_geo at gitlab_secondary] action run (skipped due to not_if)
    * postgresql_query[update mapping for gitlab_geo at gitlab_secondary] action run
      * execute[update mapping for gitlab_geo at gitlab_secondary (geo-postgresql)] action run
        [execute] ALTER USER MAPPING
        - execute /opt/gitlab/bin/gitlab-geo-psql -d gitlabhq_geo_production -c "ALTER USER MAPPING FOR gitlab_geo SERVER gitlab_secondary OPTIONS (SET user 'gitlab', ADD password 'mypassword');"
    
    * postgresql_query[grant usage on foreign server gitlab_secondary to gitlab_geo] action run (skipped due to not_if)

When password exists (existing behavior still works)

gitlabhq_geo_production=# alter user mapping for gitlab_geo server gitlab_secondary OPTIONS (SET password 'wrongpassword');
ALTER USER MAPPING
gitlabhq_geo_production=# \deu+
                            List of user mappings
      Server      | User name  |                 FDW Options                 
------------------+------------+---------------------------------------------
 gitlab_secondary | gitlab_geo | ("user" 'gitlab', password 'wrongpassword')
(1 row)

gitlabhq_geo_production=# \q
root@mike-test-geo2:~# gitlab-ctl reconfigure
Starting Chef Client, version 13.6.4
resolving cookbooks for run list: ["gitlab-ee"]
Synchronizing Cookbooks:
  - consul (0.0.0)
  - runit (0.14.2)
  - package (0.1.0)
  - gitlab-ee (0.0.1)

...snip...

  * postgresql_fdw_user_mapping[gitlab_secondary] action create
    * postgresql_query[create mapping for gitlab_geo at gitlab_secondary] action run (skipped due to not_if)
    * postgresql_query[update mapping for gitlab_geo at gitlab_secondary] action run
      * execute[update mapping for gitlab_geo at gitlab_secondary (geo-postgresql)] action run
        [execute] ALTER USER MAPPING
        - execute /opt/gitlab/bin/gitlab-geo-psql -d gitlabhq_geo_production -c "ALTER USER MAPPING FOR gitlab_geo SERVER gitlab_secondary OPTIONS (SET user 'gitlab', SET password 'mypassword');"
    
    * postgresql_query[grant usage on foreign server gitlab_secondary to gitlab_geo] action run (skipped due to not_if)
Edited by Michael Kozono

Merge request reports