From: Christian Ullrich <chris@chrullrich.net>
To: pgsql-admin@postgresql.org
Subject: Re: How to setup PostgreSQL using Windows Authentication?
Date: Sat, 16 Jun 2012 14:36:02 +0200
Message-ID: <jrhujm$2ec$1@dough.gmane.org> (raw)
In-Reply-To: <583566AE01BD5948A12C964BD739A9A24686BA6A@HARRY.sfcu.intr>
References: <583566AE01BD5948A12C964BD739A9A24686BA6A@HARRY.sfcu.intr>
* 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:
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: chris@chrullrich.net
Subject: Re: How to setup PostgreSQL using Windows Authentication?
In-Reply-To: <jrhujm$2ec$1@dough.gmane.org>
* 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