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

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.)*
>>
>
>