Re: Per database users/admins, handy for database virtual hosting...

Sean Chittenden <sean(at)chittenden(dot)org> writes:
> Another solution would be to have CREATE USER done by a local admin
> create users in the form of 'username(at)database'. This prevents
> duplicate usernames and allows us to use the current hack of local
> database users.

Yeah, I think it would be reasonable to leave that facility as-is and
invent a category of user privileges that only allows creation/deletion
of local usernames. We'd have to think about how that should interact
with other superuser attributes such as the ability to bypass
privileges. But breaking down "superuserness" into finer-grained
privileges has been on the wish list for awhile.

>> Once he creates a local user with the same name as the global
>> superuser, the door is open to problems --- not only possible bugs in
>> our own code, but plain old human error on the part of the real
>> superuser.

> How so? Can you give a scenario where this'd make a difference?

Well, it's the standard sort of attack where you try to trick a user
with more privs than you into doing something he'd not do if he was
aware of who/what he was really doing it to. Something like granting
privileges to a local user when he thought he was granting to a global
user, or vice versa. Or making the wrong user a member of a group.

I'm not sure that this is actually very probable, if the usage scenario
is that global users are always superusers --- there'd seldom be any
reason to go granting them any additional privileges. But if we are
also thinking of having multiple categories of user privileges then it's
less far-fetched.

Other possible problems include resetting the password of the wrong
user. This would be particularly bad if a database's local superuser
can choose the setting of the "encrypt passwords by default" GUC switch
--- he might have the opportunity to see the cleartext password of some
global user. Obviously that hole can be plugged now that I've pointed
it out, but what other ones are there?

regards, tom lane