RE: PostgreSQL (linux) configuration with GSSAPI to a Windows domain - Mailing list pgsql-general

Greetings,

* Jean-Philippe Chenel (jp.chenel@LIVE.CA) wrote:
> I'm trying to configure authentication between PostgreSQL database server on linux and Windows Active Directory.
>
> First part of configuration is working but when I'm trying to authenticate from Windows client, it is not working with message: Can't obtain database list from the server. SSPI continuation error. The specified target is unknown or unreachable (80090303)

> On Windows:
>
> Domain is AD.CORP.COM
>
> Host is: WIN.AD.CORP.COM, IP is 192.168.1.173
>
> On Linux (Ubuntu 16.04)
>
> hostname is UBUNTU.ad.corp.com, IP is 192.168.1.143
>
> DNS are configured to reach the AD sytem (.173)
>
> PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Ubuntu 9.6.9-2.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit

That's a rather out of date version of PG. :(  You should update to
9.6.12.

> I've created à service user called POSTGRES and a normal user in AD called ubuntupg.

Did you make sure in AD to check the "User has AES256"?

> Finally I've created the SPN:
>
> setspn -A POSTGRES/UBUNTU.ad.corp.com POSTGRES

I've not had to do this in the past..

> Generated the keytab to put on the linux server:
>
> ktpass -out postgres.keytab -princ POSTGRES/UBUNTU.ad.corp.com@AD.CORP.COM -mapUser POSTGRES -pass 'thepassword' -crypto all -ptype KRB5_NT_PRINCIPAL

This looks mostly correct.

> On the linux /etc/krb5.conf:
>
> [libdefaults]
>   debug=true
>   default_realm = AD.CORP.COM
>   dns_lookup_realm = false
>   dns_lookup_kdc = false
>   ticket_lifetime = 24h
>   renew_lifetime = 7d
>   forwardable = true
>
> [realms]
>   AD.CORP.COM = {
>
>     kdc = WIN.AD.CORP.COM
>   }
>
> [domain_realm]
>   ad.corp.com = AD.CORP.COM
>
>   .ad.corp.com = AD.CORP.COM

That seems ok.

> Making this command work and klist return a ticket:
>
> kinit -V -k -t /etc/postgresql/9.6/main/postgres.keytab POSTGRES/UBUNTU.ad.corp.com@AD.CORP.COM
>
> klist -k /etc/postgresql/9.6/main/postgres.keytab
>
> POSTGRES/UBUNTU.ad.corp.com@AD.CORP.COM

You should make sure to use klist to show the KVNO and the encryption
types too (usually -e or -v works, depending on what version of Kerberos
you're using).

What does the klist on the client look like, with verbose/enctype info
shown?

> Here is the added onfiguration to postgresql.conf
>
> krb_server_keyfile = '/etc/postgresql/9.6/main/postgres.keytab'

You might try enabling case-insensitive princs in PG using
krb_caseins_users too.

> Here is the configuration of pg_hba.conf
>
> host    all              all            0.0.0.0/0 gss
>
> Up to here, all is working as expected, kinit with ubuntupg is also working well. ubuntupg and ubuntupg@ad.corp.com is also created on the database. The probleme is when I try, from a Windows client, connecting to the DB.

So you're able to get in using Kerberos on the Ubuntu system?

> psql.exe -h 192.168.1.143 -U ubuntupg
>
> Can't obtain database list from the server. SSPI continuation error. The specified target is unknown or unreachable (80090303)
>
> PostgreSQL log file show:
>
> 2019-02-28 14:02:54.178 EST [6747] [unknown]@[unknown] LOG:  00000: connection received: host=192.168.1.176 port=57254
> 2019-02-28 14:02:54.178 EST [6747] [unknown]@[unknown] LOCATION:  BackendInitialize, postmaster.c:4188
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg FATAL:  28000: GSSAPI authentication failed for user "ubuntupg"
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg DETAIL:  Connection matched pg_hba.conf line 92: "host    all              all            0.0.0.0/0 gss"
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg LOCATION:  auth_failed, auth.c:307
>
> psql.exe -h 192.168.1.143 -U ubuntupg@ad.corp.com
>
> 2019-02-28 14:06:35.992 EST [6866] [unknown]@[unknown] LOG:  00000: connection received: host=192.168.1.176 port=57282
>
> 2019-02-28 14:06:35.992 EST [6866] [unknown]@[unknown] LOCATION:  BackendInitialize, postmaster.c:4188
>
> 2019-02-28 14:06:36.148 EST [6866] ubuntupg@ad.corp.com@ubuntupg@ad.corp.com FATAL:  28000: GSSAPI authentication failed for user "ubuntupg@ad.corp.com"
>
> 2019-02-28 14:06:36.148 EST [6866] ubuntupg@ad.corp.com@ubuntupg@ad.corp.com DETAIL:  Connection matched pg_hba.conf line 96: "host    all              all            0.0.0.0/0 gss"
>
> 2019-02-28 14:06:36.148 EST [6866] ubuntupg@ad.corp.com@ubuntupg@ad.corp.com LOCATION:  auth_failed, auth.c:307

Have you checked to make sure that the time on the server and the time
on the client and the time on the AD server are all more-or-less in sync
(within 5 minutes)?

There is also the krbsrvname option which might be useful.

Thanks!

Stephen