Re: PostgreSQL (linux) configuration with GSSAPI to a Windows domain

Greetings,

* Jean-Philippe Chenel (jp(dot)chenel(at)LIVE(dot)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(dot)ad(dot)corp(dot)com(at)AD(dot)CORP(dot)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(dot)ad(dot)corp(dot)com(at)AD(dot)CORP(dot)COM
>
> klist -k /etc/postgresql/9.6/main/postgres.keytab
>
> POSTGRES/UBUNTU(dot)ad(dot)corp(dot)com(at)AD(dot)CORP(dot)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(at)ad(dot)corp(dot)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](at)[unknown] LOG: 00000: connection received: host=192.168.1.176 port=57254
> 2019-02-28 14:02:54.178 EST [6747] [unknown](at)[unknown] LOCATION: BackendInitialize, postmaster.c:4188
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg(at)ubuntupg FATAL: 28000: GSSAPI authentication failed for user "ubuntupg"
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg(at)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(at)ubuntupg LOCATION: auth_failed, auth.c:307
>
> psql.exe -h 192.168.1.143 -U ubuntupg(at)ad(dot)corp(dot)com
>
> 2019-02-28 14:06:35.992 EST [6866] [unknown](at)[unknown] LOG: 00000: connection received: host=192.168.1.176 port=57282
>
> 2019-02-28 14:06:35.992 EST [6866] [unknown](at)[unknown] LOCATION: BackendInitialize, postmaster.c:4188
>
> 2019-02-28 14:06:36.148 EST [6866] ubuntupg(at)ad(dot)corp(dot)com@ubuntupg(at)ad(dot)corp(dot)com FATAL: 28000: GSSAPI authentication failed for user "ubuntupg(at)ad(dot)corp(dot)com"
>
> 2019-02-28 14:06:36.148 EST [6866] ubuntupg(at)ad(dot)corp(dot)com@ubuntupg(at)ad(dot)corp(dot)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(at)ad(dot)corp(dot)com@ubuntupg(at)ad(dot)corp(dot)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