Re: FW: 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
>
>
>
>