Skip to content

Allow the praefect user to connect to Pgbouncer

Anton Smith requested to merge docs/allow-praefect-user-to-connect-to-pgbouncer into master

What does this MR do?

The problem

When setting up Gitaly Cluster with a dedicated Pgbouncer node for the Praefect database and then configuring pool_mode = session, this does not work and the logs show confusing errors:

# Running a SQL ping from the Praefect node
$ sudo -u git /opt/gitlab/embedded/bin/praefect -config /var/opt/gitlab/praefect/config.toml sql-ping
sql open: pq: unexpected response from login query
# /var/log/gitlab/praefect/current
{"component":"HealthManager","error":"update checks: pq: unexpected response from login query","level":"error","msg":"checking health failed","pid":692,"time":"2022-05-03T04:57:42.145Z"}
# /var/log/gitlab/pgbouncer/current
2022-05-03_04:25:32.14990 2022-05-03 04:25:32.148 UTC [18462] WARNING C-0x55f6acfef7c0: praefect_production/(nouser)@10.128.20.4:39954 pooler error: unexpected response from login query
2022-05-03_04:25:32.16644 2022-05-03 04:25:32.166 UTC [18462] LOG S-0x55f6acffbca0: praefect_production/pgbouncer@10.128.20.3:5432 closing because: unexpected response from login query (age=0s)
# /var/log/gitlab/postgresql/current
2022-05-03_04:23:05.79637 LOG:  could not receive data from client: Connection reset by peer
2022-05-03_04:23:05.84274 ERROR:  function public.pg_shadow_lookup(unknown) does not exist at character 32
2022-05-03_04:23:05.84278 HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2022-05-03_04:23:05.84279 STATEMENT:  SELECT username, password FROM public.pg_shadow_lookup($1)

The reason this happens is that the Praefect node is attempting to connect to Pgbouncer using the praefect user, but the docs don't instruct users to add the praefect user for Pgbouncer (as described in the note in this section:

ℹ Omnibus GitLab handles the authentication requirements (using auth_query), but if you are preparing your databases manually and configuring an external PgBouncer, you must include praefect user and its password in the file used by PgBouncer. For example, userlist.txt if the auth_file configuration option is set. For more details, consult the PgBouncer documentation.

We can verify this is the case by checking the pg_auth file on the Pgbouncer node and the praefect user is missing:

$ sudo cat /var/opt/gitlab/pgbouncer/pg_auth
"pgbouncer" "PGBOUNCER_SQL_PASSWORD_HASH"

The fix

To resolve this error, we just need to add this to the gitlab.rb on the Pgbouncer node:

pgbouncer['users'] = {
  'praefect': {
    'password': PRAEFECT_SQL_PASSWORD_HASH,
  }
}

We can then verify that the praefect user has been added to Pgbouncer's auth file by checking /var/opt/gitlab/pgbouncer/pg_auth after reconfiguring GitLab:

$ sudo cat /var/opt/gitlab/pgbouncer/pg_auth
"pgbouncer" "PGBOUNCER_SQL_PASSWORD_HASH"
"praefect" "PRAEFECT_SQL_PASSWORD_HASH"

Related issues

Author's checklist

If you are a GitLab team member and only adding documentation, do not add any of the following labels:

  • ~"frontend"
  • ~"backend"
  • ~"type::bug"
  • ~"database"

These labels cause the MR to be added to code verification QA issues.

Reviewer's checklist

Documentation-related MRs should be reviewed by a Technical Writer for a non-blocking review, based on Documentation Guidelines and the Style Guide.

  • If the content requires it, ensure the information is reviewed by a subject matter expert.
  • Technical writer review items:
    • Ensure docs metadata is present and up-to-date.
    • Ensure the appropriate labels are added to this MR.
    • Ensure a release milestone is set.
    • If relevant to this MR, ensure content topic type principles are in use, including:
      • The headings should be something you'd do a Google search for. Instead of Default behavior, say something like Default behavior when you close an issue.
      • The headings (other than the page title) should be active. Instead of Configuring GDK, say something like Configure GDK.
      • Any task steps should be written as a numbered list.
      • If the content still needs to be edited for topic types, you can create a follow-up issue with the docs-technical-debt label.
  • Review by assigned maintainer, who can always request/require the reviews above. Maintainer's review can occur before or after a technical writer review.

Merge request reports