Hi,
I found an inconsistency between documentation and real behavior of
REVOKE [ADMIN OPTION FOR] ROLE.
As per documentation
(http://www.postgresql.org/docs/current/static/sql-revoke.html):
--
If GRANT OPTION FOR is specified, only the grant option for the
privilege is revoked, not the privilege itself. Otherwise, both the
privilege and the grant option are revoked.
If a user holds a privilege with grant option and has granted it to
other users then the privileges held by those other users are called
dependent privileges. If the privilege or the grant option held by the
first user is being revoked and dependent privileges exist, those
dependent privileges are also revoked if CASCADE is specified; if it is
not, the revoke action will fail.
...
When revoking membership in a role, GRANT OPTION is instead called ADMIN
OPTION, but the behavior is similar.
--
So, revoking membership in a role (or admin option for a role) should
revoke dependent memberships too. In fact it does not.
Here is a script to reproduce the issue:
\c - postgres
create user r1;
create user r2;
create role g;
grant g to r1 with admin option;
\c - r1
grant g to r2 with admin option;
\c - postgres
revoke g from r1 cascade;
I check membership with the following query:
select (select rolname from pg_roles where oid=am.roleid) "role", (select rolname from pg_roles where oid=am.member)
member, (select rolname from pg_roles where oid=am.grantor) grantor, am.admin_option
from pg_auth_members am;
Before REVOKE it shows 2 records (which is correct):
role|g
member|r1
grantor|postgres
admin_option|t
role|g
member|r2
grantor|r1
admin_option|t
After revoke it shows 1 record:
role|g
member|r2
grantor|r1
admin_option|t
No records are expected according to documentation.
I looked into the code too (backend/commands/user.c,
GrantRole(GrantRoleStmt *stmt) function) and didn't find any processing
of stmt->behavior.
So, the question: is it a documentation bug (as it seems to me), code
bug, or I missed something?
Thanks,
Egor.
