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
---------------------------------------------------------------------------------------------------------------------
Office (PK): +92.42.35717906 | Office (US): +1 (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.