Bug in pg_dumpall
I created a user with no superuser privileges:
CREATE USER aaa PASSWORD 'bbb' NOCREATEDB NOCREATEUSER;
Then i created an authorization schema:
CREATE SCHEMA AUTHORIZATION aaa;
All worked fine.
The problem is in the pg_dumpall file.
Looking into the generated sql, i find that the schema is created with
the command:
CREATE USER aaa PASSWORD 'bbb' NOCREATEDB NOCREATEUSER;
SET SESSION AUTHORIZATION aaa;
CREATE SCHEMA aaa;
This fails giving me the following error:
ERROR: aaaa: permission denied
Any ideas?
I'm using Postgresql 7.3.4.
--
Ing. Denis Gasparin: denis@edistar.com
---------------------------
Programmer & System Administrator - Edistar srl
Via dell'artigianato, 1
31050 Vedelago TV
Telefono: 0423-733209
Fax: 0423-733733
Internet: www.edistar.com
Denis Gasparin <denis@edistar.com> writes:
Looking into the generated sql, i find that the schema is created with
the command:CREATE USER aaa PASSWORD 'bbb' NOCREATEDB NOCREATEUSER;
SET SESSION AUTHORIZATION aaa;
CREATE SCHEMA aaa;This fails giving me the following error:
ERROR: aaaa: permission deniedAny ideas?
Are you doing the restore as a regular user? Output of pg_dumpall is
designed to be restored by the superuser.
-Doug
Import Notes
Reply to msg id not found: DenisGasparin'smessageofFri21Nov2003150259+0100
I'm restoring it as the superuser.
You can try to execute the commands directly from psql.
Denis
Doug McNaught wrote:
Show quoted text
Denis Gasparin <denis@edistar.com> writes:
Looking into the generated sql, i find that the schema is created with
the command:CREATE USER aaa PASSWORD 'bbb' NOCREATEDB NOCREATEUSER;
SET SESSION AUTHORIZATION aaa;
CREATE SCHEMA aaa;This fails giving me the following error:
ERROR: aaaa: permission deniedAny ideas?
Are you doing the restore as a regular user? Output of pg_dumpall is
designed to be restored by the superuser.-Doug
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Denis Gasparin <denis@edistar.com> writes:
CREATE USER aaa PASSWORD 'bbb' NOCREATEDB NOCREATEUSER;
CREATE SCHEMA AUTHORIZATION aaa;
All worked fine.
The problem is in the pg_dumpall file.
SET SESSION AUTHORIZATION aaa;
CREATE SCHEMA aaa;
This is fixed in 7.4 --- I get
CREATE SCHEMA aaa AUTHORIZATION aaa;
regards, tom lane