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.
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 ... |