Re: How to setup PostgreSQL using Windows Authentication?

On 06/16/2012 08:36 PM, Christian Ullrich wrote:
> * Peter Cheung wrote:
>
>>  I’m new to PostgreSQL.   I installed PostgreSQL on a Windows Server
>> 2008
>> R2 server.   I have created a database and an user in Windows Active
>> Directory.  How can I configure that user to access that database?
>
> 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:

That's a great explanation. I didn't see anything equivalent in the docs
- am I just blind?

If not documented anywhere I'd like to add that to the wiki.

>
> 1. Create a user account in your domain.
> 2. Change the ownership of the data directory and everything within it
>    to the new account, and grant it full control.
> 3. Change the service log on credentials so the service uses your
>    domain account.
> 4. 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 to
> your service account. You can do that with a command line tool named
> setspn.exe, of which I cannot remember the command line. You can also
> just change the attribute (servicePrincipalName) directly using either
> the "Users and Computers" MMC, or whatever 2008R2's replacement for
> that is, or ADSIedit. Anyway, your new SPN is
>
>     POSTGRES/fully.qualified.host.name
>
> 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 may help with that, but using the full name is
> better 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.
>
> --
> Christian
>
>