Skip site navigation (1) Skip section navigation (2)

Re: How to setup PostgreSQL using Windows Authentication?

From: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
To: Christian Ullrich <chris(at)chrullrich(dot)net>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: How to setup PostgreSQL using Windows Authentication?
Date: 2012-06-17 09:30:16
Message-ID: 4FDDA3A8.4030307@ringerc.id.au (view raw or flat)
Thread:
Lists: pgsql-admin
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
>
>



In response to

Responses

pgsql-admin by date

Next:From: Christian UllrichDate: 2012-06-17 09:34:37
Subject: Re: How to setup PostgreSQL using Windows Authentication?
Previous:From: Christian UllrichDate: 2012-06-16 12:39:17
Subject: Re: How to install Postgresql with GSSAPI support using One click installer?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group