Thread: GSSAPI Authentication Problem

John,

  The ODBC driver can be configured through the ODBC manager and you can
  provide the username that you want to log in as there.  The ODBC
  driver (and the libpq underneath) should still be able to use your
  AD/GSSAPI credentials to authenticate.

      Thanks,

        Stephen

* John Slattery (johntslattery@gmail.com) wrote:
>  Hi,
>
> I would like to report what seems like a problem with the driver. It
> doesn't seem possible to override the default user name for
> authentication by GSSAPI. I'm using a map in pg_ident.conf since my
> Active Directory user name isn't the same as my Postgresql user name.
> pgAdmin III and psql allow for this, the former by setting Username in
> the GUI to my Postgresql user name and the latter by specifying the -U
> option. I tried setting UID in the connection string I am using to my
> Postgresql user name but that caused the driver to return the
> following exception:
>
> Run-time error '-2147217843 (800040e4d)':
> Service negotiation failed;
> The specified target is unknown or unreachable in
> DoKerberosEtcProcessAuthentication:PerformKerberosEtcClientHandSh
>
> The connection string that produces this exception is:
>
> DRIVER={PostgreSQL
> ANSI};DATABASE=db;SERVER=postgresql.my-company.org
>
;PORT=5432;UID=john;PWD=;SSLmode=disable;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;GssAuthUseGSS=0;XaOpt=1
>
> I'm using it in a Visual Basic 6 project.
>
> The version of the driver is 9.1.1.0. The database version is 8.4 from
> Debian 6. Please find mylog_408.log attached.
>
> Thank you for taking a look at this.
>
> John


>
> --
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc

On Fri, Aug 3, 2012 at 8:51 AM, Stephen Frost <sfrost@snowman.net> wrote:
> John,
>
>   The ODBC driver can be configured through the ODBC manager and you can
>   provide the username that you want to log in as there.  The ODBC
>   driver (and the libpq underneath) should still be able to use your
>   AD/GSSAPI credentials to authenticate.
>
>         Thanks,
>
>                 Stephen
>
> * John Slattery (johntslattery@gmail.com) wrote:
>>  Hi,
>>
>> I would like to report what seems like a problem with the driver. It
>> doesn't seem possible to override the default user name for
>> authentication by GSSAPI. I'm using a map in pg_ident.conf since my
>> Active Directory user name isn't the same as my Postgresql user name.
>> pgAdmin III and psql allow for this, the former by setting Username in
>> the GUI to my Postgresql user name and the latter by specifying the -U
>> option. I tried setting UID in the connection string I am using to my
>> Postgresql user name but that caused the driver to return the
>> following exception:
>>
>> Run-time error '-2147217843 (800040e4d)':
>> Service negotiation failed;
>> The specified target is unknown or unreachable in
>> DoKerberosEtcProcessAuthentication:PerformKerberosEtcClientHandSh
>>
>> The connection string that produces this exception is:
>>
>> DRIVER={PostgreSQL
>> ANSI};DATABASE=db;SERVER=postgresql.my-company.org
>>
;PORT=5432;UID=john;PWD=;SSLmode=disable;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;GssAuthUseGSS=0;XaOpt=1
>>
>> I'm using it in a Visual Basic 6 project.
>>
>> The version of the driver is 9.1.1.0. The database version is 8.4 from
>> Debian 6. Please find mylog_408.log attached.
>>
>> Thank you for taking a look at this.
>>
>> John
>
>
>>
>> --
>> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-odbc
>

Stephen,

At your suggestion, I opened the ODBC data source administrator in
Windows XP and attempted to create a user DSN using all of the default
values and providing 'Database', 'Server', and 'User Name'. In this
case 'User Name' was the Active Directory user name. When I pressed
the 'Test' button, I received the same exception I noted in my initial
post. I repeated the test with logging turned on. Nothing seems to
have been recorded about the failed test. The log file is attached.

If I log into the same machine as a user without a mapping in
pg_ident.conf and leave 'User Name' empty, the test is successful. If
I include the user name, which in this case is the same for Active
Directory and Postgresql, I see the same exception.

Could it be that when the only means of authentication enabled in
pg_hba.conf is gss that having anything in 'User Name' is a problem?

John

John,

* John Slattery (johntslattery@gmail.com) wrote:
> At your suggestion, I opened the ODBC data source administrator in
> Windows XP and attempted to create a user DSN using all of the default
> values and providing 'Database', 'Server', and 'User Name'. In this
> case 'User Name' was the Active Directory user name. When I pressed
> the 'Test' button, I received the same exception I noted in my initial
> post. I repeated the test with logging turned on. Nothing seems to
> have been recorded about the failed test. The log file is attached.

No, you should be using the PG username of the user in PG that you want
to connect as in the ODBC driver, not the AD username.

Specifics would help here, I think.  For example-

If the AD user is "joe@REALM.COM", one PG user is "joe", and the user
that you want to actually log into the database as is "smith", then you
need this:

pg_ident mapping joe@REALM.COM (or just "joe" if you're having PG strip
the realm) to "smith".

Log into Windows as "joe@REALM.COM".

Use "smith" in the "User Name" field in the ODBC manager

> Could it be that when the only means of authentication enabled in
> pg_hba.conf is gss that having anything in 'User Name' is a problem?

No.

If you can provide actual specifics regarding the above, and excerpts
from your pg_ident.conf, PostgreSQL logs, pg_hba.conf, and the
client-side logs, I think that would go a long way to figuring this out.

    Thanks,

        Stephen

