Skip to content

Gitlab backup cannot be restored with `pg_stat_statements` enabled

Summary

Backup of PostgreSQL DB with enabled pg_stat_extension can't be restored.

"gitlab-backup create" create dump which can't be restored by user "gitlab" on target Gitlab instance.

Problem in following strings which can be executed only by user with "surepuser" role in PostgreSQL:

DROP EXTENSION pg_stat_statements
CREATE EXTENSION pg_stat_statements

Steps to reproduce

  1. Enable pg_stat_statements extension on PostgreSQL
  2. Create backup gitlab-backup create STRATEGY=copy
  3. Try to restore backup from previous step to fresh Gitlab-omnibus(I've tried to restore backup to single node gitlab and to reference architecture installation) gitlab-backup restore force=yes

What is the current bug behavior?

Several errors occur during the restore process:
When extension disabled on target database
nohup gitlab-backup restore force=yes | tee restorelog &
Non tarred backup found in /var/opt/gitlab/backups, using that
2022-01-17 11:01:35 +0300 -- Cleaning the database ...
2022-01-17 11:01:35 +0300 -- done
2022-01-17 11:01:35 +0300 -- Restoring database ...
Restoring PostgreSQL database gitlabhq_production ... ERROR: must be owner of extension pg_trgm
ERROR: must be owner of extension btree_gist
ERROR: must be owner of extension btree_gist
ERROR: permission denied to create extension "pg_stat_statements"
HINT: Must be superuser to create this extension.
ERROR: extension "pg_stat_statements" does not exist
ERROR: must be owner of extension pg_trgm
......
There were errors in restoring the schema. This may cause
issues if this results in missing indexes, constraints, or
columns. Please record the errors above and contact GitLab
Support if you have questions:
https://about.gitlab.com/support/
rake aborted!
Errno::EBADF: Bad file descriptor @ io_fillbuf - fd:0 <STDIN>
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/task_helpers.rb:64:in `gets'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/task_helpers.rb:64:in `prompt'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/task_helpers.rb:29:in `ask_to_continue'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:153:in `block (4 levels) in <top (required)>'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:73:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Tasks: TOP => gitlab:backup:db:restore
(See full trace by running task with --trace)
------ BEGIN ERRORS -----
ERROR: permission denied to create extension "pg_stat_statements"
HINT: Must be superuser to create this extension.
------ END ERRORS -------
[DONE]

When DB extension enabled on target database

gitlab-backup restore force=yes
Non tarred backup found in /var/opt/gitlab/backups, using that
2022-01-18 14:52:32 +0300 -- Cleaning the database ...
rake aborted!
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR: must be owner of view pg_stat_statements
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:42:in `block (4 levels) in <top (required)>'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:41:in `each'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:41:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:71:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Caused by:
PG::InsufficientPrivilege: ERROR: must be owner of view pg_stat_statements
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:42:in `block (4 levels) in <top (required)>'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:41:in `each'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:41:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:71:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Tasks: TOP => gitlab:db:drop_tables
(See full trace by running task with --trace)

What is the expected correct behavior?

Backup successfully restored

Output of checks

Results of GitLab environment info

Expand for output related to GitLab environment info

System information
System:		Ubuntu 20.04
Proxy:		no
Current User:	git
Using RVM:	no
Ruby Version:	2.7.2p137
Gem Version:	3.1.4
Bundler Version:2.1.4
Rake Version:	13.0.3
Redis Version:	6.0.14
Git Version:	2.32.0
Sidekiq Version:5.2.9
Go Version:	go1.13.8 linux/amd64

GitLab information
Version:	14.1.1-ee
Revision:	f331f932688
Directory:	/opt/gitlab/embedded/service/gitlab-rails
DB Adapter:	PostgreSQL
DB Version:	12.6
URL:		https://gitlab.cloud-dev
HTTP Clone URL:	https://gitlab.cloud-dev/some-group/some-project.git
SSH Clone URL:	git@gitlab.cloud-dev:some-group/some-project.git
Elasticsearch:	no
Geo:		no
Using LDAP:	yes
Using Omniauth:	yes
Omniauth Providers:

GitLab Shell
Version:	13.19.0
Repository storage paths:
- default: 	/var/opt/gitlab/git-data/repositories
GitLab Shell path:		/opt/gitlab/embedded/service/gitlab-shell
Git:		/opt/gitlab/embedded/bin/git

Results of GitLab application Check

Expand for output related to the GitLab application check

Checking GitLab subtasks ... Checking GitLab Shell ... GitLab Shell: ... GitLab Shell version >= 13.19.0 ? ... OK (13.19.0) Running /opt/gitlab/embedded/service/gitlab-shell/bin/check Internal API available: OK Redis available via internal API: OK gitlab-shell self-check successful Checking GitLab Shell ... Finished Checking Gitaly ... Gitaly: ... default ... OK Checking Gitaly ... Finished Checking Sidekiq ... Sidekiq: ... Running? ... yes Number of Sidekiq processes (cluster/worker) ... 1/1 Checking Sidekiq ... Finished Checking Incoming Email ... Incoming Email: ... Reply by email is disabled in config/gitlab.yml Checking Incoming Email ... Finished Checking LDAP ... LDAP: ... Server: ldapmain LDAP authentication... Success LDAP users with access to your GitLab server (only showing the first 100 results) User output sanitized. Found 100 users of 100 limit. Checking LDAP ... Finished Checking GitLab App ... Git configured correctly? ... yes Database config exists? ... yes All migrations up? ... yes Database contains orphaned GroupMembers? ... no GitLab config exists? ... yes GitLab config up to date? ... yes Log directory writable? ... yes Tmp directory writable? ... yes Uploads directory exists? ... yes Uploads directory has correct permissions? ... yes Uploads directory tmp has correct permissions? ... skipped (no tmp uploads folder yet) Init script exists? ... skipped (omnibus-gitlab has no init script) Init script up-to-date? ... skipped (omnibus-gitlab has no init script) Projects have namespace: ... 2/1 ... yes 2/2 ... yes 2/3 ... yes 3/4 ... yes 2/5 ... yes 2/41 ... yes 2/42 ... yes 2/43 ... yes Redis version >= 5.0.0? ... yes Ruby version >= 2.7.2 ? ... yes (2.7.2) Git version >= 2.31.0 ? ... yes (2.32.0) Git user has default SSH configuration? ... yes Active users: ... 3 Is authorized keys file accessible? ... skipped (authorized keys not enabled) GitLab configured to store new projects in hashed storage? ... yes All projects are in hashed storage? ... yes Elasticsearch version 7.x (6.4 - 6.x deprecated to be removed in 13.8)? ... skipped (elasticsearch is disabled) Checking GitLab App ... Finished Checking GitLab subtasks ... Finished

Possible fixes

  1. Add superuser role to PostgreSQL user gitlab

or

  1. Drop strings CREATE/DROP EXTENSION ... from DB dump
Edited by Gabriel Mazetto