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

I haven't forgotten this, just been busy.

My first thought was to allow a keytab file to be specified in the
ConnectionInfo map but I think Hive may have a better approach. I wanted to
bounce it past everyone.

Basically Hadoop has a class that will create a Subject from a keytab file.
You then create the database connection using

Connection conn = Subject.doAs(subject, new PrivilegedAction() { ... })

(or something like that) where the privileged action calls
DriverManager.getConnection() as usual. You don't have to wrap just the
getConneciton() call - that's best practice but it's still perfectly
acceptable to wrap the entire call to a connection pool or even more as a
privileged action. The benefit is that you don't have to worry about
passing additional information to the driver somehow - it can pull it from
the usual user authentication information.

I think I saw references to other databases using the same approach but I
couldn't tell you which ones at the moment.

(Implementation-wise - the driver can check for a keytab Subject and use
the information in it.)

Bear

On Mon, Apr 25, 2016 at 6:08 AM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

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