1

I have created a "custom" dump on one PostgreSQL 10.7 server and am restoring it on another PostgreSQL 10.7 server. Both on Linux.

This is all very standard:

pg_restore --single-transaction --clean --dbname=confluence \
  backups/uploads/confluence_2019-05-02T13\:00\:14.custom

No error messages.

Prior to the restore, the database confluence already existed on the target PostgreSQL server

The access rights to the public schema of the confluence database are as follows:

confluence=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
(1 row)

After the restore, the owner of the confluence database can no longer read its tables. The reason is that the access rights have been modified:

confluence=# \dn+
                        List of schemas
  Name  |  Owner   | Access privileges |      Description       
--------+----------+-------------------+------------------------
 public | postgres |                   | standard public schema
(1 row)

So I have to issue these commands:

confluence=# GRANT USAGE,CREATE ON SCHEMA public TO postgres;
confluence=# GRANT USAGE,CREATE ON SCHEMA public TO PUBLIC;

to get back on track.

What could be the reason for deletion of access privileges?

Update

I have not been able to reproduce the problem. From the Unix account postgres, restoring a dump using either

pg_restore --create --clean --verbose \
/var/lib/pgsql/backups/confluence_2019-05-04T09\:49\:01.custom

or

pg_restore --single-transaction --clean --verbose \
/var/lib/pgsql/backups/confluence_2019-05-04T09\:49\:01.custom

In both cases, the connection from Unix account postgres is passwordless and the restore goes into the database mentioned in the dump, as opposed to the database that would be given on the command line. In the first case, the database is dropped and created, too.

In both cases, the access rights to the public schema are correct after a restore.

  • Do you see a corresponding REVOKE in the output of pg_restore --clean backups/uploads/confluence_2019-05-02T13\:00\:14.custom | grep REVOKE? – dezso May 2 at 14:37
  • @dezso After testing this on AWS ...no! I cannot reproduce it either. I know it happened twice, but now, everything works fine. – David Tonhofer May 4 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.