Обсуждение: Prevent roles not having admin option from granting themselves to other roles

Good morning

I am investigating the authorization possiblities of PostgreSQL and I
stumbled on a case, whose rationale I could not find in any resource online.
For that reason I post my question here.

First of all let me state that the software is acting accordingly to the
documentation. The passage I'd like to ask about is in the notes to the
GRANT command and it goes es follows:

"If WITH ADMIN OPTION is specified, the member can in turn grant membership
in the role to others, and revoke membership in the role as well. Without
the admin option, ordinary users cannot do that. A role is not considered to
hold WITH ADMIN OPTION on itself, but it may grant or revoke membership in
itself from a database session where the session user matches the role.
Database superusers can grant or revoke membership in any role to anyone.
Roles having CREATEROLE privilege can grant or revoke membership in any role
that is not a superuser."

An ordinary user is therefore able to grant itself to other users in a
database session where the session user matches the role. Actually the
normal case, if am not completely wrong.
My question is: What is the point of preventing a role to grant specific
privileges it has, but allow it to grant all privileges at once, granting
itself as a role?

Now consider this case to illustrate what I mean:

Create a test environment:
charles@admin.localhost=# CREATE ROLE user1 LOGIN PASSWORD 'xxx';
charles@admin.localhost=# CREATE ROLE user2 LOGIN PASSWORD 'xxx';
charles@admin.localhost=# CREATE DATABASE test;

Login to new DB as superuser:
charles@admin.localhost=# \c test
You are now connected to database "test" as user "charles".

Change access settings and create an object:
charles@test.localhost=# REVOKE ALL ON DATABASE test FROM PUBLIC;
charles@test.localhost=# CREATE SCHEMA test_schema;
charles@test.localhost=# CREATE TABLE test_schema.test_table (id INTEGER);
charles@test.localhost=# \dt test_schema.*
             List of relations
   Schema    |    Name    | Type  |  Owner
-------------+------------+-------+---------
 test_schema | test_table | table | charles

Grant access to the object to user1:
charles@test.localhost=# GRANT CONNECT ON DATABASE test TO user1;
charles@test.localhost=# GRANT USAGE ON SCHEMA test_schema TO user1;
charles@test.localhost=# GRANT SELECT, INSERT, UPDATE, DELETE ON
test_schema.test_table TO user1;

Login as user1:
charles@test.localhost=# \c - user1
user1@test.localhost=> INSERT INTO test_schema.test_table VALUES (9);
INSERT 0 1
user1@test.localhost=> SELECT * FROM test_schema.test_table;
 id
----
  9
(1 row)

So far user2 has not received any specific grants from the superuser (or any
database admin for that sake). It cannot connect or do any damage:
user1@test.localhost=> \c - user2
Password for user user2:
FATAL:  permission denied for database "test"
DETAIL:  User does not have CONNECT privilege.

Additionally user1 cannot GRANT the privileges that it has to others:
user1@test.localhost=> GRANT SELECT ON test_schema.test_table TO user2;
WARNING:  no privileges were granted for "test_table"

But user1 can grant itself to user2:
user1@test.localhost=> GRANT user1 TO user2;

At this point user2 can do anything that user1 can do, i.e. connect to the
database and modify data:
user1@test.localhost=> \c - user2
You are now connected to database "test" as user "user2".
user2@test.localhost=> DELETE FROM test_schema.test_table;
DELETE 1

Althought this is the behaviour described in the documentation that means
that any user can potentially grant access to any database it has access to
without the knowledge of the database responsible.

IMHO not giving the ADMIN option of the GRANT statement to a role should
prevent it from granting itself to others, since it makes little sense to
prevent a user from granting individual privileges, but allow it to grant
all of its privileges at once. But there may be very good reasons for this
behaviour which I am not aware of.

I thank you for your explanations and if you have any, for a strategy to
prevent roles to grant themselves to others. In my searches so far, I could
not find any information on that and I was not able to find a strategy
myself.
Thank you and have a good day.
Charles Clavadetscher

