Configuring for single sign-on using SSPI on Windows

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
Line 1: Line 1:
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.
+
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.
 
I assume that you used the one-click installer to set up PostgreSQL on your server.
  
== PostgreSQL 9.1 ==
+
== PostgreSQL 9.2 and above: set service principal name for computer account ==
  
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:
+
In version 9.2 and above the PostgreSQL installer sets the PostgreSQL service up to run in the <tt>NETWORK SERVICE</tt> 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.
 +
 
 +
== All PostgreSQL versions: '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 <tt>NETWORKSERVICE</tt> by default.
 +
 
 +
If you want to use SSPI securely aceoss a domain, you need to change the service to run under a domain account because SSPI requires that the service uses a domain account:
  
 
# Stop the service.
 
# Stop the service.
Line 19: Line 31:
 
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.  
 
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.
+
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. Optionally creation of login roles and groups can be automated with [https://github.com/larskanis/pg-ldap-sync pg-ldap-sync].
+
== SSPI entries in pg_hba.conf ==
  
 +
For either configuration, you must finally require SSPI logon to the database in pg_hba.conf.
  
== PostgreSQL 9.2 ==
+
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".  
  
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.
+
Remember that only the first entry in pg_hba.conf that matches database, client address, and claimed user name is used.  
  
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:
+
Optionally creation of login roles and groups can be automated with [https://github.com/larskanis/pg-ldap-sync pg-ldap-sync].
  
setspn -S POSTGRES/fully.qualified.domain.name dbserver
+
The <tt>pg_ident.conf</tt> file may be used to configure username mappings if your PostgreSQL usernames aren't exactly the same as your Windows user names.
 
+
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.
+
  
 
[[Category:Windows]]
 
[[Category:Windows]]

Revision as of 07:49, 9 July 2014

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.

PostgreSQL 9.2 and above: 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.

All PostgreSQL versions: '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 aceoss 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.

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.

Personal tools