Re: GSS Authentication

Thanks for the help.

In response to your questions, I did make sure the service name was right.

klist -k on the keytab file gives:

KVNO Principal
---- --------------------------------------------------------------------------
   3 POSTGRES/hostname.domain.com@DOMAIN.COM

I replaced our real domain with an example obviously, but that's what it looks like.

I'm thinking it looks correct. 

By testing with psql locally first, do you mean running psql right on the postgres server itself?  To test the GSS authentication?  I tried to set the local connections in the pg_hba.conf to use gss authentication locally, but then when I tried to restart postgres, the logs said that GSS authentication wasn't allowed for local connections (see log message below):

2010-06-14 14:42:24 EDTLOG:  F0000: gssapi authentication is not supported on local sockets

I did change the default service name to POSTGRES instead of postgres.

Reverse DNS is working and I think the default realm is right.  I'm a little unclear on exactly what that should be, but I'm thinking that based on the example above it should be something like "domain.com". 

I did give the server side logs in my original message, but I'll include more.  So, in this log entry I'll paste below (it's a little lengthy), we have a startup, then a failed connection from the windows client, then a shutdown.

What should I try next?  Thanks for the help.

Greig Wise

--------

2010-06-14 15:12:21 EDTLOG:  00000: database system was shut down at 2010-06-14 15:12:08 EDT
2010-06-14 15:12:21 EDTLOCATION:  StartupXLOG, xlog.c:5243
2010-06-14 15:12:21 EDTDEBUG:  00000: checkpoint record is at 1/BD000020
2010-06-14 15:12:21 EDTLOCATION:  StartupXLOG, xlog.c:5340
2010-06-14 15:12:21 EDTDEBUG:  00000: redo record is at 1/BD000020; shutdown TRUE
2010-06-14 15:12:21 EDTLOCATION:  StartupXLOG, xlog.c:5366
2010-06-14 15:12:21 EDTDEBUG:  00000: next transaction ID: 0/696; next OID: 16400
2010-06-14 15:12:21 EDTLOCATION:  StartupXLOG, xlog.c:5370
2010-06-14 15:12:21 EDTDEBUG:  00000: next MultiXactId: 1; next MultiXactOffset: 0
2010-06-14 15:12:21 EDTLOCATION:  StartupXLOG, xlog.c:5373
2010-06-14 15:12:21 EDTDEBUG:  00000: transaction ID wrap limit is 2147484295, limited by database "template1"
2010-06-14 15:12:21 EDTLOCATION:  SetTransactionIdLimit, varsup.c:285
2010-06-14 15:12:21 EDTDEBUG:  00000: shmem_exit(0): 3 callbacks to make
2010-06-14 15:12:21 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:21 EDTDEBUG:  00000: proc_exit(0): 2 callbacks to make
2010-06-14 15:12:21 EDTLOCATION:  proc_exit_prepare, ipc.c:183
2010-06-14 15:12:21 EDTDEBUG:  00000: exit(0)
2010-06-14 15:12:21 EDTLOCATION:  proc_exit, ipc.c:135
2010-06-14 15:12:21 EDTDEBUG:  00000: shmem_exit(-1): 0 callbacks to make
2010-06-14 15:12:21 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:21 EDTDEBUG:  00000: proc_exit(-1): 0 callbacks to make
2010-06-14 15:12:21 EDTLOCATION:  proc_exit_prepare, ipc.c:183
2010-06-14 15:12:21 EDTDEBUG:  00000: reaping dead processes
2010-06-14 15:12:21 EDTLOCATION:  reaper, postmaster.c:2238
2010-06-14 15:12:21 EDTLOG:  00000: autovacuum launcher started
2010-06-14 15:12:21 EDTLOCATION:  AutoVacLauncherMain, autovacuum.c:529
2010-06-14 15:12:21 EDTLOG:  00000: database system is ready to accept connections
2010-06-14 15:12:21 EDTLOCATION:  reaper, postmaster.c:2326
2010-06-14 15:12:26 EDTDEBUG:  00000: forked new backend, pid=4750 socket=8
2010-06-14 15:12:26 EDTLOCATION:  BackendStartup, postmaster.c:3085
2010-06-14 15:12:26 EDTDEBUG:  00000: Processing received GSS token of length 2007
2010-06-14 15:12:26 EDTLOCATION:  pg_GSS_recvauth, auth.c:965
2010-06-14 15:12:26 EDTDEBUG:  00000: gss_accept_sec_context major: 851968, minor: -2045022973, outlen: 0, outflags: 7f
2010-06-14 15:12:26 EDTLOCATION:  pg_GSS_recvauth, auth.c:984
2010-06-14 15:12:26 EDTFATAL:  XX000: accepting GSS security context failed
2010-06-14 15:12:26 EDTDETAIL:  Miscellaneous failure: Unknown code ggss 3
2010-06-14 15:12:26 EDTLOCATION:  pg_GSS_error, auth.c:866
2010-06-14 15:12:26 EDTDEBUG:  00000: shmem_exit(1): 0 callbacks to make
2010-06-14 15:12:26 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:26 EDTDEBUG:  00000: proc_exit(1): 1 callbacks to make
2010-06-14 15:12:26 EDTLOCATION:  proc_exit_prepare, ipc.c:183
2010-06-14 15:12:26 EDTDEBUG:  00000: exit(1)
2010-06-14 15:12:26 EDTLOCATION:  proc_exit, ipc.c:135
2010-06-14 15:12:26 EDTDEBUG:  00000: shmem_exit(-1): 0 callbacks to make
2010-06-14 15:12:26 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:26 EDTDEBUG:  00000: proc_exit(-1): 0 callbacks to make
2010-06-14 15:12:26 EDTLOCATION:  proc_exit_prepare, ipc.c:183
2010-06-14 15:12:26 EDTDEBUG:  00000: reaping dead processes
2010-06-14 15:12:26 EDTLOCATION:  reaper, postmaster.c:2238
2010-06-14 15:12:26 EDTDEBUG:  00000: server process (PID 4750) exited with exit code 1
2010-06-14 15:12:26 EDTLOCATION:  LogChildExit, postmaster.c:2707
2010-06-14 15:12:31 EDTDEBUG:  00000: postmaster received signal 15
2010-06-14 15:12:31 EDTLOCATION:  pmdie, postmaster.c:2090
2010-06-14 15:12:31 EDTLOG:  00000: received smart shutdown request
2010-06-14 15:12:31 EDTLOCATION:  pmdie, postmaster.c:2105
2010-06-14 15:12:31 EDTLOG:  00000: autovacuum launcher shutting down
2010-06-14 15:12:31 EDTLOCATION:  AutoVacLauncherMain, autovacuum.c:760
2010-06-14 15:12:31 EDTDEBUG:  00000: shmem_exit(0): 1 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:31 EDTDEBUG:  00000: proc_exit(0): 2 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  proc_exit_prepare, ipc.c:183
2010-06-14 15:12:31 EDTDEBUG:  00000: exit(0)
2010-06-14 15:12:31 EDTLOCATION:  proc_exit, ipc.c:135
2010-06-14 15:12:31 EDTDEBUG:  00000: shmem_exit(-1): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:31 EDTDEBUG:  00000: proc_exit(-1): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  proc_exit_prepare, ipc.c:183
2010-06-14 15:12:31 EDTDEBUG:  00000: reaping dead processes
2010-06-14 15:12:31 EDTLOCATION:  reaper, postmaster.c:2238
2010-06-14 15:12:31 EDTDEBUG:  00000: shmem_exit(0): 3 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:31 EDTDEBUG:  00000: proc_exit(0): 2 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  proc_exit_prepare, ipc.c:183
2010-06-14 15:12:31 EDTDEBUG:  00000: exit(0)
2010-06-14 15:12:31 EDTLOCATION:  proc_exit, ipc.c:135
2010-06-14 15:12:31 EDTDEBUG:  00000: shmem_exit(-1): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:31 EDTDEBUG:  00000: proc_exit(-1): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  proc_exit_prepare, ipc.c:183
2010-06-14 15:12:31 EDTDEBUG:  00000: reaping dead processes
2010-06-14 15:12:31 EDTLOCATION:  reaper, postmaster.c:2238
2010-06-14 15:12:31 EDTLOG:  00000: shutting down
2010-06-14 15:12:31 EDTLOCATION:  ShutdownXLOG, xlog.c:6234
2010-06-14 15:12:31 EDTDEBUG:  00000: executing archive command "cp pg_xlog/0000000100000001000000BD /postgresdb/log_arch/0000000100000001000000BD </dev/null"
2010-06-14 15:12:31 EDTLOCATION:  pgarch_archiveXlog, pgarch.c:544
2010-06-14 15:12:31 EDTDEBUG:  00000: archived transaction log file "0000000100000001000000BD"
2010-06-14 15:12:31 EDTLOCATION:  pgarch_archiveXlog, pgarch.c:612
2010-06-14 15:12:31 EDTDEBUG:  00000: recycled transaction log file "0000000100000001000000BC"
2010-06-14 15:12:31 EDTLOCATION:  RemoveOldXlogFiles, xlog.c:3083
2010-06-14 15:12:31 EDTLOG:  00000: database system is shut down
2010-06-14 15:12:31 EDTLOCATION:  ShutdownXLOG, xlog.c:6256
2010-06-14 15:12:31 EDTDEBUG:  00000: shmem_exit(0): 3 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:31 EDTDEBUG:  00000: proc_exit(0): 2 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  proc_exit_prepare, ipc.c:183
2010-06-14 15:12:31 EDTDEBUG:  00000: exit(0)
2010-06-14 15:12:31 EDTLOCATION:  proc_exit, ipc.c:135
2010-06-14 15:12:31 EDTDEBUG:  00000: shmem_exit(-1): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:31 EDTDEBUG:  00000: proc_exit(-1): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  proc_exit_prepare, ipc.c:183
2010-06-14 15:12:31 EDTDEBUG:  00000: reaping dead processes
2010-06-14 15:12:31 EDTLOCATION:  reaper, postmaster.c:2238
2010-06-14 15:12:31 EDTDEBUG:  00000: shmem_exit(-1): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:31 EDTDEBUG:  00000: proc_exit(-1): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  proc_exit_prepare, ipc.c:183
2010-06-14 15:12:31 EDTDEBUG:  00000: reaping dead processes
2010-06-14 15:12:31 EDTLOCATION:  reaper, postmaster.c:2238
2010-06-14 15:12:31 EDTDEBUG:  00000: shmem_exit(-1): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:31 EDTDEBUG:  00000: proc_exit(-1): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  proc_exit_prepare, ipc.c:183
2010-06-14 15:12:31 EDTDEBUG:  00000: reaping dead processes
2010-06-14 15:12:31 EDTLOCATION:  reaper, postmaster.c:2238
2010-06-14 15:12:31 EDTDEBUG:  00000: shmem_exit(0): 3 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:31 EDTDEBUG:  00000: proc_exit(0): 3 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  proc_exit_prepare, ipc.c:183
2010-06-14 15:12:31 EDTDEBUG:  00000: exit(0)
2010-06-14 15:12:31 EDTLOCATION:  proc_exit, ipc.c:135
2010-06-14 15:12:31 EDTDEBUG:  00000: shmem_exit(-1): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:31 EDTDEBUG:  00000: proc_exit(-1): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  proc_exit_prepare, ipc.c:183
2010-06-14 15:12:31 EDTDEBUG:  00000: logger shutting down
2010-06-14 15:12:31 EDTLOCATION:  SysLoggerMain, syslogger.c:446
2010-06-14 15:12:31 EDTDEBUG:  00000: shmem_exit(0): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:31 EDTDEBUG:  00000: proc_exit(0): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  proc_exit_prepare, ipc.c:183
2010-06-14 15:12:31 EDTDEBUG:  00000: exit(0)
2010-06-14 15:12:31 EDTLOCATION:  proc_exit, ipc.c:135
2010-06-14 15:12:31 EDTDEBUG:  00000: shmem_exit(-1): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  shmem_exit, ipc.c:211
2010-06-14 15:12:31 EDTDEBUG:  00000: proc_exit(-1): 0 callbacks to make
2010-06-14 15:12:31 EDTLOCATION:  proc_exit_prepare, ipc.c:183

----- Original Message -----
From: "Stephen Frost" <sfrost@snowman.net>
To: greigwise@comcast.net
Cc: pgsql-general@postgresql.org
Sent: Saturday, June 12, 2010 12:58:03 AM GMT -05:00 US/Canada Eastern
Subject: Re: [GENERAL] GSS Authentication

* greigwise@comcast.net (greigwise@comcast.net) wrote:
> 2) Setup a new account in AD and used ktpass to create a keytab file for the SPN.

Did you make sure to use the right service name when creating the
keytab?  Can you do a klist -k on the keytab file and send the output?
Does hostname --fqdn return the correct answer on the server?  If not,
you might need to adjust what PG thinks your FQDN is (there's an option
in postgresql.conf for that too, but I'd recommend trying to fix your
server to return the right answer instead of forcing it).

> 3) Copied the keytab file onto my postgres server and updated my postgresql.conf file appropriately (set the krb_server_keyfile to point to the file I just created.)

You'll probably also need to change the default service name to POSTGRES
instead of postgres, in postgresql.conf too, klist -k should help figure
that out.

> Then I wrote a little test Perl program to connect to my postgres database.

Can you test with psql locally first?  Make sure that when you *try* to
connect, it acquires the service princ from the KDC (check using klist)
and then see if it is actually *able* to authenticate to the server.
You'll need to set the appropriate environment variables on both Linux
and Windows tho for libpq to know what the right service name is (again,
POSTGRES instead of postgres, probably).

You may also need to make sure that your default realm is set correctly
and that your reverse DNS is working.  Also, can you look in the PG
server-side logs and see what errors are being reported there?  There
may be some during startup or when the client tries to connect that
would be useful.

        Thanks,

                Stephen