Postgres Pro
Downloads
Home   >   mailing lists

Thread: postgres kerberos how to

postgres kerberos how to

From
Derrick
Date:
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.

Re: postgres kerberos how to

From
Stephen Frost
Date:
* Derrick (dstensrud@worleyco.com) wrote:
> 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.

If you're willing to use a pam-based solution it might be easier/simpler
to set up libpam-krb5 to talk to AD (which I believe there's a fair bit
of documentation for) and then have PG use PAM.  Otherwise, it depends
on what your client is, exactly, as to the difficulty getting the
built-in kerberos support interacting correctly with AD.

    Thanks,

        Stephen
Attachment

Re: postgres kerberos how to

From
"Matt Burry"
Date:
> 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

Re: postgres kerberos how to

From
Derrick Stensrud
Date:
Thanks, I'm going to go ahead and try to implement postgresql kerberos against active directory 2003 today.  I read a bit about it last night and have a few questions. 

You wrote: "
* 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
"
Questions:
- If I have 10 users in postgresql do I need to run this ktpass program for each user?  If so would I always keep the princ paramater listed as "postgres/FAUXACACIA@FAUX.COM" and do the capitals matter in this command?
- With this ktpass command, what is the +rndPass option?  I'm used to seeing something like -pass P@ssWoRd
- After this keytab file is created, I copy it to /etc/krb5.keytab on the linux box, and I edit my krb5.conf file to resemble yours listed.  Is there anything else that has to be done on the Windows or linux side?

Thanks for the help so far.




Matt Burry wrote: 
  
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 using both 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 Windows 2003 Server running Terminal Services which serves the front end. We have had greater success with Windows 2003 Server R2, though we have been able to get it to work with Windows 2000 Server and Windows 2003 server. We also needed to use 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 into the suggestion that Magnus made that 8.2's LDAP support could help solve this problem of AD integration from a slightly different 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.COMkdc_timesync = 1ccache_type = 4ticket_lifetime = 36000

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

[appdefaults]autologin = trueforward = trueforwardable = trueencrypt = 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 for Windows 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', then Kerberos authentication will fail unless the session is in install mode('change user /install'). This is because of the virtualized copy of the C:\Windows directory that is created in a Terminal Services Session. In particular, INI files are managed by a sync process between the virtual/personal Windows directory and the real one. Entering install mode points 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.

Re: postgres kerberos how to

From
Derrick Stensrud
Date:
If anyone else out there can help me understand what this ktpass program is doing I would be greatful.  Also, can anyone help answer these questions I have.  Thanks.

Questions:
- If I have 10 users in postgresql do I need to run this ktpass program for each user?  If so would I always keep the princ paramater listed as "postgres/FAUXACACIA@FAUX.COM" and do the capitals matter in this command?
- With this ktpass command, what is the +rndPass option?  I'm used to seeing something like -pass P@ssWoRd
- After this keytab file is created, I copy it to /etc/krb5.keytab on the linux box, and I edit my krb5.conf file to resemble yours listed.  Is there anything else that has to be done on the Windows or linux side?
- When using postgres kerberos support, do you have to have a local unix user account?


Derrick Stensrud wrote:
Thanks, I'm going to go ahead and try to implement postgresql kerberos against active directory 2003 today.  I read a bit about it last night and have a few questions. 

You wrote: "
* 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
"
Questions:
- If I have 10 users in postgresql do I need to run this ktpass program for each user?  If so would I always keep the princ paramater listed as "postgres/FAUXACACIA@FAUX.COM" and do the capitals matter in this command?
- With this ktpass command, what is the +rndPass option?  I'm used to seeing something like -pass P@ssWoRd
- After this keytab file is created, I copy it to /etc/krb5.keytab on the linux box, and I edit my krb5.conf file to resemble yours listed.  Is there anything else that has to be done on the Windows or linux side?

Thanks for the help so far.




Matt Burry wrote: 
  
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 using both 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 Windows 2003 Server running Terminal Services which serves the front end. We have had greater success with Windows 2003 Server R2, though we have been able to get it to work with Windows 2000 Server and Windows 2003 server. We also needed to use 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 into the suggestion that Magnus made that 8.2's LDAP support could help solve this problem of AD integration from a slightly different 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.COMkdc_timesync = 1ccache_type = 4ticket_lifetime = 36000

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

