We want to put more databases to one server, to "public" schema: DB_A, DB_B, DB_C.
The PUBLIC schema is contained within a database not the other way around, so further explanation is necessary.
Thank you, this is important information. I will read about it.
And users: US_A, US_B, US_C, and Main_Admin. We want to setup the environment. Every simple user can access his database: DB_A - US_A DB_B - US_B DB_C - US_C
They can't access other databases only theirs.
Main_Admin can access all databases.
Is Main_Admin created as a superuser?
It is not really su, but we want to use it as a "super user of these databases"
For example: there are a, b, c, d customers. Each of them have on database. All of them in one server.
We create different users for them to not see any data except theirs.
Main_Admin is a login role for us. We know each role's password, but we want to use our role to manage everything. It's not a superuser like postgres, but it can do everything what A + B + C + D can.
If I want to write this in pseudo code, I would do as:
1.) Double owning:
set owner on DB_A to US_A, Main_Admin
set owner on DB_B to US_B, Main_Admin
...
2.) Grant
Grant all rights to US_A on DB_A to all objects;
Grant all rights to Main_Admin on DB_A to all objects;
Grant all rights to US_B on DB_B to all objects;
Grant all rights to Main_Admin on DB_B to all objects;
The owner got right to every object in the database. To the future objects too.
"Double owning", or "All rights in database and contained objects" is good for us to Main_Admin can access everything on every databases.
Like superuser, but without rights to get out from these databases.
Just like sandboxes. US_A have sandbox DB_A. US_B have sandbox DB_B.
Main_Admin have sandboxes (A + B + C + D), but can't do any problem on other databases.