database ownership and dumps

From: Nicolas Kowalski <Nicolas(dot)Kowalski(at)imag(dot)fr>
To: pgsql-admin(at)postgresql(dot)org
Subject: database ownership and dumps
Date: 2002-07-25 17:25:24
Message-ID: vqo65z3oh2z.fsf@imag.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin


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.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2002-07-25 17:31:49 Re: OS File Size > 1GB
Previous Message Mathieu Arnold 2002-07-25 16:55:41 Re: CREATE TABLE AS ...