PostgreSQL SSPI authentication - FATAL: 2801: password authentication failed for user "xxx"

Question

I'm pretty new to PostgreSQL, having a fair amount of experience with MS SQL Server. One feature of PostgreSQL that has caught my eye is SSPI authentication, which I hoped would make the transition from MS SQL easier. However, I can't seem to get it to work.

For now, both server and client are running on the same Windows 7 machine, which is not a member of a domain. If I understand correctly, SSPI auth falls back from Kerberos to NTLM, so it should work without a domain - am I right?

When I try to connect (from a .NET application via Npgsql), I get an NpgsqlException with the message: Fatal: 28P01: password authentication failed for user "xxx" The message is a bit puzzling to me because I'm not using password authentication.

My pg_hba.conf:

host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             127.0.0.1/32            sspi

Npgsql connection string:

Server=127.0.0.1;Port=5432;Database=mydb;Integrated Security=true;

What am I doing wrong here? Or is it that it just doesn't work without a domain?

Answer 1

Ok, got it. I can't allow MD5 and SSPI authentication at the same time. So be it There is no "fall-through" mechanism - only the first authentication method that matches the request is attempted. So all that was left to do was to remove the first two lines from pg_hba.conf and create a role with the same name as my Windows login. What's bugging me is that what is obviously a configuration error is silently ignored, not even a warning in the log - is there any reason behind this?