Re: 回复： 回复： 回复： BUG #16101: tables in the DB is not available after pg_restore

On Sun, Nov 17, 2019 at 10:40:49PM +0800, 唯一★ wrote:
>Hi, 
>
>
>Do you have update for this issue?
>

I looked at it, and you're right - using the superuser to do the restore
allows me to reproduce the issue. And it seems very much to be related
to privileges on the public schema, as Tom speculated in his response.

What happens is that we *do not* include ACL for the public schema
unless when running in "clean" mode (i.e. when pg_dump is executed with
"-c" switch). This is the relevant comment from pg_dump.c:

     /*
      * When we are doing a 'clean' run, we will be dropping and recreating
      * the 'public' schema (the only object which has that kind of
      * treatment in the backend and which has an entry in pg_init_privs)
      * and therefore we should not consider any initial privileges in
      * pg_init_privs in that case.
      *
      * See pg_backup_archiver.c:_printTocEntry() for the details on why
      * the public schema is special in this regard.
      *
      * Note that if the public schema is dropped and re-created, this is
      * essentially a no-op because the new public schema won't have an
      * entry in pg_init_privs anyway, as the entry will be removed when
      * the public schema is dropped.
      *
      * Further, we have to handle the case where the public schema does
      * not exist at all.
      */
     if (dopt->outputClean)
         appendPQExpBuffer(query, " AND pip.objoid <> "
                           "coalesce((select oid from pg_namespace "
                           "where nspname = 'public'),0)");

So essentially what happens is that without the "-c" switch we end up
not dumping the ACL for the public schema (so the GRANT is forgotten),
then pg_restore gets executed with "-c" so it drops/recreates the
schema, but we don't have the ACLs because we haven't dumped them. So
the cm user ends up without access to the tables (which are properly
dumped and restored, the issue is just the ACLs).

So this gives you a simple workaround - you can use "-c" when runnig
pg_dump, and it should work. Alternatively, you can do GRANT on the
schema after the restore.

It's not clear to me if this is a bug or expected behavior, but it
certainly is confusing.

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services