Re: Kerberos problem with pg_ident that happens with JDBC butnot 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@fastcrypt.com> wrote:

Did you ever figure this out ?

FWIW, I'd probably try wireshark to see what the differences are 

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 20 April 2016 at 11:34, Bear Giles <bgiles@coyotesong.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@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@COYOTESONG.COM

null credentials from Ticket Cache

principal is bgiles/postgres@COYOTESONG.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@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@bgiles FATAL:  GSSAPI authentication failed for user "bgiles/postgres"

2016-04-20 00:02:49 UTC [18787-3] bgiles/postgres@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@bgiles LOG:  no match in usermap "gss" for user "bgiles/postgres" authenticated as "bgiles/postgres@COYOTESONG.COM"

2016-04-20 00:13:16 UTC [18919-2] bgiles/postgres@bgiles FATAL:  GSSAPI authentication failed for user "bgiles/postgres"

2016-04-20 00:13:16 UTC [18919-3] bgiles/postgres@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@COYOTESONG\.COM$ \1

(Realm added since I have 'include_realm' in pg_hba.conf configuration. It works with psql.)