From: aaron-congo (@aaron-congo) <noreply+aaron-congo@github.com>
To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>
Subject: [aws/aws-advanced-jdbc-wrapper] PR #2261: fix: use the built-in equality operator in KMS metadata and key SQL on PostgreSQL
Date: Thu, 19 Nov 2026 17:24:57 +0000
Message-ID: <gh-aws-aws-advanced-jdbc-wrapper-2261@github.com> (raw)
### Summary
Use the built-in equality operator in the KMS encryption plugin's metadata and key SQL on PostgreSQL, and fix integer key id binding in the key lookup methods.
### Description
**Built-in equality operator on PostgreSQL**
- Every comparison in the plugin's SQL against `encryption_metadata` and `key_storage` now uses `OPERATOR(pg_catalog.=)` on PostgreSQL. This covers `MetadataManager`, `KeyManager` and `KeyManagementUtility`. MySQL SQL is unchanged.
- The re-encryption UPDATE on the application's table also uses `OPERATOR(pg_catalog.=)` for the encrypted value column, which is always `bytea` or the `encrypted_data` domain over `bytea`. The primary key comparisons (`pk = ?`, `pk > ?`) keep plain operators, because the primary key can be any type and its own operators define its equality and ordering.
- The re-encryption probe query uses `WHERE FALSE` instead of `WHERE 1=0`.
- `SchemaValidator` calls `pg_catalog.current_schema()`.
- The new `EncryptionSql` helper picks the operator for the database. `MetadataManager` reads the database type from the plugin service, or from the first connection when it's built with a `Connection` or `DataSource`.
**search_path on the plugin's own connections**
- The connections `MetadataManager` and `KeyManager` open for themselves through `PluginService.forceConnect` now run `SET search_path = pg_catalog, pg_temp` on PostgreSQL. These are new, unpooled connections that are closed after each use, so the setting never reaches the application.
- If that statement fails, the connection is closed and the error is raised.
- Connections supplied by the caller (the `Connection` and `DataSource` constructors, and `KeyManagementUtility`) are never changed.
**Integer key ids**
- `KeyManager.getKeyMetadata`, `KeyManager.updateLastUsed` and `KeyManagementUtility.getColumnsUsingKey` bound the integer `key_storage.id` with `setString`, which fails on PostgreSQL. New `int` overloads bind it with `setInt`.
- The `String` versions are deprecated. They parse the id and delegate, and throw `IllegalArgumentException` for a non-numeric id.
- `updateLastUsed` filtered on a `key_id` column that `key_storage` doesn't have. It now filters on `id`.
- `KeyManagementExample` uses the `int` overload.
**Tests**
- Unit tests:
- PostgreSQL metadata and key SQL uses only the qualified operator, and MySQL SQL is unchanged.
- The plugin's own connections have their search_path pinned before each query, and a failed pin closes the connection.
- Caller-supplied connections and MySQL connections are never changed.
- Key ids are bound as integers.
- `FakeReencryptionDatabase` now fails on any PostgreSQL metadata SQL that compares with a plain `=`, which covers the rotation, re-encryption and removal paths.
- New PostgreSQL integration tests in `KmsEncryptionIntegrationTest` add a schema with an always-true `=` on `varchar` and `integer` to the search_path, plus a second encrypted column with its own key:
- Through the plugin, with the schema in the connection's `currentSchema`, only the configured column is encrypted.
- Through `MetadataManager`, `KeyManager` and `KeyManagementUtility` on a session that runs `SET search_path`, lookups and `removeEncryptionForColumn` return or affect only the right rows, and the session's search_path is unchanged afterwards.
- The shadow-schema helpers moved into `KmsTestUtility`, and `KmsTriggerIntegrationTest` uses them.
No user-facing configuration changes. The docs add one requirement for re-encryption on PostgreSQL: the primary key column must have a built-in type.
### Additional Reviewers
### By submitting this pull request, I confirm that my contribution is made under the terms of the Apache 2.0 license.