Обсуждение: Restricted access on DataBases

I think, it worth to try pg_hba.conf configuration[1].


 [1]. https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

Regards,
Amul Sul

On Mon, Sep 5, 2016 at 6:15 PM, Durumdara <durumdara@gmail.com> wrote:
> Dear PG-masters!
>
> We want to put more databases to one server, to "public" schema:
> DB_A, DB_B, DB_C.
> 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.
>
> Main_Admin can access all databases.
>
> 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.
>
> I can reown DB_A to US_A, but this revoke all rights from Main_Admin.
>
> 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...
>
> 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).
>
> As I think we can't mix the rights (Main_Admin = US_A + US_B  + US_C...).
>
> Thank you for the help. information, or an example!
>
>     DD
>

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

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...).
>
> Thank you for the help. information, or an example!
>
>     DD
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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

Hello,
On Mon, 2016-09-05 at 14:45 +0200, Durumdara wrote:
> Dear PG-masters!
>
> We want to put more databases to one server, to "public" schema:
> DB_A, DB_B, DB_C.
> 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.
>
> Main_Admin can access all databases.
>
> 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.
>
> I can reown DB_A to US_A, but this revoke all rights from Main_Admin.
>
> 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).
>

I'm not a pg_master.

Do you mean multiple databases or multiple schemas?
If you have multiple databases then main_admin would have to connect
and disconnect over and over in order to look at each database.

If you are setting this up for students to learn RDBMS skills, then
wouldn't you be better off with a single database instance containing
multiple schemas?

Create all the roles with the necessary privileges, then:-

CREATE SCHEMA db_a AUTHORIZATION us_a;

GRANT ALL ON ALL TABLES IN SCHEMA db_a TO main_admin;

Repeat as necessary for each schema/role combination.

Then main_admin would have to prefix all tables, sequences, etc. with
the schema name in order to run queries, etc.


HTH,
Rob

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

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

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

On 09/14/2016 06:52 AM, Durumdara wrote:
> Dear Charles!
>
> I checked your solution. For example:
> db - database
> dbuser, mainuser
>
> 1. dbuser own the database, and the objects in it.
> 2. mainuser member of dbuser.
> 3. public connection revoked.
>
> Ok.
>
> Then dbuser can see all tables, and mainuser too.
>
> Ok.
>
> The operation (overlord):
> 1. set role to mainuser (or login).
> 2. create table test_mainuser(id integer);
> 3. set role to dbuser (or login).
> 4. select * from test_mainuser;
>
> Result: Permission denied.
>
> Hmmm... the owner of test_mainuser is mainuser...
>
> Then I dropped the test_mainuser table.
>
> I tried to use default privileges. They are for future, so they must be
> affected on newly created table.
> I set them all.
>
>
> ALTER DEFAULT PRIVILEGES  GRANT INSERT, SELECT, UPDATE, DELETE,
> TRUNCATE, REFERENCES, TRIGGER ON TABLES to dbuser;
>
> I thought this makes all rights to the newly generated table.
>
> I do the test again, but I got same result.
>
> Why? What I do wrong? (Maybe only my mistake).
>
> I thought before this test that mainuser get all rights as dbuser, so it
> have rights to the next (future) objects too.
> So mainuser and dbuser have equivalent rights in db database.
>
> Thanks for your every info!

Without seeing the actual GRANT commands you issued it will be difficult
to sort out what the state of the permissions is.

FYI, in psql you can use \dp or \z to see the privileges on a table.
That information would also be helpful.

>
> Regards
> dd
>
>
>
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 09/14/2016 06:52 AM, Durumdara wrote:
> Dear Charles!
>

>
> I thought before this test that mainuser get all rights as dbuser, so it
> have rights to the next (future) objects too.
> So mainuser and dbuser have equivalent rights in db database.
>
> Thanks for your every info!

In my previous post I mentioned using \dp or \z. The output from those
commands can be hard to understand without a key, which I forgot to
mention. The key can be found here:

https://www.postgresql.org/docs/9.5/static/sql-grant.html

in the Notes section.

>
> Regards
> dd
>
>
>
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Durumdara wrote:
[...]
> --- login with postgres:
[...]
>     ALTER DEFAULT PRIVILEGES
>         GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
>         TO u_tr_db;
>
> ---- login with u_tr_main:
>
>     create table t_canyouseeme_1 (k int);
>
> ---- login with u_tr_db:
> 
>     select * from t_canyouseeme_1;
> 
>     ERROR: permission denied for relation t_canyouseeme_1
>     SQL state: 42501
> 
>  As you see before, u_tr_db got all default privileges on future tables, so I don't understand why he
> don't get to "t_canyouseeme_1".

You should have written

   ALTER DEFAULT PRIVILEGES FOR ROLE u_tr_main ...

The way you did it, you effectively wrote "FOR ROLE postgres" because
you were connected as that user.

Than means that all future tables created *by postgres* will have
privileges for user "u_tr_db" added.  But you want tables created
*by u_tr_main* to get the privileges.

Yours,
Laurenz Albe

Hello

