Hello,
On Mon, 2016-09-05 at 14:45 +0200, Durumdara wrote:
> Dear PG-masters!
>
> We want to put more databases to one server, to "public" schema:
> DB_A, DB_B, DB_C.
> 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.
>
> I'm not sure how to do it perfectly.
> We tried to remove "public" role, and add US_A to DB_A.
> But the subobjects (table named "teszt") aren't accessable.
>
> I can reown DB_A to US_A, but this revoke all rights from Main_Admin.
>
> What is the simple way to we can avoid the access from another users,
> but give needed rights to DB_[n] and Main_Admin? (Tables, Sequences,
> etc).
>
I'm not a pg_master.
Do you mean multiple databases or multiple schemas?
If you have multiple databases then main_admin would have to connect
and disconnect over and over in order to look at each database.
If you are setting this up for students to learn RDBMS skills, then
wouldn't you be better off with a single database instance containing
multiple schemas?
Create all the roles with the necessary privileges, then:-
CREATE SCHEMA db_a AUTHORIZATION us_a;
GRANT ALL ON ALL TABLES IN SCHEMA db_a TO main_admin;
Repeat as necessary for each schema/role combination.
Then main_admin would have to prefix all tables, sequences, etc. with
the schema name in order to run queries, etc.
HTH,
Rob