Dear Stephen,
Thank you very much for your help.
I think I was missing an important command in the equation.
sudo realm --verbose join ad.corp.com --user=Administrateur
--user-principal=postgres/ubuntu.ad.corp....@ad.corp.com
The Linux server as joint the AD and now, psql connection work very well from
the clients!
Also, I've enabled the krb_caseins_users parameter and the time synchronization
to the AD.
With best regards,
________________________________
De : Stephen Frost <sfr...@snowman.net>
Envoyé : 1 mars 2019 11:54
À : Jean-Philippe Chenel
Cc : pgsql-general@lists.postgresql.org
Objet : Re: PostgreSQL (linux) configuration with GSSAPI to a Windows domain
Greetings,
* Jean-Philippe Chenel (jp.che...@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....@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....@ad.corp.com
>
> klist -k /etc/postgresql/9.6/main/postgres.keytab
>
> POSTGRES/ubuntu.ad.corp....@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 ubunt...@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 ubunt...@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] ubunt...@ad.corp.com@ubunt...@ad.corp.com
> FATAL: 28000: GSSAPI authentication failed for user "ubunt...@ad.corp.com"
>
> 2019-02-28 14:06:36.148 EST [6866] ubunt...@ad.corp.com@ubunt...@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] ubunt...@ad.corp.com@ubunt...@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
