Re: Enabling Windows Authentication

* 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