Have you tried your tickets on the client machine ? From my (little) understanding, the postgresql server complain that the client initiated the communication with an ticket signed with a different key (kvno 3 vs. 4). Hope it help.
For information here the differences from your setup with mine (debian 10 / AD 2012) :
1) postgresql server not joined in domain
2) keytab generated with
ktpass -out postgres.keytab ^
-princ POSTGRES/debby@dom.local ^
-mapUser DOM\postgres ^
-rndpass ^
-mapOp set ^
-maxpass ^
-crypto AES256-SHA1 ^
-ptype KRB5_NT_PRINCIPAL
Le ven. 26 mai 2023 à 20:35, Jean-Philippe Chenel <jp.chenel@xxxxxxx> a écrit :
Dear Tumasgiu Rossini,
When I do the ktpass command on Windows AD, I can see that there is no other AD account mapped, otherwise it will raise an exception (Failed to set property 'servicePrincipalName').
Here is the klist command:root@SFADAPGDDF02:/# klist -k /etc/postgresql/postgres.keytabKVNO Principal
---- --------------------------------------------------------------------------4 postgres/UBUNTU.ad.corp.com@xxxxxxxxxxx
Windows AD command:PS C:\Users\Administrateur> get-aduser pgsql_ubuntu -properties msDS-KeyVersionNumber
DistinguishedName : CN=pgsql_ubuntu,CN=Managed Service Accounts,DC=ad,DC=corp,DC=comEnabled : TrueGivenName : pgsql_ubuntumsDS-KeyVersionNumber : 4Name : pgsql_ubuntuObjectClass : userObjectGUID : dcaadc3c-2faf-44cf-a558-2a441cca690cSamAccountName : pgsql_ubuntuSID : S-1-5-21-1388463811-2779960163-2428466526-1204Surname :UserPrincipalName : postgres/UBUNTU.ad.corp.com@xxxxxxxxxxx
If I look at the postgresql.log, I saw another kvno number. This one is matching the user trying to connect.
2023-05-26 18:30:08.576 UTC [4033] jp.chenel@template1 LOG: accepting GSS security context failed
2023-05-26 18:30:08.576 UTC [4033] jp.chenel@template1 DETAIL: Unspecified GSS failure. Minor code may provide more information: Request ticket server postgres/sfadapgddf02.ad.sygifcorp.com@xxxxxxxxxxxxxxxx not found in keytab (ticket kvno 3)
Like I said, if I make a new keytab, just changing "-pass postgres", connections will work again. How to change this password ! For security reason, I don't want to let this password.
With best regards,
De : Tumasgiu Rossini <rossini.t@xxxxxxxxx>
Envoyé : 26 mai 2023 12:09
À : Jean-Philippe Chenel <jp.chenel@xxxxxxx>
Objet : Re: PostgreSQL GSSAPI Windows ADHi,
are you sure that there is no other ad account mapped to the postgres/UBUNTU.ad.corp.com@xxxxxxxxxxx principal ?
Also you should check that the kvnos of both your keytab and your ad account matches, with the following commands :
in linux for the keytabklist /path/to/the/keytab
and in Windows for the accountget-aduser <username> -properties msDS-KeyVersionNumber
Le jeu. 25 mai 2023 à 23:51, Jean-Philippe Chenel <jp.chenel@xxxxxxx> a écrit :
Hi,
I've recently updated from PostgreSQL 9.6 to 14 and also ubuntu 16.04 to 22.04.I've made all the installation required for postgresql to connect in GSSAPI authentication to a Windows domain.
Something is going wrong and I don't know why.When I change the mapped user password from "postgres" to anything else, the connection stop to work
Log of postgres:Unspecified GSS failure. Minor code may provide more information: Request ticket server postgres/ubuntu.ad.corp.com@xxxxxxxxxxx not found in keytab (ticket kvno 3)
Here is the ktpass command (Windows AD):
working:ktpass -out postgres.keytab -princ postgres/UBUNTU.ad.corp.com@xxxxxxxxxxx -mapUser AD\pgsql_ubuntu -pass postgres -mapOp add -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPAL
not working:ktpass -out postgres.keytab -princ postgres/UBUNTU.ad.corp.com@xxxxxxxxxxx -mapUser AD\pgsql_ubuntu -pass other_password -mapOp add -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPAL
I put the keytab on the postgres server, the keytab file is referenced in the postgresql.conf file.
Here is the full procedure:
- Create user in AD for postgresql mapping (pgsql_ubuntu), always valid, support AES256
- Create another user for connection testing
- run ktpass command
- put the keytab file on the pg server in /etc/postgresql, chown to postgres and chmod 600
- postgresql.conf krb_server_keyfile = '/etc/postgresql/postgres.keytab'
- pg_hba is configured to connect over gss
- ubuntu server (postgres) is added to domain with this command:
sudo realm join server.ad.corp.com -U Administrateur
I don't know why it works when the password is "postgres" and why I can't change it.
With best regards,