Message-ID: <4FDDA3A8.4030307@ringerc.id.au>
Date: Sun, 17 Jun 2012 17:30:16 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
To: Christian Ullrich <chris@chrullrich.net>
CC: pgsql-admin@postgresql.org
Subject: Re: How to setup PostgreSQL using Windows Authentication?
References: <583566AE01BD5948A12C964BD739A9A24686BA6A@HARRY.sfcu.intr>
	<jrhujm$2ec$1@dough.gmane.org>
In-Reply-To: <jrhujm$2ec$1@dough.gmane.org>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

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
>
>