Gitlab backup cannot be restored with `pg_stat_statements` enabled (#351071) · Issues · GitLab.org / GitLab

Gitlab backup cannot be restored with `pg_stat_statements` enabled

<details> <summary> Everyone can contribute. [Help move this issue forward](https://handbook.gitlab.com/handbook/marketing/developer-relations/contributor-success/community-contributors-workflows/#contributor-links) while earning points, leveling up and collecting rewards. </summary> - [Close this issue](https://contributors.gitlab.com/manage-issue?action=close&projectId=278964&issueIid=351071) </details>  ### 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 1. Create backup `gitlab-backup create STRATEGY=copy` 1. 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? <details> <summary>Several errors occur during the restore process:</summary> <pre> 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) ``` </pre> </details> ### What is the expected *correct* behavior? Backup successfully restored ### Output of checks  #### Results of GitLab environment info  <details> <summary>Expand for output related to GitLab environment info</summary> <pre> 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 </pre> </details> #### Results of GitLab application Check  <details> <summary>Expand for output related to the GitLab application check</summary> <pre> 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 </pre> </details> ### Possible fixes 1. Add superuser role to PostgreSQL user `gitlab` or 2. Drop strings `CREATE/DROP EXTENSION ...` from DB dump

issue