Another solution would be to have CREATE USER done by a local admin create users in the form of '[EMAIL PROTECTED]'. 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.
How about splitting usesuper into just the following two privileges:
*) createuser *) usesuper
A normal user can be the owner of a database so there's no need for an "admin" like priv. createuser allows the user to create other users with the same or lesser privs, and usesuper is basically exactly what we've got now. How's that? It doesn't give us database local usernames, but it's a big first step toward virtual hosting.
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.
Eh, yes and no. The cluster database admin is the last line of defense... the local dba is who users would talk to and even then, the cluster database admin should only restore local admin privs to the local dba. Beyond that, the cluster dba shouldn't be involved with much... separating the UID ranges for global vs local users wouldn't be a bad idea the more I think about it, even though I don't think there's much of a risk except for possibly to the local database integrity.
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?
Other holes... none that I can think of beyond:
*) changing privs - accidentally elevating a global user's privs when thinking it was a local user that was getting its privs elevated.
*) changing the wrong password - same prob as changing the privs
*) granting privs to structures to the wrong UID because of incorrect username resolution
Beyond leaving admins with possible land mines that needs to be well documented... but that's the trade off for the flexibility, IMHO. Proxy authentication has the same pit falls in kerberos or SASL but that hasn't stopped the security community from adopting kerberos in high security installations.... the pit falls just need to be documented.
A lot of this could be subverted if pg_hba.conf was also stored in the database and could be used to limit what users could connect to what databases and from what IPs. :)
BTW, has it been discussed to add a way in pg_hba.conf to limit the number of connections per IP address or per subnet mask? 2 connections per /32 or 4 connections per /30?
-sc
-- Sean Chittenden
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
