Re: FW: postgres kerberos how to

We bit the bullet and upgraded to 8.2 so that we could get the built in
ldap auth support, and boom! It worked out of the box. Thanks a
million and special thanks to Magnus!

Garner Chung wrote:
> Nope, no Unix accounts are necessary besides the one that PG runs as.
> You do need the PG roles, however. Good luck!
>
> garner
>
> ------------------------------------------------------------------------
> *From:* Derrick Stensrud [mailto:dstensrud(at)worleyco(dot)com]
> *Sent:* Wednesday, December 20, 2006 10:36 AM
> *To:* Garner Chung
> *Cc:* pgsql-general(at)postgresql(dot)org
> *Subject:* Re: FW: [GENERAL] postgres kerberos how to
>
> Great! Thanks so much, I'm going to try to get this going right now.
> Do I need unix user accounts?
>
> Garner Chung wrote:
>> Hey, I think this made it to the list. But I had also CC:ed you
>> separately -- to which I got a bounce back. So here's my second attempt
>> to essentially CC: you.
>>
>> garner
>>
>> -----Original Message-----
>> From: Garner Chung
>> Sent: Wednesday, December 20, 2006 10:10 AM
>> To: 'pgsql-general(at)postgresql(dot)org'
>> Cc: 'Derrick Stensrud '
>> Subject: RE: [GENERAL] postgres kerberos how to
>>
>>
>> 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(at)worleyco(dot)com]
>> Sent: Wednesday, December 20, 2006 5:07 AM
>> To: Matt Burry
>> Cc: pgsql-general(at)postgresql(dot)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(at)FAUX(dot)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(at)FAUX(dot)COM" <mailto:postgres/FAUXACACIA(at)FAUX(dot)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(at)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(at)postgresql(dot)org
>> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of
>> Derrick
>> Sent: Tuesday, December 19, 2006 5:52 PM
>> To: pgsql-general(at)postgresql(dot)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(at)FAUX(dot)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
>>
>>
>>
>>