Tom Lane wrote:
Kenneth Downs <ken@secdat.com> writes:
The biggest security limitation we have is actually a weakness in
Postgres - the inability to restrict the abilities of a user with
CREATUSER rights, they can make somebody who can do anything. For
higher security this requires no ability for public registration of
accounts. This would be solved if we could restrict a CREATUSER user to
only GRANTing to roles they themselves are in.
I thought about this for awhile, but I think you are missing the reason
why it's designed the way it is. The point of CREATEROLE privilege is
to be a slightly safer form of superuser: that is, to allow the DBA to
do all his day-to-day management of user accounts without being a real
superuser who can corrupt the database arbitrarily badly. If we
restricted CREATEROLE as you suggest, then either DBAs would have to
make their CREATEROLE account a member of every role they manage, or
they'd have to run as real superusers. Either choice represents a
significant increase in the capabilities of the CREATEROLE account and
thus more chance for mistakes. So while a miscreant with CREATEROLE
can certainly avail himself of any database privilege short of
superuserness, in the intended use of the feature it is actually
possible for DBAs to operate with *fewer* privileges than they would
need to get useful work done if we adopted your suggestion.
Tom, it sounds like you've thought this through, and I can't disagree with the reality of what DBA's are doing, but does it have to be one or the other?
Perhaps a lesser form of CREATEROLE, CREATEROLE_LIMITED, who can create roles and only grant to the roles he himself is a member of.
This suggestion I think would be in line with your own reasoning. Just as CREATEROLE is a lesser SUPERUSER, so CREATEROLE_LIMITED is the next logical extension, a lesser CREATEROLE.
At any rate, I hope I can convince somebody, cuz ole Ken don't code in C no more :)