On Fri, Aug 3, 2012 at 11:54 AM, Stephen Frost <sfrost@snowman.net> wrote:
> John,
>
> * John Slattery (johntslattery@gmail.com) wrote:
>> At your suggestion, I opened the ODBC data source administrator in
>> Windows XP and attempted to create a user DSN using all of the default
>> values and providing 'Database', 'Server', and 'User Name'. In this
>> case 'User Name' was the Active Directory user name. When I pressed
>> the 'Test' button, I received the same exception I noted in my initial
>> post. I repeated the test with logging turned on. Nothing seems to
>> have been recorded about the failed test. The log file is attached.
>
> No, you should be using the PG username of the user in PG that you want
> to connect as in the ODBC driver, not the AD username.
>
> Specifics would help here, I think.  For example-
>
> If the AD user is "joe@REALM.COM", one PG user is "joe", and the user
> that you want to actually log into the database as is "smith", then you
> need this:
>
> pg_ident mapping joe@REALM.COM (or just "joe" if you're having PG strip
> the realm) to "smith".
>
> Log into Windows as "joe@REALM.COM".
>
> Use "smith" in the "User Name" field in the ODBC manager
>
>> Could it be that when the only means of authentication enabled in
>> pg_hba.conf is gss that having anything in 'User Name' is a problem?
>
> No.
>
> If you can provide actual specifics regarding the above, and excerpts
> from your pg_ident.conf, PostgreSQL logs, pg_hba.conf, and the
> client-side logs, I think that would go a long way to figuring this out.
>
>         Thanks,
>
>                 Stephen

Stephen,

First, I must apologize. I proofed that post several times but missed
that I indicated it was the AD name when in fact I had used the PG
name.

Following is the information you suggested reporting. The test is with
'User Name' = 'john'. I used a system DSN generated with the ODBC data
source administrator. Before I set 'User Name' = 'john', I
successfully tested the DSN with user csmprovver whose AD and PG names
are identical with 'User Name' = ''.

*users*

The AD user is jslatter@SOMEREALM.ORG and the PG user is john.

*pg_hba.conf*

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    all         all         10.29.136.81/32       md5
host    all         john        10.29.136.0/21        gss       map=gssapi
host    csmprovver  csmprovver  74.203.196.84/32      gss
host    all         all         10.29.136.0/21        gss

*pg_ident.conf*

# MAPNAME     SYSTEM-USERNAME    PG-USERNAME
gssapi        jslatter           john

*exception generated*

Run-time error '-2147217843 (80040e4d)':
Service negotiation failed;
The specified target is unknown or unreachable in
DoKerberosEtcProcessAuthentication:PerformKerberosEtcClientHandsh

*pg_log*

012-08-03 14:09:42 CDT FATAL:  GSSAPI authentication failed for user "john"

*client logs*

mylog_1116.log and psqlodbc_1116.log are attached. An MSDTC log does
not seem to have been produced.

Thanks for your help.

John

John,

* John Slattery (johntslattery@gmail.com) wrote:
> Following is the information you suggested reporting. The test is with
> 'User Name' = 'john'. I used a system DSN generated with the ODBC data
> source administrator. Before I set 'User Name' = 'john', I
> successfully tested the DSN with user csmprovver whose AD and PG names
> are identical with 'User Name' = ''.

After you have tried to connect, you might try running 'klist' on the
Windows system and reviewing the tickets to see if you acquired a ticket
for the postgres service.

In general, this does look very similar to our setup (which works just
fine).  I will say that we always use "include_realm=1" and then have
the mapping include the realm, eg:

pg_hba.conf:

host    all         all         0.0.0.0/0             gss include_realm=1 map=krbmap

pg_ident.conf:

krbmap        /^[mM]12345@REALM\.ORG$     sfrost

In the end, however, it sounds like that's some kind of GSSAPI issue
that's causing trouble (hence the gssapi auth complaint in the server
log).  Is there any additional information around that error about what
the GSSAPI error is?  Have you tried increasing the verbosity of the
server messages to see if more information is provided?

    Thanks,

        Stephen

John,

  As these are two different users...  Did you have to set any of the PG
  environment variables for libpq?  If so, are you sure that you set
  them for both users..?

  The main one being PGKRBSRVNAME which you might have set to 'postgres'
  (the default is 'POSTGRES' on Windows systems..).

      Thanks,

        Stephen

* John Slattery (johntslattery@gmail.com) wrote:
> On Fri, Aug 3, 2012 at 11:54 AM, Stephen Frost <sfrost@snowman.net> wrote:
> > John,
> >
> > * John Slattery (johntslattery@gmail.com) wrote:
> >> At your suggestion, I opened the ODBC data source administrator in
> >> Windows XP and attempted to create a user DSN using all of the default
> >> values and providing 'Database', 'Server', and 'User Name'. In this
> >> case 'User Name' was the Active Directory user name. When I pressed
> >> the 'Test' button, I received the same exception I noted in my initial
> >> post. I repeated the test with logging turned on. Nothing seems to
> >> have been recorded about the failed test. The log file is attached.
> >
> > No, you should be using the PG username of the user in PG that you want
> > to connect as in the ODBC driver, not the AD username.
> >
> > Specifics would help here, I think.  For example-
> >
> > If the AD user is "joe@REALM.COM", one PG user is "joe", and the user
> > that you want to actually log into the database as is "smith", then you
> > need this:
> >
> > pg_ident mapping joe@REALM.COM (or just "joe" if you're having PG strip
> > the realm) to "smith".
> >
> > Log into Windows as "joe@REALM.COM".
> >
> > Use "smith" in the "User Name" field in the ODBC manager
> >
> >> Could it be that when the only means of authentication enabled in
> >> pg_hba.conf is gss that having anything in 'User Name' is a problem?
> >
> > No.
> >
> > If you can provide actual specifics regarding the above, and excerpts
> > from your pg_ident.conf, PostgreSQL logs, pg_hba.conf, and the
> > client-side logs, I think that would go a long way to figuring this out.
> >
> >         Thanks,
> >
> >                 Stephen
>
> Stephen,
>
> First, I must apologize. I proofed that post several times but missed
> that I indicated it was the AD name when in fact I had used the PG
> name.
>
> Following is the information you suggested reporting. The test is with
> 'User Name' = 'john'. I used a system DSN generated with the ODBC data
> source administrator. Before I set 'User Name' = 'john', I
> successfully tested the DSN with user csmprovver whose AD and PG names
> are identical with 'User Name' = ''.
>
> *users*
>
> The AD user is jslatter@SOMEREALM.ORG and the PG user is john.
>
> *pg_hba.conf*
>
> # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
> host    all         all         10.29.136.81/32       md5
> host    all         john        10.29.136.0/21        gss       map=gssapi
> host    csmprovver  csmprovver  74.203.196.84/32      gss
> host    all         all         10.29.136.0/21        gss
>
> *pg_ident.conf*
>
> # MAPNAME     SYSTEM-USERNAME    PG-USERNAME
> gssapi        jslatter           john
>
> *exception generated*
>
> Run-time error '-2147217843 (80040e4d)':
> Service negotiation failed;
> The specified target is unknown or unreachable in
> DoKerberosEtcProcessAuthentication:PerformKerberosEtcClientHandsh
>
> *pg_log*
>
> 012-08-03 14:09:42 CDT FATAL:  GSSAPI authentication failed for user "john"
>
> *client logs*
>
> mylog_1116.log and psqlodbc_1116.log are attached. An MSDTC log does
> not seem to have been produced.
>
> Thanks for your help.
>
> John

