Trying to get SSPI/JDBC working

Better luck on this list.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

---------- Forwarded message ----------
From: Dave Rosckes <dave.rosckes@gmail.com>
Date: 2 December 2014 at 11:10
Subject: [GENERAL] Trying to get SSPI/JDBC working
To: pgsql-general@postgresql.org

I am new to Postgresql. I have a postgres server running on a windows
platform. The DBs will not have any remote access, only users logged onto
the localhost will have access. SSPI seems like a great authentication
protocol to use in this case.

I believe I have followed all the instructions on how to get this to work:

Postgresql: postgresql-9.3.4-4-windows-x64

Updated pg_hba.conf to have the following lines:
host all all 127.0.0.1/32 sspi
host all all ::1/128 md5

Created role that matches userid via pgAdmin III

JDBC connection url:
"jdbc:postgresql://localhost/postgresDB?integratedSecurity=true"
JDBC jar: postgresql-9.3-1100.jdbc4.jar

Looking in the pglog all I see is:

CST FATAL: SSPI authentication failed for user "roscked"
CST DETAIL: Connection matched pg_hba.conf line 80: "host
all all 127.0.0.1/32 sspi"

The exception I see:

org.postgresql.util.PSQLException: GSS Authentication failed

Any guidance on how to get more information on the failure, or suggestion
on what I am doing wrong would be greatly appreciated.

On 12/02/2014 08:10 AM, Dave Rosckes wrote:

I am new to Postgresql. I have a postgres server running on a windows
platform. The DBs will not have any remote access, only users logged
onto the localhost will have access. SSPI seems like a great
authentication protocol to use in this case.

I believe I have followed all the instructions on how to get this to work:

Postgresql: postgresql-9.3.4-4-windows-x64

Updated pg_hba.conf to have the following lines:
host all all 127.0.0.1/32
<http://127.0.0.1/32&gt; sspi
host all all ::1/128 md5

Created role that matches userid via pgAdmin III

JDBC connection url:
"jdbc:postgresql://localhost/postgresDB?integratedSecurity=true"
JDBC jar: postgresql-9.3-1100.jdbc4.jar

Looking in the pglog all I see is:

CST FATAL: SSPI authentication failed for user "roscked"
CST DETAIL: Connection matched pg_hba.conf line 80: "host
all all 127.0.0.1/32 <http://127.0.0.1/32&gt; sspi"

The exception I see:

org.postgresql.util.PSQLException: GSS Authentication failed

Is there more to the exception, like maybe a traceback?

Is there anything in the Windows system logs that would help?

Any guidance on how to get more information on the failure, or
suggestion on what I am doing wrong would be greatly appreciated.

--
Adrian Klaver
adrian.klaver@aklaver.com

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

JDBC connection url:
"jdbc:postgresql://localhost/postgresDB?integratedSecurity=true"

I don't know where you got "integratedSecurity=true". The only reference
I can find to that is in Microsoft's documentation for MS SQL server.
There is no such parameter in PgJDBC, nor in core PotsgreSQL.

Until very recently (i.e. last week) PgJDBC did not support SSPI
natively. It was potentially possible to use it via direct access to the
Kerberos ticket, but it required lots of setup and was generally painful
and impractical.

I implemented SSPI support for PgJDBC a few weeks ago, and merged it
into the core driver this week. If you download the latest git tree of
PgJDBC and compile it, you will be able to use SSPI. No additional
parameters should be required.

There is not yet a binary release of the driver with SSPI support, but
it's pretty easy to compile PgJDBC if you've used git before. Just clone
the repository from https://github.com/pgjdbc/pgjdbc and then "ant -lib
lib all" in the source tree. You can also "ant -lib lib dist" if you
want to make a distribution zip file. See the README shown at the bottom
of https://github.com/pgjdbc/pgjdbc for more details.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

On 12/03/2014 10:14 PM, Dave Rosckes wrote:

Craig,

Thanks for implementing the SSPI support for PgJDBC. When will the new
PgJDBC be released?

I have never used git before, so if there is a place I can download the
jdbc jar with the SSPI support that would be great. If that is not an
option, I will look into setting up a git environment.

Dave,

(Please reply-to-all, so the mailing list sees the conversation for
others' benefit later, searches, etc.)

There's no set release date for the first 9.4.x driver at this point.
We'll likely release some time after PostgreSQL 9.4.0 goes final, when
time permits. Resources for PgJDBC driver development are unfortunately
extremely limited.

I can send you a .zip of the jar and dependencies if you don't want to
build with git. It'll just be a build of the latest git master head
produced with "ant dist", and it'll very much be a "no guarantees, no
promises" snapshot build. You'd need to tell me what JDBC version / JDK
version you need. I won't necessarily be able to follow up with new
builds if you need newer snapshots though, so I really recommend
learning to compile it yourself.

git is pretty friendly for the basics, and it's a tool I think every
developer should become familiar with. Perhaps now is a good
opportunity? All you need to build the driver is git (to check it out
from revision control) and ant (to build it), plus a JDK matching the
version you want.

msysgit offers a pretty nice GUI for basic git operations on Windows:

http://git-scm.com/download/win

http://msysgit.github.io/

and the README for the PgJDBC driver explains how to compile it. If you
have problems compiling it, please follow up on the PgJDBC mailing list.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

If you could send me the jdbc4 (I am using 1.6) that would be great. If I
need further builds before its officially released, I will get git setup in
my environment and build it for myself.

Again thanks a lot for your help,

On Wed, Dec 3, 2014 at 8:28 AM, Craig Ringer <craig@2ndquadrant.com> wrote:

Craig,

I got git working and have built the latest version of the PgJDBC
(postgresql-9.4-1200.jdbc4.jar which includes the SSPI support). Do I need
to also use Postgres 9.4 (RC1) with this JDBC, or should it work with the
9.3 version I currently have installed?

On Wed, Dec 3, 2014 at 8:28 AM, Craig Ringer <craig@2ndquadrant.com> wrote:

On 12/04/2014 10:31 PM, Dave Rosckes wrote:

I got git working and have built the latest version of the PgJDBC
(postgresql-9.4-1200.jdbc4.jar which includes the SSPI support). Do I
need to also use Postgres 9.4 (RC1) with this JDBC, or should it work
with the 9.3 version I currently have installed?

Great, I'm glad to hear it.

The 9.4 JDBC driver supports at least as far back as PostgreSQL 8.2.
You'll be quite fine using it on 9.3.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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