Re: Restricted access on DataBases

Hello

I did oversee the additional questions...

On 09/07/2016 06:45 PM, Charles Clavadetscher wrote:
> 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?

It depends. From the requirements that you submitted, you don't need to
alter default privileges. Your single user, let's say us_a, can create
objects, including schemas and then tables in that schema within db_a
and your main_admin will be able to access them via the grant of role
us_a to him.

>> 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)".

No. You need to change the default privileges in other scenarios. If the
explanations so far don't match your requirements, I may have
misunderstood what you are trying to achieve. In that case send please a
more clarifying use case.

Charles

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