Re: PostgreSQL GSSAPI Windows AD

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(at)dom(dot)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(dot)chenel(at)live(dot)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(at)SFADAPGDDF02:/# klist -k /etc/postgresql/postgres.keytab
> KVNO Principal
> ----
> --------------------------------------------------------------------------
> 4 postgres/UBUNTU(dot)ad(dot)corp(dot)com(at)AD(dot)CORP(dot)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(dot)ad(dot)corp(dot)com(at)AD(dot)CORP(dot)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(dot)chenel(at)template1 LOG: accepting
> GSS security context failed
> 2023-05-26 18:30:08.576 UTC [4033] jp(dot)chenel(at)template1 DETAIL:
> Unspecified GSS failure. Minor code may provide more information: Request
> ticket server postgres/sfadapgddf02(dot)ad(dot)sygifcorp(dot)com(at)AD(dot)SYGIFCORP(dot)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(dot)t(at)gmail(dot)com>
> *Envoyé :* 26 mai 2023 12:09
> *À :* Jean-Philippe Chenel <jp(dot)chenel(at)live(dot)ca>
> *Objet :* Re: PostgreSQL GSSAPI Windows AD
>
> Hi,
>
> are you sure that there is no other ad account mapped to the postgres/
> UBUNTU(dot)ad(dot)corp(dot)com(at)AD(dot)CORP(dot)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(dot)chenel(at)live(dot)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(dot)ad(dot)corp(dot)com(at)AD(dot)CORP(dot)COM not found in keytab
> (ticket kvno 3)
>
> Here is the ktpass command (Windows AD):
>
> working:
> ktpass -out postgres.keytab -princ postgres/UBUNTU(dot)ad(dot)corp(dot)com(at)AD(dot)CORP(dot)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(dot)ad(dot)corp(dot)com(at)AD(dot)CORP(dot)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,
>
>