Hi John,

(2012/08/03 21:31), John Slattery wrote:
> Hi,
>
> I would like to report what seems like a problem with the driver. It
> doesn't seem possible to override the default user name for
> authentication by GSSAPI. I'm using a map in pg_ident.conf since my
> Active Directory user name isn't the same as my Postgresql user name.
> pgAdmin III and psql allow for this, the former by setting Username in
> the GUI to my Postgresql user name and the latter by specifying the -U
> option. I tried setting UID in the connection string I am using to my
> Postgresql user name but that caused the driver to return the
> following exception:
>
> Run-time error '-2147217843 <tel:2147217843> (800040e4d)':
> Service negotiation failed;
> The specified target is unknown or unreachable in
> DoKerberosEtcProcessAuthentication:PerformKerberosEtcClientHandSh

How do you login to your Kerberos system?

regards,
Hiroshi Inoue

On Fri, Aug 3, 2012 at 4:41 PM, Stephen Frost <sfrost@snowman.net> wrote:
> John,
>
> * John Slattery (johntslattery@gmail.com) wrote:
>> Following is the information you suggested reporting. The test is with
>> 'User Name' = 'john'. I used a system DSN generated with the ODBC data
>> source administrator. Before I set 'User Name' = 'john', I
>> successfully tested the DSN with user csmprovver whose AD and PG names
>> are identical with 'User Name' = ''.
>
> After you have tried to connect, you might try running 'klist' on the
> Windows system and reviewing the tickets to see if you acquired a ticket
> for the postgres service.
>
> In general, this does look very similar to our setup (which works just
> fine).  I will say that we always use "include_realm=1" and then have
> the mapping include the realm, eg:
>
> pg_hba.conf:
>
> host    all         all         0.0.0.0/0             gss include_realm=1 map=krbmap
>
> pg_ident.conf:
>
> krbmap        /^[mM]12345@REALM\.ORG$     sfrost
>
> In the end, however, it sounds like that's some kind of GSSAPI issue
> that's causing trouble (hence the gssapi auth complaint in the server
> log).  Is there any additional information around that error about what
> the GSSAPI error is?  Have you tried increasing the verbosity of the
> server messages to see if more information is provided?
>
>         Thanks,
>
>                 Stephen

Stephen,

'klist tickets' on the Windows XP client does not show a ticket for
the Postgresql service principal after failed attempts to
authenticate. It does show a ticket after a successful gssapi
authentication with psql.

I added 'include_realm=1' to pg_hba.conf and included the realm in
pg_ident.conf. Authentication by psqlODBC failed. Authentication with
psql was successful.

I increased the logging level on the server as far as it will go, I
believe, and don't see anything that suggests the source of the
authentication failure. The log entries from configuration reload to
authentication failure follow:

*pg_log with log_min_messages = debug5 and log_error_verbosity = verbose*

2012-08-06 08:41:01 CDT LOG:  08P01: incomplete startup packet
2012-08-06 08:41:01 CDT LOCATION:  ProcessStartupPacket, postmaster.c:1525
2012-08-06 08:41:01 CDT LOG:  00000: received SIGHUP, reloading
configuration files
2012-08-06 08:41:01 CDT LOCATION:  SIGHUP_handler, postmaster.c:2051
2012-08-06 08:41:13 CDT DEBUG:  00000: forked new backend, pid=16722 socket=10
2012-08-06 08:41:13 CDT LOCATION:  BackendStartup, postmaster.c:3108
2012-08-06 08:41:13 CDT FATAL:  28000: GSSAPI authentication failed
for user "john"
2012-08-06 08:41:13 CDT LOCATION:  auth_failed, auth.c:273
2012-08-06 08:41:13 CDT DEBUG:  00000: shmem_exit(1): 0 callbacks to make
2012-08-06 08:41:13 CDT LOCATION:  shmem_exit, ipc.c:211
2012-08-06 08:41:13 CDT DEBUG:  00000: proc_exit(1): 1 callbacks to make
2012-08-06 08:41:13 CDT LOCATION:  proc_exit_prepare, ipc.c:183
2012-08-06 08:41:13 CDT DEBUG:  00000: exit(1)
2012-08-06 08:41:13 CDT LOCATION:  proc_exit, ipc.c:135
2012-08-06 08:41:13 CDT DEBUG:  00000: shmem_exit(-1): 0 callbacks to make
2012-08-06 08:41:13 CDT LOCATION:  shmem_exit, ipc.c:211

