Re: Restricted access on DataBases

Hello

On 09/07/2016 03:24 PM, Durumdara wrote:
> Dear Everybody!
>
> I read the documentation based on your example. First reactions.
> 2016-09-05 18:25 GMT+02:00 Charles Clavadetscher
> <clavadetscher@swisspug.org <mailto:clavadetscher@swisspug.org>>:
>
>
>         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?

None.

Just before I go on answering your questions, a general statement from
my side. If I understood you correctly you have a set of customers that
each is owner of his database. Other customers are not supposed to look
into his data. But for maintenance reasons you have a main_admin user
that must have the same privileges as your various customers.

So we have distinct databases, not distinct schemas in a single database.

> 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't, how should he? In order to access other databases you would
need to set up foreign data wrappers and adjust the privileges on it.
But for your use case there is no need for it. Even if the community
would implement something like multidatabase query natively I would
expect the privileges on the database to hold on.

> He can set his role to bla, and he can insert the table db_a.X...

If you are speaking still of us_b, well he can't. A user can only set a
role he belongs to (see documentation). Now if you have a user bla that
has access to db_a and you granted that role to us_b, well, yes he can,
but this is your responsibility. PostgreSQL does not protect you from
doing security design errors.

> He can read the temp tables of db_a...

Well, a temp table is usually created within a transaction, so no other
users have access to them anyway. Besides they are created in the
owner's database, so without connect, no way.

> He can read the structure of db_a

No. User us_b has access to the structure of db_b not db_a. This is
defined on a database level and not global (like, e.g. roles).

> He can break out from his sandbox by...???

AFAIK he can't. But maybe some other specialist will be able to build an
attack vector to it.

So far, I would say that you are on a pretty sound ground and that is
due to a very clean implementation from the community.

Charles


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

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