Skip to content

support setting the gitlab-psql password in postgresql and populating it in the patroni configuration

Summary

Customer raised a ticket following an audit by their security/compliance folks. GitLab team members can read more in the ticket.

They need to switch to md5_auth_cidr_addresses and ensure all accounts have passwords set, including the gitlab-psql (AKA the database install user / OID 10)

Proposal

Ensure all accounts can be secured to reasonably common corporate compliance requirements, including setting gitlab-psql with password that has been generated by the customer - for example using a corporate password vault for non-human accounts.

Additionally, put the gitlab-psql password in the patroni configuration. This appears to be needed when md5_auth_cidr_addresses is selected. Currently only the username for superuser is provided.

postgresql:
  authentication:
    superuser:
      username: gitlab-psql
      password: ******
    replication:
      username: gitlab_replicator
      password: ******

operations not affected

A reasonable number of operations within a node seem to use other accounts (or the socket) and so the gitlab-psql password can be set/reset without affecting a patroni node. This includes

  • re-initializing a replica (PG11, Omnibus 13.12.15 (containers))
  • major version upgrade (Omnibus 13.12.15 (containers) from PG11 to PG12)

pg_rewind is affected

A replica recovery that uses pg_rewind requires the gitlab-psql password in /var/opt/gitlab/patroni/patroni.yaml. I established this by adding it in manually. Then, the replica was able to recover.

postgresql:
  authentication:
    superuser:
      username: gitlab-psql
      password: ******
    replication:
      username: gitlab_replicator
      password: ******

Having set the password on the primary node, I restarted a cluster members (docker containers as it happens) and on recovery, a new node was selected as leader. The original leader failed to recover:

errors from leader in patroni log 
2021-12-20_10:14:56.79572 2021-12-20 10:14:56,793 INFO: Lock owner: gitlab-testdbc10-patroni1; I am gitlab-testdbc10-patroni3
2021-12-20_10:14:56.82191 2021-12-20 10:14:56,818 INFO: running pg_rewind from gitlab-testdbc10-patroni1
2021-12-20_10:14:56.84355 2021-12-20 10:14:56,841 ERROR: Exception when working with leader
2021-12-20_10:14:56.84359 Traceback (most recent call last):
2021-12-20_10:14:56.84360   File "/opt/gitlab/embedded/lib/python3.7/site-packages/patroni/postgresql/rewind.py", line 66, in check_leader_is_not_in_recovery
2021-12-20_10:14:56.84360     with get_connection_cursor(connect_timeout=3, options='-c statement_timeout=2000', **kwargs) as cur:
2021-12-20_10:14:56.84361   File "/opt/gitlab/embedded/lib/python3.7/contextlib.py", line 112, in __enter__
2021-12-20_10:14:56.84361     return next(self.gen)
2021-12-20_10:14:56.84362   File "/opt/gitlab/embedded/lib/python3.7/site-packages/patroni/postgresql/connection.py", line 43, in get_connection_cursor
2021-12-20_10:14:56.84362     with psycopg2.connect(**kwargs) as conn:
2021-12-20_10:14:56.84363   File "/opt/gitlab/embedded/lib/python3.7/site-packages/psycopg2-2.8.6-py3.7-linux-x86_64.egg/psycopg2/__init__.py", line 127, in connect
2021-12-20_10:14:56.84363     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
2021-12-20_10:14:56.84364 psycopg2.OperationalError: fe_sendauth: no password supplied

The node was in a crash loop like this, retrying multiple times.

After added the gitlab-psql password to that node's patroni configuration:

pg_rewind executes 
2021-12-20_10:18:53.70187 
2021-12-20_10:18:53.71154 2021-12-20 10:18:53,709 INFO: Lock owner: gitlab-testdbc10-patroni1; I am gitlab-testdbc10-patroni3
2021-12-20_10:18:53.75086 2021-12-20 10:18:53,749 INFO: Local timeline=12 lsn=0/16000028
2021-12-20_10:18:53.78498 2021-12-20 10:18:53,783 INFO: master_timeline=13
2021-12-20_10:18:53.78501 2021-12-20 10:18:53,784 INFO: master: history=9   0/10CA4F80  no recovery target specified
2021-12-20_10:18:53.78502 10    0/111578A8  no recovery target specified
2021-12-20_10:18:53.78502 11    0/11689A60  no recovery target specified
2021-12-20_10:18:53.78504 12    0/15422EF0  no recovery target specified
2021-12-20_10:18:53.84004 2021-12-20 10:18:53,829 INFO: Deregister service postgresql-ha/gitlab-testdbc10-patroni3
2021-12-20_10:18:53.84007 2021-12-20 10:18:53,832 INFO: running pg_rewind from gitlab-testdbc10-patroni1
2021-12-20_10:18:53.84008 2021-12-20 10:18:53,835 INFO: Lock owner: gitlab-testdbc10-patroni1; I am gitlab-testdbc10-patroni3
2021-12-20_10:18:53.84009 2021-12-20 10:18:53,837 INFO: running pg_rewind from gitlab-testdbc10-patroni1 in progress
2021-12-20_10:18:53.86849 2021-12-20 10:18:53,864 INFO: running pg_rewind from dbname=postgres user=gitlab-psql host=172.18.10.111 port=5432
2021-12-20_10:18:56.70993 2021-12-20 10:18:56,709 INFO: pg_rewind exit code=0
2021-12-20_10:18:56.70998 2021-12-20 10:18:56,709 INFO:  stdout=servers diverged at WAL location 0/15422EF0 on timeline 12
2021-12-20_10:18:56.70999 rewinding from last common checkpoint at 0/15422E48 on timeline 12
2021-12-20_10:18:56.70999 Done!
2021-12-20_10:18:56.71000

References

Edited by Ben Prescott_