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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

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

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      16101
Logged by:          zeng
Email address:      [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

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

Tomas Vondra-4
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:      [hidden email]
>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


Reply | Threaded
Open this post in threaded view
|

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

唯一★

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?

 

Thanks

Zeng

 

Restore command and log:

pg_restore -h 127.0.0.1 -U postgres -d cmdb bk_1 -c -v

pg_restore: connecting to database for restore

pg_restore: dropping TRIGGER employees last_name_changes

pg_restore: dropping CONSTRAINT pgbench_tellers pgbench_tellers_pkey

pg_restore: dropping CONSTRAINT pgbench_branches pgbench_branches_pkey

pg_restore: dropping CONSTRAINT pgbench_accounts pgbench_accounts_pkey

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 3398; 2606 47961 CONSTRAINT pgbench_accounts pgbench_accounts_pkey cm

pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.pgbench_accounts" does not exist

    Command was: ALTER TABLE ONLY public.pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey;

 

pg_restore: dropping CONSTRAINT employee_audits employee_audits_pkey

pg_restore: dropping SEQUENCE SET employee_audits_id_seq

pg_restore: dropping TABLE DATA pgbench_tellers

pg_restore: dropping TABLE DATA pgbench_history

pg_restore: dropping TABLE DATA pgbench_branches

pg_restore: dropping TABLE DATA pgbench_accounts

pg_restore: dropping TABLE DATA employees

pg_restore: dropping TABLE DATA employee_audits

pg_restore: dropping DEFAULT employee_audits id

pg_restore: dropping TABLE pgbench_tellers

pg_restore: dropping TABLE pgbench_history

pg_restore: dropping TABLE pgbench_branches

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;

 

pg_restore: dropping TABLE employees

pg_restore: dropping SEQUENCE OWNED BY employee_audits_id_seq

pg_restore: dropping SEQUENCE employee_audits_id_seq

pg_restore: dropping TABLE employee_audits

pg_restore: dropping FUNCTION log_last_name_changes()

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"

pg_restore: creating FUNCTION "public.log_last_name_changes()"

pg_restore: creating TABLE "public.employee_audits"

pg_restore: creating SEQUENCE "public.employee_audits_id_seq"

pg_restore: creating SEQUENCE OWNED BY "public.employee_audits_id_seq"

pg_restore: creating TABLE "public.employees"

pg_restore: creating TABLE "public.pgbench_accounts"

pg_restore: creating TABLE "public.pgbench_branches"

pg_restore: creating TABLE "public.pgbench_history"

pg_restore: creating TABLE "public.pgbench_tellers"

pg_restore: creating DEFAULT "public.employee_audits id"

pg_restore: processing data for table "public.employee_audits"

pg_restore: processing data for table "public.employees"

pg_restore: processing data for table "public.pgbench_accounts"

pg_restore: processing data for table "public.pgbench_branches"

pg_restore: processing data for table "public.pgbench_history"

pg_restore: processing data for table "public.pgbench_tellers"

pg_restore: executing SEQUENCE SET employee_audits_id_seq

pg_restore: creating CONSTRAINT "public.employee_audits employee_audits_pkey"

pg_restore: creating CONSTRAINT "public.pgbench_accounts pgbench_accounts_pkey"

pg_restore: creating CONSTRAINT "public.pgbench_branches pgbench_branches_pkey"

pg_restore: creating CONSTRAINT "public.pgbench_tellers pgbench_tellers_pkey"

pg_restore: creating TRIGGER "public.employees last_name_changes"

WARNING: errors ignored on restore: 2

 

 

Check log:

sun:/backup1 # psql -h 127.0.0.1 -U cm -d cmdb             psql (10.9)

Type "help" for help.

 

cmdb=>

cmdb=>

cmdb=> show search_path;

   search_path

-----------------

 "$user", public

(1 row)

 

cmdb=>

cmdb=> \dt

Did not find any relations.

cmdb=>

cmdb=> \dt

Did not find any relations.

cmdb=>

cmdb=>\q

sun:/backup1 #

sun:/backup1 #

sun:/backup1 # psql -h 127.0.0.1 -U postgres -d cmdb

psql (10.9)

Type "help" for help.

 

cmdb=# \dt

             List of relations

 Schema |       Name       | Type  | Owner

--------+------------------+-------+-------

 public | employee_audits  | table | cm

 public | employees        | table | cm

 public | pgbench_accounts | table | cm

 public | pgbench_branches | table | cm

 public | pgbench_history  | table | cm

 public | pgbench_tellers  | table | cm

(6 rows)

 

cmdb=# show search_path;

   search_path

-----------------

 "$user", public

(1 row)

 

cmdb=#




------------------ 原始邮件 ------------------
发件人: "Tomas Vondra"<[hidden email]>;
发送时间: 2019年11月8日(星期五) 晚上7:13
收件人: "唯一★"<[hidden email]>;"pgsql-bugs"<[hidden email]>;
主题: Re: BUG #16101: tables in the DB is not available after pg_restore

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:      [hidden email]
>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
Reply | Threaded
Open this post in threaded view
|

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

Tomas Vondra-4
On Wed, Nov 13, 2019 at 03:02:14PM +0800, 唯一★ wrote:
>Hi,
>
>After restore the database via pg_restore,&nbsp; 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


Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by 唯一★
"=?gb18030?B?zqjSu6Hv?=" <[hidden email]> writes:
> After restore the database via pg_restore,&nbsp; 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


Reply | Threaded
Open this post in threaded view
|

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

Tomas Vondra-4
In reply to this post by Tomas Vondra-4
On Wed, Nov 13, 2019 at 10:43:20PM +0800, 唯一★ wrote:

>hi,&nbsp;
>
>
>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
>&nbsp;CREATE DATABASE cmdb OWNER cm WITH TEMPLATE = template0;
>&nbsp;GRANT ALL PRIVILEGES ON DATABASE cmdb to cm;
>
>
>
>2.pop the data to DB.
>&nbsp;pgbench -i -h 127.0.01 -U cm -s 20 -d cmdb
>
>
>3.use the pg_dump to backup the data.
>&nbsp;pg_dump -h 127.0.0.1 -U postgres -d cmdb -p 5432 -Fc -f bk_1
>4.take the restore via pg_restore.
>&nbsp;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


Reply | Threaded
Open this post in threaded view
|

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

唯一★

Hi,

 

Thanks for your support.

I saw you take the restore on new DB "cmdb2" and not original DB "cmdb" when taking the backup via pg_dump. My reproduce is use the same DB.

Furthermore, your commands does not specified the username, it will use the same username with environment. it does not reproduce it if use the same users.  

My reproduce is use supper user to take backup and restore. And connect to database with DB owner “cmto” check the tables.

I am sure that it takes the pg_dump and pg_restore on the same system and DB.

Could you reproduce it with the same steps?

 

Thanks

Zeng


------------------ 原始邮件 ------------------
发件人: "Tomas Vondra"<[hidden email]>;
发送时间: 2019年11月13日(星期三) 晚上11:18
收件人: "唯一★"<[hidden email]>;
抄送: "pgsql-bugs"<[hidden email]>;
主题: Re: 回复: 回复: BUG #16101: tables in the DB is not available after pg_restore

On Wed, Nov 13, 2019 at 10:43:20PM +0800, 唯一★ wrote:

>hi,&nbsp;
>
>
>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
>&nbsp;CREATE DATABASE cmdb OWNER cm WITH TEMPLATE = template0;
>&nbsp;GRANT ALL PRIVILEGES ON DATABASE cmdb to cm;
>
>
>
>2.pop the data to DB.
>&nbsp;pgbench -i -h 127.0.01 -U cm -s 20 -d cmdb
>
>
>3.use the pg_dump to backup the data.
>&nbsp;pg_dump -h 127.0.0.1 -U postgres -d cmdb -p 5432 -Fc -f bk_1
>4.take the restore via pg_restore.
>&nbsp;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
Reply | Threaded
Open this post in threaded view
|

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

唯一★
Hi, 

Do you have update for this issue?

Thanks
Zeng


------------------ 原始邮件 ------------------
发件人: "唯一★"<[hidden email]>;
发送时间: 2019年11月14日(星期四) 上午10:05
收件人: "Tomas Vondra"<[hidden email]>;
抄送: "pgsql-bugs"<[hidden email]>;
主题: 回复: 回复: 回复: BUG #16101: tables in the DB is not available after pg_restore

Hi,

 

Thanks for your support.

I saw you take the restore on new DB "cmdb2" and not original DB "cmdb" when taking the backup via pg_dump. My reproduce is use the same DB.

Furthermore, your commands does not specified the username, it will use the same username with environment. it does not reproduce it if use the same users.  

My reproduce is use supper user to take backup and restore. And connect to database with DB owner “cmto” check the tables.

I am sure that it takes the pg_dump and pg_restore on the same system and DB.

Could you reproduce it with the same steps?

 

Thanks

Zeng


------------------ 原始邮件 ------------------
发件人: "Tomas Vondra"<[hidden email]>;
发送时间: 2019年11月13日(星期三) 晚上11:18
收件人: "唯一★"<[hidden email]>;
抄送: "pgsql-bugs"<[hidden email]>;
主题: Re: 回复: 回复: BUG #16101: tables in the DB is not available after pg_restore

On Wed, Nov 13, 2019 at 10:43:20PM +0800, 唯一★ wrote:

>hi,&nbsp;
>
>
>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
>&nbsp;CREATE DATABASE cmdb OWNER cm WITH TEMPLATE = template0;
>&nbsp;GRANT ALL PRIVILEGES ON DATABASE cmdb to cm;
>
>
>
>2.pop the data to DB.
>&nbsp;pgbench -i -h 127.0.01 -U cm -s 20 -d cmdb
>
>
>3.use the pg_dump to backup the data.
>&nbsp;pg_dump -h 127.0.0.1 -U postgres -d cmdb -p 5432 -Fc -f bk_1
>4.take the restore via pg_restore.
>&nbsp;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
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
"=?gb18030?B?zqjSu6Hv?=" <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Tomas Vondra-4
In reply to this post by 唯一★
On Sun, Nov 17, 2019 at 10:40:49PM +0800, 唯一★ wrote:
>Hi,&nbsp;
>
>
>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



Reply | Threaded
Open this post in threaded view
|

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

Tomas Vondra-4
In reply to this post by Tom Lane-2
On Sun, Nov 17, 2019 at 12:00:31PM -0500, Tom Lane wrote:

>"=?gb18030?B?zqjSu6Hv?=" <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by Tomas Vondra-4
Tomas Vondra <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Tomas Vondra-4
On Sun, Nov 17, 2019 at 02:06:33PM -0500, Tom Lane wrote:

>Tomas Vondra <[hidden email]> 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