Thank you for staying with me on this.

John

On Fri, Aug 3, 2012 at 4:45 PM, Stephen Frost <sfrost@snowman.net> wrote:
> John,
>
>   As these are two different users...  Did you have to set any of the PG
>   environment variables for libpq?  If so, are you sure that you set
>   them for both users..?
>
>   The main one being PGKRBSRVNAME which you might have set to 'postgres'
>   (the default is 'POSTGRES' on Windows systems..).
>
>         Thanks,
>
>                 Stephen
>
> * John Slattery (johntslattery@gmail.com) wrote:
>> On Fri, Aug 3, 2012 at 11:54 AM, Stephen Frost <sfrost@snowman.net> wrote:
>> > John,
>> >
>> > * John Slattery (johntslattery@gmail.com) wrote:
>> >> At your suggestion, I opened the ODBC data source administrator in
>> >> Windows XP and attempted to create a user DSN using all of the default
>> >> values and providing 'Database', 'Server', and 'User Name'. In this
>> >> case 'User Name' was the Active Directory user name. When I pressed
>> >> the 'Test' button, I received the same exception I noted in my initial
>> >> post. I repeated the test with logging turned on. Nothing seems to
>> >> have been recorded about the failed test. The log file is attached.
>> >
>> > No, you should be using the PG username of the user in PG that you want
>> > to connect as in the ODBC driver, not the AD username.
>> >
>> > Specifics would help here, I think.  For example-
>> >
>> > If the AD user is "joe@REALM.COM", one PG user is "joe", and the user
>> > that you want to actually log into the database as is "smith", then you
>> > need this:
>> >
>> > pg_ident mapping joe@REALM.COM (or just "joe" if you're having PG strip
>> > the realm) to "smith".
>> >
>> > Log into Windows as "joe@REALM.COM".
>> >
>> > Use "smith" in the "User Name" field in the ODBC manager
>> >
>> >> Could it be that when the only means of authentication enabled in
>> >> pg_hba.conf is gss that having anything in 'User Name' is a problem?
>> >
>> > No.
>> >
>> > If you can provide actual specifics regarding the above, and excerpts
>> > from your pg_ident.conf, PostgreSQL logs, pg_hba.conf, and the
>> > client-side logs, I think that would go a long way to figuring this out.
>> >
>> >         Thanks,
>> >
>> >                 Stephen
>>
>> Stephen,
>>
>> First, I must apologize. I proofed that post several times but missed
>> that I indicated it was the AD name when in fact I had used the PG
>> name.
>>
>> Following is the information you suggested reporting. The test is with
>> 'User Name' = 'john'. I used a system DSN generated with the ODBC data
>> source administrator. Before I set 'User Name' = 'john', I
>> successfully tested the DSN with user csmprovver whose AD and PG names
>> are identical with 'User Name' = ''.
>>
>> *users*
>>
>> The AD user is jslatter@SOMEREALM.ORG and the PG user is john.
>>
>> *pg_hba.conf*
>>
>> # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
>> host    all         all         10.29.136.81/32       md5
>> host    all         john        10.29.136.0/21        gss       map=gssapi
>> host    csmprovver  csmprovver  74.203.196.84/32      gss
>> host    all         all         10.29.136.0/21        gss
>>
>> *pg_ident.conf*
>>
>> # MAPNAME     SYSTEM-USERNAME    PG-USERNAME
>> gssapi        jslatter           john
>>
>> *exception generated*
>>
>> Run-time error '-2147217843 (80040e4d)':
>> Service negotiation failed;
>> The specified target is unknown or unreachable in
>> DoKerberosEtcProcessAuthentication:PerformKerberosEtcClientHandsh
>>
>> *pg_log*
>>
>> 012-08-03 14:09:42 CDT FATAL:  GSSAPI authentication failed for user "john"
>>
>> *client logs*
>>
>> mylog_1116.log and psqlodbc_1116.log are attached. An MSDTC log does
>> not seem to have been produced.
>>
>> Thanks for your help.
>>
>> John
>
>
>

Stephen,

I have PGKRBSRVNAME=POSTGRESQL for both users. The name of the service
principal for PostgreSQL on the server is POSTGRESQL. I also have
PGGSSAPI=gssapi for both users. I'm not really sure the latter is
necessary, but haven't had the opportunity to investigate it yet.

John

On Fri, Aug 3, 2012 at 4:41 PM, Stephen Frost <sfrost@snowman.net> wrote:
> John,
>
> * John Slattery (johntslattery@gmail.com) wrote:
>> Following is the information you suggested reporting. The test is with
>> 'User Name' = 'john'. I used a system DSN generated with the ODBC data
>> source administrator. Before I set 'User Name' = 'john', I
>> successfully tested the DSN with user csmprovver whose AD and PG names
>> are identical with 'User Name' = ''.
>
> After you have tried to connect, you might try running 'klist' on the
> Windows system and reviewing the tickets to see if you acquired a ticket
> for the postgres service.
>
> In general, this does look very similar to our setup (which works just
> fine).  I will say that we always use "include_realm=1" and then have
> the mapping include the realm, eg:
>
> pg_hba.conf:
>
> host    all         all         0.0.0.0/0             gss include_realm=1 map=krbmap
>
> pg_ident.conf:
>
> krbmap        /^[mM]12345@REALM\.ORG$     sfrost
>
> In the end, however, it sounds like that's some kind of GSSAPI issue
> that's causing trouble (hence the gssapi auth complaint in the server
> log).  Is there any additional information around that error about what
> the GSSAPI error is?  Have you tried increasing the verbosity of the
> server messages to see if more information is provided?
>
>         Thanks,
>
>                 Stephen

