1

I am working on a parser that sniffs network traffic and parses the messages of PostgreSQL (Server version 9.6.11). I need to be able to pass the application_name parameter in the login flow (before it returns authentication successful) meaning I cannot rely on "SET application_name" etc.

Is this possible with JDBC? For instance, using ODBC it is possible by setting the libpq parameters: application_name=myapp. And the startup message (https://www.postgresql.org/docs/9.5/protocol-message-formats.html) will contain the parameter alongside username and database.

I am using Wireshark to verify it.

I have tried reading some suggestions here on SO but all solutions rely on setting the application name after the login is complete.

I've also tried via DBeaver's connection settings -> Driver properties -> ApplicationName and I'm getting the same results.

Properties props = new Properties();
props.setProperty("user",user);
props.setProperty("password",password);
props.setProperty("ssl","true");
props.setProperty("sslmode","disable");
props.setProperty("ApplicationName","my-app");

try (Connection connection = DriverManager.getConnection("jdbc:postgresql://"+host+
        ":5432/" + db , props)) {

    System.out.println("Java JDBC PostgreSQL Example");
    Class.forName("org.postgresql.Driver");
    System.out.println("Connected to PostgreSQL database!");
    Statement statement = connection.createStatement();
}

or this,

Properties props = new Properties();
props.setProperty("user",user);
props.setProperty("password",password);
props.setProperty("ssl","true");
props.setProperty("sslmode","disable");

try (Connection connection = DriverManager.getConnection("jdbc:postgresql://"+host+
        ":5432/" + db+ "?application_name=my-app" , props)) {

    System.out.println("Java JDBC PostgreSQL Example");
    Class.forName("org.postgresql.Driver");
    System.out.println("Connected to PostgreSQL database!");
    Statement statement = connection.createStatement();
}

I am looking for a way to replicate the ODBC behavior.

This is not the same question as How to set application name in a Postgresql JDBC url?. I'm asking if its possible to have this parameter passed before the login protocol is completed. When I look at the messages with Wireshark, I can clearly see that with JDBC all methods of passing this parameter are broken down to this message sequence:

  1. login request
  2. login success
  3. SET application_name = my-app

in ODBC its like this:

  1. login request (contains the parameter application_name)
  2. login success
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
skekTek
  • 19
  • 7
  • 2
    `?ApplicationName=my-app` (**not** `application_name`) in the URL [should work](https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters). What is your JDBC driver version? –  Oct 02 '19 at 13:47
  • According to the Postgres JDBC sources the property name is `ApplicationName`. You can try using the `PgProperty.APPLICATION_NAME.getName()` to get the proper name. Or use one of the [`DataSource`](https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/core/BaseConnection.html) implementation from postgres instead of the `DriverManager`. You can use `setApplicationName` on the `BaseDataSource` to set the application name. – M. Deinum Oct 02 '19 at 13:53
  • @a_horse_with_no_name see in my first example I already tried ApplicationName but I tried what you suggested anyway and still the same issue. it sets it after login is complete – skekTek Oct 02 '19 at 14:13
  • @M.Deinum I tried your suggestion and used PGPoolingDataSource (even though its deprecated) with setApplicationName() - still the same – skekTek Oct 02 '19 at 14:16
  • If it sets it after authentication is complete, then there is no other way than to file an improvement request at https://github.com/pgjdbc/pgjdbc However, as far as I can tell from the pgjdbc source code, it already sets it during the StartupPacket **before** authentication. Make sure you are using a recent driver. – Mark Rotteveel Oct 02 '19 at 15:05
  • Specifically see lines 137 and 326 in [`ConnectionFactoryImpl`](https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/core/v3/ConnectionFactoryImpl.java). It **also** runs a `set application_name` statement (line 742) after authentication. – Mark Rotteveel Oct 02 '19 at 15:11
  • @MarkRotteveel I am using JDBC 4.2 PostgreSQL bundle version 42.2.8 – skekTek Oct 02 '19 at 15:13
  • Could you post your Wireshark trace, because according to the implementation, it sets it in the StartupPacket (the first packet sent on connect before the authentication phase) – Mark Rotteveel Oct 02 '19 at 15:21
  • @MarkRotteveel your comment directing me to the source code helped me understand the problem! thanks I had to set PGProperty.ASSUME_MIN_SERVER_VERSION.getName() as well to enter that branch: props.setProperty(PGProperty.ASSUME_MIN_SERVER_VERSION.getName(),"9.6.11"); without this line the message in wireshark doesnt contain the application_name parameter – skekTek Oct 02 '19 at 15:24
  • I had missed that. I have reopened the question, that seems like a specific bit of information that might warrant its own answer. Could you make your comment into an answer? – Mark Rotteveel Oct 02 '19 at 15:27

1 Answers1

1

in the source code https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/core/v3/ConnectionFactoryImpl.java

specifically starting from line 311:

private List<String[]> getParametersForStartup(String user, String database, Properties info) {
    List<String[]> paramList = new ArrayList<String[]>();
    paramList.add(new String[]{"user", user});
    paramList.add(new String[]{"database", database});
    paramList.add(new String[]{"client_encoding", "UTF8"});
    paramList.add(new String[]{"DateStyle", "ISO"});
    paramList.add(new String[]{"TimeZone", createPostgresTimeZone()});

    Version assumeVersion = ServerVersion.from(PGProperty.ASSUME_MIN_SERVER_VERSION.get(info));

    if (assumeVersion.getVersionNum() >= ServerVersion.v9_0.getVersionNum()) {
      // User is explicitly telling us this is a 9.0+ server so set properties here:
      paramList.add(new String[]{"extra_float_digits", "3"});
      String appName = PGProperty.APPLICATION_NAME.get(info);
      if (appName != null) {
        paramList.add(new String[]{"application_name", appName});
      }
    } else {
      // User has not explicitly told us that this is a 9.0+ server so stick to old default:
      paramList.add(new String[]{"extra_float_digits", "2"});
    }

application_name is added to paramList (which is the param list for the startup message as the function name suggests) only if the ASSUME_MIN_SERVER_VERSION is greater or equal to the server-version: 9.0 . this code works as I expect:

        Properties props = new Properties();
        props.setProperty("user",user);
        props.setProperty("password",password);
        props.setProperty("ssl","true");
        props.setProperty("sslmode","disable");
        props.setProperty(PGProperty.ASSUME_MIN_SERVER_VERSION.getName(),"9.6.11");
        props.setProperty(PGProperty.APPLICATION_NAME.getName(),"my-app");
        try (Connection connection = DriverManager.getConnection("jdbc:postgresql://"+host+
                ":5432/" + db+ "?ApplicationName=my-app" , props)) {

            System.out.println("Java JDBC PostgreSQL Example");
            Class.forName("org.postgresql.Driver");
            System.out.println("Connected to PostgreSQL database!");
            Statement statement = connection.createStatement();
        }

main change:

props.setProperty(PGProperty.ASSUME_MIN_SERVER_VERSION.getName(),"9.6.11");
skekTek
  • 19
  • 7