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

>> Another solution would be to have CREATE USER done by a local admin
>> create users in the form of 'username@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.

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