From nsimela@gmail.com Sun Mar  11 06:08:15 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 2EBDC31C0C4
	for <pgsql-admin@postgresql.org>; Sun, 11 Mar 2012 03:08:15 -0300 (ADT)
Received: from mail-iy0-f174.google.com ([209.85.210.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <nsimela@gmail.com>) id 1S6bx0-00073n-7n
	for pgsql-admin@postgresql.org; Sun, 11 Mar 2012 06:08:14 +0000
Received: by iagz16 with SMTP id z16so4868624iag.19
	for <pgsql-admin@postgresql.org>; Sat, 10 Mar 2012 22:08:01 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:from:date:message-id:subject:to:content-type;
	bh=h15QVZeEUf8RyVmka+lnm+TNxJA9tlozDAbZVGAJTrs=;
	b=LzhbokmTW40z0Evfgni2UZ5jEeZfhJJ4pe6nmeJR0BuUi7ubUPHeN+T8AcE1ZGLyvG
	0SbudQQ4h8FOCG2CQh7qBOQY2jlcxCOYXjGfP3CrtZ+zZOWTqHxEL+wCiwn1V9JQllNt
	z1PIbw4e/vyOpB7ZgWqdPtBaX5u7xyC6rg4rOvMIaEEcbd6tpGzA516dmfsBzwzX9bUX
	V9EYB5iTNdOw2E9FiBQikvAgND8jumftkmQweT85e41nWlqO9E72J5TFxNN77M4PrIB/
	02tEFoILh3sFI+jLFdGKrni3kub5Wfk1ijHWsNUMX3vZyFrD1d0/HFD6hifa++jWU8Ep
	0+SQ==
Received: by 10.182.231.100 with SMTP id tf4mr3528291obc.56.1331446081747;
	Sat, 10 Mar 2012 22:08:01 -0800 (PST)
MIME-Version: 1.0
From: Noel Simela <nsimela@gmail.com>
Date: Sun, 11 Mar 2012 08:07:56 +0200
Message-ID: <-2948926600410804477@unknownmsgid>
Subject: Enabling Windows Authentication
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Content-Type: multipart/alternative; boundary=f46d04479615bd7bd404baf172be
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201203/62
X-Sequence-Number: 39250

--f46d04479615bd7bd404baf172be
Content-Type: text/plain; charset=ISO-8859-1

Hi all,

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

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.



Sent from my Windows Phone

--f46d04479615bd7bd404baf172be
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<html><head><meta content=3D"text/html; charset=3Dwindows-1252" http-equiv=
=3D"Content-Type"></head><body><div><div style=3D"font-family:Calibri,sans-=
serif;font-size:11pt">Hi all,<br><br>I am working on an Analysis cube that =
gathers data directly from a Postgres 8.4 db connected via PGNP data source=
.<br>
<br>Problem is I can&#39;t browse the cube because my windows credentials a=
re not authenticated on the postgres server.How do I enable this?<br><br>Th=
e postgres server is hosted on Windows 2008 Server platform.<br><br><br>
<br>Sent from my Windows Phone<br></div></div></body></html>

--f46d04479615bd7bd404baf172be--



From chris@chrullrich.net Fri Mar  16 08:36:10 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 4019C131F6D0
	for <pgsql-admin@postgresql.org>; Fri, 16 Mar 2012 05:36:10 -0300 (ADT)
Received: from plane.gmane.org ([80.91.229.3])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <sql-pgsql-admin@m.gmane.org>) id 1S8Sdt-00023m-Ec
	for pgsql-admin@postgresql.org; Fri, 16 Mar 2012 08:36:09 +0000
Received: from list by plane.gmane.org with local (Exim 4.69)
	(envelope-from <sql-pgsql-admin@m.gmane.org>) id 1S8Sdf-0005ZK-Kr
	for pgsql-admin@postgresql.org; Fri, 16 Mar 2012 09:35:55 +0100
Received: from 217.31.70.82 ([217.31.70.82])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-admin@postgresql.org>; Fri, 16 Mar 2012 09:35:55 +0100
Received: from chris by 217.31.70.82 with local (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-admin@postgresql.org>; Fri, 16 Mar 2012 09:35:55 +0100
X-Injected-Via-Gmane: http://gmane.org/
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
X-Complaints-To: usenet@dough.gmane.org
Cc: nsimela@gmail.com
X-Gmane-NNTP-Posting-Host: 217.31.70.82
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:11.0) Gecko/20120312 Thunderbird/11.0
In-Reply-To: <-2948926600410804477@unknownmsgid>
X-Pg-Spam-Score: -0.7 (/)
X-Archive-Number: 201203/95
X-Sequence-Number: 39283

* 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