Thread: BUG #16101: tables in the DB is not available after pg_restore

The following bug has been logged on the website:

Bug reference:      16101
Logged by:          zeng
Email address:      270246512@qq.com
PostgreSQL version: 10.10
Operating system:   suse
Description:

hi, 
I have one question about pg_restore tool, tables in the DB is not available
after restore. Even if the user is still the owner of table after restore.
Steps:
1.    Create one database and the owner is user1. And create table and pop
the data with user1
2.    Use pg_dump tool to backup one database with superuser postgres.
3.    Use pg_restore tool to restore the database with the parameter –clean
and supper user.
4.    Connect the db with user1 and found the user does not select table as
below.
  cdb=> \dt
Did not find any relations.

On Fri, Nov 08, 2019 at 07:37:24AM +0000, PG Bug reporting form wrote:
>The following bug has been logged on the website:
>
>Bug reference:      16101
>Logged by:          zeng
>Email address:      270246512@qq.com
>PostgreSQL version: 10.10
>Operating system:   suse
>Description:
>
>hi,
>I have one question about pg_restore tool, tables in the DB is not available
>after restore. Even if the user is still the owner of table after restore.
>Steps:
>1.    Create one database and the owner is user1. And create table and pop
>the data with user1
>2.    Use pg_dump tool to backup one database with superuser postgres.
>3.    Use pg_restore tool to restore the database with the parameter –clean
>and supper user.
>4.    Connect the db with user1 and found the user does not select table as
>below.
>  cdb=> \dt
>Did not find any relations.
>

A wild guess - the objects are in a schema that is not listed in
search_path.

If that's not the case, you'll have to give us more details, and perhaps
a reproducer so that we can look more closely.

regards

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

On Wed, Nov 13, 2019 at 03:02:14PM +0800, 唯一★ wrote:
>Hi,
>
>After restore the database via pg_restore,  the search_path look well and is the same as before restore.
>
>Could you help check the issue?
>

How did you create the dump? Having a reproducer would be helpful.

regards

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

"=?gb18030?B?zqjSu6Hv?=" <270246512@qq.com> writes:
> After restore the database via pg_restore,  the search_path look well and is the same as before restore.

This looks like maybe your user "cm" lacks permissions on the public
schema?  If "select current_schemas(true)" doesn't show the public
schema as part of the active search path, that's probably your issue.
"\dn+" is another way to check.

            regards, tom lane

On Wed, Nov 13, 2019 at 10:43:20PM +0800, 唯一★ wrote:
>hi, 
>
>
>The restore command is "pg_dump -h 127.0.0.1 -U postgres -d cmdb -p 5432 -Fc -f bk_1"
>Here are reproduce steps.
>1. login in the postgres DB with super user postgres and create DB
> CREATE DATABASE cmdb OWNER cm WITH TEMPLATE = template0;
> GRANT ALL PRIVILEGES ON DATABASE cmdb to cm;
>
>
>
>2.pop the data to DB.
> pgbench -i -h 127.0.01 -U cm -s 20 -d cmdb
>
>
>3.use the pg_dump to backup the data.
> pg_dump -h 127.0.0.1 -U postgres -d cmdb -p 5432 -Fc -f bk_1
>4.take the restore via pg_restore.
> pg_restore -h 127.0.0.1 -U postgres -d cmdb bk_1 -c -v
>

