pgbouncer not accepting connections in staging

Staging has stopped working after an upgrade to 10.0 on the pgbouncer nodes with the error: Pooler Error: unexpected response from login query

after a long time of tracking down why the user accessing the db was not working, i discovered this:

gitlabhq_production=> SELECT username, password FROM public.pg_shadow_lookup('gitlab-psql');                                                                                                                         ERROR:  permission denied for function pg_shadow_lookup
gitlabhq_production=> ^D\q
root@pgbouncer-01.db.stg.gitlab.com:/var/opt/gitlab/pgbouncer# /opt/gitlab/embedded/bin/psql -U pgbouncer -p 5432 -d gitlabhq_production -h postgres01.db.stg.gitlab.com
Password for user pgbouncer:
psql (9.6.3)
Type "help" for help.

gitlabhq_production=> SELECT username, password FROM public.pg_shadow_lookup('gitlab-psql');
  username   | password
-------------+----------
 gitlab-psql |
(1 row)

gitlabhq_production=>

the user used by pgbouncer to connect to the db (gitlab-consul) was not able to execute the password lookup. Somewhere between 9.5 and 10.0 the user used to connect was changed from pgbouncer to gitlab-consul.

After manually changing it and informing @ibaum about the issue, pgbouncer started accepting connections again.

closed

related to https://gitlab.com/gitlab-org/omnibus-gitlab/issues/2786