Re: Kerberos problem with pg_ident that happens with JDBC but not with PSQL.

That would be awesome. Look forward to it!

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 25 April 2016 at 07:33, Bear Giles <bgiles(at)coyotesong(dot)com> wrote:

> Yes, I was finally able to find the appropriate code and saw that the JDBC
> driver assumes the jdbc connection user and password are the Kerberos user
> and password. It doesn't support keytabs and when I tried giving my user
> and password I ran into another problem because my principal is
> bgiles/postgres (which works with psql) but I don't recall the details.
>
> I've penciled in time to work on a patch.
>
> Bear
>
> On Sun, Apr 24, 2016 at 5:10 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>
>> Did you ever figure this out ?
>>
>> FWIW, I'd probably try wireshark to see what the differences are
>>
>> Dave Cramer
>>
>> davec(at)postgresintl(dot)com
>> www.postgresintl.com
>>
>> On 20 April 2016 at 11:34, Bear Giles <bgiles(at)coyotesong(dot)com> wrote:
>>
>>> I have a 9.4 server configured to work with MIT Kerberos. My pg_hba.conf
>>> file requires matching my realm and my pg_ident.conf file matches anything
>>> with the pattern /([^/]+)/postgres to \1.
>>>
>>> *I can log in via psql.* That's important - it tells me that whatever
>>> is going on is not due to the PostgreSQL server or Kerberos server or their
>>> configuration. I can verify that it's not just blindly matching since I can
>>> log in as bgiles/postgres but not as bgiles or without a kerberos principal
>>> at all.
>>>
>>> *I cannot log in via jdbc/jaas/keytab file.* According to the logs I am
>>> getting authenticated as a member of my realm (so I'm getting past
>>> pg_hba.conf) but I'm not matching anything in the pg_ident.conf file.
>>>
>>> Sometimes it looks like the system is trying to match
>>> bgiles/postgres(at)bgiles instead of bgiles/develop but I'm not seeing
>>> that with the most recent configuration.
>>>
>>> I've tried simplifying the pg_ident.conf entry but with no joy. However
>>> that sidesteps the bigger issue since I can log in via psql. The
>>> configuration files are valid.
>>>
>>> Anyway my breakdown is:
>>>
>>> identical:
>>> - MIT kerberos
>>> - postgresql 9.4
>>> - principal
>>> - keytab file (I'm initializing kinit using the keytab file to be
>>> absolutely certain of this)
>>> - network (same hardware)
>>>
>>> different
>>> - psql (works)
>>> - jdbc (9.4 driver), jaas, java 1.8. (does not)
>>>
>>> My JAAS code based on material I found online. It seems to work (I am
>>> recognized as a valid user by the PostgreSQL server) and I didn't find any
>>> references to the code being broken. It did take me a few hours to find the
>>> right combination of configuration values that let me authenticate per the
>>> logs and per the error message. FWIW it says 'bgiles/postgres' can't be
>>> authenticated but like I said the logs show that I'm getting to the
>>> pg_ident stage.
>>>
>>> That leaves the jdbc driver. Does this make any sense at all?
>>>
>>> I can provide access to the server if it will help. All of this has been
>>> done on AWS EC2 instances and it doesn't take long to spin up.
>>>
>>> *Configuration file:*
>>>
>>> pgjdbc {
>>> com.sun.security.auth.module.Krb5LoginModule required
>>> refreshKrb5Config=true
>>> doNotPrompt=true
>>> useTicketCache=true
>>> renewTGT=false
>>> useKeyTab=true
>>> keyTab="/tmp/krb5.keytab"
>>> debug=true
>>> client=true
>>> principal="bgiles/postgres"
>>> ;
>>> };
>>>
>>> *Test file:*
>>>
>>> public class KerberosPostgreSQLTest {
>>>
>>> static {
>>> URL url =
>>> Thread.currentThread().getContextClassLoader().getResource("jaas.conf");
>>> System.setProperty("java.security.auth.login.config",
>>> url.toExternalForm());
>>> System.setProperty("java.security.krb5.realm", "SNAPLOGIC.COM");
>>> System.setProperty("java.security.krb5.kdc", "kdc");
>>> }
>>>
>>> @Test
>>> public void test() throws Exception {
>>> String url = "jdbc:postgresql://kpg/bgiles";
>>> String user = "bgiles/postgres";
>>>
>>> Properties connInfo = new Properties();
>>> connInfo.put("user", user);
>>> //connInfo.put("kerberosServerName", "postgres");
>>> connInfo.put("jaasApplicationName", "pgjdbc");
>>>
>>> try (Connection conn = DriverManager.getConnection(url,
>>> connInfo)) {
>>>
>>> }
>>> }
>>> }
>>>
>>> *Console:*
>>>
>>> Debug is true storeKey false useTicketCache true useKeyTab true
>>> doNotPrompt true ticketCache is null isInitiator true KeyTab is
>>> /tmp/krb5.keytab refreshKrb5Config is true principal is bgiles/postgres
>>> tryFirstPass is false useFirstPass is false storePass is false clearPass is
>>> false
>>>
>>> Refreshing Kerberos configuration
>>> Acquire TGT from Cache
>>> Principal is bgiles/postgres(at)COYOTESONG(dot)COM
>>> null credentials from Ticket Cache
>>> principal is bgiles/postgres(at)COYOTESONG(dot)COM
>>> Will use keytab
>>> Commit Succeeded
>>>
>>> *(the 'success' refers to being successfully recognized by Kerberos. The
>>> PostgreSQL failure appears as a stack trace.)*
>>>
>>> *Stack Trace:*
>>>
>>> org.postgresql.util.PSQLException: FATAL: GSSAPI authentication failed
>>> for user "bgiles/postgres"
>>> at
>>> org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:433)
>>> at
>>> org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:208)
>>> at
>>> org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
>>> at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:215)
>>> at org.postgresql.Driver.makeConnection(Driver.java:406)
>>> at org.postgresql.Driver.connect(Driver.java:274)
>>> at java.sql.DriverManager.getConnection(DriverManager.java:664)
>>> at java.sql.DriverManager.getConnection(DriverManager.java:208)
>>> at
>>> com.snaplogic.sandbox.KerberosPostgreSQLTest.test(KerberosPostgreSQLTest.java:54)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>> at
>>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
>>> at
>>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>> at java.lang.reflect.Method.invoke(Method.java:498)
>>> at
>>> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
>>> at
>>> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
>>> at
>>> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
>>> at
>>> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
>>> at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
>>> at
>>> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
>>> at
>>> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
>>> at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
>>> at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
>>> at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
>>> at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
>>> at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
>>> at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
>>> at
>>> org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
>>> at
>>> org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
>>> at
>>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
>>> at
>>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:670)
>>> at
>>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
>>> at
>>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
>>>
>>> *PostgreSQL log:*
>>>
>>> 016-04-20 00:02:49 UTC [18787-1] bgiles/postgres(at)bgiles LOG: no match
>>> in usermap "gss" for user "bgiles/postgres" authenticated as
>>> "bgiles/postgres"
>>> 2016-04-20 00:02:49 UTC [18787-2] bgiles/postgres(at)bgiles FATAL: GSSAPI
>>> authentication failed for user "bgiles/postgres"
>>> 2016-04-20 00:02:49 UTC [18787-3] bgiles/postgres(at)bgiles DETAIL:
>>> Connection matched pg_hba.conf line 101: "host all all 75.144.16.201/32 gss
>>> map=gss"
>>>
>>> 016-04-20 00:13:16 UTC [18919-1] bgiles/postgres(at)bgiles LOG: no match
>>> in usermap "gss" for user "bgiles/postgres" authenticated as "bgiles/
>>> postgres(at)COYOTESONG(dot)COM"
>>> 2016-04-20 00:13:16 UTC [18919-2] bgiles/postgres(at)bgiles FATAL: GSSAPI
>>> authentication failed for user "bgiles/postgres"
>>> 2016-04-20 00:13:16 UTC [18919-3] bgiles/postgres(at)bgiles DETAIL:
>>> Connection matched pg_hba.conf line 100: "host all all 75.144.16.201/32 gss
>>> include_realm=1 map=gss krb_realm=COYOTESONG.COM"
>>>
>>> *pg_hba.conf*
>>>
>>> host all all 75.144.16.201/32 gss include_realm=1 map=gss krb_realm=
>>> COYOTESONG.COM
>>>
>>>
>>> *pg_ident.conf*
>>>
>>> # MAPNAME SYSTEM-USERNAME PG-USERNAME
>>> gss /^(.*)/postgres(at)COYOTESONG\(dot)COM$ \1
>>>
>>> *(Realm added since I have 'include_realm' in pg_hba.conf configuration.
>>> It works with psql.)*
>>>
>>
>>
>