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: [email protected]=# CREATE ROLE user1 LOGIN PASSWORD 'xxx'; [email protected]=# CREATE ROLE user2 LOGIN PASSWORD 'xxx'; [email protected]=# CREATE DATABASE test; Login to new DB as superuser: [email protected]=# \c test You are now connected to database "test" as user "charles". Change access settings and create an object: [email protected]=# REVOKE ALL ON DATABASE test FROM PUBLIC; [email protected]=# CREATE SCHEMA test_schema; [email protected]=# CREATE TABLE test_schema.test_table (id INTEGER); [email protected]=# \dt test_schema.* List of relations Schema | Name | Type | Owner -------------+------------+-------+--------- test_schema | test_table | table | charles Grant access to the object to user1: [email protected]=# GRANT CONNECT ON DATABASE test TO user1; [email protected]=# GRANT USAGE ON SCHEMA test_schema TO user1; [email protected]=# GRANT SELECT, INSERT, UPDATE, DELETE ON test_schema.test_table TO user1; Login as user1: [email protected]=# \c - user1 [email protected]=> INSERT INTO test_schema.test_table VALUES (9); INSERT 0 1 [email protected]=> 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: [email protected]=> \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: [email protected]=> GRANT SELECT ON test_schema.test_table TO user2; WARNING: no privileges were granted for "test_table" But user1 can grant itself to user2: [email protected]=> GRANT user1 TO user2; At this point user2 can do anything that user1 can do, i.e. connect to the database and modify data: [email protected]=> \c - user2 You are now connected to database "test" as user "user2". [email protected]=> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