Stephen,

I noticed a configuration option in postgresql.conf to increase the
message level to the client. I set client_min_messages = debug5 and
generated the attached mylog files.

mylog_1812.log is for an unsuccessful attempt to authenticate with
'User Name' = 'john'. This line from the log seems to suggest that
psqlODBC is not using the correct SPN:

    [3876-0.060]!!! inlen=0 svcprinc=postgres/postgresql.columbia-stmarys.org

It should be 'POSTGRESQL/postgresql.columbia-stmarys.org. An
examination of tickets on the client with klist shows that a ticket is
not present for POSTGRESQL.

The attempt fails with:

    [3876-0.060](-2146893053)The specified target is unknown or
unreachable in DoKerberosEtcProcessAuthentication:PerformKerberosEtcClientHandshake
ERRNO=1

mylog_936.log is for an unsuccessful attempt to authenticate with
'User Name' = 'jslatter'. Predictably, it fails with:

    [2608-0.120]CONN ERROR: func=LIBPQ_connect, desc='', errnum=101,
errmsg='FATAL:  role "jslatter" does not exist

but doesn't complain about a target being unreachable. An examination
of tickets on the client shows that one for
POSTGRESQL/postgresql.columbia-stmarys.org is now present.

Though you've already indicated it's not possible, the only thing that
occurs to me is that in the special case where 'User Name' is
specified, psqlODBC may not be respecting the PGKRBSRVNAME environment
variable.

John

On Sat, Aug 4, 2012 at 3:50 AM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
> Hi John,
>
>
> (2012/08/03 21:31), John Slattery wrote:
>>
>> Hi,
>>
>> I would like to report what seems like a problem with the driver. It
>> doesn't seem possible to override the default user name for
>> authentication by GSSAPI. I'm using a map in pg_ident.conf since my
>> Active Directory user name isn't the same as my Postgresql user name.
>> pgAdmin III and psql allow for this, the former by setting Username in
>> the GUI to my Postgresql user name and the latter by specifying the -U
>> option. I tried setting UID in the connection string I am using to my
>> Postgresql user name but that caused the driver to return the
>> following exception:
>>
>> Run-time error '-2147217843 <tel:2147217843> (800040e4d)':
>>
>> Service negotiation failed;
>> The specified target is unknown or unreachable in
>> DoKerberosEtcProcessAuthentication:PerformKerberosEtcClientHandSh
>
>
> How do you login to your Kerberos system?
>
> regards,
> Hiroshi Inoue
>

Hiroshi,

I'm not sure I understand your question, but I'll take a shot at
answering it. The client is Windows XP, so I would say I'm using the
standard/default Windows GINA for Winlogon.

John

On Mon, Aug 6, 2012 at 10:49 AM, John Slattery <johntslattery@gmail.com> wrote:
> On Fri, Aug 3, 2012 at 4:41 PM, Stephen Frost <sfrost@snowman.net> wrote:
>> John,
>>
>> * John Slattery (johntslattery@gmail.com) wrote:
>>> Following is the information you suggested reporting. The test is with
>>> 'User Name' = 'john'. I used a system DSN generated with the ODBC data
>>> source administrator. Before I set 'User Name' = 'john', I
>>> successfully tested the DSN with user csmprovver whose AD and PG names
>>> are identical with 'User Name' = ''.
>>
>> After you have tried to connect, you might try running 'klist' on the
>> Windows system and reviewing the tickets to see if you acquired a ticket
>> for the postgres service.
>>
>> In general, this does look very similar to our setup (which works just
>> fine).  I will say that we always use "include_realm=1" and then have
>> the mapping include the realm, eg:
>>
>> pg_hba.conf:
>>
>> host    all         all         0.0.0.0/0             gss include_realm=1 map=krbmap
>>
>> pg_ident.conf:
>>
>> krbmap        /^[mM]12345@REALM\.ORG$     sfrost
>>
>> In the end, however, it sounds like that's some kind of GSSAPI issue
>> that's causing trouble (hence the gssapi auth complaint in the server
>> log).  Is there any additional information around that error about what
>> the GSSAPI error is?  Have you tried increasing the verbosity of the
>> server messages to see if more information is provided?
>>
>>         Thanks,
>>
>>                 Stephen
>
> Stephen,
>
> I noticed a configuration option in postgresql.conf to increase the
> message level to the client. I set client_min_messages = debug5 and
> generated the attached mylog files.
>
> mylog_1812.log is for an unsuccessful attempt to authenticate with
> 'User Name' = 'john'. This line from the log seems to suggest that
> psqlODBC is not using the correct SPN:
>
>     [3876-0.060]!!! inlen=0 svcprinc=postgres/postgresql.columbia-stmarys.org
>
> It should be 'POSTGRESQL/postgresql.columbia-stmarys.org. An
> examination of tickets on the client with klist shows that a ticket is
> not present for POSTGRESQL.
>
> The attempt fails with:
>
>     [3876-0.060](-2146893053)The specified target is unknown or
> unreachable in DoKerberosEtcProcessAuthentication:PerformKerberosEtcClientHandshake
> ERRNO=1
>
> mylog_936.log is for an unsuccessful attempt to authenticate with
> 'User Name' = 'jslatter'. Predictably, it fails with:
>
>     [2608-0.120]CONN ERROR: func=LIBPQ_connect, desc='', errnum=101,
> errmsg='FATAL:  role "jslatter" does not exist
>
> but doesn't complain about a target being unreachable. An examination
> of tickets on the client shows that one for
> POSTGRESQL/postgresql.columbia-stmarys.org is now present.
>
> Though you've already indicated it's not possible, the only thing that
> occurs to me is that in the special case where 'User Name' is
> specified, psqlODBC may not be respecting the PGKRBSRVNAME environment
> variable.
>
> John

