Re: role self-revocation

I don't think we're that far from having all of these though.  To start
with, we remove from CREATEROLE the random things that it does which go
beyond what folks tend to expect- remove the whole 'grant any role to
any other' stuff, remove the 'drop role' exception, remove the
'alter role' stuff.  Do make it so that when you create a role, however,
the above GRANT is effectively done.  Now, for the items above where we
removed the checks against have_createrole_privilege() we go back and
add in checks using is_admin_of_role().  Of course, also remove the role
self-administration bug.

That's step #1, but it gets us more-or-less what you're looking for, I
think, and brings us a lot closer to what the spec has.