Request a Demo
Home   >   mailing lists

Re: PostgreSQL GSSAPI Windows AD - Mailing list pgsql-general

From Jean-Philippe Chenel
Subject Re: PostgreSQL GSSAPI Windows AD
Date
Msg-id DS7PR05MB7304FAF926E6F584D5F405A2FD479@DS7PR05MB7304.namprd05.prod.outlook.com
Whole thread Raw
In response to PostgreSQL GSSAPI Windows AD  (Jean-Philippe Chenel <jp.chenel@LIVE.CA>)
Responses Re: PostgreSQL GSSAPI Windows AD
List pgsql-general
Tree view
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)

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
 
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 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:
  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 -U Administrateur

I don't know why it works when the password is "postgres" and why I can't change it.

With best regards,

pgsql-general by date:

Previous
From: Ron
Date:
Subject: Re: DB migration : Sybase to Postgres
Next
From: Rajiv Harlalka
Date:
Subject: Suggestions on pg_statviz Extension