Thread: Rights in 7.4

Recently switched to 7.4.1 and noticed that there are now several schemas
besides public. In addition even if I create the database with
AUTHORIZATION to a particular user the schemas are owned by the superuser.
This is a problem with restores. In particular with restoring sequences.
How does one now create a database and make sure that everything inside is
owned by a particular user?

For the public schema I just dropped it and then recreated and gave a
particular user ownership, but pg_catalog has many tables and I could not
do the same procedure.

The line from the sql file that is failing, a dump, is
SELECT pg_catalog.setval('invoicesdetails_invoicesdetailsid_seq', 18,
true);

Francisco Reyes <lists@natserv.com> writes:
> How does one now create a database and make sure that everything inside is
> owned by a particular user?

System objects are not, and should not be, owned by any nonprivileged
user.  I fail to see what your complaint is.

> The line from the sql file that is failing, a dump, is
> SELECT pg_catalog.setval('invoicesdetails_invoicesdetailsid_seq', 18,
> true);

You have not given us any context to interpret this report in, but
I do not think it has anything to do with the ownership of the public
schema.

            regards, tom lane

On Wed, 14 Jan 2004, Tom Lane wrote:

> > The line from the sql file that is failing, a dump, is
> > SELECT pg_catalog.setval('invoicesdetails_invoicesdetailsid_seq', 18,
> > true);
>
> You have not given us any context to interpret this report in, but
> I do not think it has anything to do with the ownership of the public
> schema.


I need to dump/restore frequently from one database/machine to other
database/machines.

I did the dump as a regular user and was trying to do the restore as the
same user, but to another DB. Both DBs are owned by the user I did the
dump with.

Should restores be always done by the super user?

Francisco Reyes <lists@natserv.com> writes:
> Should restores be always done by the super user?

If every object in the dump is owned by the same nonprivileged user,
it will work to restore as that user.  You can also use --no-owner
to get the same effect (everything winds up owned by the user running
restore).  If you are trying to copy a database that includes objects
of varying ownership, though, you gotta start as superuser.

Now that I think about it, the standard dump sequence will attempt to
set permissions and comment on the public schema, which will fail if
you're not superuser.  But it should be okay to ignore those errors.

            regards, tom lane

On Wed, 14 Jan 2004, Tom Lane wrote:

> Francisco Reyes <lists@natserv.com> writes:
> > Should restores be always done by the super user?
>
> If every object in the dump is owned by the same nonprivileged user,
> it will work to restore as that user.

They should all be owned by the user. I did the restore to the source DB
with that user.

>  You can also use --no-owner to get the same effect

That didn't help.
Before I wrote to the list I searched archived and tried the following
line options:
--no-privileges --no-owner --no-reconnect

> Now that I think about it, the standard dump sequence will attempt to
> set permissions and comment on the public schema, which will fail if
> you're not superuser.  But it should be okay to ignore those errors.

Before I tried the options above it also complained about an error to the
public schema, but that did not happen with the options above, however it
still had problems trying to set one sequence. I ended up doing that one
sequence with the superuser. Perhaps somehow I created that one sequence
with the superuser, but shouldn't the options above fix that case?