SSO on Postgres with Active Directory : troubleshooting

October 22, 2019
Administration PostgreSQL

Single Sign On (SSO) is a great security feature because if users don’t have to create many accounts and manage several passwords, they will agree on using a complex password more easily (or at least, we hope they do).

The thing is, most of the time, companies use Active Directory to manage their accounts but Postgres usually works on Linux… And even if these two worlds can work together, it’s difficult to find people that master both environments.

Note that I’m not a system administrator and I’m not an active directory expert. I just want to write down some things I learnt by working with a customer on trying to use Postgres GSSAPI authentication with an Active Directory.

The 0x13 warning (Failed to set property ‘servicePrincipalName’)

This warning is issued by the ktpass tool. This tool will create for you the keytab file you’ll need to set up your Kerberos authentication.

This warning can be raised for several reasons. Here are the thre most common:

If the user has already a mapping

Maybe it’s just normal and you can just ignore that warning. But maybe it’s not normal and you want to change it.

You may use the following command to understand the problem better and/or fix it:

That command will list the mapping you already have:

setspn -Q host/<principal name>

This command will delete an existing mapping

setspn -d host/<principal name> <user name>

If it’s a permission issue

Simply retry with administrator permissions and you should be fine.

If it’s a syntax problem

It seems that the old syntax was somethimg like user/host@DOMAIN and the new syntax is user@host@DOMAIN. As I’m not an expert, I’m sorry that I’m not abble to explain when each syntax should be used but I can advise you to try with the other syntax,just in case.

The “kinit: Preauthentication failed while getting initial credentials” error

If you find this error, it might be that the “This account supports Kerberos AES 256 bit encryption” checkbox for your Active directory account wasn’t checked. You may check it afterwards, but as RC4 passwords are not salted whereas the AES one is, you’ll need to recreate your keytab file.

You can check your encryption using the command ktlist -e to show the encryption in your file.

If this doesn’t do the trick, it can’t hurt to check your keytab file is the right one (yes, it happens to everyone, even the most experienced ones).

At last, you might find more precisions on what’s causing this error in your postgresql logfile. If it’s a kvno-number-missmatch kind of error, you may try the kvno service/host@REALM command to display the kvno.

Managing conf files

September 7, 2019
Administration PostgreSQL

Interacting with Postgres community

March 25, 2019
PostgreSQL Hacking

Adding your patch to the next commitfest

January 31, 2019
PostgreSQL Hacking