SSPI authentication about npgsql HOT 24 CLOSED

Thanks @ekolb ! We will have a look at this. Your feedback is very good. It will help us very much to fix this problem.

from npgsql.

Hi! I am having the same issue and have resolved it by the modification suggested by @ekolb .

Thanks :)

from npgsql.

Hi all!

This is now fixed in master branch. Please give it a try and let me know if it works ok for you.

from npgsql.

Hi @franciscojunior, I just tried compiling from master and it works, but it seems to be a lot slower than the .dll I built from 2.0.14.3 with the changes proposed here (Both are Release builds for .NET 4.0)

from npgsql.

Hmmmm, I think this is caused by an LDAP query the new code does.
I'll check with @fluggo if we can make something about it.

Can you do a test for me, please? Can you tell what is the UserName being used? This can be obtained with this code:

new NpgsqlConnectionStringBuilder("Server=APserver;Port=5432;Database=postgres; Integrated Security= true;").UserName;

Thanks in advance.

from npgsql.

It's "simon.sotak", my company username.

from npgsql.

Yes, the LDAP lookup for the user name is likely to make new connections slow. I was waiting for a flash of inspiration on how to overcome that.

As luck would have it, I just thought of something. I could cache the UPN by SID. Let me whip up a few tests and see what I come up with.

from npgsql.

@fluggo , I was thinking about the following scenario: @the21st doesn't seem to be using ldap server, or at least didn't need one to make SSPI connection work. So, I was thinking about another approach where we could skip the ldap query.
I'm asking that because if the slow down was caused by timeout trying to connect to an non-existent ldap server, even if you use a cache strategy, it will continue to be slow.

What do you think?

from npgsql.

And by skip the ldap query, I mean we could make this query optional and only usable by those who really want/need to use it.

from npgsql.

Like I said before, I think he succeeded because his username is all lowercase. Had his username included capital letters, he probably would have needed the LDAP query.

The LDAP query is basically necessary because of a Windows issue of not hanging on to the user's UPN with its proper, Active Directory-sanctioned case. So in the most common scenario of using integrated security to connect from a Windows domain machine, this is the right thing to do, and it should always work. Disabling it by default would mean breaking integrated security for anyone with a capital letter in their username.

The code probably won't work in two scenarios I can think of: connecting from a non-domain Windows machine (because the machine probably won't understand an LDAP://rootDSE query, I might be able to address this), and connecting using Kerberos from a Linux machine (because the code assumes the availability of a WindowsIdentity and SID).

Short of a general solution that includes those two scenarios, the question comes down to who do you want to inconvenience more: those using Integrated Security from a non-domain or non-Windows machine, or Windows users with capital letters in their UPN?

from npgsql.

Like I said before, I think he succeeded because his username is all lowercase. Had his username included capital letters, he probably would have needed the LDAP query.

I see. I don't have a test setup for integrated security so I didn't know the capital letter in the username would make such a difference.

Short of a general solution that includes those two scenarios, the question comes down to who do you want to inconvenience more: those using Integrated Security from a non-domain or non-Windows machine, or Windows users with capital letters in their UPN?

Good question. I think the most common usage of integrated security is from domain based windows users. So, I think users on non domain or non-windows machines would face a little delay when trying to use integrated security.
Would it be possible that we add another check for non windows machines? This way we could save the ldap query which wouldn't work anyway.

from npgsql.

@the21st , can you merge the latest @fluggo patch #172 and check if the slowdown you are facing is diminished? Thanks in advance.

from npgsql.

As one would expect with a cache, the slowdown is present during the first request and eliminated in the subsequent queries. I assume the slowdown will emerge once every 30 seconds? Which, in the case I do a query once per minute, can be painful :)

from npgsql.

Hmm. Well, I can increase it. How long is it taking you to connect? Are we talking multiple seconds? You might actually be encountering an exception in the LDAP code, and I wish there was a good way to report it; can you ask your debugger to stop on all exceptions and see if there's an exception thrown there?

I'm sitting on the same network as my domain servers, so in my test I was doing 500 straight connection attempts, and I went from doing that in eight seconds to doing it in two.

from npgsql.

@franciscojunior I can see what it will take to get this working from a non-domain Windows machine. I hadn't planned to, but I don't want this to be a major issue.