[appdefaults]autologin = trueforward = trueforwardable = trueencrypt = 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 for Windows 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', then Kerberos authentication will fail unless the session is in install mode('change user /install'). This is because of the virtualized copy of the C:\Windows directory that is created in a Terminal Services Session. In particular, INI files are managed by a sync process between the virtual/personal Windows directory and the real one. Entering install mode points 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.

Re: postgres kerberos how to

From
"Garner Chung"
Date:
Hey Derrick,

I work with Matt, and I was involved in our Kerberos implementation.
I'll try to answer your questions...

- You only need to make the mapping to an Active Directory(AD) user once
for that PG service. What that ktpass command does is map the principal
name for your PG service to an AD user. More importantly, with the
resulting keytab file you will establish the shared secret between the
service(PG) and the KDC(which is really a close partner if not subset of
AD). The secret is what AD will use to identify itself as a trustable
KDC to PG. So, no, you do not need to do that for every PG user.
However, you do need to create each database user in both AD and PG.
- +rndPass (which is not available in older versions of ktpass) will use
a random shared secret(password). There's no need for anyone to know
what the actual value of that pre shared key is. However it is very
important that the resulting keytab file is protected via filesystem
permissions.
-
   * Yes that keytab file needs to be in /etc/krb5.keytab which is an
MIT library file location. I don't think PG will look at that file
directly. In fact, you specify separately in postgresql.conf where PG
should look for its keytab file. As far as we understand that
/etc/krb5.keytab file would contain multiple keytabs. If any MIT
Kerberos experts out there could chime, please do. ie. Would should the
permissions on that file be set to? To be clear, we got an error
message, which I never wrote down, without PG's keytab file not there
also.
   * Yes, you also need to edit your /etc/krb5.conf file. You are
essentially telling the MIT Kerberos library that the default realm for
it is the AD realm/domain. You are also telling it where the KDC for
that realm is.
   * You also need edit postgresql.conf where its keytab is.

Hope this helps, please do follow up with clarifying questions if you
have any. There is, IMHO, a paucity of good documentation on getting PG
to authenticate against AD using Kerberos.

garner


________________________________

From: Derrick Stensrud [mailto:dstensrud@worleyco.com]
Sent: Wednesday, December 20, 2006 5:07 AM
To: Matt Burry
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres kerberos how to


Thanks, I'm going to go ahead and try to implement postgresql kerberos
against active directory 2003 today.  I read a bit about it last night
and have a few questions.

You wrote: "

* 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
"

Questions:
- If I have 10 users in postgresql do I need to run this ktpass program
for each user?  If so would I always keep the princ paramater listed as
"postgres/FAUXACACIA@FAUX.COM" <mailto:postgres/FAUXACACIA@FAUX.COM>
and do the capitals matter in this command?
- With this ktpass command, what is the +rndPass option?  I'm used to
seeing something like -pass P@ssWoRd
- After this keytab file is created, I copy it to /etc/krb5.keytab on
the linux box, and I edit my krb5.conf file to resemble yours listed.
Is there anything else that has to be done on the Windows or linux side?

Thanks for the help so far.




Matt Burry wrote:




        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 using both 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 Windows 2003 Server running Terminal
Services which serves the front end. We have had greater success with
Windows 2003 Server R2, though we have been able to get it to work with
Windows 2000 Server and Windows 2003 server. We also needed to use 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 into the suggestion that
Magnus made that 8.2's LDAP support could help solve this problem of AD
integration from a slightly different 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 for
Windows 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', then
Kerberos authentication will fail unless the session is in install
mode('change user /install'). This is because of the virtualized copy of
the C:\Windows directory that is created in a Terminal Services Session.
In particular, INI files are managed by a sync process between the
virtual/personal Windows directory and the real one. Entering install
mode points 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 incoming message.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.15.25/593 - Release Date:
12/19/2006 1:17 PM



--
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