Thread: 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 denied
>
> Any ideas?

Are you doing the restore as a regular user?  Output of pg_dumpall is
designed to be restored by the superuser.

-Doug

I'm restoring it as the superuser.

You can try to execute the commands directly from psql.

Denis

Doug McNaught wrote:
> 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 denied
>>
>>Any 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