Repository: GoogleCloudPlatform/pgadapter
Pull request #169 (2022-06-08 18:58:48)

feat: automatically detect client that is connected
Automatically detects the client that is creating a connection to
PGAdapter based on the data that PGAdapter receives in the
StartupMessage. Not all clients can be uniquely identified this way, and
clients could easily pretend to be a client they are not, but this
feature is only used to automatically apply query replacements based on
the client that is connecting.

Fixes b/235348017

codecov[bot]: # [Codecov](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) Report
> Merging [#169](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) (082236f) into [postgresql-dialect](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/commit/1f0ab3ad03dc0a0608163a4331efbdac454a02aa?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) (1f0ab3a) will **decrease** coverage by `0.31%`.
> The diff coverage is `71.01%`.

> :exclamation: Current head 082236f differs from pull request most recent head aa8e225. Consider uploading reports for the commit aa8e225 to get more accurate results

```diff
@@                   Coverage Diff                    @@
##             postgresql-dialect     #169      +/-   ##
========================================================
- Coverage                 77.64%   77.32%   -0.32%     
- Complexity                  958      976      +18     
========================================================
  Files                        85       86       +1     
  Lines                      3283     3343      +60     
  Branches                    384      402      +18     
========================================================
+ Hits                       2549     2585      +36     
- Misses                      578      594      +16     
- Partials                    156      164       +8     
```

| Flag | Coverage Δ | |
|---|---|---|
| all_tests | `77.32% <71.01%> (-0.32%)` | :arrow_down: |

Flags with carried forward coverage won't be shown. [Click here](https://docs.codecov.io/docs/carryforward-flags?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#carryforward-flags-in-the-pull-request-comment) to find out more.

| [Impacted Files](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) | Coverage Δ | |
|---|---|---|
| [...ud/spanner/pgadapter/utils/ClientAutoDetector.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3V0aWxzL0NsaWVudEF1dG9EZXRlY3Rvci5qYXZh) | `42.85% <42.85%> (ø)` | |
| [...d/spanner/pgadapter/wireprotocol/QueryMessage.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3dpcmVwcm90b2NvbC9RdWVyeU1lc3NhZ2UuamF2YQ==) | `90.74% <50.00%> (-1.72%)` | :arrow_down: |
| [...spanner/pgadapter/statements/MatcherStatement.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3N0YXRlbWVudHMvTWF0Y2hlclN0YXRlbWVudC5qYXZh) | `85.00% <78.57%> (-15.00%)` | :arrow_down: |
| [...gle/cloud/spanner/pgadapter/ConnectionHandler.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL0Nvbm5lY3Rpb25IYW5kbGVyLmphdmE=) | `72.30% <100.00%> (-0.56%)` | :arrow_down: |
| [...ud/spanner/pgadapter/metadata/OptionsMetadata.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL21ldGFkYXRhL09wdGlvbnNNZXRhZGF0YS5qYXZh) | `80.10% <100.00%> (+0.43%)` | :arrow_up: |
| [...er/pgadapter/statements/IntermediateStatement.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3N0YXRlbWVudHMvSW50ZXJtZWRpYXRlU3RhdGVtZW50LmphdmE=) | `94.16% <100.00%> (+0.02%)` | :arrow_up: |
| [...anner/pgadapter/wireprotocol/BootstrapMessage.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3dpcmVwcm90b2NvbC9Cb290c3RyYXBNZXNzYWdlLmphdmE=) | `95.45% <100.00%> (+0.58%)` | :arrow_up: |
| [...spanner/pgadapter/wireprotocol/StartupMessage.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL3dpcmVwcm90b2NvbC9TdGFydHVwTWVzc2FnZS5qYXZh) | `65.78% <100.00%> (+12.21%)` | :arrow_up: |
| [...om/google/cloud/spanner/pgadapter/ProxyServer.java](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform#diff-c3JjL21haW4vamF2YS9jb20vZ29vZ2xlL2Nsb3VkL3NwYW5uZXIvcGdhZGFwdGVyL1Byb3h5U2VydmVyLmphdmE=) | `72.12% <0.00%> (-1.22%)` | :arrow_down: |
| ... and [1 more](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169/diff?src=pr&el=tree-more&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform). Last update [1f0ab3a...aa8e225](https://codecov.io/gh/GoogleCloudPlatform/pgadapter/pull/169?src=pr&el=lastupdated&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=GoogleCloudPlatform).


Patch:
diff --git a/.ci/run-with-credentials.sh b/.ci/run-with-credentials.sh
index ee2c4799..96f6705f 100755
--- a/.ci/run-with-credentials.sh
+++ b/.ci/run-with-credentials.sh
@@ -76,7 +76,7 @@ e2e-psql)
   echo "Starting PGAdapter"
   ls -lh target
   UBER_JAR="pgadapter.jar"
-  (java -jar target/"${UBER_JAR}" -p "${GOOGLE_CLOUD_PROJECT}" -i "${GOOGLE_CLOUD_INSTANCE}" -d "${GOOGLE_CLOUD_DATABASE_WITH_VERSION}" -e "${GOOGLE_CLOUD_ENDPOINT}" -s 4242 -q -ddl AutocommitImplicitTransaction > /dev/null 2>&1) &
+  (java -jar target/"${UBER_JAR}" -p "${GOOGLE_CLOUD_PROJECT}" -i "${GOOGLE_CLOUD_INSTANCE}" -d "${GOOGLE_CLOUD_DATABASE_WITH_VERSION}" -e "${GOOGLE_CLOUD_ENDPOINT}" -s 4242 -ddl AutocommitImplicitTransaction > /dev/null 2>&1) &
   BACK_PID=$!
   sleep 1
 #  execute psql and evaluate result
diff --git a/README.md b/README.md
index 0557c1fe..fe2dff49 100644
--- a/README.md
+++ b/README.md
@@ -167,18 +167,6 @@ The following options are optional:
     authenticate. Note that SSL is not included for the same reason that
     authentication logic is not: since all connections are local, sniffing
     traffic should not generally be a concern.
-
--q
-  * PSQL Mode. Use this option when fronting PSQL. This option will incur some
-    performance penalties due to query matching and translation and as such is
-    not recommended for production. It is further not guaranteed to perfectly
-    match PSQL logic. Please only use this mode when using PSQL.
-    
--jdbc
-  * JDBC Mode. Use this option when fronting the native PostgreSQL JDBC driver.
-    This option will inspect queries to look for native JDBC metadata queries and
-    replace these with queries that are suppported by Cloud Spanner PostgreSQL
-    databases.
     
 -c <multi-statementcommand>
     Runs a single command before exiting. This command can be comprised of multiple 
diff --git a/docs/jdbc.md b/docs/jdbc.md
index 79873e37..c2ce16fe 100644
--- a/docs/jdbc.md
+++ b/docs/jdbc.md
@@ -5,11 +5,11 @@ version 42.0.0 and higher.
 
 ## Usage
 
-First start PGAdapter in JDBC mode by adding `-jdbc` to the command line parameters:
+First start PGAdapter:
 
 ```shell
 wget https://storage.googleapis.com/pgadapter-jar-releases/pgadapter.tar.gz && tar -xzvf pgadapter.tar.gz
-java -jar pgadapter.jar -p my-project -i my-instance -d my-database -jdbc
+java -jar pgadapter.jar -p my-project -i my-instance -d my-database
 ```
 
 Connect to PGAdapter like this:
diff --git a/docs/psql.md b/docs/psql.md
index 9463f83d..c0d43d48 100644
--- a/docs/psql.md
+++ b/docs/psql.md
@@ -4,7 +4,7 @@ PGAdapter supports [psql](https://www.postgresql.org/docs/current/app-psql.html)
 
 ## Usage
 
-First start PGAdapter in `psql` mode by adding `-q` to the command line parameters.
+First start PGAdapter.
 
 ```shell
 export GOOGLE_APPLICATION_CREDENTIALS=/path/to/credentials.json
@@ -15,12 +15,11 @@ docker run \
   -e GOOGLE_APPLICATION_CREDENTIALS \
   gcr.io/cloud-spanner-pg-adapter/pgadapter \
   -p my-project -i my-instance -d my-database \
-  -x \
-  -q
+  -x
 ```
 
-- The `-q` option tells PGAdapter to translate queries from `psql` for meta commands like `\dt` into
-  queries that are supported by Cloud Spanner.
+- PGAdapter automatically recognizes connections from psql and translates queries from `psql` for 
+  meta commands like `\dt` into queries that are supported by Cloud Spanner.
 - The `-x` option tells PGAdapter to accept incoming connections from other hosts than localhost.
   This is necessary when running PGAdapter in a Docker container, as you will be connecting to it
   from outside the container, which will be seen as a connection from a different host.
@@ -46,8 +45,7 @@ docker run \
   -e GOOGLE_APPLICATION_CREDENTIALS \
   gcr.io/cloud-spanner-pg-adapter/pgadapter \
   -p my-project -i my-instance -d my-database \
-  -x \
-  -q
+  -x
 psql -h localhost -p 5433
 ```
 
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/ConnectionHandler.java b/src/main/java/com/google/cloud/spanner/pgadapter/ConnectionHandler.java
index cea50164..ab17325a 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/ConnectionHandler.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/ConnectionHandler.java
@@ -27,6 +27,7 @@ import com.google.cloud.spanner.pgadapter.statements.ExtendedQueryProtocolHandle
 import com.google.cloud.spanner.pgadapter.statements.IntermediatePortalStatement;
 import com.google.cloud.spanner.pgadapter.statements.IntermediatePreparedStatement;
 import com.google.cloud.spanner.pgadapter.statements.IntermediateStatement;
+import com.google.cloud.spanner.pgadapter.utils.ClientAutoDetector.WellKnownClient;
 import com.google.cloud.spanner.pgadapter.wireoutput.ErrorResponse;
 import com.google.cloud.spanner.pgadapter.wireoutput.ErrorResponse.Severity;
 import com.google.cloud.spanner.pgadapter.wireoutput.ReadyResponse;
@@ -83,6 +84,7 @@ public class ConnectionHandler extends Thread {
   private ConnectionMetadata connectionMetadata;
   private WireMessage message;
   private Connection spannerConnection;
+  private WellKnownClient wellKnownClient;
   private ExtendedQueryProtocolHandler extendedQueryProtocolHandler;
 
   ConnectionHandler(ProxyServer server, Socket socket) {
@@ -467,6 +469,14 @@ public class ConnectionHandler extends Thread {
     this.status = status;
   }
 
+  public WellKnownClient getWellKnownClient() {
+    return wellKnownClient;
+  }
+
+  public void setWellKnownClient(WellKnownClient wellKnownClient) {
+    this.wellKnownClient = wellKnownClient;
+  }
+
   /** Status of a {@link ConnectionHandler} */
   public enum ConnectionStatus {
     UNAUTHENTICATED,
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadata.java b/src/main/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadata.java
index 3dddf719..fa122231 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadata.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadata.java
@@ -70,6 +70,7 @@ public class OptionsMetadata {
   private static final String OPTION_CREDENTIALS_FILE = "c";
   private static final String OPTION_BINARY_FORMAT = "b";
   private static final String OPTION_AUTHENTICATE = "a";
+  private static final String OPTION_DISABLE_AUTO_DETECT_CLIENT = "disable_auto_detect_client";
   private static final String OPTION_PSQL_MODE = "q";
   private static final String OPTION_DDL_TRANSACTION_MODE = "ddl";
   private static final String OPTION_JDBC_MODE = "jdbc";
@@ -99,6 +100,7 @@ public class OptionsMetadata {
   private final TextFormat textFormat;
   private final boolean binaryFormat;
   private final boolean authenticate;
+  private final boolean disableAutoDetectClient;
   private final boolean requiresMatcher;
   private final DdlTransactionMode ddlTransactionMode;
   private final boolean replaceJdbcMetadataQueries;
@@ -128,6 +130,7 @@ public class OptionsMetadata {
     this.textFormat = TextFormat.POSTGRESQL;
     this.binaryFormat = commandLine.hasOption(OPTION_BINARY_FORMAT);
     this.authenticate = commandLine.hasOption(OPTION_AUTHENTICATE);
+    this.disableAutoDetectClient = commandLine.hasOption(OPTION_DISABLE_AUTO_DETECT_CLIENT);
     this.requiresMatcher =
         commandLine.hasOption(OPTION_PSQL_MODE)
             || commandLine.hasOption(OPTION_COMMAND_METADATA_FILE);
@@ -182,6 +185,7 @@ public class OptionsMetadata {
     this.textFormat = textFormat;
     this.binaryFormat = forceBinary;
     this.authenticate = authenticate;
+    this.disableAutoDetectClient = false;
     this.requiresMatcher = requiresMatcher;
     this.ddlTransactionMode = DdlTransactionMode.AutocommitImplicitTransaction;
     this.replaceJdbcMetadataQueries = replaceJdbcMetadataQueries;
@@ -419,11 +423,19 @@ public class OptionsMetadata {
         "authenticate",
         false,
         "Whether you wish the proxy to perform an authentication step.");
+    options.addOption(
+        null,
+        OPTION_DISABLE_AUTO_DETECT_CLIENT,
+        false,
+        "This option turns off automatic detection of well-known clients. "
+            + "Use this option if you do not want PGAdapter to automatically apply query "
+            + "replacements based on the client that is connected to PGAdapter.");
     options.addOption(
         OPTION_PSQL_MODE,
         "psql-mode",
         false,
-        "This option turns on PSQL mode. This mode allows better compatibility to PSQL, with an"
+        "DEPRECATED: PGAdapter will automatically detect connections from psql."
+            + " This option turns on PSQL mode. This mode allows better compatibility to PSQL, with an"
             + " added performance cost. PSQL mode is implemented using predefined dynamic matchers"
             + " and as such cannot be used with the option -j. This mode should not be used for"
             + " production, and we do not guarantee its functionality beyond the basics.");
@@ -451,7 +463,8 @@ public class OptionsMetadata {
         OPTION_JDBC_MODE,
         "jdbc-mode",
         false,
-        "This option turns on JDBC mode. This mode allows better compatibility with the "
+        "DEPRECATED: PGAdapter will automatically detect connections from JDBC. "
+            + "This option turns on JDBC mode. This mode allows better compatibility with the "
             + "PostgreSQL JDBC driver. It will automatically inspect incoming queries to look for "
             + "known JDBC metadata queries, and replace these with queries that are compatible with "
             + "Cloud Spanner. JDBC mode is implemented using predefined fixed matchers and should "
@@ -519,6 +532,7 @@ public class OptionsMetadata {
         help.printHelp(CLI_ARGS, options);
         System.exit(0);
       }
+      printDeprecatedWarnings(commandLine);
       return commandLine;
     } catch (ParseException e) {
       help.printHelp(CLI_ARGS, options);
@@ -526,6 +540,21 @@ public class OptionsMetadata {
     }
   }
 
+  static void printDeprecatedWarnings(CommandLine commandLine) {
+    if (commandLine.hasOption(OPTION_PSQL_MODE)) {
+      System.out.printf(
+          "It is no longer necessary to add psql mode (-%s) to the command line arguments.\n"
+              + "PGAdapter now automatically recognizes connections from psql.\n",
+          OPTION_PSQL_MODE);
+    }
+    if (commandLine.hasOption(OPTION_JDBC_MODE)) {
+      System.out.printf(
+          "It is no longer necessary to add JDBC mode (-%s) to the command line arguments.\n"
+              + "PGAdapter now automatically recognizes connections from the PostgreSQL JDBC driver.\n",
+          OPTION_JDBC_MODE);
+    }
+  }
+
   public DdlTransactionMode getDdlTransactionMode() {
     return this.ddlTransactionMode;
   }
@@ -594,6 +623,10 @@ public class OptionsMetadata {
     return this.authenticate;
   }
 
+  public boolean shouldAutoDetectClient() {
+    return !this.disableAutoDetectClient;
+  }
+
   public boolean requiresMatcher() {
     return this.requiresMatcher;
   }
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/statements/SimpleQueryStatement.java b/src/main/java/com/google/cloud/spanner/pgadapter/statements/SimpleQueryStatement.java
index ba183623..6c8caa3b 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/statements/SimpleQueryStatement.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/statements/SimpleQueryStatement.java
@@ -23,6 +23,7 @@ import com.google.cloud.spanner.connection.PostgreSQLStatementParser;
 import com.google.cloud.spanner.pgadapter.ConnectionHandler;
 import com.google.cloud.spanner.pgadapter.commands.Command;
 import com.google.cloud.spanner.pgadapter.metadata.OptionsMetadata;
+import com.google.cloud.spanner.pgadapter.utils.ClientAutoDetector.WellKnownClient;
 import com.google.cloud.spanner.pgadapter.wireprotocol.BindMessage;
 import com.google.cloud.spanner.pgadapter.wireprotocol.ControlMessage.ManuallyCreatedToken;
 import com.google.cloud.spanner.pgadapter.wireprotocol.DescribeMessage;
@@ -66,10 +67,13 @@ public class SimpleQueryStatement {
     // Do a Parse-Describe-Bind-Execute round-trip for each statement in the query string.
     // Finish with a Sync to close any implicit transaction and to return the results.
     for (ParsedStatement statement : this.statements) {
-      if (options.requiresMatcher()) {
+      if (options.requiresMatcher()
+          || connectionHandler.getWellKnownClient() == WellKnownClient.PSQL) {
         statement = translatePotentialMetadataCommand(statement, connectionHandler);
       }
-      statement = replaceKnownUnsupportedQueries(this.options, statement);
+      statement =
+          replaceKnownUnsupportedQueries(
+              this.connectionHandler.getWellKnownClient(), this.options, statement);
       new ParseMessage(connectionHandler, statement).send();
       new BindMessage(connectionHandler, ManuallyCreatedToken.MANUALLY_CREATED_TOKEN).send();
       new DescribeMessage(connectionHandler, ManuallyCreatedToken.MANUALLY_CREATED_TOKEN).send();
@@ -80,8 +84,8 @@ public class SimpleQueryStatement {
 
   /** Replaces any known unsupported query (e.g. JDBC metadata queries). */
   static ParsedStatement replaceKnownUnsupportedQueries(
-      OptionsMetadata options, ParsedStatement parsedStatement) {
-    if (options.isReplaceJdbcMetadataQueries()
+      WellKnownClient client, OptionsMetadata options, ParsedStatement parsedStatement) {
+    if ((options.isReplaceJdbcMetadataQueries() || client == WellKnownClient.JDBC)
         && JdbcMetadataStatementHelper.isPotentialJdbcMetadataStatement(
             parsedStatement.getSqlWithoutComments())) {
       return PARSER.parse(
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/utils/ClientAutoDetector.java b/src/main/java/com/google/cloud/spanner/pgadapter/utils/ClientAutoDetector.java
new file mode 100644
index 00000000..e373290c
--- /dev/null
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/utils/ClientAutoDetector.java
@@ -0,0 +1,94 @@
+// Copyright 2022 Google LLC
+//
+// Licensed under the Apache License, Version 2.0 (the "License");
+// you may not use this file except in compliance with the License.
+// You may obtain a copy of the License at
+//
+//      http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+
+package com.google.cloud.spanner.pgadapter.utils;
+
+import com.google.api.core.InternalApi;
+import java.util.List;
+import java.util.Map;
+
+/**
+ * Utility class that tries to automatically detect well-known clients and drivers that are
+ * connecting to PGAdapter.
+ */
+@InternalApi
+public class ClientAutoDetector {
+  public enum WellKnownClient {
+    PSQL {
+      @Override
+      boolean isClient(List<String> orderedParameterKeys, Map<String, String> parameters) {
+        // PSQL makes it easy for us, as it sends its own name in the application_name parameter.
+        return parameters.containsKey("application_name")
+            && parameters.get("application_name").equals("psql");
+      }
+    },
+    JDBC {
+      @Override
+      boolean isClient(List<String> orderedParameterKeys, Map<String, String> parameters) {
+        // JDBC always sends the following startup parameters as the first five parameters (and has
+        // done so for more than 5 years):
+        // 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()});
+        if (orderedParameterKeys.size() < 5) {
+          return false;
+        }
+        if (!orderedParameterKeys.get(0).equals("user")) {
+          return false;
+        }
+        if (!orderedParameterKeys.get(1).equals("database")) {
+          return false;
+        }
+        if (!orderedParameterKeys.get(2).equals("client_encoding")) {
+          return false;
+        }
+        if (!orderedParameterKeys.get(3).equals("DateStyle")) {
+          return false;
+        }
+        if (!orderedParameterKeys.get(4).equals("TimeZone")) {
+          return false;
+        }
+        if (!parameters.get("client_encoding").equals("UTF8")) {
+          return false;
+        }
+        return parameters.get("DateStyle").equals("ISO");
+      }
+    },
+    PGX {
+      @Override
+      boolean isClient(List<String> orderedParameterKeys, Map<String, String> parameters) {
+        // pgx does not send enough unique parameters for it to be auto-detected.
+        return false;
+      }
+    };
+
+    abstract boolean isClient(List<String> orderedParameterKeys, Map<String, String> parameters);
+  }
+
+  /**
+   * Returns the {@link WellKnownClient} that the detector thinks is connecting to PGAdapter based
+   * purely on the list of parameters.
+   */
+  public static WellKnownClient detectClient(
+      List<String> orderParameterKeys, Map<String, String> parameters) {
+    for (WellKnownClient client : WellKnownClient.values()) {
+      if (client.isClient(orderParameterKeys, parameters)) {
+        return client;
+      }
+    }
+    return null;
+  }
+}
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/BootstrapMessage.java b/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/BootstrapMessage.java
index cba6c40f..bf48a01d 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/BootstrapMessage.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/BootstrapMessage.java
@@ -23,7 +23,9 @@ import com.google.cloud.spanner.pgadapter.wireoutput.ParameterStatusResponse;
 import com.google.cloud.spanner.pgadapter.wireoutput.ReadyResponse;
 import com.google.cloud.spanner.pgadapter.wireoutput.ReadyResponse.Status;
 import java.io.DataOutputStream;
+import java.util.ArrayList;
 import java.util.HashMap;
+import java.util.List;
 import java.util.Map;
 import java.util.TimeZone;
 
@@ -81,6 +83,19 @@ public abstract class BootstrapMessage extends WireMessage {
     return parameters;
   }
 
+  /**
+   * Parses the parameter keys from the given raw parameter string and returns these in the received
+   * order. This can be used to detect the client that is connecting to PGAdapter.
+   */
+  protected List<String> parseParameterKeys(String rawParameters) {
+    List<String> keys = new ArrayList<>();
+    String[] paramArray = rawParameters.split(new String(new byte[] {(byte) 0}));
+    for (int i = 0; i < paramArray.length; i = i + 2) {
+      keys.add(paramArray[i]);
+    }
+    return keys;
+  }
+
   /**
    * Expected PG start-up reply, including Auth approval, Key Data connection-specific info,
    * PGAdapter specific parameters, and a ready signal.
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/StartupMessage.java b/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/StartupMessage.java
index 1923c702..34c076f4 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/StartupMessage.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/wireprotocol/StartupMessage.java
@@ -17,10 +17,14 @@ package com.google.cloud.spanner.pgadapter.wireprotocol;
 import com.google.api.core.InternalApi;
 import com.google.cloud.spanner.pgadapter.ConnectionHandler;
 import com.google.cloud.spanner.pgadapter.ConnectionHandler.ConnectionStatus;
+import com.google.cloud.spanner.pgadapter.utils.ClientAutoDetector;
+import com.google.cloud.spanner.pgadapter.utils.ClientAutoDetector.WellKnownClient;
 import com.google.cloud.spanner.pgadapter.wireoutput.MD5AuthenticationRequest;
 import java.io.IOException;
 import java.text.MessageFormat;
 import java.util.Map;
+import java.util.logging.Level;
+import java.util.logging.Logger;
 
 /**
  * The first (non-encryption, non-admin) message expected in from a client in a connection loop.
@@ -28,7 +32,7 @@ import java.util.Map;
  */
 @InternalApi
 public class StartupMessage extends BootstrapMessage {
-
+  private static final Logger logger = Logger.getLogger(StartupMessage.class.getName());
   private static final String USER_KEY = "user";
   public static final int IDENTIFIER = 196608; // First Hextet: 3 (version), Second Hextet: 0
 
@@ -38,7 +42,21 @@ public class StartupMessage extends BootstrapMessage {
   public StartupMessage(ConnectionHandler connection, int length) throws Exception {
     super(connection, length);
     this.authenticate = connection.getServer().getOptions().shouldAuthenticate();
-    this.parameters = this.parseParameters(this.readAll());
+    String rawParameters = this.readAll();
+    this.parameters = this.parseParameters(rawParameters);
+    if (connection.getServer().getOptions().shouldAutoDetectClient()) {
+      WellKnownClient wellKnownClient =
+          ClientAutoDetector.detectClient(this.parseParameterKeys(rawParameters), this.parameters);
+      if (wellKnownClient != null) {
+        logger.log(
+            Level.INFO,
+            () ->
+                String.format(
+                    "Well-known client %s detected for connection %d.",
+                    wellKnownClient, connection.getConnectionId()));
+        connection.setWellKnownClient(wellKnownClient);
+      }
+    }
   }
 
   @Override
@@ -56,13 +74,7 @@ public class StartupMessage extends BootstrapMessage {
     }
   }
 
-  /**
-   * Here we expect the nextHandler to be {@link PasswordMessage} if we authenticate. Otherwise
-   * default.
-   *
-   * @return PasswordMessage if auth is set to true, else default.
-   * @throws Exception
-   */
+  /** Here we expect the nextHandler to be {@link PasswordMessage} if we authenticate. */
   @Override
   public void nextHandler() throws Exception {
     if (authenticate) {


Test patch:
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/statements/IntermediateStatement.java b/src/main/java/com/google/cloud/spanner/pgadapter/statements/IntermediateStatement.java
index 5b9fcdd9..cf78356f 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/statements/IntermediateStatement.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/statements/IntermediateStatement.java
@@ -69,7 +69,8 @@ public class IntermediateStatement {
     this.connectionHandler = connectionHandler;
     this.options = options;
     this.parsedStatement =
-        SimpleQueryStatement.replaceKnownUnsupportedQueries(this.options, parsedStatement);
+        SimpleQueryStatement.replaceKnownUnsupportedQueries(
+            this.connectionHandler.getWellKnownClient(), this.options, parsedStatement);
     this.connection = connectionHandler.getSpannerConnection();
     this.command = StatementParser.parseCommand(this.parsedStatement.getSqlWithoutComments());
     this.commandTag = this.command;
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/ITJdbcMetadataTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/ITJdbcMetadataTest.java
index ddd972ac..380ea367 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/ITJdbcMetadataTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/ITJdbcMetadataTest.java
@@ -36,7 +36,6 @@ import java.sql.SQLException;
 import java.sql.SQLFeatureNotSupportedException;
 import java.sql.Types;
 import java.util.Arrays;
-import java.util.Collections;
 import java.util.HashMap;
 import java.util.Map;
 import java.util.function.Consumer;
@@ -67,8 +66,7 @@ public class ITJdbcMetadataTest implements IntegrationTest {
   public static void setup() {
     testEnv.setUp();
     database = testEnv.createDatabase(getDdlStatements());
-    testEnv.startPGAdapterServerWithDefaultDatabase(
-        database.getId(), getAdditionalPGAdapterOptions());
+    testEnv.startPGAdapterServerWithDefaultDatabase(database.getId(), ImmutableList.of());
   }
 
   @AfterClass
@@ -77,10 +75,6 @@ public class ITJdbcMetadataTest implements IntegrationTest {
     testEnv.cleanUp();
   }
 
-  private static Iterable<String> getAdditionalPGAdapterOptions() {
-    return Collections.singleton("-jdbc");
-  }
-
   private static Iterable<String> getDdlStatements() {
     ImmutableList.Builder<String> builder = ImmutableList.builder();
     builder.addAll(PgAdapterTestEnv.DEFAULT_DATA_MODEL);
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadataTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadataTest.java
index 4b6a8328..4a9ee5fd 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadataTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/metadata/OptionsMetadataTest.java
@@ -63,4 +63,13 @@ public class OptionsMetadataTest {
         new OptionsMetadata(new String[] {"-p p", "-i i", "-max_backlog 100"});
     assertEquals(100, options.getMaxBacklog());
   }
+
+  @Test
+  public void testDisableAutoDetectClient() {
+    OptionsMetadata options = new OptionsMetadata(new String[] {"-p p", "-i i"});
+    assertTrue(options.shouldAutoDetectClient());
+
+    options = new OptionsMetadata(new String[] {"-p p", "-i i", "-disable_auto_detect_client"});
+    assertFalse(options.shouldAutoDetectClient());
+  }
 }
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/utils/ClientAutoDetectorTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/utils/ClientAutoDetectorTest.java
new file mode 100644
index 00000000..b101546d
--- /dev/null
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/utils/ClientAutoDetectorTest.java
@@ -0,0 +1,197 @@
+// Copyright 2022 Google LLC
+//
+// Licensed under the Apache License, Version 2.0 (the "License");
+// you may not use this file except in compliance with the License.
+// You may obtain a copy of the License at
+//
+//      http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+
+package com.google.cloud.spanner.pgadapter.utils;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNotEquals;
+
+import com.google.cloud.spanner.pgadapter.utils.ClientAutoDetector.WellKnownClient;
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableMap;
+import java.util.ArrayList;
+import java.util.List;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.JUnit4;
+
+@RunWith(JUnit4.class)
+public class ClientAutoDetectorTest {
+  @Test
+  public void testJdbc() {
+    // The JDBC driver will **always** include these startup parameters in exactly this order.
+    // The values of 'client_encoding' and 'DateStyle' are fixed.
+    // Any other permutation of the first 5 parameters should fail the JDBC check.
+    assertEquals(
+        WellKnownClient.JDBC,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of("user", "database", "client_encoding", "DateStyle", "TimeZone"),
+            ImmutableMap.of(
+                "user", "some-user",
+                "database", "some-database",
+                "client_encoding", "UTF8",
+                "DateStyle", "ISO",
+                "TimeZone", "some-time-zone")));
+    // Additional parameters are allowed.
+    assertEquals(
+        WellKnownClient.JDBC,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of(
+                "user", "database", "client_encoding", "DateStyle", "TimeZone", "some-other-param"),
+            ImmutableMap.of(
+                "user", "some-user",
+                "database", "some-database",
+                "client_encoding", "UTF8",
+                "DateStyle", "ISO",
+                "TimeZone", "some-time-zone",
+                "some-other-param", "param-value")));
+
+    assertNotEquals(
+        WellKnownClient.JDBC,
+        ClientAutoDetector.detectClient(ImmutableList.of(), ImmutableMap.of()));
+    assertNotEquals(
+        WellKnownClient.JDBC,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of("user", "database", "client_encoding", "DateStyle"),
+            ImmutableMap.of(
+                "user", "some-user",
+                "database", "some-database",
+                "client_encoding", "UTF8",
+                "DateStyle", "ISO")));
+    assertNotEquals(
+        WellKnownClient.JDBC,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of("user", "database", "client_encoding", "TimeZone"),
+            ImmutableMap.of(
+                "user", "some-user",
+                "database", "some-database",
+                "client_encoding", "UTF8",
+                "TimeZone", "some-time-zone")));
+    assertNotEquals(
+        WellKnownClient.JDBC,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of("user", "database", "DateStyle", "TimeZone"),
+            ImmutableMap.of(
+                "user", "some-user",
+                "database", "some-database",
+                "DateStyle", "ISO",
+                "TimeZone", "some-time-zone")));
+    assertNotEquals(
+        WellKnownClient.JDBC,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of("user", "client_encoding", "DateStyle", "TimeZone"),
+            ImmutableMap.of(
+                "user", "some-user",
+                "client_encoding", "UTF8",
+                "DateStyle", "ISO",
+                "TimeZone", "some-time-zone")));
+    assertNotEquals(
+        WellKnownClient.JDBC,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of("database", "client_encoding", "DateStyle", "TimeZone"),
+            ImmutableMap.of(
+                "database", "some-database",
+                "client_encoding", "UTF8",
+                "DateStyle", "ISO",
+                "TimeZone", "some-time-zone")));
+    // A different value (even if it's only a difference in case) for client_encoding or DateStyle
+    // will also fail the detection of the JDBC driver.
+    assertNotEquals(
+        WellKnownClient.JDBC,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of("user", "database", "client_encoding", "DateStyle", "TimeZone"),
+            ImmutableMap.of(
+                "user", "some-user",
+                "database", "some-database",
+                "client_encoding", "utf8",
+                "DateStyle", "ISO",
+                "TimeZone", "some-time-zone")));
+    assertNotEquals(
+        WellKnownClient.JDBC,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of("user", "database", "client_encoding", "DateStyle", "TimeZone"),
+            ImmutableMap.of(
+                "user", "some-user",
+                "database", "some-database",
+                "client_encoding", "UTF8",
+                "DateStyle", "iso",
+                "TimeZone", "some-time-zone")));
+    ImmutableList<String> keys =
+        ImmutableList.of("user", "database", "client_encoding", "DateStyle", "TimeZone");
+    for (int i = 0; i < keys.size(); i++) {
+      List<String> list = new ArrayList<>(keys);
+      list.set(i, "foo");
+      assertNotEquals(
+          WellKnownClient.JDBC,
+          ClientAutoDetector.detectClient(
+              list,
+              ImmutableMap.of(
+                  "user", "some-user",
+                  "database", "some-database",
+                  "client_encoding", "UTF8",
+                  "DateStyle", "ISO",
+                  "TimeZone", "some-time-zone")));
+    }
+  }
+
+  @Test
+  public void testPsql() {
+    // Psql always includes itself as the application_name.
+    assertEquals(
+        WellKnownClient.PSQL,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of("application_name"), ImmutableMap.of("application_name", "psql")));
+    assertEquals(
+        WellKnownClient.PSQL,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of("application_name", "some-param"),
+            ImmutableMap.of(
+                "application_name", "psql",
+                "some-param", "some-value")));
+    assertEquals(
+        WellKnownClient.PSQL,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of("some-param1", "application_name", "some-param2"),
+            ImmutableMap.of(
+                "some-param1", "some-value1",
+                "application_name", "psql",
+                "some-param2", "some-value2")));
+    assertNotEquals(
+        WellKnownClient.PSQL,
+        ClientAutoDetector.detectClient(ImmutableList.of(), ImmutableMap.of()));
+    assertNotEquals(
+        WellKnownClient.PSQL,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of("some-param1"), ImmutableMap.of("some-param1", "some-value1")));
+    assertNotEquals(
+        WellKnownClient.PSQL,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of("application_name"), ImmutableMap.of("application_name", "JDBC")));
+    assertNotEquals(
+        WellKnownClient.PSQL,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of("application_name"), ImmutableMap.of("application_name", "PSQL")));
+  }
+
+  @Test
+  public void testPgx() {
+    assertNotEquals(
+        WellKnownClient.PGX,
+        ClientAutoDetector.detectClient(ImmutableList.of(), ImmutableMap.of()));
+    assertNotEquals(
+        WellKnownClient.PGX,
+        ClientAutoDetector.detectClient(
+            ImmutableList.of("some-param"), ImmutableMap.of("some-param", "some-value")));
+  }
+}