Re: postgres kerberos how to

> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Derrick
> Sent: Tuesday, December 19, 2006 5:52 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] postgres kerberos how to
>
> I was wondering if anyone out there could point me in the
> right direction.  I'm looking for a decent how to on using
> postgresql's built in kerberos support to authenticate
> against windows 2003 active directory.  I was trying to use
> pam_ldap, but had to give up and decide on using kerberos
> instead.  Any help what so ever is much appreciated.
> Thanks.
>

Derrick,

We've been setting it up in our shop and it's been a bit of a bear. We're connecting to PG through the ODBC driver
usingboth PG 8.1 and 8.2. Below are the steps we've taken to get it to work with a Windows 2003 Domain Controller and
Windows2003 Server running Terminal Services which serves the front end. We have had greater success with Windows 2003
ServerR2, though we have been able to get it to work with Windows 2000 Server and Windows 2003 server. We also needed
touse MIT's Net ID manager. 

It would great if others could point to ways that we could simplify these steps. I wasn't the one here who implemented
things,so one of my colleagues may need to followup if there are specific questions. Additionally, we have not looked
intothe suggestion that Magnus made that 8.2's LDAP support could help solve this problem of AD integration from a
slightlydifferent direction. 

-Matt


Kerberos Configuration Steps
**************************

PG Server=fauxacacia
Active Directory Domain=faux.com
Active Directory Domain Controller=fauxsol
Windows 2003 Server R2 (Terminal Services server, in our case, used to serve our front end)=fauxsaturn

On Domain Controller: fauxsol(Windows 2003)

    * Add entries to Domain Controller/DNS for all relevant servers so that all principals can contact each other by
name.

192.168.4.26    fauxsol fauxsol.faux.com
192.168.4.28    fauxacacia fauxacacia.faux.com
192.168.4.29    fauxsaturn fauxsaturn.faux.com


On PG Server: fauxacacia(Linux)

Samba

    * Install Samba to facilitate copying of keytab file from Windows Domain Controller

NTP (Network Time Protocol to make sure that servers stay with same time, required with Kerberos)

    * Install package, ntpdate
    * Edit '/etc/default/ntpdate' to point to the Windows Domain Controller/KDC Server

# servers to check.   (Separate multiple servers with spaces.)
#NTPSERVERS="pool.ntp.org"
#NTPSERVERS="ntp.ubuntu.com"
NTPSERVERS="fauxsol"
#
# additional options for ntpdate
#NTPOPTIONS="-v"
NTPOPTIONS="-u"

    * Add an hourly cron entry, '/etc/cron.hourly/ntpdate', and make it executable, 'chmod ugo+x
/etc/cron.hourly/ntpdate' 

#!/bin/sh

NTPDATE=/usr/sbin/ntpdate
NTPDEFAULTS=/etc/default/ntpdate
NTPFALLBACK="ntp.ubuntu.com"

if [ -f "$NTPDEFAULTS" ]; then
        . $NTPDEFAULTS
        test -n "$NTPSERVERS" || NTPSERVERS=$NTPFALLBACK
else
        NTPSERVERS=$NTPFALLBACK
fi

if [ -x "$NTPDATE" ]; then
        $NTPDATE -s $NTPOPTIONS $NTPSERVERS
fi

    * Restart cron, '/etc/init.d/cron restart'

MIT Kerberos Library

    * Install Kerberos Libraries

          krb5-config, krb5-user

    * Edit Kerberos config files ('/etc/krb5.conf')

[libdefaults]
        default_realm = [faux.com]
        krb4_config = /etc/krb.conf
        krb4_realms = /etc/krb.realms
        kdc_timesync = 1
        ccache_type = 4
        forwardable = true
        proxiable = true

[realms]
        FAUX.COM = {
                kdc = [fauxsol]
                admin_server = [fauxsol]
        }

DNS

    * Edit DNS settings file ('/etc/resolv.conf')

nameserver [ip address of DNS server 1]
nameserver [ip address of DNS server 2]

On Domain Controller: fauxsol (Windows 2003)

    * Install Windows Supporting Tools (ktpass), found on Windows installation CD as SUPPORT\TOOLS\SUPTOOLS.MSI
    * Map PG on database server as a user in Active Directory
          o Create user account in AD for PG service

          ie. 'postgres_fauxacacia'

          o Make keytab file with AD PG user account. This will also map the user to the service.

ktpass princ postgres/FAUXACACIA@FAUX.COM -mapuser postgres_fauxacacia +rndPass -out postgres_fauxacacia.keytab -target
fauxsol-ptype KRB5_NT_PRINCIPAL 

[edit]
On PG Server: fauxacacia (Linux)

    * Move keytab file to PG Server (Linux).

          '/usr/local/pgsql/etc/krb5.keytab' and '/etc/krb5.keytab'

    * Secure keytab files
          o 'chown postgres:postgres /usr/local/pgsql/etc/krb5.keytab'
          o 'chmod ugo-wx /usr/local/pgsql/etc/krb5.keytab' - Files created by samba have executable bit set for user.
          o 'chmod go-r /usr/local/pgsql/etc/krb5.keytab'
    * Configure PG to use '/usr/local/pgsql/etc/krb5.keytab'

# Kerberos
krb_server_keyfile = '/usr/local/pgsql/etc/krb5.keytab'                 # (change requires restart)
#krb_srvname = 'postgres'               # (change requires restart)
#krb_server_hostname = ''               # empty string matches any keytab entry
                                        # (change requires restart)
#krb_caseins_users = off                # (change requires restart)

    * Restart PG

[edit]
On Front End Server: fauxsaturn (Windows 2003)

    * Install MIT Kerberos For Windows
          o Make sure that Net ID Manager is configured to start when user logs in(default).
    * Create 'C:\Program Files\MIT\Kerberos\bin\krb5.ini':

[libdefaults]
    default_realm =    FAUX.COM
    kdc_timesync = 1
    ccache_type = 4
    ticket_lifetime = 36000

[realms]
    FAUX.COM = {
        kdc = FAUXSOL
        admin_server = FAUXSOL
    }

[appdefaults]
    autologin = true
    forward = true
    forwardable = true
    encrypt = true

    * Delete all krb5_32.dll's other than the one in the MIT\Kerberos\bin folder.

          ie. 'C:\Program Files\psqlODBC\0802\bin' or 'C:\Program Files\pgAdmin III\1.6'

          o This ensures that all client side PG Kerberos authentication will occur via the just installed MIT Kerberos
forWindows package. 
          o The secondary, desired effect is that only the one MIT\Kerberos\bin\krb5.ini file is used.
          o NOTE: This is just for our Terminal Services environment: If the krb5.ini file is placed in 'C:\Windows',
thenKerberos authentication will fail unless the session is in install mode('change user /install'). This is because of
thevirtualized copy of the C:\Windows directory that is created in a Terminal Services Session. In particular, INI
filesare managed by a sync process between the virtual/personal Windows directory and the real one. Entering install
modepoints the user's session to the _real_ Windows directory. 
                + As long as there is the one 'C:\Program Files\MIT\Kerberos\bin\krb5_32.dll' and the one 'C:\Program
Files\MIT\Kerberos\bin\krb5.ini',there 'change user /install' should not be necesssary.  

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.15.25/593 - Release Date: 12/19/2006 1:17 PM