Configuring for single sign-on using SSPI on Windows

From PostgreSQL wiki

Revision as of 09:56, 12 October 2012 by Chrullrich (Talk | contribs)

Jump to: navigation, search

PostgreSQL supports single sign-on using SSPI (what other databases call "Windows Integrated Authentication"). This is only possible, however, if you are in a Windows domain environment, because a Kerberos KDC is required.

I assume that you used the one-click installer to set up PostgreSQL on your server.

PostgreSQL 9.1

After the installation, PostgreSQL is running under a local account named "postgres". First, you have to change that, because SSPI requires that the service uses a domain account:

  1. Stop the service.
  2. Create a user account in your domain.
  3. Change the ownership of the data directory and everything within it to the new account, and grant it full control.
  4. Change the service log on credentials so the service uses your domain account.
  5. Start the service to see if everything works. Try logging on as before, create a database, drop some tables, call pg_switch_xlog(). If you can log on at all, just about anything that goes wrong later indicates missing permissions on the data files.

Now you have to tell Active Directory that your service account is running the database. For that, you add a Service Principal Name (SPN) to your service account. There is a command-line tool for that, called setspn.exe:

setspn -S POSTGRES/fully.qualified.domain.name DOMAIN\service_account_name

Alternatively, you can just change the attribute (servicePrincipalName) directly using any tool that can modify the directory, including the "Users and Computers" MMC, or the equivalent tool in more recent versions of Windows Server, or ADSIedit.

In my experience (which may be incomplete), you also have to make sure that all your clients use the full host name, because otherwise they may not get service tickets. Adding a second SPN with just the host name without the domain ("fully" in the above example) may help with that, but using the full name is more secure anyway.

The last step is to allow SSPI logon to the database. For that, you need to create some login roles that have the same name as your domain users, and an entry in pg_hba.conf with authentication method "sspi". Remember that only the first entry in pg_hba.conf that matches database, client address, and claimed user name is used.

PostgreSQL 9.2

In version 9.2, the one-click installer does not create the local "postgres" account anymore, but uses the built-in "NETWORK SERVICE" account instead. While you can still follow all the instructions above just the same, there is a simpler, but less secure, alternative.

To use it, add the Service Principal Name to the computer account; no further changes are needed. If your server is called "dbserver", you can use this command:

setspn -S POSTGRES/fully.qualified.domain.name dbserver

Be aware that running the database under the shared account allows all other services using the same account to read and modify the database files directly. If this is not acceptable in your environment, follow the steps for version 9.1 above to use a dedicated service account.

Personal tools