Sorry. I have a correction to make. The following

    mylog_936.log is for an unsuccessful attempt to authenticate with
'User Name' = 'jslatter'. Predictably, it fails with:

should have been

    mylog_936.log is for an unsuccessful attempt to authenticate with
'User Name' = ''. Predictably, it fails with:

since the test was to specifying nothing for 'User Name'.

(2012/08/07 1:02), John Slattery wrote:
> On Sat, Aug 4, 2012 at 3:50 AM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
>> Hi John,
>>
>>
>> (2012/08/03 21:31), John Slattery wrote:
>>>
>>> Hi,
>>>
>>> I would like to report what seems like a problem with the driver. It
>>> doesn't seem possible to override the default user name for
>>> authentication by GSSAPI. I'm using a map in pg_ident.conf since my
>>> Active Directory user name isn't the same as my Postgresql user name.
>>> pgAdmin III and psql allow for this, the former by setting Username in
>>> the GUI to my Postgresql user name and the latter by specifying the -U
>>> option. I tried setting UID in the connection string I am using to my
>>> Postgresql user name but that caused the driver to return the
>>> following exception:
>>>
>>> Run-time error '-2147217843 <tel:2147217843> (800040e4d)':
>>>
>>> Service negotiation failed;
>>> The specified target is unknown or unreachable in
>>> DoKerberosEtcProcessAuthentication:PerformKerberosEtcClientHandSh
>>
>>
>> How do you login to your Kerberos system?
>>
>> regards,
>> Hiroshi Inoue
>>
>
> Hiroshi,
>
> I'm not sure I understand your question, but I'll take a shot at
> answering it. The client is Windows XP, so I would say I'm using the
> standard/default Windows GINA for Winlogon.

OK I'd like to confirm SSPI is used.
Could you try to set SSLMODE to 'allow' with the user name John?

regards,
Hiroshi Inoue

On Tue, Aug 7, 2012 at 5:51 AM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
> (2012/08/07 1:02), John Slattery wrote:
>>
>> On Sat, Aug 4, 2012 at 3:50 AM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
>>>
>>> Hi John,
>>>
>>>
>>> (2012/08/03 21:31), John Slattery wrote:
>>>>
>>>>
>>>> Hi,
>>>>
>>>> I would like to report what seems like a problem with the driver. It
>>>> doesn't seem possible to override the default user name for
>>>> authentication by GSSAPI. I'm using a map in pg_ident.conf since my
>>>> Active Directory user name isn't the same as my Postgresql user name.
>>>> pgAdmin III and psql allow for this, the former by setting Username in
>>>> the GUI to my Postgresql user name and the latter by specifying the -U
>>>> option. I tried setting UID in the connection string I am using to my
>>>> Postgresql user name but that caused the driver to return the
>>>> following exception:
>>>>
>>>> Run-time error '-2147217843 <tel:2147217843> (800040e4d)':
>>>>
>>>> Service negotiation failed;
>>>> The specified target is unknown or unreachable in
>>>> DoKerberosEtcProcessAuthentication:PerformKerberosEtcClientHandSh
>>>
>>>
>>>
>>> How do you login to your Kerberos system?
>>>
>>> regards,
>>> Hiroshi Inoue
>>>
>>
>> Hiroshi,
>>
>> I'm not sure I understand your question, but I'll take a shot at
>> answering it. The client is Windows XP, so I would say I'm using the
>> standard/default Windows GINA for Winlogon.
>
>
> OK I'd like to confirm SSPI is used.
> Could you try to set SSLMODE to 'allow' with the user name John?
>
> regards,
> Hiroshi Inoue
>

Hiroshi,

I set 'User Name' = 'john' and changed 'SSL Mode' from 'disable' to 'allow'.

It worked.

And I'm baffled. Is there a reason it shouldn't work with 'SSL Mode' =
'disable'? Would you explain?

Thank you.

John

(2012/08/07 23:13), John Slattery wrote:
> On Tue, Aug 7, 2012 at 5:51 AM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
>> (2012/08/07 1:02), John Slattery wrote:
>>>
>>> On Sat, Aug 4, 2012 at 3:50 AM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
>>>>
>>>> Hi John,
>>>>
>>>>
>>>> (2012/08/03 21:31), John Slattery wrote:
>>>>>
>>>>>
>>>>> Hi,
>>>>>
>>>>> I would like to report what seems like a problem with the driver. It
>>>>> doesn't seem possible to override the default user name for
>>>>> authentication by GSSAPI. I'm using a map in pg_ident.conf since my
>>>>> Active Directory user name isn't the same as my Postgresql user name.
>>>>> pgAdmin III and psql allow for this, the former by setting Username in
>>>>> the GUI to my Postgresql user name and the latter by specifying the -U
>>>>> option. I tried setting UID in the connection string I am using to my
>>>>> Postgresql user name but that caused the driver to return the
>>>>> following exception:
>>>>>
>>>>> Run-time error '-2147217843 <tel:2147217843> (800040e4d)':
>>>>>
>>>>> Service negotiation failed;
>>>>> The specified target is unknown or unreachable in
>>>>> DoKerberosEtcProcessAuthentication:PerformKerberosEtcClientHandSh
>>>>
>>>>
>>>>
>>>> How do you login to your Kerberos system?
>>>>
>>>> regards,
>>>> Hiroshi Inoue
>>>>
>>>
>>> Hiroshi,
>>>
>>> I'm not sure I understand your question, but I'll take a shot at
>>> answering it. The client is Windows XP, so I would say I'm using the
>>> standard/default Windows GINA for Winlogon.
>>
>>
>> OK I'd like to confirm SSPI is used.
>> Could you try to set SSLMODE to 'allow' with the user name John?
>>
>> regards,
>> Hiroshi Inoue
>>
>
> Hiroshi,
>
> I set 'User Name' = 'john' and changed 'SSL Mode' from 'disable' to 'allow'.
>
> It worked.
>
> And I'm baffled. Is there a reason it shouldn't work with 'SSL Mode' =
> 'disable'? Would you explain?

