Re: Restricted access on DataBases

Dear Everybody!

I'm sorry because lack of answer - I try to do it now.

2016-09-05 16:19 GMT+02:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 09/05/2016 05:45 AM, Durumdara wrote:
>
>> Dear PG-masters!
>>
>> 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.

I will read your answers.

Very-very thank you.

dd