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.keytab KVNO Principal ---- -------------------------------------------------------------------------- 4 postgres/ubuntu.ad.corp....@ad.corp.com 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=com Enabled : True GivenName : pgsql_ubuntu msDS-KeyVersionNumber : 4 Name : pgsql_ubuntu ObjectClass : user ObjectGUID : dcaadc3c-2faf-44cf-a558-2a441cca690c SamAccountName : pgsql_ubuntu SID : S-1-5-21-1388463811-2779960163-2428466526-1204 Surname : UserPrincipalName : postgres/ubuntu.ad.corp....@ad.corp.com 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....@ad.sygifcorp.com 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 <rossin...@gmail.com> Envoyé : 26 mai 2023 12:09 À : Jean-Philippe Chenel <jp.che...@live.ca> Objet : Re: PostgreSQL GSSAPI Windows AD Hi, are you sure that there is no other ad account mapped to the postgres/ubuntu.ad.corp....@ad.corp.com<mailto:ubuntu.ad.corp....@ad.corp.com> 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 keytab klist /path/to/the/keytab and in Windows for the account get-aduser <username> -properties msDS-KeyVersionNumber Le jeu. 25 mai 2023 à 23:51, Jean-Philippe Chenel <jp.che...@live.ca<mailto:jp.che...@live.ca>> 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....@ad.corp.com<mailto:ubuntu.ad.corp....@ad.corp.com> not found in keytab (ticket kvno 3) Here is the ktpass command (Windows AD): working: ktpass -out postgres.keytab -princ postgres/ubuntu.ad.corp....@ad.corp.com<mailto:ubuntu.ad.corp....@ad.corp.com> -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....@ad.corp.com<mailto:ubuntu.ad.corp....@ad.corp.com> -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: 1. Create user in AD for postgresql mapping (pgsql_ubuntu), always valid, support AES256 2. Create another user for connection testing 3. run ktpass command 4. put the keytab file on the pg server in /etc/postgresql, chown to postgres and chmod 600 5. postgresql.conf krb_server_keyfile = '/etc/postgresql/postgres.keytab' 6. pg_hba is configured to connect over gss 7. ubuntu server (postgres) is added to domain with this command: sudo realm join server.ad.corp.com<http://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,
