To: pgsql-admin@postgresql.org
From: Christian Ullrich <chris@chrullrich.net>
Subject: Re: Enabling Windows Authentication
Date: Fri, 16 Mar 2012 09:35:43 +0100
Lines: 68
Message-ID: <4F62FB5F.7000701@chrullrich.net>
References: <-2948926600410804477@unknownmsgid>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Cc: nsimela@gmail.com
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:11.0) Gecko/20120312 Thunderbird/11.0
In-Reply-To: <-2948926600410804477@unknownmsgid>

* Noel Simela wrote:

> I am working on an Analysis cube that gathers data directly from a
> Postgres 8.4 db connected via PGNP data source.

PGNP is based on libpq, so it should work the same way as for ODBC.

> Problem is I can't browse the cube because my windows credentials are
> not authenticated on the postgres server.How do I enable this?
>
> The postgres server is hosted on Windows 2008 Server platform.

If you are in a domain environment, read on. Otherwise, it does not 
work at all.


Very short guide to configuring PostgreSQL on Windows for SSPI
==============================================================

1. Requirements

    - The server and all client systems must belong to the same
      domain. (I expect it also works if the server's domain
      does at least trust the clients', but I have never tried
      that.)

    - All client processes must likewise run under a domain
      account. No local user accounts on client systems can
      authenticate to the server.

    - The clients must connect to the server using the FQDN
      (Fully Qualified Domain Name) to avoid Kerberos
      complications.

2. Server Configuration

    1. Create a service account in the domain for the PostgreSQL
       service.

    2. Stop the PostgreSQL service.

    3. Assign the required SPN (Service Principal Name) to
       the service account:

       setspn -A postgres/<Server FQDN> <Service Account>

    4. Reconfigure the service to log on as the new service
       account.

    5. Change ownership and permissions of the data directory
       and its contents so they belong to the service account
       and it has full control.

    6. Add the required entries to pg_hba.conf with
       authentication method "sspi". Keep a line for a superuser
       with a different auth method above the SSPI lines, so you
       can get in even if it does not work right.

    7. Start the PostgreSQL service.

You can use the "klist" command on the clients to check whether they 
get service tickets from the domain controller.

Good luck.

-- 
Christian Ullrich