Обсуждение: restore database with schema: owner schema must have superuser privileges?

Greetings,

I have a database which has a schema owned by a user (with no superuser
privileges). When restoring the dump file with my superuser database,
the dump script connects with my owner user (which I created before),
and tries to create the schema with that user, and of course it cannot
do it, because my owner user hasn't super user privileges that permits
create a schema!

So, my question is, should the procedure to give temporary privileges of
super user to the user that will be the owner of schema is a right
procedure, or should the dump script provide to that operation be done
with my super user database and only after that it would connect with
the owner user?

thanks,
Pedro Salazar.
--
PS
pedro-b-salazar@ptinovacao.pt
PGP:0E129E31D803BC61

Pedro Salazar <pedro-b-salazar@ptinovacao.pt> writes:
> I have a database which has a schema owned by a user (with no superuser
> privileges). When restoring the dump file with my superuser database,
> the dump script connects with my owner user (which I created before),
> and tries to create the schema with that user, and of course it cannot
> do it, because my owner user hasn't super user privileges that permits
> create a schema!

You don't need superuser privs to create a schema, only CREATE privs on
the database.  Why would you have revoked CREATE-schema privs from the
database owner??  Seems downright weird.

pg_dump does have some known problems with situations where a
previously-held right has been revoked.  I guess this is another one for
the to-do list.

            regards, tom lane

On Mon, 2003-04-21 at 14:50, Tom Lane wrote:
> Pedro Salazar <pedro-b-salazar@ptinovacao.pt> writes:
> > I have a database which has a schema owned by a user (with no superuser
> > privileges). When restoring the dump file with my superuser database,
> > the dump script connects with my owner user (which I created before),
> > and tries to create the schema with that user, and of course it cannot
> > do it, because my owner user hasn't super user privileges that permits
> > create a schema!
>
> You don't need superuser privs to create a schema, only CREATE privs on
> the database.  Why would you have revoked CREATE-schema privs from the
> database owner??  Seems downright weird.
>
> pg_dump does have some known problems with situations where a
> previously-held right has been revoked.  I guess this is another one for
> the to-do list.

You're right. Indeed, is enough the CREATE privileges in that schema and
not a superuser privilege. However, it wasn't a issue of revoking CREATE
SCHEMA privilege to the owner of the database, but a issue of the owner
of schema isn't the owner of the database.

Thanks for your help,
PS