From: Craig Ringer <ringerc@ringerc.id.au>
To: Christian Ullrich <chris@chrullrich.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: How to setup PostgreSQL using Windows Authentication?
Date: Sun, 17 Jun 2012 17:30:16 +0800
Message-ID: <4FDDA3A8.4030307@ringerc.id.au> (raw)
In-Reply-To: <jrhujm$2ec$1@dough.gmane.org>
References: <583566AE01BD5948A12C964BD739A9A24686BA6A@HARRY.sfcu.intr>
<jrhujm$2ec$1@dough.gmane.org>
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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ringerc@ringerc.id.au, chris@chrullrich.net
Subject: Re: How to setup PostgreSQL using Windows Authentication?
In-Reply-To: <4FDDA3A8.4030307@ringerc.id.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox