Geo: Verify correct user_mapping configuration for FDW using SystemChecks
Problem to solve
When Geo is used with a non-Omnibus PostgreSQL database, some FDW settings have to be done manually. If the installation is old enough, chances are that they used trust
authentication instead of md5
with a password. With the updated requirements, a password is necessary and FDW that previously worked will not anymore without a password.
Detecting this is not straightforward as FDW is not a wildly used feature, and the error messages don't help much.
Further details
For an example of the troubleshooting please read this thread in slack: https://gitlab.slack.com/archives/C32LCGC1H/p1536171163000100.
The error that may be displayed in the rails log is similar to this one:
ActiveRecord::StatementInvalid: PG::SqlclientUnableToEstablishSqlconnection: ERROR: could not connect to server "gitlab_secondary"
DETAIL: fe_sendauth: no password supplied
Proposal
We can check that FDW has a user_mapping, match the user against current user and verify that the password field is defined.
This is a simple snippet on how we can retrieve the data and what it looks like:
Geo::BaseFdw.connection.execute(“select * from pg_user_mappings;“).to_a
=> [{“umid”=>“16456", “srvid”=>“16455", “srvname”=>“gitlab_secondary”, “umuser”=>“16384", “usename”=>“gitlab_geo”, “umoptions”=>“{user=gitlab,password=mypassword}“}]
What does success look like, and how can we measure that?
We should get a check failere when running rake gitlab:geo:check
if pg_user_mappings doesn't include an entry for srvname => gitlab_secondary
or it's pointing to a usename
that is different than the current one in the connnection, or its missing the two values in the example above in the umoptions
.
Links / references
https://www.postgresql.org/docs/9.6/static/postgres-fdw.html