BUG #13854: SSPI authentication failure: wrong realm name used

From: chris(at)chrullrich(dot)net
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13854: SSPI authentication failure: wrong realm name used
Date: 2016-01-08 00:25:26
Message-ID: 20160108002526.16053.79037@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

The following bug has been logged on the website:

Bug reference: 13854
Logged by: Christian Ullrich
Email address: chris(at)chrullrich(dot)net
PostgreSQL version: 9.5.0
Operating system: Windows
Description:

According to the release notes, the default for the "include_realm" option
in SSPI authentication was changed from off to on in 9.5 for improved
security. However, the authenticated user name, with the option enabled, now
includes the NetBIOS domain name, *not* the Kerberos realm name:

[chul(at)itdb 2016-01-08 00:31:56 CET] ([unknown]) LOG: provided user name
(chul) and authenticated user name (chul(at)LOCAL-DOM) do not match
[chul(at)itdb 2016-01-08 00:31:56 CET] ([unknown]) FATAL: SSPI authentication
failed for user "chul"
[chul(at)itdb 2016-01-08 00:31:56 CET] ([unknown]) DETAIL: Connection matched
pg_hba.conf line 101: "host all all
192.168.0.1/32 sspi"

"LOCAL-DOM" is the domain short name/NetBIOS name. The realm name is
(typically, and in this case) the domain DNS name in uppercase. The string
used for the realm name is retrieved from the LookupAccountSid() function,
which will always return the short name:

(Python 3.5 on a Windows 10 client in the same domain):
>>> from win32security import LookupAccountName, LookupAccountSid
>>> sid = LookupAccountName(None, "chul")[0]
>>> LookupAccountSid(None, sid)
('chul', 'LOCAL-DOM', 1)

Login is successful if I add "include_realm=0 krb_realm=LOCAL-DOM" to
pg_hba.conf. If I use the actual Kerberos realm name instead, I simply get
"SSPI authentication failed" with no further information in the log.

I am aware of the option of using pg_ident.conf to map authenticated user
names with realm to bare database role names, but I would have to put the
wrong realm name string in there as well, so it is not a fix.

A possible fix might be to convert the user name/domain name retrieved from
LookupAccountSid() using TranslateName()/IADsNameTranslate to get the
Kerberos UPN, which includes the actual realm name. There may be
compatibility issues with that, because the first part of the UPN need not
equal sAMAccountName (the logon user name). Apparently [1] you can also get
an explicit mapping (look up dnsRoot by nETBIOSName) from AD, but whether
that is the correct approach, I don't know.

My distribution is from
<http://get.enterprisedb.com/postgresql/postgresql-9.5.0-1-windows-x64-binaries.zip>.

[1] https://stackoverflow.com/questions/12606466

--
Christian

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jim Nasby 2016-01-08 00:33:51 BEGINNER HACKERS: array_remove(anyarray, anyarray)
Previous Message Michael Paquier 2016-01-08 00:04:45 Re: Re: [COMMITTERS] pgsql: Windows: Make pg_ctl reliably detect service status

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2016-01-08 00:53:21 Re: BUG #13854: SSPI authentication failure: wrong realm name used
Previous Message Greg Stark 2016-01-07 23:50:55 Re: BUG #13849: Need a parameter added similar to "edb_stmt_level_tx"