Обсуждение: table owner of cloned databases

Hi all,

I would like to create a database based upon a template which also
contains some tables. After invoking

psql -T <templateName> -U <dbuser> -O <dbowner> test

the new database 'test' belongs to <dbowner> but all tables are still
belonging to the user who owns the template <templateName>. This results
in the following error message when accessing the tables:

ERROR:  permission denied for relation <some table>

Of course, I could alter the permissions in way that <dbowner> has
access to all tables. But it would be much nicer, if the owner of the
cloned database were inherited to all tables. Is there a way to achieve
this?

Thanks in advance,
Stefan.

On Mon, 2006-03-27 at 11:04, Stefan Fink wrote:
> Hi all,
>
> I would like to create a database based upon a template which also
> contains some tables. After invoking
>
> psql -T <templateName> -U <dbuser> -O <dbowner> test
>
> the new database 'test' belongs to <dbowner> but all tables are still
> belonging to the user who owns the template <templateName>. This results
> in the following error message when accessing the tables:
>
> ERROR:  permission denied for relation <some table>
>
> Of course, I could alter the permissions in way that <dbowner> has
> access to all tables. But it would be much nicer, if the owner of the
> cloned database were inherited to all tables. Is there a way to achieve
> this?

The easy way to do this is to dump the source database with the -O
switch, which turns off the generation of the sql that sets owner on the
objects created during restore.

If you can't get a fresh dump, for whatever reason, then you'll have to
either hand edit the dump to remove those lines or write a one line sed
script to remove them on the fly.

Scott Marlowe wrote:
> On Mon, 2006-03-27 at 11:04, Stefan Fink wrote:
>
>>Hi all,
>>
>>I would like to create a database based upon a template which also
>>contains some tables. After invoking
>>
>>psql -T <templateName> -U <dbuser> -O <dbowner> test
>>
>>the new database 'test' belongs to <dbowner> but all tables are still
>>belonging to the user who owns the template <templateName>. This results
>>in the following error message when accessing the tables:
>>
>>ERROR:  permission denied for relation <some table>
>>
>>Of course, I could alter the permissions in way that <dbowner> has
>>access to all tables. But it would be much nicer, if the owner of the
>>cloned database were inherited to all tables. Is there a way to achieve
>>this?
>
>
> The easy way to do this is to dump the source database with the -O
> switch, which turns off the generation of the sql that sets owner on the
> objects created during restore.
>
> If you can't get a fresh dump, for whatever reason, then you'll have to
> either hand edit the dump to remove those lines or write a one line sed
> script to remove them on the fly.

This makes usage of a template pointless at all - very unsatisfying.
I think the easiest way is to simply use a database defintion file and
to create every database by invoking

psql -U <dbuser> < <dbdefinition.sql>

This is exactly what I wanted to avoid :-(

Thanks anyway,
Stefan.