GSSAPI server side on Linux, SSPI client side on Windows

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

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

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

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

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

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

* 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

--
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 5, 2013 at 2:35 PM, Christian Ullrich <chris@chrullrich.net>wrote:

* 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.

Did you make your changes in the NpgsqlState.cs file?

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.

I didn't write SSPI support code so I'm sorry I'm not being able to provide
you with a much better explanation about this problem.

I hope it helps.

--
Christian

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

--
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://gplus.to/franciscojunior
http://fxjr.blogspot.com
http://twitter.com/franciscojunior

* 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

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

* 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

--
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 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

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

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

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

* 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 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).

* 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 valid for the client SSPI to send a GSSAPI token to a server using the Negotiate package (instead of going through SPNEGO to 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 tradition that everyone quotes at everyone else. I have been using SSPI and GSSAPI since the days of Windows 2000, and it has 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 what the 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 Windows and others is kind of my hobby), but the sooner, the better.

--
Christian

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

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.

On Tue, Nov 12, 2013 at 6:56 AM, Christian Ullrich <chris@chrullrich.net>wrote:

* 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 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).

Yes, windows is really annoying in this regard. It preserves what you
*logged in* with as your username - not what's stored in Active Directory
or the SAM.

* 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 valid for the client SSPI to
send a GSSAPI token to a server using the Negotiate package (instead of
going through SPNEGO to arrive at the same protocol).

I believe it should be ok, yes.

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 tradition that everyone quotes at
everyone else. I have been using SSPI and GSSAPI since the days of Windows
2000, and it has always worked quite well without it.

I think it also depends on which kerberos libraries you have on each end.
I've certainly had a lot of issues with it in the past, with Windows 2000
AD controllers and I think also Windows 2003.

"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 what the 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 Windows and others is kind of
my hobby), but the sooner, the better.

+1 :)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

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

--
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 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

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

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?

—Brian

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

* 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 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

--
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 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

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