Create new database using custom template and changing ownership of tables

user_868941 0
Viewed 97

I have a custom template database "template_custom", containing some tables, owned by role "suser".

Now i need a new database for role "tenant1:

CREATE DATABASE db_tenant1 TEMPLATE template_custom OWNER tenant1;

The tables (and sequences) in database template_custom are still owned by suser. So:

\c db_tenant1 suser
REASSIGN OWNED BY suser TO tenant1;

Now, for database db_tenant1 everything is fine. However, the template database "template_custom" is now owned by tenant1, so are all tables. This is of course not what i want (and neither expect). So what is the right way of doing this?

(using PostgreSQL 11.9)

postgresql
1 Answers

That's unfortunate, but there is no way to avoid it.

I would work around the problem by changing the owner of the tables in template_custom to another user, for example suser2. Then the REASSIGNED OWNED won't affect the template database, which is still owned by suser.

user_64643080
Related