Обсуждение: GSSAPI server side on Linux, SSPI client side on Windows

Hello again!

I've been setting up my PostgreSQL server by doing something I've
never done before: I've joined a Linux server to a domain so I can use
integrated Kerberos authentication from server to server.

I've managed to make this work from Linux machine to Linux machine. On
the client, I retrieve my Kerberos ticket with kinit, and then I can
use psql with my username to connect to the server without a password
over GSSAPI. So far, so good.

However, the eventual goal was to connect to this same server from a
.NET app running on Windows, and here I've run into a snag. The Npgsql
library does not support GSSAPI—it only supports SSPI, which is
nearly-but-not-enough-like the same thing to count in this situation,
because I can't seem to configure my PostgreSQL on Linux to accept
SSPI. If I try it, I get the error:

  invalid authentication method "sspi": not supported by this build

The docs say that SSPI is supported if GSSAPI is available
(http://www.postgresql.org/docs/9.3/static/auth-methods.html#SSPI-AUTH).
Is this just a matter of me running the wrong build? I'm using
PostgreSQL 9.3 from the official builds for Debian 6.

If the docs are wrong, and SSPI isn't available server-side on Linux,
what are my other options?

—Brian

I've thought of one option, which I'm investigating: implementing
GSSAPI support in Npgsql. Microsoft claims this is possible using the
SSPI API: http://msdn.microsoft.com/en-us/library/windows/desktop/aa380496(v=vs.85).aspx

—Brian

On Wed, Oct 30, 2013 at 3:16 PM, Brian Crowell <brian@fluggo.com> wrote:
> Hello again!
>
> I've been setting up my PostgreSQL server by doing something I've
> never done before: I've joined a Linux server to a domain so I can use
> integrated Kerberos authentication from server to server.
>
> I've managed to make this work from Linux machine to Linux machine. On
> the client, I retrieve my Kerberos ticket with kinit, and then I can
> use psql with my username to connect to the server without a password
> over GSSAPI. So far, so good.
>
> However, the eventual goal was to connect to this same server from a
> .NET app running on Windows, and here I've run into a snag. The Npgsql
> library does not support GSSAPI—it only supports SSPI, which is
> nearly-but-not-enough-like the same thing to count in this situation,
> because I can't seem to configure my PostgreSQL on Linux to accept
> SSPI. If I try it, I get the error:
>
>   invalid authentication method "sspi": not supported by this build
>
> The docs say that SSPI is supported if GSSAPI is available
> (http://www.postgresql.org/docs/9.3/static/auth-methods.html#SSPI-AUTH).
> Is this just a matter of me running the wrong build? I'm using
> PostgreSQL 9.3 from the official builds for Debian 6.
>
> If the docs are wrong, and SSPI isn't available server-side on Linux,
> what are my other options?
>
> —Brian

On Wed, Oct 30, 2013 at 3:16 PM, Brian Crowell <brian@fluggo.com> wrote:
> Hello again!
>
> I've been setting up my PostgreSQL server by doing something I've
> never done before: I've joined a Linux server to a domain so I can use
> integrated Kerberos authentication from server to server.
>
> I've managed to make this work from Linux machine to Linux machine. On
> the client, I retrieve my Kerberos ticket with kinit, and then I can
> use psql with my username to connect to the server without a password
> over GSSAPI. So far, so good.
>
> However, the eventual goal was to connect to this same server from a
> .NET app running on Windows, and here I've run into a snag. The Npgsql
> library does not support GSSAPI—it only supports SSPI, which is
> nearly-but-not-enough-like the same thing to count in this situation,
> because I can't seem to configure my PostgreSQL on Linux to accept
> SSPI. If I try it, I get the error:
>
>   invalid authentication method "sspi": not supported by this build
>
> The docs say that SSPI is supported if GSSAPI is available
> (http://www.postgresql.org/docs/9.3/static/auth-methods.html#SSPI-AUTH).
> Is this just a matter of me running the wrong build? I'm using
> PostgreSQL 9.3 from the official builds for Debian 6.
>
> If the docs are wrong, and SSPI isn't available server-side on Linux,
> what are my other options?

hm -- maybe use ODBC?

merlin

Brian,

* Brian Crowell (brian@fluggo.com) wrote:
> However, the eventual goal was to connect to this same server from a
> .NET app running on Windows, and here I've run into a snag. The Npgsql
> library does not support GSSAPI—it only supports SSPI, which is
> nearly-but-not-enough-like the same thing to count in this situation,

Uhhh, why not?

> because I can't seem to configure my PostgreSQL on Linux to accept
> SSPI. If I try it, I get the error:

Nono, you don't try to build SSPI on Linux- that's Microsoft's
implementation of GSSAPI (more-or-less).  You continue to use GSSAPI on
Linux...

> The docs say that SSPI is supported if GSSAPI is available
> (http://www.postgresql.org/docs/9.3/static/auth-methods.html#SSPI-AUTH).
> Is this just a matter of me running the wrong build? I'm using
> PostgreSQL 9.3 from the official builds for Debian 6.

The docs could probably be a bit clearer.

> If the docs are wrong, and SSPI isn't available server-side on Linux,
> what are my other options?

Build the server-side w/ GSSAPI.  Your Linux system can then either join
the Windows AD domain (you'll need to create a princ on the AD and then
export it over to the Linux box), or set up two KDCs (one on Linux, one
for AD) and then have a cross-realm trust.

        Thanks,

            Stephen

* Stephen Frost wrote:

> * Brian Crowell (brian@fluggo.com) wrote:
>> However, the eventual goal was to connect to this same server from a
>> .NET app running on Windows, and here I've run into a snag. The Npgsql
>> library does not support GSSAPI—it only supports SSPI, which is
>> nearly-but-not-enough-like the same thing to count in this situation,
>
> Uhhh, why not?

Because the server on Linux sends AUTH_REQ_GSS, which Npgsql does not
recognize.

I tried to fix it using the reverse of they one-line fix that worked in
both JDBC and libpq. There, the problem was that they only supported
GSSAPI and had no clue about SSPI (except libpq on Windows). The fix was
to basically declare GSSAPI and SSPI to be the same. It didn't work.

In Npgsql's case, the problem is the other way around -- it only knows
SSPI. While making GSSAPI the same as SSPI should work in principle,
there must be some difference somewhere.

--
Christian

* Christian Ullrich (chris@chrullrich.net) wrote:
> I tried to fix it using the reverse of they one-line fix that worked
> in both JDBC and libpq. There, the problem was that they only
> supported GSSAPI and had no clue about SSPI (except libpq on
> Windows). The fix was to basically declare GSSAPI and SSPI to be the
> same. It didn't work.

If Npgsql does the same as libpq-on-Windows, it should all work just
fine..

> In Npgsql's case, the problem is the other way around -- it only
> knows SSPI. While making GSSAPI the same as SSPI should work in
> principle, there must be some difference somewhere.

Well, what happened after you hacked Npgsql?  It's possible there's a
bug or something it's doing different from libpq-on-Windows that is
causing a problem, or it might simply be an issue with configuration
(for example, you have to use POSTGRES as your server-side princ because
Windows is case-insensitive).

    Thanks,

        Stephen

* Stephen Frost wrote:

> * Christian Ullrich (chris@chrullrich.net) wrote:

>> I tried to fix it using the reverse of they one-line fix that worked
>> in both JDBC and libpq. There, the problem was that they only
>> supported GSSAPI and had no clue about SSPI (except libpq on
>> Windows). The fix was to basically declare GSSAPI and SSPI to be the
>> same. It didn't work.
>
> If Npgsql does the same as libpq-on-Windows, it should all work just
> fine..

Hence my suspicion that it doesn't. I did not have the time to compare
every function call yet.

>> In Npgsql's case, the problem is the other way around -- it only
>> knows SSPI. While making GSSAPI the same as SSPI should work in
>> principle, there must be some difference somewhere.
>
> Well, what happened after you hacked Npgsql?  It's possible there's a

Nov  1 10:21:44 infra1 postgres[24864]: [7-1] FATAL:  GSSAPI
authentication failed for user "chris"
Nov  1 10:25:27 infra1 postgres[25030]: [7-1] FATAL:  accepting GSS
security context failed
Nov  1 10:25:27 infra1 postgres[25030]: [7-2] DETAIL:  An unsupported
mechanism was requested: Unknown error
Nov  1 10:26:28 infra1 postgres[25079]: [7-1] FATAL:  accepting GSS
security context failed
Nov  1 10:26:28 infra1 postgres[25079]: [7-2] DETAIL:  An unsupported
mechanism was requested: Unknown error
Nov  1 10:30:41 infra1 postgres[25193]: [7-1] FATAL:  canceling
authentication due to timeout
Nov  1 10:31:50 infra1 postgres[25277]: [7-1] FATAL:  accepting GSS
security context failed
Nov  1 10:31:50 infra1 postgres[25277]: [7-2] DETAIL:  An unsupported
mechanism was requested: Unknown error
Nov  1 10:39:31 infra1 postgres[25587]: [7-1] FATAL:  accepting GSS
security context failed
Nov  1 10:39:31 infra1 postgres[25587]: [7-2] DETAIL:  Unspecified GSS
failure.  Minor code may provide more information:
Nov  1 10:44:32 infra1 postgres[25778]: [7-1] FATAL:  accepting GSS
security context failed
Nov  1 10:44:32 infra1 postgres[25778]: [7-2] DETAIL:  Unspecified GSS
failure.  Minor code may provide more information:
Nov  1 10:44:56 infra1 postgres[25789]: [7-1] FATAL:  accepting GSS
security context failed
Nov  1 10:44:56 infra1 postgres[25789]: [7-2] DETAIL:  Unspecified GSS
failure.  Minor code may provide more information:

At some point during that I changed the principal that Npgsql gets its
service ticket for from POSTGRES/<IP address> to POSTGRES/<host name>.
There is a comment in the source that it does not work with the host
name, with no more details, and I chose not to believe that. The result
did nothing to prove me right, though. I think it was where the errors
change from "accepting context failed" to "unspecified error", but I may
be wrong.

The GSSAPI error messages are of the usual helpful kind, even including
the colon that is followed by no detail.

I will spend more time on it once I have managed to keep my job this week.

--
Christian

* Francisco Figueiredo Jr. wrote:

> On Tue, Nov 5, 2013 at 2:35 PM, Christian Ullrich <chris@chrullrich.net
> <mailto:chris@chrullrich.net>> wrote:
>
>     * Stephen Frost wrote:
>
>         * Brian Crowell (brian@fluggo.com <mailto:brian@fluggo.com>) wrote:
>
>             However, the eventual goal was to connect to this same
>             server from a
>             .NET app running on Windows, and here I've run into a snag.
>             The Npgsql
>             library does not support GSSAPI—it only supports SSPI, which is
>             nearly-but-not-enough-like the same thing to count in this
>             situation,
>
>
>         Uhhh, why not?
>
>
>     Because the server on Linux sends AUTH_REQ_GSS, which Npgsql does
>     not recognize.
>
>     I tried to fix it using the reverse of they one-line fix that worked
>     in both JDBC and libpq. There, the problem was that they only
>     supported GSSAPI and had no clue about SSPI (except libpq on
>     Windows). The fix was to basically declare GSSAPI and SSPI to be the
>     same. It didn't work.
>
>     In Npgsql's case, the problem is the other way around -- it only
>     knows SSPI. While making GSSAPI the same as SSPI should work in
>     principle, there must be some difference somewhere.
>
>
>
> Did you make your changes in the NpgsqlState.cs file?

Yes.

> Also, while checking this part of the code, it seems Npgsql isn't
> handling the AuthenticationGSS message. It is only handling
> AuthenticationGSSContinue messages.
 >
 > I think you could try adding the  AuthenticationGSS case to Npgsql
 > and see if it can authenticate correctly. You could add a second
 > switch case below the case
 > AuthenticationRequestType.AuthenticationSSPI and see if Npgsql can
 > also handle the GSS authentication correctly.

That is exactly what I did.

I remember from my work on libpq that there is a slight difference in
how it handles the two authentication types, but there it is just a flag
for whether to treat the user name case-sensitively or not. Here, I
control the case of the user part of the UPN, the claimed user name in
the startup packet, and the role name in the database, and I know they
are all identical. Therefore it should not matter for now whether Npgsql
has similar logic already. To make GSSAPI support production-ready, I
may have to add it, of course.

--
Christian

* Christian Ullrich wrote:

> Nov  1 10:31:50 infra1 postgres[25277]: [7-1] FATAL:  accepting GSS
> security context failed
> Nov  1 10:31:50 infra1 postgres[25277]: [7-2] DETAIL:  An unsupported
> mechanism was requested: Unknown error
> Nov  1 10:39:31 infra1 postgres[25587]: [7-1] FATAL:  accepting GSS
> security context failed
> Nov  1 10:39:31 infra1 postgres[25587]: [7-2] DETAIL:  Unspecified GSS
> failure.  Minor code may provide more information:

> name, with no more details, and I chose not to believe that. The result
> did nothing to prove me right, though. I think it was where the errors
> change from "accepting context failed" to "unspecified error", but I may
> be wrong.

For "accepting context failed" read "unsupported mechanism", of course.

--
Christian

On Tue, Nov 5, 2013 at 11:35 AM, Christian Ullrich <chris@chrullrich.net> wrote:
> Hence my suspicion that it doesn't. I did not have the time to compare every
> function call yet.

It doesn't. But it's a pretty close match; it looks like it was ported
directly from the libpq code. libpq actually uses the same code path
for SSPI and GSSAPI on Windows, with a boolean to switch a few things
between the two. I've been trying to duplicate those changes in
Npgsql.

I think I'm getting closer though. I have psql on Windows successfully
authenticating, so I can't be too far off.


> The GSSAPI error messages are of the usual helpful kind, even including the
> colon that is followed by no detail.

I've been trying to enable the KRB5_TRACE environment variable in the
Postgres child processes, but I can't seem to make it stick. That
would (should!) provide some sort of help.

—Brian

On Mon, Nov 11, 2013 at 10:51 PM, Brian Crowell <brian@fluggo.com> wrote:
> I think I'm getting closer though. I have psql on Windows successfully
> authenticating, so I can't be too far off.

Got it.

The NpgsqlPasswordPacket class has a bug: a utility function it calls
appends a null character to the data, which completely screws up
GSSAPI. Now that I fixed that, I've got successful integrated
authentication from Windows to PostgreSQL on Linux.

However:

* If I don't specify my username, Npgsql sends it in lowercase "bcrowell"
* Npgsql isn't sending the realm, and I've got PostgreSQL configured
to expect it

Otherwise, it's working. As far as I know, the changes necessary are:

* Use hostname in the SPN instead of IP address
* Use "kerberos" package in AcquireCredentialsHandle call instead of "negotiate"
* Fix PGUtil.WriteBytes to not send the extra null (this method is
only used by NpgsqlPasswordPacket, but this fix will most likely break
other authentication methods)
* As stated above, may need to specify username manually (UserName =
"BCrowell@DOMAIN.COM"); I want to fix this

If I figure out the username issue, I'll submit a patch.

Also, in my case, it doesn't seem to matter for the SPN whether the
service name is "postgres" or "POSTGRES." I've got PostgreSQL set to
"postgres", and Npgsql is specifying "POSTGRES", but I also at some
point configured two sets of SPNs on the domain for uppercase and
lowercase, so I don't know if that's a mitigating factor.

—Brian

* From: Brian Crowell

> On Mon, Nov 11, 2013 at 10:51 PM, Brian Crowell <brian@fluggo.com> wrote:
> > I think I'm getting closer though. I have psql on Windows successfully
> > authenticating, so I can't be too far off.
>
> Got it.

Great!

> The NpgsqlPasswordPacket class has a bug: a utility function it calls
> appends a null character to the data, which completely screws up GSSAPI.
> Now that I fixed that, I've got successful integrated authentication from
> Windows to PostgreSQL on Linux.
>
> However:
>
> * 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
namecase-sensitively when looking up the user info in pg_authid, and if the user logged on to Windows with a name
differingin case from what the database thinks it is, authentication fails. Npgsql sending it always in lower case is
preciselywhat I landed on as a workaround (basically overriding libpq's automatic user name detection in the ODBC
connectionstring by appending a UID option). 

> * Use "kerberos" package in AcquireCredentialsHandle call instead of
> "negotiate"

As long as it is the client that does that, it should be fine. According to the documentation on SSPI packages, it is
validfor the client SSPI to send a GSSAPI token to a server using the Negotiate package (instead of going through
SPNEGOto arrive at the same protocol). 

> Also, in my case, it doesn't seem to matter for the SPN whether the
> service name is "postgres" or "POSTGRES." I've got PostgreSQL set to

Yeah, I think that bit about "you have to make the service name uppercase in postgresql.conf" is some kind of oral
traditionthat everyone quotes at everyone else. I have been using SSPI and GSSAPI since the days of Windows 2000, and
ithas always worked quite well without it. 

> "postgres", and Npgsql is specifying "POSTGRES", but I also at some point
> configured two sets of SPNs on the domain for uppercase and lowercase, so
> I don't know if that's a mitigating factor.

The client gets its service ticket, with only one service name in it, before contacting the server, so it cannot know
whatthe server wants to see. 

Congratulations on getting it to work. I'm a bit envious that you beat me to it (GSS auth interop between PostgreSQL on
Windowsand others is kind of my hobby), but the sooner, the better. 

--
Christian

Em 12/11/2013 03:37, "Brian Crowell" <brian@fluggo.com> escreveu:
>
> On Mon, Nov 11, 2013 at 10:51 PM, Brian Crowell <brian@fluggo.com> wrote:
> > I think I'm getting closer though. I have psql on Windows successfully
> > authenticating, so I can't be too far off.
>
> Got it.
>
> The NpgsqlPasswordPacket class has a bug: a utility function it calls
> appends a null character to the data, which completely screws up
> GSSAPI. Now that I fixed that, I've got successful integrated
> authentication from Windows to PostgreSQL on Linux.
>

That's great!

We have made a lot of changes to those utility functions and now we have methods which don't append that null char.

> However:
>
> * If I don't specify my username, Npgsql sends it in lowercase "bcrowell"
> * Npgsql isn't sending the realm, and I've got PostgreSQL configured
> to expect it
>
> Otherwise, it's working. As far as I know, the changes necessary are:
>
> * Use hostname in the SPN instead of IP address
> * Use "kerberos" package in AcquireCredentialsHandle call instead of "negotiate"
> * Fix PGUtil.WriteBytes to not send the extra null (this method is
> only used by NpgsqlPasswordPacket, but this fix will most likely break
> other authentication methods)
> * As stated above, may need to specify username manually (UserName =
> "BCrowell@DOMAIN.COM"); I want to fix this
>
> If I figure out the username issue, I'll submit a patch.
>

Excellent, Brian!

I'm looking forward your patch.
Npgsql source can be found at github.com/npgsql/Npgsql

If you need any help to understand Npgsql, please let me know. Unfortunately as I'm not the original developer of the sspi code, I may not be very helpful on this specific issue, but I can help you out regarding other parts of Npgsql code.

> Also, in my case, it doesn't seem to matter for the SPN whether the
> service name is "postgres" or "POSTGRES." I've got PostgreSQL set to
> "postgres", and Npgsql is specifying "POSTGRES", but I also at some
> point configured two sets of SPNs on the domain for uppercase and
> lowercase, so I don't know if that's a mitigating factor.
>

It would be awesome if you could write a little guide about how to configure PostgreSQL to work with sspi authentication from Windows.
I could add it to our Npgsql user manual...

Thank you all for having a look at those Npgsql authentication issues.

> —Brian
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On Tue, Nov 12, 2013 at 5:45 AM, Francisco Figueiredo Jr.
<francisco@npgsql.org> wrote:
> It would be awesome if you could write a little guide about how to configure
> PostgreSQL to work with sspi authentication from Windows.
> I could add it to our Npgsql user manual...

A guide will have to wait until I do all this again in a few months,
but I'll jot down my mental notes. Here's what I did, using Debian 6,
Samba 3.5 and PostgreSQL 9.3 against a Win2k3 domain:

1. Set up Kerberos for the domain. Plenty of guides for this.

2. Joined the Linux machine to the domain. Also lots of guides for
this, but most of them are outdated and add a ton of settings that you
don't need. Don't follow a guide until you read the relevant chapters
of the Samba HOWTO. Familiarity with Active Directory helps. I'm using
winbindd because I think it maintains the server's TGTs, but I'm not
really sure.

3. Created a service principal for PostgreSQL as root using the Samba
"net ads keytab add" command (helpful hint: leave items off the end of
the net command to see other available options). I created two service
principals, one for the short name and one for the FQDN:

  net ads keytab add postgres/machinename@REALM.COM -U DOMAIN\Administrator
  net ads keytab add postgres/machinename.domain.com@REALM.COM -U
DOMAIN\Administrator

I verified the service principals with the command "net ads keytab
list". They should also end up in the system keytab, which you can
verify with a command like "klist -k /etc/krb5.keytab". (If they
don't, then I probably did "net ads kerberos kinit" at this point.) My
Samba produced six entries; three encryption types for each of the two
principal names.

4. Exported the service principals to a Postgres-specific keytab. I
did this with ktutil. You'll start ktutil and issue a "read_kt
/etc/krb5.keytab" to import the system keytab, then issue delete_entry
to delete all the but the Postgres principals. Use write_kt to write
them to a Postgres-specific location; I used "write_kt
/etc/postgresql/9.3/main/krb5.keytab" where the postgres account will
own the file. I also removed write permissions from this file.

5. In postgres.conf, I set krb_server_keyfile =
'/etc/postgresql/9.3/main/krb5.keytab' and krb_srvname = 'postgres'.
krb_srvname's case didn't seem to matter for me, YMMV.

(I just noticed this "environment" file sitting here. I wonder if I
could use that with MIT Kerberos 1.9 and the KRB5_TRACE variable to
get detailed Kerberos traces. Hmmm....)

6. In pg_hba.conf, I added the line "host all all all gss
include_realm=1". I'm including the realm because I'm in a forest.

7. Restart PostgreSQL.

8. Log into Postgres locally and create a role for your domain self:

  create role "BCrowell@REALM.COM" login inherit;

9. If all went well, and I didn't leave out any steps, on a Windows
machine you should be able to go to the command prompt and do:

  psql --host=machinename.domain.com --username=BCrowell@REALM.COM postgres

...and get in without any password prompts, assuming you got the case
on your username correct. If the case is wrong, Postgres will tell you
what it's supposed to be in the system logs.

10. Patch Npgsql to understand GSSAPI authentication  :P

—Brian

On Tue, Nov 12, 2013 at 9:13 AM, Brian Crowell <brian@fluggo.com> wrote:
>   net ads keytab add postgres/machinename@REALM.COM -U DOMAIN\Administrator
>   net ads keytab add postgres/machinename.domain.com@REALM.COM -U
> DOMAIN\Administrator

D'oh! These should be:

  net ads keytab add postgres/machinename@REALM.COM -U Administrator@REALM.COM
  net ads keytab add postgres/machinename.domain.com@REALM.COM -U
Administrator@REALM.COM

Stupid SAM names.

—Brian

On Mon, Nov 11, 2013 at 11:56 PM, Christian Ullrich
<chris@chrullrich.net> wrote:
>> On Mon, Nov 11, 2013 at 10:51 PM, Brian Crowell <brian@fluggo.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
username case-sensitively when looking up the user info in pg_authid, and if the user logged on to Windows with a name
differingin case from what the database thinks it is, authentication fails. Npgsql sending it always in lower case is
preciselywhat I landed on as a workaround (basically overriding libpq's automatic user name detection in the ODBC
connectionstring by appending a UID option). 

The message I get in the log is "provided user name
(bcrowell@REALM.COM) and authenticated username (BCrowell@REALM.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?

—Brian

* From: Brian Crowell

> On Mon, Nov 11, 2013 at 11:56 PM, Christian Ullrich
> <chris@chrullrich.net> wrote:
> >> On Mon, Nov 11, 2013 at 10:51 PM, Brian Crowell <brian@fluggo.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@REALM.COM) and authenticated username (BCrowell@REALM.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
somethingalong the lines of "DSN=xyz;Option1=foo;Option2=bar", supplementing (or overriding) options from the DSN with
localvalues. 

I used that to supply an explicit "UID" option giving the result of converting the current user name to another format
usingIADsNameTranslate. That works because it pulls the information from the directory rather than just munging the
resultof 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

On Tue, Nov 12, 2013 at 10:03 AM, Christian Ullrich
<chris@chrullrich.net> wrote:
> 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. 

I don't see any reason this can't be put in Npgsql. If the username
isn't supplied, the NpgsqlConnectionStringBuilder guesses it in the
UserName property. I'm going to make it guess correctly.

—Brian

On Tue, Nov 12, 2013 at 5:45 AM, Francisco Figueiredo Jr.
<francisco@npgsql.org> wrote:
> I'm looking forward your patch.
> Npgsql source can be found at github.com/npgsql/Npgsql

I figured out the username issue, and so I've sent a pull request:
https://github.com/npgsql/Npgsql/pull/95

I encountered an issue where connection pooling doesn't distinguish
between Integrated Security users. I tried to fix that. Hopefully it's
ship-shape.

—Brian