Robert Haas <robertmhaas@gmail.com> writes:
> ... Suppose the superuser grants "admin" to both "joe" and "sally".
> Now "joe" can SET ROLE to "admin" and revoke it from "sally", and the
> superuser has no tool to prevent this.
Really?
regression=# grant admin to joe;
GRANT ROLE
regression=# grant admin to sally;
GRANT ROLE
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> revoke admin from sally;
ERROR: must have admin option on role "admin"
regression=> set role admin;
SET
regression=> revoke admin from sally;
ERROR: must have admin option on role "admin"
I think there is an issue here around exactly what the admin option
means, but if it doesn't grant you the ability to remove grants
made by other people, it's pretty hard to see what it's for.
Precisely.
The current system, with the session_user exception, basically guides a superuser to define two kinds of roles.
Groups: No login, permission grants
Users: Login, inherits permissions from groups, can manage group membership if given WITH ADMIN OPTION.
The original example using only users is not all that compelling to me. IMO, DBAs should not be setting up their system that way.
Two questions remain:
1. Are we willing to get rid of the session_user exception?
2. Do we want to track who the grantor is for role membership grants and institute a requirement that non-superusers can only revoke the grants that they personally made?
I'm personally in favor of getting rid of the session_user exception, which nicely prevents the problem at the beginning of this thread and further encourages the DBA to define groups and roles with a greater separation-of-concerns design. WITH ADMIN OPTION is sufficient.
I think tracking grantor information for role membership would allow for greater auditing capabilities and a better degree of control in the permissions system.
In short, I am in favor of both options. The grantor tracking seems to be headed for acceptance.
So, do we really want to treat every single login role as a potential group by keeping the session_user exception?
David J.