On 06/11/2015 01:23 AM, Charles Clavadetscher wrote:
> Good morning
>
> I am investigating the authorization possiblities of PostgreSQL and I
> stumbled on a case, whose rationale I could not find in any resource online.
> For that reason I post my question here.
>
> First of all let me state that the software is acting accordingly to the
> documentation. The passage I'd like to ask about is in the notes to the
> GRANT command and it goes es follows:
>
> "If WITH ADMIN OPTION is specified, the member can in turn grant membership
> in the role to others, and revoke membership in the role as well. Without
> the admin option, ordinary users cannot do that. A role is not considered to
> hold WITH ADMIN OPTION on itself, but it may grant or revoke membership in
> itself from a database session where the session user matches the role.
> Database superusers can grant or revoke membership in any role to anyone.
> Roles having CREATEROLE privilege can grant or revoke membership in any role
> that is not a superuser."
>
> An ordinary user is therefore able to grant itself to other users in a
> database session where the session user matches the role. Actually the
> normal case, if am not completely wrong.
> My question is: What is the point of preventing a role to grant specific
> privileges it has, but allow it to grant all privileges at once, granting
> itself as a role?
>
> Now consider this case to illustrate what I mean:
>
> Create a test environment:
> charles@admin.localhost=# CREATE ROLE user1 LOGIN PASSWORD 'xxx';
> charles@admin.localhost=# CREATE ROLE user2 LOGIN PASSWORD 'xxx';
> charles@admin.localhost=# CREATE DATABASE test;
>
> Login to new DB as superuser:
> charles@admin.localhost=# \c test
> You are now connected to database "test" as user "charles".
>
> Change access settings and create an object:
> charles@test.localhost=# REVOKE ALL ON DATABASE test FROM PUBLIC;
> charles@test.localhost=# CREATE SCHEMA test_schema;
> charles@test.localhost=# CREATE TABLE test_schema.test_table (id INTEGER);
> charles@test.localhost=# \dt test_schema.*
>               List of relations
>     Schema    |    Name    | Type  |  Owner
> -------------+------------+-------+---------
>   test_schema | test_table | table | charles
>
> Grant access to the object to user1:
> charles@test.localhost=# GRANT CONNECT ON DATABASE test TO user1;
> charles@test.localhost=# GRANT USAGE ON SCHEMA test_schema TO user1;
> charles@test.localhost=# GRANT SELECT, INSERT, UPDATE, DELETE ON
> test_schema.test_table TO user1;
>
> Login as user1:
> charles@test.localhost=# \c - user1
> user1@test.localhost=> INSERT INTO test_schema.test_table VALUES (9);
> INSERT 0 1
> user1@test.localhost=> SELECT * FROM test_schema.test_table;
>   id
> ----
>    9
> (1 row)
>
> So far user2 has not received any specific grants from the superuser (or any
> database admin for that sake). It cannot connect or do any damage:
> user1@test.localhost=> \c - user2
> Password for user user2:
> FATAL:  permission denied for database "test"
> DETAIL:  User does not have CONNECT privilege.
>
> Additionally user1 cannot GRANT the privileges that it has to others:
> user1@test.localhost=> GRANT SELECT ON test_schema.test_table TO user2;
> WARNING:  no privileges were granted for "test_table"
>
> But user1 can grant itself to user2:
> user1@test.localhost=> GRANT user1 TO user2;
>
> At this point user2 can do anything that user1 can do, i.e. connect to the
> database and modify data:
> user1@test.localhost=> \c - user2
> You are now connected to database "test" as user "user2".
> user2@test.localhost=> DELETE FROM test_schema.test_table;
> DELETE 1
>
> Althought this is the behaviour described in the documentation that means
> that any user can potentially grant access to any database it has access to
> without the knowledge of the database responsible.
>
> IMHO not giving the ADMIN option of the GRANT statement to a role should
> prevent it from granting itself to others, since it makes little sense to
> prevent a user from granting individual privileges, but allow it to grant
> all of its privileges at once. But there may be very good reasons for this
> behaviour which I am not aware of.

