Configuring for single sign-on using SSPI on Windows

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. SSPI also works for authentication of users making connections to localhost on a standalone Windows computer.

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

Configure the service

If you're using PostgreSQL on a domain, you must set the service principal name on the account or computer PostgreSQL is running as.

This is not required if you're simply using SSPI for localhost-to-localhost authentication on a standalone computer; skip straight to the pg_hba.conf setup in that case.

For PostgreSQL 9.2 and above on a domain: set service principal name for computer account

In version 9.2 and above the PostgreSQL installer sets the PostgreSQL service up to run in the NETWORK SERVICE account by default. You can set PostgreSQL up under a domain account and follow the more secure, but more complex, instructions in "Domain account" below, but there's a simple (albeit less secure) alternative that will work for many.

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.

For all PostgreSQL versions on a domain: 'postgres' as a domain account

After the installation, PostgreSQL 9.1 or below is running under a local account named "postgres". 9.2 or above instead install into NETWORKSERVICE by default.

If you want to use SSPI securely access a domain, you need to change the service to run under a domain account 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.

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.

Add SSPI entries in pg_hba.conf

For either configuration, you must finally require SSPI logon to the database in pg_hba.conf.

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.

Optionally creation of login roles and groups can be automated with pg-ldap-sync.

The pg_ident.conf file may be used to configure username mappings if your PostgreSQL usernames aren't exactly the same as your Windows user names.