from npgsql.

I tried stopping on all exceptions, but none were thrown.

The first connection takes about 1.5 secs. The thing is, I am using Npgsql in a stateless app that creates a new connection every time the user requests a save/load from a DB. Which can happen at any time interval. So that's a pretty specific use, I don't want you to accommodate the library to my needs.

I am using the .dll I compiled from the changes suggested by the issue creator and everything works just fine.

from npgsql.

Ahh. Well, I chose 30 seconds just because it was the smallest effective number I could think of, but honestly, the SID to UPN mapping is really, really unlikely to change. I could put the cache timeout at 24 hours and fix your use case. I think I'll do that.

from npgsql.

@franciscojunior I can see what it will take to get this working from a non-domain Windows machine. I hadn't planned to, but I don't want this to be a major issue.

Ahh, ok. I thought that on a non windows machine, only the username got from WindowsIdentity.GetCurrent() would be enough.

But for while, I think the current strategy will work for most of the use cases. We can think about how to make it work on non-windows machines in the future.

from npgsql.

@franciscojunior Welp. Turns out that trying to get this to work (automagically, anyways) from a non-domain Windows machine is a whole other can of worms.

So let's start from the top.

1. PostgreSQL expects a user name in its startup packet. It throws a fit if it's missing. (I just tried.) So we need to specify a user name.
2. GSSAPI and SSPI specify an entire Kerberos ticket as their password, and that ticket includes the Kerberos user name.
3. PostgreSQL expects these to match, and the match is case-sensitive.
4. A programmer coming from the normal world of integrated security expects to just be able to set Integrated Security=true, and the rest will just work. Windows knows the user name, right?
5. Windows is Windows and doesn't care about the user name's case, so it doesn't care about getting it right when it create the user's token. I've heard the case matches what the user typed at the login prompt, I don't know.

So there's the rationale for the LDAP query. We need to contact the domain and ask it what the user's official UPN is, case and all.

Now, if you're on a Windows machine that isn't on the domain, you can't actually log in as a domain user. You can't even really impersonate one. What you can do is attach new network credentials to your user token using the LOGON32_LOGON_NEW_CREDENTIALS logon type (same as using runas /netonly). We do this in our in-house apps to enable them to run on users' home machines against SQL Server. Works like a charm.

However, those credentials are not available to the running program. I cannot discover what your LOGON32_LOGON_NEW_CREDENTIALS are, because I am not actually running as that user. I can construct and send a Kerberos ticket to Postgres, and this will contain your Kerberos user name, BUT I will not be able to determine the right user name to send in the Postgres startup packet.

So really, the correct solution here is for Postgres to not require the user name in the startup packet when using SSPI or GSSAPI (or at least ignore it). It should discover the name from the Kerberos ticket we send. That will always be accurate, and it doesn't require us to do all this extra work. It would work just like integrated security on SQL Server.

That's the long, painful story. But there is a workaround: if you're on a non-domain machine, specify the user name. Otherwise, doing the right thing requires a server-side change.

from npgsql.

Filed this as bug 9337 with Postgres.

from npgsql.

That's the long, painful story. But there is a workaround: if you're on a non-domain machine, specify the user name. Otherwise, doing the right thing requires a server-side change.

Wow! Thanks for the thoroughly explanation of the situation, Brian! I didn't know anything about that.

I'll add your explanation to the documentation of integrated security. But I think integrated security on domain-windows machines will cover the majority of the use cases.

from npgsql.

@ekolb , can you check if current code fixes the issue for you too, please? This way we can close this issue. Thanks in advance.

from npgsql.

@ekolb , if you still find problems with SSPI connections, please let us know.
#170 fixed this problem. Closing now.

from npgsql.

Thanks.
I am OK with closing.

Emanuel

From: Francisco Figueiredo Jr. [mailto:[email protected]]
Sent: Sonntag, 17. August 2014 21:06
To: npgsql/Npgsql
Cc: Emanuel Kolb
Subject: Re: [Npgsql] SSPI authentication (#162)

@ekolbhttps://github.com/ekolb , if you still find problems with SSPI connections, please let us know.
#170#170 fixed this problem. Closing now.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/162#issuecomment-52431427.

from npgsql.