Thread: PostgreSQL GSSAPI Windows AD
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@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.com@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.com@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:
- 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,
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.com@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.com@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.com@AD.SYGIFCORP.COM not found in keytab (ticket kvno 3)
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@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 <rossini.t@gmail.com>
Envoyé : 26 mai 2023 12:09
À : Jean-Philippe Chenel <jp.chenel@live.ca>
Objet : Re: PostgreSQL GSSAPI Windows AD
Envoyé : 26 mai 2023 12:09
À : Jean-Philippe Chenel <jp.chenel@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.com@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.chenel@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 workLog of postgres:Unspecified GSS failure. Minor code may provide more information: Request ticket server postgres/ubuntu.ad.corp.com@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.com@AD.CORP.COM -mapUser AD\pgsql_ubuntu -pass postgres -mapOp add -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPALnot working:ktpass -out postgres.keytab -princ postgres/UBUNTU.ad.corp.com@AD.CORP.COM -mapUser AD\pgsql_ubuntu -pass other_password -mapOp add -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPALI 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 AdministrateurI don't know why it works when the password is "postgres" and why I can't change it.With best regards,
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@live.ca> 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@AD.CORP.COMWindows AD command:PS C:\Users\Administrateur> get-aduser pgsql_ubuntu -properties msDS-KeyVersionNumberDistinguishedName : 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@AD.CORP.COMIf 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@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 <rossini.t@gmail.com>
Envoyé : 26 mai 2023 12:09
À : Jean-Philippe Chenel <jp.chenel@live.ca>
Objet : Re: PostgreSQL GSSAPI Windows ADHi,are you sure that there is no other ad account mapped to the postgres/UBUNTU.ad.corp.com@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 keytabklist /path/to/the/keytaband in Windows for the accountget-aduser <username> -properties msDS-KeyVersionNumberLe jeu. 25 mai 2023 à 23:51, Jean-Philippe Chenel <jp.chenel@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 workLog of postgres:Unspecified GSS failure. Minor code may provide more information: Request ticket server postgres/ubuntu.ad.corp.com@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.com@AD.CORP.COM -mapUser AD\pgsql_ubuntu -pass postgres -mapOp add -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPALnot working:ktpass -out postgres.keytab -princ postgres/UBUNTU.ad.corp.com@AD.CORP.COM -mapUser AD\pgsql_ubuntu -pass other_password -mapOp add -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPALI 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 AdministrateurI don't know why it works when the password is "postgres" and why I can't change it.With best regards,