Re: template1, createdb, schemas, and owners - Mailing list pgsql-general

From Rachel McConnell
Subject Re: template1, createdb, schemas, and owners
Date
Msg-id 40A1825F.9050800@enginegreen.com
Whole thread Raw
In response to template1, createdb, schemas, and owners  (CSN <cool_screen_name90001@yahoo.com>)
List pgsql-general
CSN wrote:
> I have two machines between which I exchange dumps a
> lot. On the first (Windows/cygwin), pgsql was set up
> with "Administrator" as the main superuser - who owns
> all schemas in template0 and template1. On the second
> machine (Linux), "postgres" is pgsql's main superuser.
> On whatever machines I do "createdb", the owner of the
> schemas in template0/1 is copied over to the schemas
> in the new database, even when specifying the owner
> parameter (shouldn't the owner of the database own all
> schemas in it?). This creates problems when dumping
> and importing between the machines. The "SET SESSION
> AUTHORIZATION 'Administrator';" causes errors when
> trying to import on the machine without user
> "Administrator".
>
> What's the best way to remedy the problems caused by
> the two different superusers? I've thought about
> trying to change all instances of "Administrator" to
> "postgres" on the first machine, but don't know how to
> go about it.
>
> TIA,
> CSN

If you haven't already, check out pg_dump's -O option.  This suppresses
all ownership data from the backup, so you'll never get any "SET SESSION
AUTHORIZATION..." lines at all.  My setup doesn't use schemas, though,
so I can't be sure there aren't any issues lurking there, but I can't
see why there would be.

Rachel


pgsql-general by date:

Previous
From: Kris Jurka
Date:
Subject: Re: LISTEN/NOTIFY with JDBC
Next
From: Tom Lane
Date:
Subject: Re: Type conversions and nulls