Search Postgresql Archives

Re: Restricted access on DataBases

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxx <mailto:clavadetscher@xxxxxxxxxxxx>>:


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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux