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.