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 <brian@xxxxxxxxxx> wrote:
On Tue, Nov 12, 2013 at 5:45 AM, Francisco Figueiredo Jr.A guide will have to wait until I do all this again in a few months,
<francisco@xxxxxxxxxx> 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...
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/machinename@xxxxxxxxx -U DOMAIN\Administrator
net ads keytab add postgres/machinename.domain.com@xxxxxxxxx -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 "BCrowell@xxxxxxxxx" 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@xxxxxxxxx postgres
...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
