Dear Everybody!
I read the documentation based on your example. First reactions.
2016-09-05 18:25 GMT+02:00 Charles Clavadetscher <clavadetscher@xxxxxxxxxxxx>:
GRANT us_a, us_b, us_c TO main_admin;
Ah, it's good. I can merge the "owner" rights to one. :-)
It's like "doubling"! :-)
Here an example (obviously you will choose secure passwords and initialize them using \password <username>. This is just a very simple example). I used 9.5 but it would work with earlier versions as well.
-- Create roles and databases
CREATE ROLE main_admin LOGIN PASSWORD 'xxx';
CREATE ROLE us_a LOGIN PASSWORD 'xxx';
CREATE DATABASE db_a;
ALTER DATABASE db_a OWNER TO us_a;
CREATE ROLE us_b LOGIN PASSWORD 'xxx';
CREATE DATABASE db_b;
ALTER DATABASE db_b OWNER TO us_b;
-- Restrict access
REVOKE CONNECT ON DATABASE db_a FROM public;
GRANT CONNECT ON DATABASE db_a TO us_a;
REVOKE CONNECT ON DATABASE db_b FROM public;
GRANT CONNECT ON DATABASE db_b TO us_b;
-- Grant all user rights to main_admin:
GRANT us_a, us_b TO main_admin;
What could be the problem with the revoking only "connect" priv? What are/would be the silent side-effects?
For example:
Ok, us_b can't connect to db_a, but...
He can connect to db_b and may he can start(?) a multidatabase query...
He can set his role to bla, and he can insert the table db_a.X...
He can read the temp tables of db_a...
He can read the structure of db_a
He can break out from his sandbox by...???
---
Other question:
Can I imagine the GRANT as present, and the DEFAULT PRIVILEGES as future?
Your two solutions are seem to be better like "revoke public in all and grant all rights in all object in the present (GRANT) and in the future (DEF. PRIV)".
Very-very thank you!
dd
