Executing a backup with PgBouncer can cause a full site outage
ZD: https://gitlab.zendesk.com/agent/tickets/99873
Customer has an HA setup with PgBouncer. Upon running the backup Rake task, customer saw these types of errors:
ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR: relation "keys" does not exist
LINE 1: SELECT "keys".* FROM "keys" WHERE "keys"."fingerprint" = 'b...It turns out PostgreSQL 9.6.8 changed the behavior of pg_dump to set a null search path and explicitly include the schema in every SQL query to address CVE-2018-1058 (Uncontrolled search path element in pg_dump and other client applications). When Rails uses the connection, the SQL query fails because the public schema isn't in the search path.
The workaround is to bypass PgBouncer and conduct a backup directly.
Discourse worked around this by adding configuration options to allow the backup task to specify a separate database connection for backups. We should consider doing this too.
Related links:
- https://www.postgresql.org/about/news/1834/
- https://github.com/pgbouncer/pgbouncer/issues/246#issuecomment-383582053
- https://meta.discourse.org/t/changes-introduced-in-postgresql-9-5-12-and-10-3-cause-backup-related-failures/82555/3
- https://github.com/discourse/discourse/commit/a89f3160a53081adbd2c05c4b3b146414500248a
/cc: @collen, @dblessing, @yorickpeterse