Re: Restricted access on DataBases

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;

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  |
|                       |
+-----------------------+