I've been trying to update from PostgreSQL v9.4.17 to PostgreSQL v10.4 in my Windows 10 Enterprise 2016 LTSB. I used to connect to server using sspi authentication while in 9.4.17 and it worked perfectly. I used the same pg_hba.conf file for PostgreSQL v10.4, but I repeatedly got FATAL: SSPI authentication failed for user "administrator".
administrator is my windows logged in user.
Please let me know if I have done any mistake in my setting.
My pg_hba.conf file is given below:
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
#host all all 127.0.0.1/32 md5
#host all all 0.0.0.0/0 sspi
host all all 127.0.0.1/32 sspi
# IPv6 local connections:
#host all postgres ::1/128 md5
#host all all ::0/0 sspi
host all all ::1/128 sspi
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host replication all 127.0.0.1/32 md5
#host replication all ::1/128 md5
Your pg_hba.conf looks fine to me. However, I would request you to check your SSPI configuration options for upgraded PostgreSQL-10.4.
The following configuration options are supported for SSPI:
If set to 0, the realm name from the authenticated user principal is stripped off before being passed through the username mapping. This is discouraged and is primarily available for backward compatibility, as it is not secure in multi-realm environments unless krb_realm is also used. It is recommended to leave include_realm set to the default (1) and to provide an explicit mapping in pg_ident.conf to convert principal names to PostgreSQL user names.
If set to 1, the domain's SAM-compatible name (also known as the NetBIOS name) is used for the include_realm option. This is the default. If set to 0, the true realm name from the Kerberos user principal name is used.
Do not disable this option unless your server runs under a domain account (this includes virtual service accounts on a domain member system) and all clients authenticating through SSPI are also using domain accounts, or authentication will fail.
If this option is enabled along with compat_realm, the username from the Kerberos UPN is used for authentication. If it is disabled (the default), the SAM-compatible username is used. By default, these two names are identical for new user accounts.
Note that libpq uses the SAM-compatible name if no explicit username is specified. If you use libpq or a driver based on it, you should leave this option disabled or explicitly specify the username in the connection string.
Allows for mapping between system and database usernames. For a SSPI/Kerberos principal, such as username@EXAMPLE.COM (or, less commonly,username/hostbased@EXAMPLE.COM), the username used for mapping is username@EXAMPLE.COM (or username/hostbased@EXAMPLE.COM, respectively), unless include_realm has been set to 0, in which case username (or username/hostbased) is what is seen as the system username when mapping.
Sets the realm to match user principal names against. If this parameter is set, only users of that realm will be accepted. If it is not set, users of any realm can connect, subject to whatever username mapping is done.