From: | Jean-Philippe Chenel <jp(dot)chenel(at)LIVE(dot)CA> |
---|---|
To: | Stephen Frost <sfrost(at)snowman(dot)net> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | RE: PostgreSQL (linux) configuration with GSSAPI to a Windows domain |
Date: | 2019-03-01 20:52:00 |
Message-ID: | BYAPR03MB448502649DB009D7FE0DE405FD760@BYAPR03MB4485.namprd03.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)ad(dot)corp(dot)com(at)AD(dot)CORP(dot)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 <sfrost(at)snowman(dot)net>
Envoyé : 1 mars 2019 11:54
À : Jean-Philippe Chenel
Cc : pgsql-general(at)lists(dot)postgresql(dot)org
Objet : 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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2019-03-01 21:00:50 | Re: PostgreSQL (linux) configuration with GSSAPI to a Windows domain |
Previous Message | Jean-Philippe Chenel | 2019-03-01 20:46:20 | RE: PostgreSQL (linux) configuration with GSSAPI to a Windows domain |