Though psqlodbc supports SSPI authentication by itself, it doesn't
look at PGKRBSRVNAME environment variable as you pointed out.
Could you please try the drivers on testing for 9.1.0101 at
   http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/
?

Though psqlodbc communicates with servers by itself, it uses libpq
connections in some cases.
Setting sslmode to other than 'disable' forces psqlodbc to use libpq
connections.
Setting user name to '' also forces psqlodbc to use libpq connections.

regards,
Hiroshi Inoue

On Tue, Aug 7, 2012 at 1:42 PM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
> (2012/08/07 23:13), John Slattery wrote:
>>
>> On Tue, Aug 7, 2012 at 5:51 AM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
>>>
>>> (2012/08/07 1:02), John Slattery wrote:
>>>>
>>>>
>>>> On Sat, Aug 4, 2012 at 3:50 AM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
>>>>>
>>>>>
>>>>> Hi John,
>>>>>
>>>>>
>>>>> (2012/08/03 21:31), John Slattery wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I would like to report what seems like a problem with the driver. It
>>>>>> doesn't seem possible to override the default user name for
>>>>>> authentication by GSSAPI. I'm using a map in pg_ident.conf since my
>>>>>> Active Directory user name isn't the same as my Postgresql user name.
>>>>>> pgAdmin III and psql allow for this, the former by setting Username in
>>>>>> the GUI to my Postgresql user name and the latter by specifying the -U
>>>>>> option. I tried setting UID in the connection string I am using to my
>>>>>> Postgresql user name but that caused the driver to return the
>>>>>> following exception:
>>>>>>
>>>>>> Run-time error '-2147217843 <tel:2147217843> (800040e4d)':
>>>>>>
>>>>>> Service negotiation failed;
>>>>>> The specified target is unknown or unreachable in
>>>>>> DoKerberosEtcProcessAuthentication:PerformKerberosEtcClientHandSh
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> How do you login to your Kerberos system?
>>>>>
>>>>> regards,
>>>>> Hiroshi Inoue
>>>>>
>>>>
>>>> Hiroshi,
>>>>
>>>> I'm not sure I understand your question, but I'll take a shot at
>>>> answering it. The client is Windows XP, so I would say I'm using the
>>>> standard/default Windows GINA for Winlogon.
>>>
>>>
>>>
>>> OK I'd like to confirm SSPI is used.
>>> Could you try to set SSLMODE to 'allow' with the user name John?
>>>
>>> regards,
>>> Hiroshi Inoue
>>>
>>
>> Hiroshi,
>>
>> I set 'User Name' = 'john' and changed 'SSL Mode' from 'disable' to
>> 'allow'.
>>
>> It worked.
>>
>> And I'm baffled. Is there a reason it shouldn't work with 'SSL Mode' =
>> 'disable'? Would you explain?
>
>
> Though psqlodbc supports SSPI authentication by itself, it doesn't
> look at PGKRBSRVNAME environment variable as you pointed out.
> Could you please try the drivers on testing for 9.1.0101 at
>   http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/
> ?
>
> Though psqlodbc communicates with servers by itself, it uses libpq
> connections in some cases.
> Setting sslmode to other than 'disable' forces psqlodbc to use libpq
> connections.
> Setting user name to '' also forces psqlodbc to use libpq connections.
>
> regards,
> Hiroshi Inoue

A connection test with the 9.1.0101 testing 32bit drivers is
successful when 'User Name' = 'john' and 'SSL Mode' = 'allow'. When
'User Name' = 'john' and 'SSL Mode' = 'disable', the connection test
responds with: Warning: GSS authentication not supported.

Is there anything else I should try?

(2012/08/08 5:03), John Slattery wrote:
> On Tue, Aug 7, 2012 at 1:42 PM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
>> (2012/08/07 23:13), John Slattery wrote:
>>>
>>> On Tue, Aug 7, 2012 at 5:51 AM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
>>>>
>>>> (2012/08/07 1:02), John Slattery wrote:
>>>>>
>>>>> On Sat, Aug 4, 2012 at 3:50 AM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
>>>>>>
>>>>>>
>>>>>> Hi John,
>>>>>>
>>>>>> (2012/08/03 21:31), John Slattery wrote:
>>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> I would like to report what seems like a problem with the driver. It
>>>>>>> doesn't seem possible to override the default user name for
>>>>>>> authentication by GSSAPI. I'm using a map in pg_ident.conf since my
>>>>>>> Active Directory user name isn't the same as my Postgresql user name.
>>>>>>> pgAdmin III and psql allow for this, the former by setting Username in
>>>>>>> the GUI to my Postgresql user name and the latter by specifying the -U
>>>>>>> option. I tried setting UID in the connection string I am using to my
>>>>>>> Postgresql user name but that caused the driver to return the
>>>>>>> following exception:
>>>>>>>
>>>>>>> Run-time error '-2147217843 <tel:2147217843> (800040e4d)':
>>>>>>>
>>>>>>> Service negotiation failed;
>>>>>>> The specified target is unknown or unreachable in
>>>>>>> DoKerberosEtcProcessAuthentication:PerformKerberosEtcClientHandSh
>>>>>>
>>>>>>
>>>>>> How do you login to your Kerberos system?
>>>>>>
>>>>>> regards,
>>>>>> Hiroshi Inoue
>>>>>>
>>>>> Hiroshi,
>>>>>
>>>>> I'm not sure I understand your question, but I'll take a shot at
>>>>> answering it. The client is Windows XP, so I would say I'm using the
>>>>> standard/default Windows GINA for Winlogon.
>>>>
>>>>
>>>> OK I'd like to confirm SSPI is used.
>>>> Could you try to set SSLMODE to 'allow' with the user name John?
>>>>
>>>> regards,
>>>> Hiroshi Inoue
>>>>
>>>
>>> Hiroshi,
>>>
>>> I set 'User Name' = 'john' and changed 'SSL Mode' from 'disable' to
>>> 'allow'.
>>>
>>> It worked.
>>>
>>> And I'm baffled. Is there a reason it shouldn't work with 'SSL Mode' =
>>> 'disable'? Would you explain?
>>
>>
>> Though psqlodbc supports SSPI authentication by itself, it doesn't
>> look at PGKRBSRVNAME environment variable as you pointed out.
>> Could you please try the drivers on testing for 9.1.0101 at
>>    http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/
>> ?
>>
>> Though psqlodbc communicates with servers by itself, it uses libpq
>> connections in some cases.
>> Setting sslmode to other than 'disable' forces psqlodbc to use libpq
>> connections.
>> Setting user name to '' also forces psqlodbc to use libpq connections.
>>
>> regards,
>> Hiroshi Inoue
>
> A connection test with the 9.1.0101 testing 32bit drivers is
> successful when 'User Name' = 'john' and 'SSL Mode' = 'allow'. When
> 'User Name' = 'john' and 'SSL Mode' = 'disable', the connection test
> responds with: Warning: GSS authentication not supported.
>
> Is there anything else I should try?