> >     Also try this:
> >    ALTER DEFAULT PRIVILEGES FOR ex_mainuser GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
> >    ON TABLES TO ex_dbuser;
> >
> >    You execute the ALTER DEFAULT PRIVILEGES as su, so the grant applies to objects created by su and not
> >    ex_mainuser, unless you specify it with FOR ex_mainuser.
> >
>
> So... I repeated the test.
>
> --- login with postgres:
>
>     CREATE DATABASE db_testrole
>       WITH ENCODING='UTF8'
>            TEMPLATE=template0
>           CONNECTION LIMIT=-1;
>
>     CREATE ROLE u_tr_db LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
>
>
>     CREATE ROLE u_tr_main LOGIN
>       NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
>     GRANT u_tr_db TO u_tr_main;
>
>
>     ALTER DATABASE db_testrole
>       OWNER TO u_tr_db;
>
>     REVOKE ALL ON DATABASE db_testrole FROM public;
>     GRANT CREATE, TEMPORARY ON DATABASE db_testrole TO public;
>     GRANT ALL ON DATABASE db_testrole TO u_tr_db;
>
>     ALTER DEFAULT PRIVILEGES
>         GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
>         TO u_tr_db;

Here you are telling PostgreSQL to grant those privileges to u_tr_db on tables created by user postgres.

> ---- login with u_tr_main:
>
>     create table t_canyouseeme_1 (k int);
>
> ---- login with u_tr_db:
>
>     select * from t_canyouseeme_1;
>
>     ERROR: permission denied for relation t_canyouseeme_1
>     SQL state: 42501
>  As you see before, u_tr_db got all default privileges on future tables, so I don't understand why he don't get to
> "t_canyouseeme_1".

This is not correct. You issued the ALTER DEFAULT PRIVILEGES statement as user postgres. So u_tr_db is granted
privilegesonly on tables created by user postgres. Since you created the table as user u_tr_main the default privileges
don'tapply, because there are none defined. 

> If I try to use these things they would work:
>
>     A.)
>
>     ---- login with u_tr_main:
>
>         set role u_tr_db;
>
>         create table t_canyouseeme_2 (k int);
>
>     ---- login with u_tr_db:
>
>         select * from t_canyouseeme_2; -- OK!

Yes, because the owner of the table is u_tr_db. With set role user u_tr_main is impersonating user u_tr_db.

>     B.)
>
>     ---- login with su:
>
>
>         ALTER DEFAULT PRIVILEGES FOR role u_tr_main  GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE,
> REFERENCES, TRIGGER ON TABLES TO u_tr_db;

Here you are telling PostgreSQL to grant privileges on tables created by u_tr_main to u_tr_db.

>     ---- login with u_tr_main:
>
>         create table t_canyouseeme_3 (k int);
>
>     ---- login with u_tr_db:
>
>         select * from t_canyouseeme_3; -- OK!
>
>
> A.) is because I can set role to u_tr_db and then he is the creator, he get all rights.
> B.) I don't understand this statement... :-( :-( :-(
>
> So the main questions.
> Why the default privilege settings aren't affected on newly created table?
> See:
>
>     ALTER DEFAULT PRIVILEGES
>         GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
>         TO u_tr_db;

They do if the user creating the table is the user that issued the statement. In the case above postgres.

> What are the meaning of this statement if they won't usable for object created by another users?
> U_TR_DB is owner, so they have all privileges for next tables he will create.
> So I supposed that "default privileges" is for future objects created by different users.
> But this not works here.
>
> I don't understand case B.
> U_TR_MAIN gives all privileges to U_TR_DB for all newly created table?

Yes. You may also choose to restrict the privileges, instead of granting all of them.

> What are the differences between?
>
>     1.  ALTER DEFAULT PRIVILEGES
>         GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
>         TO u_tr_db;
>     2.  ALTER DEFAULT PRIVILEGES FOR role u_tr_main  GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES,
> TRIGGER ON TABLES TO u_tr_db;

In 1 the rule apply for tables created by the user that created the default privileges. Specifically the current_user
isthe one used for authorization checks. 
In 2 you say explicitly that the rule applies to tables created by user u_tr_main.

> Why the second works and first not?

They both work. In the first statement it works if you create tables as the user who was the current_user when you
issuedthe alter default privileges statement. In the second it works if you create a table as user u_tr_main. 

> ---
>
>
>     db_testrole-# \ddp
>                     Default access privileges
>        Owner   | Schema | Type  |      Access privileges
>     -----------+--------+-------+-----------------------------
>      postgres  |        | table | postgres=arwdDxt/postgres  +
>                |        |       | u_tr_db=arwdDxt/postgres
>      u_tr_main |        | table | u_tr_db=arwdDxt/u_tr_main  +
>                |        |       | u_tr_main=arwdDxt/u_tr_main
>     (2 rows)

Here you see in different form what I already mentioned above.
Bye
Charles

>
>     db_testrole-# \d
>                   List of relations
>      Schema |      Name       | Type  |   Owner
>     --------+-----------------+-------+-----------
>      public | t_canyouseeme_1 | table | u_tr_main
>      public | t_canyouseeme_2 | table | u_tr_db
>      public | t_canyouseeme_3 | table | u_tr_main
>     (3 rows)
>
>
> ---
>
>
>
> Thank you for your help!
>
> Best wishes
>    dd
>