https://wiki.postgresql.org/api.php?action=feedcontributions&user=Chrullrich&feedformat=atomPostgreSQL wiki - User contributions [en]2024-03-29T05:32:33ZUser contributionsMediaWiki 1.35.13https://wiki.postgresql.org/index.php?title=Configuring_for_single_sign-on_using_SSPI_on_Windows&diff=18377Configuring for single sign-on using SSPI on Windows2012-10-12T09:56:00Z<p>Chrullrich: </p>
<hr />
<div>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.<br />
<br />
I assume that you used the one-click installer to set up PostgreSQL on your server.<br />
<br />
== PostgreSQL 9.1 ==<br />
<br />
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:<br />
<br />
# Stop the service.<br />
# Create a user account in your domain.<br />
# Change the ownership of the data directory and everything within it to the new account, and grant it full control.<br />
# Change the service log on credentials so the service uses your domain account.<br />
# 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.<br />
<br />
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:<br />
<br />
setspn -S POSTGRES/fully.qualified.domain.name DOMAIN\service_account_name<br />
<br />
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. <br />
<br />
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.<br />
<br />
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.<br />
<br />
== PostgreSQL 9.2 ==<br />
<br />
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.<br />
<br />
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:<br />
<br />
setspn -S POSTGRES/fully.qualified.domain.name dbserver<br />
<br />
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.<br />
<br />
[[Category:Windows]]</div>Chrullrichhttps://wiki.postgresql.org/index.php?title=Configuring_for_single_sign-on_using_SSPI_on_Windows&diff=18188Configuring for single sign-on using SSPI on Windows2012-09-05T10:17:58Z<p>Chrullrich: </p>
<hr />
<div>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.<br />
<br />
The one-click installer (assuming you used that) left you with PostgreSQL running under a local account named "postgres". First, you have to change that, because SSPI requires that the service uses a domain account:<br />
<br />
# Stop the service.<br />
# Create a user account in your domain.<br />
# Change the ownership of the data directory and everything within it to the new account, and grant it full control.<br />
# Change the service log on credentials so the service uses your domain account.<br />
# 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.<br />
<br />
Now you have to tell Active Directory that your service account is running the database. For that, you add a Service Principal Name to your service account. There is a command-line tool for that, called setspn.exe:<br />
<br />
setspn -A POSTGRES/fully.qualified.domain.name DOMAIN\service_account_name<br />
<br />
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. <br />
<br />
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.<br />
<br />
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.<br />
<br />
[[Category:Windows]]</div>Chrullrichhttps://wiki.postgresql.org/index.php?title=Configuring_for_single_sign-on_using_SSPI_on_Windows&diff=18187Configuring for single sign-on using SSPI on Windows2012-09-05T10:17:14Z<p>Chrullrich: Created page with "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 enviā¦"</p>
<hr />
<div>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.<br />
<br />
The one-click installer (assuming you used that) left you with PostgreSQL running under a local account named "postgres". First, you have to change that, because SSPI requires that the service uses a domain account:<br />
<br />
# Stop the service.<br />
# Create a user account in your domain.<br />
# Change the ownership of the data directory and everything within it to the new account, and grant it full control.<br />
# Change the service log on credentials so the service uses your domain account.<br />
# 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.<br />
<br />
Now, you have to tell Active Directory that your service account is running the database. For that, you add a Service Principal Name to your service account. There is a command-line tool for that, called setspn.exe:<br />
<br />
setspn -A POSTGRES/fully.qualified.domain.name DOMAIN\service_account_name<br />
<br />
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. <br />
<br />
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.<br />
<br />
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.<br />
<br />
[[Category:Windows]]</div>Chrullrich