Skip to content

GitLab

Closed
Created by Administrator@rootOwner

During restore of gitlab-7.4.3-omnibus: "ERROR: must be owner of extension plpgsql"

Closed

During restore of gitlab-7.4.3-omnibus: "ERROR: must be owner of extension plpgsql"

Created by: ghost

I'm attempting to migrate an Omnibus GitLab 7.4.3 hosted on CentOS6 to another CentOS6 machine, using backup and restore. The source instance runs "gitlab-rake gitlab:backup:create" every night, and I copied the resulting tarball to the freshly installed destination instance, where I then tried:

gitlab-rake gitlab:backup:restore BACKUP=1415689405

This results in one error, and several warnings. The error comes right at the start:

Unpacking backup ... done
Restoring database ...
Restoring PostgreSQL database gitlabhq_production ... SET
SET
SET
SET
SET
CREATE EXTENSION
psql:/var/opt/gitlab/backups/db/database.sql:22: ERROR:  must be owner of extension plpgsql

but this does not seem immediately fatal, it continues with printing database statements (why are these echo'd anyway?):

SET
CREATE SEQUENCE
ALTER TABLE
[...]

This goes on for some time, and after a while it prints these warnings:

[...]
ALTER TABLE
psql:/var/opt/gitlab/backups/db/database.sql:15761: WARNING:  no privileges could be revoked for "public"
REVOKE
psql:/var/opt/gitlab/backups/db/database.sql:15762: WARNING:  no privileges could be revoked for "public"
REVOKE
psql:/var/opt/gitlab/backups/db/database.sql:15763: WARNING:  no privileges were granted for "public"
GRANT
psql:/var/opt/gitlab/backups/db/database.sql:15764: WARNING:  no privileges were granted for "public"
GRANT
[DONE]

I saw something that looked like the "must be owner" error in #6067 (closed), but that issue appears to have been closed due to a timeout, without a real resolution.

Any idea whether the restored instance will now have a correct database? The line 22 about which the error is reported just has:

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

while lines 15761 through 15764 are:

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM "gitlab-psql";
GRANT ALL ON SCHEMA public TO "gitlab-psql";
GRANT ALL ON SCHEMA public TO PUBLIC;
  • Administrator
    Administrator @root ·
    Owner

    Created by: ghost

    The error about the plpgsql extension seems to be correct indeed, since the extension is created via /opt/gitlab/embedded/service/gitlab-rails/db/schema.rb, and I suppose the whole schema setup is run as the PostgreSQL superuser (e.g. gitlab-psql).

    If I run a restore operation from the created database dump as the gitlab-psql user, it works without any error or warning (note that I added the --clean option to pg_dump to ensure the tables are dropped before restoring):

    $ whoami
gitlab-psql
$ pg_dump -f test.sql --clean gitlabhq_production
$ ls -l test.sql
-rw-rw-r--. 1 gitlab-psql gitlab-psql 10257229 Nov 15 17:07 test.sql
$ psql -f test.sql gitlabhq_production
SET
SET
SET
SET
SET
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE
DROP EXTENSION
DROP SCHEMA
CREATE SCHEMA
ALTER SCHEMA
COMMENT
CREATE EXTENSION
COMMENT
SET
CREATE SEQUENCE
ALTER TABLE
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
 setval
--------
      1
(1 row)

 setval
--------
      3
(1 row)

 setval
--------
      2
(1 row)

 setval
--------
  14777
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
    188
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
   1935
(1 row)

 setval
--------
      8
(1 row)

 setval
--------
     10
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
     73
(1 row)

 setval
--------
    203
(1 row)

 setval
--------
    139
(1 row)

 setval
--------
      9
(1 row)

 setval
--------
     78
(1 row)

 setval
--------
      4
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
     68
(1 row)

 setval
--------
      2
(1 row)

 setval
--------
     58
(1 row)

ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT

    I think you can possibly argue that this is a PostgreSQL weirdness, in that non-superuser database users can dump their databases, but the dumps will contain statements modifying objects they don't own. The discussion here on the pgsql-general mailing list seems to indicate the extension should have been created by the gitlab user instead.

    Even then, the 'no privileges could be revoked for "public"' warnings will appear, but these appear to benign, as mentioned here on the pgsql-admin mailing list.

    I'm not sure whether it is easily possible to let the database backup/restore run as the PostgreSQL superuser (gitlab-psql) instead of the gitlab user, that would probably be the best solution.

    Alternatively, the following statements which always fail could be filtered out of the dump:

    COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM "gitlab-psql";
GRANT ALL ON SCHEMA public TO "gitlab-psql";
GRANT ALL ON SCHEMA public TO PUBLIC;

    I assume these comments and grant/revokes are unnecessary after the initial PostgreSQL setup which is done by Chef during gitlab-ctl reconfigure.

    By Administrator on 2014-11-15T16:22:08 (imported from GitLab project)

  • Administrator
    Administrator @root ·
    Owner

    Created by: phillxnet

    I have just had a near identical situation when restoring a backup made on one machine to another. The target machine had a fresh install of gitlab_7.5.1-omnibus.5.2.0.ci-1_amd64.deb (ie only one prior run of sudo gitlab-ctl reconfigure) on a fresh Ubuntu 14.04.1 server install:-

    Restoring database ... Restoring PostgreSQL database gitlabhq_production ... SET SET SET SET SET CREATE EXTENSION psql:/var/opt/gitlab/backups/db/database.sql:22: ERROR: must be owner of extension plpgsql SET SET SET CREATE TABLE

    and much further down:-

    CREATE INDEX CREATE INDEX psql:/var/opt/gitlab/backups/db/database.sql:2610: WARNING: no privileges could be revoked for "public" REVOKE psql:/var/opt/gitlab/backups/db/database.sql:2611: WARNING: no privileges could be revoked for "public" REVOKE psql:/var/opt/gitlab/backups/db/database.sql:2612: WARNING: no privileges were granted for "public" GRANT psql:/var/opt/gitlab/backups/db/database.sql:2613: WARNING: no privileges were granted for "public" GRANT [DONE] done Restoring repositories ... These all looked good [DONE]

    Can we assume this is a viable restore as the error is at least unsettling. I am less familiar with sql db than the OP but happy to help via specific instructions / more info if need be. Thanks to all who are involved in gitlab.

    By Administrator on 2014-11-28T20:43:33 (imported from GitLab project)

Please register or sign in to reply

Menu

Help