On Tue, Nov 12, 2013 at 5:45 AM, Francisco Figueiredo Jr. <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... 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/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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
