Re: GSSAPI server side on Linux, SSPI client side on Windows

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(at)fluggo(dot)com> wrote:

> On Tue, Nov 12, 2013 at 5:45 AM, Francisco Figueiredo Jr.
> <francisco(at)npgsql(dot)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/machinename(at)REALM(dot)COM -U
> DOMAIN\Administrator
> net ads keytab add postgres/machinename(dot)domain(dot)com(at)REALM(dot)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 "BCrowell(at)REALM(dot)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(at)REALM(dot)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
>