OK I updated the drivers.
PLease retry the drivers on testing for 9.1.0101 at
   http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/
.

regards,
Hiroshi Inoue

On Wed, Aug 8, 2012 at 8:22 AM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
> (2012/08/08 5:03), John Slattery wrote:
>>
>> On Tue, Aug 7, 2012 at 1:42 PM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
>>>
>>> (2012/08/07 23:13), John Slattery wrote:
>>>>
>>>>
>>>> On Tue, Aug 7, 2012 at 5:51 AM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
>>>>>
>>>>>
>>>>> (2012/08/07 1:02), John Slattery wrote:
>>>>>>
>>>>>>
>>>>>> On Sat, Aug 4, 2012 at 3:50 AM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Hi John,
>>>>>>>
>>>>>>> (2012/08/03 21:31), John Slattery wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> I would like to report what seems like a problem with the driver. It
>>>>>>>> doesn't seem possible to override the default user name for
>>>>>>>> authentication by GSSAPI. I'm using a map in pg_ident.conf since my
>>>>>>>> Active Directory user name isn't the same as my Postgresql user
>>>>>>>> name.
>>>>>>>> pgAdmin III and psql allow for this, the former by setting Username
>>>>>>>> in
>>>>>>>> the GUI to my Postgresql user name and the latter by specifying the
>>>>>>>> -U
>>>>>>>> option. I tried setting UID in the connection string I am using to
>>>>>>>> my
>>>>>>>> Postgresql user name but that caused the driver to return the
>>>>>>>> following exception:
>>>>>>>>
>>>>>>>> Run-time error '-2147217843 <tel:2147217843> (800040e4d)':
>>>>>>>>
>>>>>>>> Service negotiation failed;
>>>>>>>> The specified target is unknown or unreachable in
>>>>>>>> DoKerberosEtcProcessAuthentication:PerformKerberosEtcClientHandSh
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> How do you login to your Kerberos system?
>>>>>>>
>>>>>>> regards,
>>>>>>> Hiroshi Inoue
>>>>>>>
>>>>>> Hiroshi,
>>>>>>
>>>>>> I'm not sure I understand your question, but I'll take a shot at
>>>>>> answering it. The client is Windows XP, so I would say I'm using the
>>>>>> standard/default Windows GINA for Winlogon.
>>>>>
>>>>>
>>>>>
>>>>> OK I'd like to confirm SSPI is used.
>>>>> Could you try to set SSLMODE to 'allow' with the user name John?
>>>>>
>>>>> regards,
>>>>> Hiroshi Inoue
>>>>>
>>>>
>>>> Hiroshi,
>>>>
>>>> I set 'User Name' = 'john' and changed 'SSL Mode' from 'disable' to
>>>> 'allow'.
>>>>
>>>> It worked.
>>>>
>>>> And I'm baffled. Is there a reason it shouldn't work with 'SSL Mode' =
>>>> 'disable'? Would you explain?
>>>
>>>
>>>
>>> Though psqlodbc supports SSPI authentication by itself, it doesn't
>>> look at PGKRBSRVNAME environment variable as you pointed out.
>>> Could you please try the drivers on testing for 9.1.0101 at
>>>    http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/
>>> ?
>>>
>>> Though psqlodbc communicates with servers by itself, it uses libpq
>>> connections in some cases.
>>> Setting sslmode to other than 'disable' forces psqlodbc to use libpq
>>> connections.
>>> Setting user name to '' also forces psqlodbc to use libpq connections.
>>>
>>> regards,
>>> Hiroshi Inoue
>>
>>
>> A connection test with the 9.1.0101 testing 32bit drivers is
>> successful when 'User Name' = 'john' and 'SSL Mode' = 'allow'. When
>> 'User Name' = 'john' and 'SSL Mode' = 'disable', the connection test
>> responds with: Warning: GSS authentication not supported.
>>
>> Is there anything else I should try?
>
>
> OK I updated the drivers.
> PLease retry the drivers on testing for 9.1.0101 at
>   http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/
> .
>
> regards,
> Hiroshi Inoue

Connection tests with the ANSI and Unicode 8/8/2012 9.1.0101 testing
32bit drivers were successful on both

    'User Name' = 'john' and 'SSL Mode' = 'allow'

and

    'User Name' = 'john' and 'SSL Mode' = 'disable'

I also ran the same cases in my test application successfully.

I think you have it!

Thanks.

John