Re: Prevent roles not having admin option from granting themselves to other roles

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(at)admin(dot)localhost=# CREATE ROLE user1 LOGIN PASSWORD 'xxx';
> charles(at)admin(dot)localhost=# CREATE ROLE user2 LOGIN PASSWORD 'xxx';
> charles(at)admin(dot)localhost=# CREATE DATABASE test;
>
> Login to new DB as superuser:
> charles(at)admin(dot)localhost=# \c test
> You are now connected to database "test" as user "charles".
>
> Change access settings and create an object:
> charles(at)test(dot)localhost=# REVOKE ALL ON DATABASE test FROM PUBLIC;
> charles(at)test(dot)localhost=# CREATE SCHEMA test_schema;
> charles(at)test(dot)localhost=# CREATE TABLE test_schema.test_table (id INTEGER);
> charles(at)test(dot)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(at)test(dot)localhost=# GRANT CONNECT ON DATABASE test TO user1;
> charles(at)test(dot)localhost=# GRANT USAGE ON SCHEMA test_schema TO user1;
> charles(at)test(dot)localhost=# GRANT SELECT, INSERT, UPDATE, DELETE ON
> test_schema.test_table TO user1;
>
> Login as user1:
> charles(at)test(dot)localhost=# \c - user1
> user1(at)test(dot)localhost=> INSERT INTO test_schema.test_table VALUES (9);
> INSERT 0 1
> user1(at)test(dot)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(at)test(dot)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(at)test(dot)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(at)test(dot)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(at)test(dot)localhost=> \c - user2
> You are now connected to database "test" as user "user2".
> user2(at)test(dot)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(dot)klaver(at)aklaver(dot)com