Skip to content

Geo: FDW user mapping alter user fails if it was created without a password

Summary

If the FDW user mapping has been created without a password, such as pre-10.6, the alter user command will fail when a password is supplied.

Steps to reproduce

  1. Create FDW user mapping without a password
  2. Verify no password is set in the db console with \deu+
  3. Set geo_postgresql['fdw_external_password'] or gitlab_rails['db_password']
  4. Reconfigure

What is the current bug behavior?

The reconfigure will fail with [execute] ERROR: option "password" not found. In order to get the reconfigure to pass, we had to run ALTER USER MAPPING FOR gitlab_geo SERVER gitlab_secondary OPTIONS (ADD user 'gitlab', ADD password '');. We had to manually ADD the password before the command with SET would run.

What is the expected correct behavior?

The reconfigure should run and not error out on the alter user command when updating the FDW user mapping with a password.

Links

Customer ticket -> https://gitlab.zendesk.com/agent/tickets/102822 (internal use)

We saw this on 10.6.6 after they upgraded from 10.5.7. They are running Geo HA with the secondary read-only database and the tracking database on separate nodes.