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

Hello

On 09/05/2016 05:56 PM, Charles Clavadetscher wrote:
> Hello
>
> On 09/05/2016 04:19 PM, Adrian Klaver wrote:
>> 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.
>>
>>> 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.
>
> When use speak of "their database", do you mean that they are the owner
> of it or that they simply should have specific privileges?
>
> If not, is main_admin the owner of all databases?
>
>>> Main_Admin can access all databases.
>>
>> Is Main_Admin created as a superuser?
>>
>> If not what role attributes does it have?
>>
>>>
>>> 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.
>>
>> How did you specify GRANTing permissions on DB_A to US_A?
>>
>> You might to want to look at the privileges that are provided to various
>> objects by GRANT:
>>
>> https://www.postgresql.org/docs/9.5/static/sql-grant.html
>
> Yes, read this document, it helps a lot.
>
> Pragmatically I find a simple way to restrict access to a database is to
> revoke CONNECT on it from public and then GRANT CONNECT and, if
> necessary, privileges on objects in that database to the legitimate
> user(s):
>
> REVOKE CONNECT ON DATABASE db_a FROM public;
> GRANT CONNECT ON DATABASE db_a TO us_a;
>
> This will still not free you from managing the privileges on the objects
> created. If main_admin is a superuser it will hav.e access to everything
> anyway and you don't need to manage grants for it. If not, as Adrian
> said, and assuming in db_a, only us_a will create objects, you will have
> to alter the default privileges of us_a to grant privileges to
> main_admin. This must be done for each database, i.e. db_b, db_c, etc.
>
>>
>> GRANT on Database Objects
>>
>> For instance
>>
>> CREATE
>>
>>     For databases, allows new schemas to be created within the database.
>>
>>
>>>
>>> I can reown DB_A to US_A, but this revoke all rights from Main_Admin.
>>
>> Hard to answer until we know what permissions Main_Admin has.
>>
>>>
>>> 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).
>>>
>>> And how we keep this state later? For example: DB_A creates a new table.
>>> Main_Admin must access this automatically...
>>
>> Defualt privileges:
>>
>> https://www.postgresql.org/docs/9.5/static/sql-alterdefaultprivileges.html
>>
>>
>>>
>>> I don't understand this area properly. For me the "public" means "access
>>> for all users", which isn't good (DB_A vs. US_C).
>>
>> Actually it is not as broad as that.
>>
>> https://www.postgresql.org/docs/9.5/static/sql-grant.html
>>
>> "PostgreSQL grants default privileges on some types of objects to
>> PUBLIC. No privileges are granted to PUBLIC by default on tables,
>> columns, schemas or tablespaces. For other types, the default privileges
>> granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for
>> databases; EXECUTE privilege for functions; and USAGE privilege for
>> languages. The object owner can, of course, REVOKE both default and
>> expressly granted privileges. (For maximum security, issue the REVOKE in
>> the same transaction that creates the object; then there is no window in
>> which another user can use the object.) Also, these initial default
>> privilege settings can be changed using the ALTER DEFAULT PRIVILEGES
>> command.
>>
>> "
>>>
>>> As I think we can't mix the rights (Main_Admin = US_A + US_B  +
>>> US_C...).
>
> Actually you could:
>
> 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;

Test:

-- Connect as us_a to db_a:

charles@charles.localhost=# \c db_a us_a
Password for user us_a:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
You are now connected to database "db_a" as user "us_a".

-- Create a table and enter some data:

us_a@db_a.localhost=> CREATE TABLE test (id INTEGER, tst TEXT);
CREATE TABLE
us_a@db_a.localhost=> INSERT INTO test VALUES (1,'Blabla');
INSERT 0 1

-- Try to connect as user us_b to db_a:

us_a@db_a.localhost=> \c db_a us_b
Password for user us_b:
FATAL:  permission denied for database "db_a"
DETAIL:  User does not have CONNECT privilege.
Previous connection kept

-- Connect as user main_admin to db_a:

us_a@db_a.localhost=> \c db_a main_admin
Password for user main_admin:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
You are now connected to database "db_a" as user "main_admin".

-- Make some stuff:

main_admin@db_a.localhost=> SELECT * FROM test;
  id |  tst
----+--------
   1 | Blabla
(1 row)

main_admin@db_a.localhost=> INSERT INTO test VALUES (2,'Blublu');
INSERT 0 1

-- Connect again as us_a:

main_admin@db_a.localhost=> \c db_a us_a
Password for user us_a:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
You are now connected to database "db_a" as user "us_a".

-- You see the changes done by main_admin:

us_a@db_a.localhost=> SELECT * FROM test;
  id |  tst
----+--------
   1 | Blabla
   2 | Blublu
(2 rows)

Bye
Charles

>
> Now, if you have time for it, I would suggest that you take it to read
> about the roles and privileges system in PostgreSQL. This will strongly
> help you understanding what you are doing.
>
> Charles
>
>>>
>>> Thank you for the help. information, or an example!
>>>
>>>     DD
>>>
>>
>>
>

--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+-----------------------+
|   ____  ______  ___   |
|  /    )/      \/   \  |
| (     / __    _\    ) |
|  \    (/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
|     \  /\_/    \)/    |
|      \/ <//|  |\\>    |
|           _|  |       |
|           \|_/        |
|                       |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|                       |
+-----------------------+