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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