Works just fine for me:

     [user@development ~]$ pg_ctl -D tmp/data init
     ...
     [user@development ~]$ pg_ctl -D tmp/data -l pg.log start
     waiting for server to start.... done
     server started
     [user@development ~]$ createdb cmdb
     [user@development ~]$ pgbench -i -s 1 cmdb
     dropping old tables...
     NOTICE:  table "pgbench_accounts" does not exist, skipping
     NOTICE:  table "pgbench_branches" does not exist, skipping
     NOTICE:  table "pgbench_history" does not exist, skipping
     NOTICE:  table "pgbench_tellers" does not exist, skipping
     creating tables...
     generating data...
     100000 of 100000 tuples (100%) done (elapsed 0.06 s, remaining 0.00 s)
     vacuuming...
     creating primary keys...
     done.
     [user@development ~]$ pg_dump -d cmdb -p 5432 -Fc -f bk_1
     [user@development ~]$ pg_restore -d cmdb2 bk_1 -c -v
     pg_restore: connecting to database for restore
     ...
     pg_restore: warning: errors ignored on restore: 7
     [user@development ~]$ psql cmdb2
     psql (12.1)
     Type "help" for help.

     cmdb2=# \d
                  List of relations
      Schema |       Name       | Type  | Owner
     --------+------------------+-------+-------
      public | pgbench_accounts | table | user
      public | pgbench_branches | table | user
      public | pgbench_history  | table | user
      public | pgbench_tellers  | table | user
     (4 rows)

Are you sure you're running the pg_restore on the right machine and not
on the same system where you did the pg_dump? Don't you have some
per-user search_path setting applied using ALTER USER?

regards

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

"=?gb18030?B?zqjSu6Hv?=" <270246512@qq.com> writes:
> Do you have update for this issue?

You've done nothing to convince anyone that this isn't local
misconfiguration or process error on your part.

In particular, I still like the theory I offered in

https://www.postgresql.org/message-id/5802.1573657223%40sss.pgh.pa.us

that the permissions on the public schema don't allow your
non-superuser role to access anything in that schema.

Looking closer at the "pg_restore -v" trace you posted in

https://www.postgresql.org/message-id/tencent_5865E10D689BCC05DFD0BC291ED869BEAA05%40qq.com

bolsters this theory, because I see

pg_restore: dropping COMMENT SCHEMA public
pg_restore: dropping SCHEMA public
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA public"

but there's never any later

pg_restore: creating ACL "SCHEMA public"

which there ought to be, and there is when I try to reproduce this.
That means the public schema is ending up with default permissions,
which grant no access to anyone but the owner.

Perhaps this happened because you did the dump or the restore
with -x (--no-privileges).  Or possibly that schema's privileges
were manually modified at some earlier point.

In any case, it's fairly hard to believe that you're giving us
a completely accurate statement of facts, because the restore
trace also includes errors like

pg_restore: dropping TABLE pgbench_accounts
pg_restore: [archiver (db)] Error from TOC entry 199; 1259 47945 TABLE pgbench_accounts cm
pg_restore: [archiver (db)] could not execute query: ERROR:  table "pgbench_accounts" does not exist
Command was: DROP TABLE public.pgbench_accounts;

It seems very unlikely that you could have gotten that if you
were restoring a dump you'd just created from the same database.
So there are additional moving parts here that you have not
mentioned.

            regards, tom lane

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

On Sun, Nov 17, 2019 at 12:00:31PM -0500, Tom Lane wrote:
>"=?gb18030?B?zqjSu6Hv?=" <270246512@qq.com> writes:
>> Do you have update for this issue?
>
>You've done nothing to convince anyone that this isn't local
>misconfiguration or process error on your part.
>
>In particular, I still like the theory I offered in
>
>https://www.postgresql.org/message-id/5802.1573657223%40sss.pgh.pa.us
>
>that the permissions on the public schema don't allow your
>non-superuser role to access anything in that schema.
>
>Looking closer at the "pg_restore -v" trace you posted in
>
>https://www.postgresql.org/message-id/tencent_5865E10D689BCC05DFD0BC291ED869BEAA05%40qq.com
>
>bolsters this theory, because I see
>
>pg_restore: dropping COMMENT SCHEMA public
>pg_restore: dropping SCHEMA public
>pg_restore: creating SCHEMA "public"
>pg_restore: creating COMMENT "SCHEMA public"
>
>but there's never any later
>
>pg_restore: creating ACL "SCHEMA public"
>
>which there ought to be, and there is when I try to reproduce this.
>That means the public schema is ending up with default permissions,
>which grant no access to anyone but the owner.
>
>Perhaps this happened because you did the dump or the restore
>with -x (--no-privileges).  Or possibly that schema's privileges
>were manually modified at some earlier point.
>

