Allow the praefect user to connect to Pgbouncer
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 (usingauth_query
), but if you are preparing your databases manually and configuring an external PgBouncer, you must includepraefect
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
-
Optional. Consider taking the GitLab Technical Writing Fundamentals course. -
Follow the: -
If you're adding or changing the main heading of the page (H1), ensure that the product tier badge is added. -
If you are a GitLab team member, request a review based on: - The documentation page's metadata.
- The associated Technical Writer.
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 likeDefault behavior when you close an issue
. -
The headings (other than the page title) should be active. Instead of Configuring GDK
, say something likeConfigure 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.