Re: Restricted access on DataBases - Mailing list pgsql-general

GRANT us_a, us_b, us_c TO main_admin;

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;