Not quite, what seems to make the difference is whether pg_dump was
executed with '-c' switch. Without the switch we end up without ACL.
Consider this:

     $ psql -U postgres -c "CREATE DATABASE cmdb WITH OWNER cm TEMPLATE = template0"
     $ psql -U postgres -c "GRANT ALL PRIVILEGES ON SCHEMA public to cm"

     $ pg_dump -U postgres -d cmdb -Fc -f cmdb.dump -v
     $ pg_restore -U postgres -d cmdb cmdb.dump -c -v
     pg_restore: connecting to database for restore
     pg_restore: dropping COMMENT EXTENSION plpgsql
     pg_restore: dropping EXTENSION plpgsql
     pg_restore: dropping COMMENT SCHEMA public
     pg_restore: dropping SCHEMA public
     pg_restore: creating SCHEMA "public"
     pg_restore: creating COMMENT "SCHEMA public"
     pg_restore: creating EXTENSION "plpgsql"
     pg_restore: creating COMMENT "EXTENSION plpgsql"

     $ dropdb cmdb
     $ psql -U postgres -c "CREATE DATABASE cmdb WITH OWNER cm TEMPLATE = template0"
     $ psql -U postgres -c "GRANT ALL PRIVILEGES ON SCHEMA public to cm"

     $ pg_dump -U postgres -d cmdb -Fc -c -f cmdb.dump -v
     $ pg_restore -U postgres -d cmdb cmdb.dump -c -v

     pg_restore: connecting to database for restore
     pg_restore: dropping COMMENT EXTENSION plpgsql
     pg_restore: dropping EXTENSION plpgsql
     pg_restore: dropping ACL SCHEMA public
     pg_restore: dropping COMMENT SCHEMA public
     pg_restore: dropping SCHEMA public
     pg_restore: creating SCHEMA "public"
     pg_restore: creating COMMENT "SCHEMA public"
     pg_restore: creating EXTENSION "plpgsql"
     pg_restore: creating COMMENT "EXTENSION plpgsql"
     pg_restore: creating ACL "SCHEMA public"


>In any case, it's fairly hard to believe that you're giving us
>a completely accurate statement of facts, because the restore
>trace also includes errors like
>
>pg_restore: dropping TABLE pgbench_accounts
>pg_restore: [archiver (db)] Error from TOC entry 199; 1259 47945 TABLE pgbench_accounts cm
>pg_restore: [archiver (db)] could not execute query: ERROR:  table "pgbench_accounts" does not exist
>Command was: DROP TABLE public.pgbench_accounts;
>
>It seems very unlikely that you could have gotten that if you
>were restoring a dump you'd just created from the same database.
>So there are additional moving parts here that you have not
>mentioned.
>

Yeah, there's a fair amount of noise in this thread :-(


regards

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

Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
> 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:

Note that that bit is new since v11; the OP is complaining about v10
which acted differently.

I did some simple experiments that suggest that things are fine in
this area as of v11+, but the reason we changed it is that it wasn't
fine before.  The most likely bet IMO is that the OP did a
dump/restore with -c -x at some point, and that lost the ACL for
the public schema, and now that state will be preserved by
additional dump/restore cycles with -c.  It'd be advisable to
drop the database and create it fresh instead of using -c.

            regards, tom lane

On Sun, Nov 17, 2019 at 02:06:33PM -0500, Tom Lane wrote:
>Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
>> 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:
>
>Note that that bit is new since v11; the OP is complaining about v10
>which acted differently.
>
>I did some simple experiments that suggest that things are fine in
>this area as of v11+, but the reason we changed it is that it wasn't
>fine before.  The most likely bet IMO is that the OP did a
>dump/restore with -c -x at some point, and that lost the ACL for
>the public schema, and now that state will be preserved by
>additional dump/restore cycles with -c.  It'd be advisable to
>drop the database and create it fresh instead of using -c.
>

All the experiments I did were done on 10.11. Same for the code.


regards

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