pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16243: non super user take pg_restore found some errors.
2+ messages / 2 participants
[nested] [flat]
* BUG #16243: non super user take pg_restore found some errors.
@ 2020-02-04 06:18 PG Bug reporting form <noreply@postgresql.org>
2020-02-04 16:27 ` Re: BUG #16243: non super user take pg_restore found some errors. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2020-02-04 06:18 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 270246512@qq.com
The following bug has been logged on the website:
Bug reference: 16243
Logged by: zeng
Email address: 270246512@qq.com
PostgreSQL version: 10.10
Operating system: suse
Description:
Hi,
When take the pg restore, both backup action and restore use non-super role
user “cm” which is the owner of the DB. During pg_restore, the log show some
errors such as "could not execute query: ERROR: must be owner of extension
plpgsql", "could not execute query: ERROR: must be owner of extension
plpgsql". I have some confusion. Are they correct behavior? If not, how to
avoid them? thanks in advance.
Thanks
zeng
Reproduce steps:
1. Create DB cm db
CREATE DATABASE cmdb OWNER cm WITH TEMPLATE = template0;
GRANT ALL PRIVILEGES ON DATABASE cmdb to cm;
2. Insert the data
pgbench -i -h 127.0.01 -U cm -s 20 -d cmdb
3. Take backup action for the cmdb.
pg_dump -h 127.0.0.1 -U cm -d cmdb -p 5432 -Fc -f bk_1
4. Take restore action for the cmdb.
pg_restore -h 127.0.0.1 -U cm -d cmdb bk_1 -c -v
log:
$:/var/lib/postgresql/data/bk # pg_restore -h 127.0.0.1 -U cm -d cmdb bk_1
-c -v
pg_restore: connecting to database for restore
Password:
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: 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 pgbench_tellers
pg_restore: dropping TABLE pgbench_history
pg_restore: dropping TABLE pgbench_branches
pg_restore: dropping TABLE pgbench_accounts
pg_restore: dropping COMMENT EXTENSION plpgsql
pg_restore: dropping EXTENSION plpgsql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1; 3079 13628 EXTENSION
plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner
of extension plpgsql
Command was: DROP EXTENSION plpgsql;
pg_restore: dropping COMMENT SCHEMA public
pg_restore: dropping SCHEMA public
pg_restore: [archiver (db)] Error from TOC entry 3; 2615 2200 SCHEMA public
postgres
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner
of schema public
Command was: DROP SCHEMA public;
pg_restore: creating SCHEMA "public"
pg_restore: [archiver (db)] could not execute query: ERROR: schema "public"
already exists
Command was: CREATE SCHEMA public;
pg_restore: creating COMMENT "SCHEMA public"
pg_restore: [archiver (db)] Error from TOC entry 3520; 0 0 COMMENT SCHEMA
public postgres
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner
of schema public
Command was: COMMENT ON SCHEMA public IS 'standard public schema';
pg_restore: creating EXTENSION "plpgsql"
pg_restore: creating COMMENT "EXTENSION plpgsql"
pg_restore: [archiver (db)] Error from TOC entry 3521; 0 0 COMMENT EXTENSION
plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner
of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural
language';
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: 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: 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"
WARNING: errors ignored on restore: 5
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #16243: non super user take pg_restore found some errors.
2020-02-04 06:18 BUG #16243: non super user take pg_restore found some errors. PG Bug reporting form <noreply@postgresql.org>
@ 2020-02-04 16:27 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2020-02-04 16:27 UTC (permalink / raw)
To: 270246512@qq.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> When take the pg restore, both backup action and restore use non-super role
> user “cm” which is the owner of the DB. During pg_restore, the log show some
> errors such as "could not execute query: ERROR: must be owner of extension
> plpgsql", "could not execute query: ERROR: must be owner of extension
> plpgsql". I have some confusion. Are they correct behavior? If not, how to
> avoid them? thanks in advance.
This is expected behavior in v10. In later versions (I forget if
this was changed in v11 or v12), we rearranged the duties of pg_dump
so that it won't try to drop and recreate the plpgsql extension.
That wasn't an entirely backwards-compatible change though, so
it wasn't back-patched.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-02-04 16:27 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-04 06:18 BUG #16243: non super user take pg_restore found some errors. PG Bug reporting form <noreply@postgresql.org>
2020-02-04 16:27 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox