Hi,

*Problem Summary:*
I'm facing changing ownership issue for a DB and all its DB objects (DB,
schema, tables, sequences etc ownership).

*Experience So Far:*
As per my knowledge and experience every DB being created use a DB template
at the time of creation (default to "template1"). If "WITH OWNER" is not
mentioned in the CREATE DATABASE command then ownership of all the template
objects e.g. schema, tables etc is also inherited to the new DB but if
"WITH OWNER" option is defined then only the DB ownership is changed to the
new user but not rest of the objects like schema, tables etc which means
these db objects would always be in the ownership of the user who has
ownership of the template db objects.

*Problem Explanation:*
I'm implementing a secure multi tenancy system in a way that each
subscriber/client would have his own corresponding DB and a DB user. At the
time of subscription I would have a pre-installed "template_db" with
ownership of "sys_admin" user (not "SUPERUSER") and when creating a copy of
this template the newly created db e.g. "delta_db_1" should have
"delta_user_1" as owner and not only DB but all its objects like Schema,
tables, sequences etc. should have owner as "delta_user_1".

Please note that my "template0" is in the ownership of "postgres" user and
so all the objects of "template_db" were in ownership of "postgres"
initially so for "template_db" I changed the ownership of all its objects
to "sys_admin" by-hand (manually) but, of course, I can't do that manual
work for each DB being created on run-time subscription.

Please help me. I would prefer to give ownership of the new DB to the new
user in one simple sql statement at the time of DB creation. Thanks.

-- 
Best Regards,

Billal Mahmood

Pr. Software Engineer

*Email*: billal.mahm...@rolustech.com

*Mobile*: +92.321.4193915 <%2B92.345.4253072> | *Skype*:
rolustech.billal.mahmood

---------------------------------------------------------------------------------------------------------------------

*Office (PK)*: +92.42.35717906 | *Office (US)*: +1 (310) 492-5564
<%2B1.310.492.5564>

Rolustech Dynamic IT Solutions

*Disclaimer:*
This e-mail is confidential and may also be legally privileged. If you are
not the addressee you may not copy, forward, disclose or use any part of
it. If you have received this message in error, please delete it and all
copies from your system and notify the sender immediately by return e-mail.
Neither Rolustech nor any of its affiliates shall be held liable for the
e-mail if altered or falsified nor shall they be liable for any damage
caused by any virus that might be transmitted with this e-mail.

Reply via email to