See here:

http://www.postgresql.org/docs/9.4/interactive/sql-createrole.html

In particular the section on INHERIT and in NOTES this:

"The INHERIT attribute is the default for reasons of backwards
compatibility: in prior releases of PostgreSQL, users always had access
to all privileges of groups they were members of. However, NOINHERIT
provides a closer match to the semantics specified in the SQL standard."

Also see here:

http://www.postgresql.org/docs/9.4/interactive/sql-set-role.html

for what SET ROLE does under INHERIT versus NOINHERIT

>
> I thank you for your explanations and if you have any, for a strategy to
> prevent roles to grant themselves to others. In my searches so far, I could
> not find any information on that and I was not able to find a strategy
> myself.
> Thank you and have a good day.
> Charles Clavadetscher
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Hello Adrian

Thank you very much for your response. As a matter of fact I already had a
look into the NOINHERIT attribute and its consequences. This partially
solves the problem but makes the usage of user accounts in applications more
complex.

If I wanted to create a group for accessing the database, i.e. grant CONNECT
to the group and grant the group to the users that should be able to access
that specific database, then users having the NOINHERIT attribute will not
be able to connect. So I would end up granting CONNECT to each individual
user. The approach would help in terms of disabling the ability for a role
granting itself to another to pass on the CONNECT privilege (for the same
reason that it does not work with groups). But legitimate users in the
database would not profit from the grouping ability or, better said, the
application would need to switch between containing roles in order to
perform their tasks. This requires the application to know in which roles to
"set" into in order to access specific objects.

Maybe I still don't see the point, but this approach seems to me to be more
complicated (and error prone), while it would be much simpler to only allow
superusers to grant roles to roles.

My original question was also about the reason why ordinary users should be
able to grant all their privileges to others granting them their role. This
sounds more of less like giving out your password. The latter we may not be
able to prevent, but the first could have a technical solution.

Regards
Charles

>See here:
>
>http://www.postgresql.org/docs/9.4/interactive/sql-createrole.html
>
>In particular the section on INHERIT and in NOTES this:
>
>"The INHERIT attribute is the default for reasons of backwards
>compatibility: in prior releases of PostgreSQL, users always had access to
all privileges of groups they were members of. However, NOINHERIT provides a
closer match to the semantics specified in the SQL standard."
>
>Also see here:
>
>http://www.postgresql.org/docs/9.4/interactive/sql-set-role.html
>
>for what SET ROLE does under INHERIT versus NOINHERIT

Hello

Well I was thinking a litte more on this. Basically I think that it could
end up with a small set of "rules":

- Organize privileges in groups (nologin, inherit) is a way that
independently of how many levels of indirection you have each definite scope
or application has a single group to "set role" into.
- Users have noinherit, thus forcing applications they use to log into the
database to perform a set role to the group designed for that application.
- Users have individually CONNECT privilege to the database(s) they need to
connect to.
- Users have no other privileges granted directly (apart from connect to
database) to avoid switching between group and user.

Well at least where I am it is getting late and I guess I can use some fresh
air.

Regards
Charles

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Charles
Clavadetscher
Sent: Donnerstag, 11. Juni 2015 16:28
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Prevent roles not having admin option from granting
themselves to other roles

Hello Adrian

Thank you very much for your response. As a matter of fact I already had a
look into the NOINHERIT attribute and its consequences. This partially
solves the problem but makes the usage of user accounts in applications more
complex.

If I wanted to create a group for accessing the database, i.e. grant CONNECT
to the group and grant the group to the users that should be able to access
that specific database, then users having the NOINHERIT attribute will not
be able to connect. So I would end up granting CONNECT to each individual
user. The approach would help in terms of disabling the ability for a role
granting itself to another to pass on the CONNECT privilege (for the same
reason that it does not work with groups). But legitimate users in the
database would not profit from the grouping ability or, better said, the
application would need to switch between containing roles in order to
perform their tasks. This requires the application to know in which roles to
"set" into in order to access specific objects.

