Thought I might add, now that I've had more time to work with it, that adding the PostgreSQL host to the domain isn't necessary, and at least with Samba 3 is a huge waste of time thanks to the machine password changing policies in Active Directory.
Much better to create an ordinary user account with a password that can't change, and then use ktpass to generate a password and create an appropriate keytab. You may or may not be able to use ktpass to set up an SPN, I didn't go about that in an orthodox way. --Brian On Tue, Nov 12, 2013 at 9:13 AM, Brian Crowell <br...@fluggo.com> wrote: > On Tue, Nov 12, 2013 at 5:45 AM, Francisco Figueiredo Jr. > <franci...@npgsql.org> wrote: > > It would be awesome if you could write a little guide about how to > configure > > PostgreSQL to work with sspi authentication from Windows. > > I could add it to our Npgsql user manual... > > A guide will have to wait until I do all this again in a few months, > but I'll jot down my mental notes. Here's what I did, using Debian 6, > Samba 3.5 and PostgreSQL 9.3 against a Win2k3 domain: > > 1. Set up Kerberos for the domain. Plenty of guides for this. > > 2. Joined the Linux machine to the domain. Also lots of guides for > this, but most of them are outdated and add a ton of settings that you > don't need. Don't follow a guide until you read the relevant chapters > of the Samba HOWTO. Familiarity with Active Directory helps. I'm using > winbindd because I think it maintains the server's TGTs, but I'm not > really sure. > > 3. Created a service principal for PostgreSQL as root using the Samba > "net ads keytab add" command (helpful hint: leave items off the end of > the net command to see other available options). I created two service > principals, one for the short name and one for the FQDN: > > net ads keytab add postgres/machinen...@realm.com -U > DOMAIN\Administrator > net ads keytab add postgres/machinename.domain....@realm.com -U > DOMAIN\Administrator > > I verified the service principals with the command "net ads keytab > list". They should also end up in the system keytab, which you can > verify with a command like "klist -k /etc/krb5.keytab". (If they > don't, then I probably did "net ads kerberos kinit" at this point.) My > Samba produced six entries; three encryption types for each of the two > principal names. > > 4. Exported the service principals to a Postgres-specific keytab. I > did this with ktutil. You'll start ktutil and issue a "read_kt > /etc/krb5.keytab" to import the system keytab, then issue delete_entry > to delete all the but the Postgres principals. Use write_kt to write > them to a Postgres-specific location; I used "write_kt > /etc/postgresql/9.3/main/krb5.keytab" where the postgres account will > own the file. I also removed write permissions from this file. > > 5. In postgres.conf, I set krb_server_keyfile = > '/etc/postgresql/9.3/main/krb5.keytab' and krb_srvname = 'postgres'. > krb_srvname's case didn't seem to matter for me, YMMV. > > (I just noticed this "environment" file sitting here. I wonder if I > could use that with MIT Kerberos 1.9 and the KRB5_TRACE variable to > get detailed Kerberos traces. Hmmm....) > > 6. In pg_hba.conf, I added the line "host all all all gss > include_realm=1". I'm including the realm because I'm in a forest. > > 7. Restart PostgreSQL. > > 8. Log into Postgres locally and create a role for your domain self: > > create role "bcrow...@realm.com" login inherit; > > 9. If all went well, and I didn't leave out any steps, on a Windows > machine you should be able to go to the command prompt and do: > > psql --host=machinename.domain.com --username=BCrowell@REALM.COMpostgres > > ...and get in without any password prompts, assuming you got the case > on your username correct. If the case is wrong, Postgres will tell you > what it's supposed to be in the system logs. > > 10. Patch Npgsql to understand GSSAPI authentication :P > > --Brian >
