Thread: database ownership and dumps

Hello.

We use PostgreSQL 7.1.3 on Debian GNU/Linux.

I would like to 1) create a database owned by a particular user but
without giving him/her the right to create databases (some users make
mistakes, or bad things, whatever), and 2) keep consistent backups.

I managed to do 1) by creating a database as a superuser, then
changing the ownership directly into the pg_database table. The user
owning the database is now able to create his/her
tables/views/whatever, in this database only. Fine.

But for 2), I noticed an inconsistency in the backup file, see
below. The user that is the datdba has the same name ('sthomas') :

...
--
-- Database sthomas
--
\connect template1 sthomas
CREATE DATABASE "sthomas" WITH TEMPLATE = template0 ENCODING = 'SQL_ASCII';
\connect sthomas sthomas

...

This will fail in the case of a database reconstruction (after a crash
for example), because this user is not authorized to create databases,
right ?

I think these lines should look like :

\connect template1 postgres
CREATE DATABASE "sthomas" WITH TEMPLATE = template0 ENCODING = 'SQL_ASCII';
UPDATE pg_database SET datdba = <usesysid-for-sthomas>
 WHERE datname = 'sthomas' ;
\connect sthomas sthomas


Am I wrong ? Couldn't this be the "standard" pg_dump(|all) behaviour ?

Nicolas.


PS : I can edit manually the dump files, so this isn't really
     important.

Nicolas Kowalski <Nicolas.Kowalski@imag.fr> writes:
> CREATE DATABASE "sthomas" WITH TEMPLATE = template0 ENCODING = 'SQL_ASCII';
> UPDATE pg_database SET datdba = <usesysid-for-sthomas>
>  WHERE datname = 'sthomas' ;

This requires that you run the script as a superuser with catupd
enabled.  Pretty ugly.

> Am I wrong ? Couldn't this be the "standard" pg_dump(|all) behaviour ?

There is a better solution in development sources.

            regards, tom lane