Maybe I still don't see the point, but this approach seems to me to be more
complicated (and error prone), while it would be much simpler to only allow
superusers to grant roles to roles.

My original question was also about the reason why ordinary users should be
able to grant all their privileges to others granting them their role. This
sounds more of less like giving out your password. The latter we may not be
able to prevent, but the first could have a technical solution.

Regards
Charles

>See here:
>
>http://www.postgresql.org/docs/9.4/interactive/sql-createrole.html
>
>In particular the section on INHERIT and in NOTES this:
>
>"The INHERIT attribute is the default for reasons of backwards
>compatibility: in prior releases of PostgreSQL, users always had access
>to
all privileges of groups they were members of. However, NOINHERIT provides a
closer match to the semantics specified in the SQL standard."
>
>Also see here:
>
>http://www.postgresql.org/docs/9.4/interactive/sql-set-role.html
>
>for what SET ROLE does under INHERIT versus NOINHERIT




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

On 06/11/2015 07:55 AM, Charles Clavadetscher wrote:
> Hello
>
> Well I was thinking a litte more on this. Basically I think that it could
> end up with a small set of "rules":
>
> - Organize privileges in groups (nologin, inherit) is a way that
> independently of how many levels of indirection you have each definite scope
> or application has a single group to "set role" into.
> - Users have noinherit, thus forcing applications they use to log into the
> database to perform a set role to the group designed for that application.
> - Users have individually CONNECT privilege to the database(s) they need to
> connect to.
> - Users have no other privileges granted directly (apart from connect to
> database) to avoid switching between group and user.
>
> Well at least where I am it is getting late and I guess I can use some fresh
> air.

Remember you can also use pg_hba.conf to restrict access:

http://www.postgresql.org/docs/9.4/interactive/auth-pg-hba-conf.html

>
> Regards
> Charles
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Charles
> Clavadetscher
> Sent: Donnerstag, 11. Juni 2015 16:28
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Prevent roles not having admin option from granting
> themselves to other roles
>
> Hello Adrian
>
> Thank you very much for your response. As a matter of fact I already had a
> look into the NOINHERIT attribute and its consequences. This partially
> solves the problem but makes the usage of user accounts in applications more
> complex.
>
> If I wanted to create a group for accessing the database, i.e. grant CONNECT
> to the group and grant the group to the users that should be able to access
> that specific database, then users having the NOINHERIT attribute will not
> be able to connect. So I would end up granting CONNECT to each individual
> user. The approach would help in terms of disabling the ability for a role
> granting itself to another to pass on the CONNECT privilege (for the same
> reason that it does not work with groups). But legitimate users in the
> database would not profit from the grouping ability or, better said, the
> application would need to switch between containing roles in order to
> perform their tasks. This requires the application to know in which roles to
> "set" into in order to access specific objects.
>
> Maybe I still don't see the point, but this approach seems to me to be more
> complicated (and error prone), while it would be much simpler to only allow
> superusers to grant roles to roles.
>
> My original question was also about the reason why ordinary users should be
> able to grant all their privileges to others granting them their role. This
> sounds more of less like giving out your password. The latter we may not be
> able to prevent, but the first could have a technical solution.
>
> Regards
> Charles
>
>> See here:
>>
>> http://www.postgresql.org/docs/9.4/interactive/sql-createrole.html
>>
>> In particular the section on INHERIT and in NOTES this:
>>
>> "The INHERIT attribute is the default for reasons of backwards
>> compatibility: in prior releases of PostgreSQL, users always had access
>> to
> all privileges of groups they were members of. However, NOINHERIT provides a
> closer match to the semantics specified in the SQL standard."
>>
>> Also see here:
>>
>> http://www.postgresql.org/docs/9.4/interactive/sql-set-role.html
>>
>> for what SET ROLE does under INHERIT versus NOINHERIT
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com