Re: GSSAPI server side on Linux, SSPI client side on Windows

* From: Brian Crowell

> On Mon, Nov 11, 2013 at 11:56 PM, Christian Ullrich
> <chris(at)chrullrich(dot)net> wrote:
> >> On Mon, Nov 11, 2013 at 10:51 PM, Brian Crowell <brian(at)fluggo(dot)com>
> wrote:
> >> * If I don't specify my username, Npgsql sends it in lowercase
> "bcrowell"
> >
> > Hmm. That is related one problem I've been having with SSPI auth from
> > libpq/ODBC. The database treats the claimed user name case-sensitively
> > when looking up the user info in pg_authid, and if the user logged on to
> > Windows with a name differing in case from what the database thinks it is,
> > authentication fails. Npgsql sending it always in lower case is precisely
> > what I landed on as a workaround (basically overriding libpq's automatic
> > user name detection in the ODBC connection string by appending a UID
> > option).
>
> The message I get in the log is "provided user name
> (bcrowell(at)REALM(dot)COM) and authenticated username (BCrowell(at)REALM(dot)COM)
> do not match," so it looks like I have to teach Npgsql to match
> whatever Windows is sending in GSSAPI. That, or teach Postgres how to
> lowercase the name on arrival.
>
> What did you do to get around this?

ODBC supports several connection string types. The simplest is the name of a system or user DSN alone. Another is something along the lines of "DSN=xyz;Option1=foo;Option2=bar", supplementing (or overriding) options from the DSN with local values.

I used that to supply an explicit "UID" option giving the result of converting the current user name to another format using IADsNameTranslate. That works because it pulls the information from the directory rather than just munging the result of GetUserName().

Pseudocode:

n = GetUserNameEx(NameSamCompatible) // "logon screen" case
NameTranslate.Set(ADS_NAME_TYPE_NT4, n)
n = NameTranslate.Get(ADS_NAME_TYPE_DOMAIN_SIMPLE) // "official" case
n = n.CutAtTheAtSign()
db.Connect("DSN=foo;UID=" + n)

To get a usable realm name, ADS_NAME_TYPE_USER_PRINCIPAL_NAME is probably more correct.

This works if the role name in pg_authid matches the user name in the directory, case-wise. It cannot be shortened to GetUserNameEx(NameUserPrincipal) because that also returns "logon screen" case.

--
Christian