Re: db_user_namespace a "temporary measure"

On 03/11/2014 07:41 PM, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> The docs say:
>> db_user_namespace causes the client's and server's user name
>> representation to differ. Authentication checks are always done with
>> the server's user name so authentication methods must be configured
>> for the server's user name, not the client's. Because md5 uses the
>> user name as salt on both the client and server, md5 cannot be used
>> with db_user_namespace.
>> Is that the only major issue?
> That's one major issue.
>
> Another one is the hacky way of distinguishing global from per-db users
> (ie, user must append @ to log in as a global user). I'd love to get rid
> of that requirement, but not sure how. Would it be all right for the
> server to first probe for user(at)db and then for just user, or vice versa?

user(at)db first, I think. I guess that means don't name your global users
the same as db-specific users. Maybe we should prevent a conflict? Or if
we allow a conflict then only require user@ in conflicting cases.

> How would this integrate with pg_hba.conf?

Not sure.

>
>> Why not have the server strip out the @db part if this is on?
> Hmm ... that's a thought. IIRC, the client doesn't actually know that
> this is going on, it just sends the user name, and hashes against that
> too. If the server remembered the bare user name it could hash against
> that as well.
>
> At least, that would work for db-local usernames. It *doesn't* work for
> global names, because the client side has no idea that the @ ought to not
> be counted for hashing. Again, if we could get rid of that convention,
> it'd be far less messy.

Right.

>
> A possible objection would be that changing the definition of what to hash
> would invalidate existing MD5 password hashes; but since MD5 passwords
> have never been allowed with db_user_namespace names anyway, that doesn't
> seem very forceful.

Agreed.

>
>> If we made this an initdb-time setting rather than a
>> GUC then we'd remove the problems caused by turning this on and off.
> Why do we need to restrict that?
>
>

Oh, probably a thinko in my part. I thought the setting would affect the
stored password, but now I think about it some more I see it doesn't.

I think we might be on the track of something useful here.

cheers

andre