pg.ddx.io  aws/aws-advanced-jdbc-wrapper GitHub issues and pull requests  
help / color / mirror / Atom feed
[aws/aws-advanced-jdbc-wrapper] PR #2261: fix: use the built-in equality operator in KMS metadata and key SQL on PostgreSQL
7+ messages / 2 participants
[nested] [flat]

* [aws/aws-advanced-jdbc-wrapper] PR #2261: fix: use the built-in equality operator in KMS metadata and key SQL on PostgreSQL
@ 2026-11-19 17:24  "aaron-congo (@aaron-congo)" <noreply+aaron-congo@github.com>
  0 siblings, 0 replies; 7+ messages in thread

From: aaron-congo (@aaron-congo) @ 2026-11-19 17:24 UTC (permalink / raw)
  To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>

### 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.


^ permalink  raw  [nested|flat] 7+ messages in thread

* Re: [aws/aws-advanced-jdbc-wrapper] PR #2261: fix: use the built-in equality operator in KMS metadata and key SQL on PostgreSQL
@ 2026-11-19 19:44  "sophia-bq (@sophia-bq)" <noreply+sophia-bq@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: sophia-bq (@sophia-bq) @ 2026-11-19 19:44 UTC (permalink / raw)
  To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>

(on wrapper/src/main/java/software/amazon/jdbc/plugin/encryption/key/KeyManagementUtility.java:262)

does this also need qualification?

^ permalink  raw  [nested|flat] 7+ messages in thread

* Re: [aws/aws-advanced-jdbc-wrapper] PR #2261: fix: use the built-in equality operator in KMS metadata and key SQL on PostgreSQL
@ 2026-11-19 19:51  "sophia-bq (@sophia-bq)" <noreply+sophia-bq@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: sophia-bq (@sophia-bq) @ 2026-11-19 19:51 UTC (permalink / raw)
  To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>

(on wrapper/src/main/java/software/amazon/jdbc/plugin/encryption/metadata/MetadataManager.java:116)

Will this skip the pin of the search_path? If we were to SET LOCAL before returning it would that handle the worry about pinning search path when it is a connection we don't own or do we just need documentation this case is not covered?

Similar thing is in KeyManagementUtility

^ permalink  raw  [nested|flat] 7+ messages in thread

* Re: [aws/aws-advanced-jdbc-wrapper] PR #2261: fix: use the built-in equality operator in KMS metadata and key SQL on PostgreSQL
@ 2026-11-19 21:27  "aaron-congo (@aaron-congo)" <noreply+aaron-congo@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: aaron-congo (@aaron-congo) @ 2026-11-19 21:27 UTC (permalink / raw)
  To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>

(on wrapper/src/main/java/software/amazon/jdbc/plugin/encryption/metadata/MetadataManager.java:116)

Yes, that's intentional - the pin only happens on the connections the plugin opens for itself through `forceConnect`. For a `Connection` or `DataSource` the caller passes in (here and in `KeyManagementUtility`), we leave the session alone and rely on the SQL itself: every comparison uses `OPERATOR(pg_catalog.=)` / `OPERATOR(pg_catalog.>)` and every table name is schema-qualified. The casts, `ORDER BY` and `DISTINCT` come from the column types rather than the search_path, so there's nothing left in these statements for the session's search_path to change. On the plugin's own connections the pin is just defense in depth.

I don't think `SET LOCAL` works well for caller-owned connections:
- In autocommit mode, which is the usual case, `SET LOCAL` outside a transaction block has no effect (PostgreSQL just warns), so the next statement would still use the session's search_path.
- To make it stick we'd have to open a transaction on the caller's connection, set it, run the query and commit, which changes their transaction behavior.
- If the caller is already in a transaction, `SET LOCAL` would carry over to the rest of their statements in it, which is the session change we're trying to avoid.

On documentation: there's no gap for users to act on, so I'd rather guard it with tests. `MetadataManagerTest` and `KeyManagerTest` assert the qualified SQL, `FakeReencryptionDatabase` now fails on any plain `=` or `>` in PostgreSQL SQL from `KeyManagementUtility`, and the new integration test runs these lookups on a caller connection with a session-defined `=` and checks that the session's search_path is unchanged.


^ permalink  raw  [nested|flat] 7+ messages in thread

* Re: [aws/aws-advanced-jdbc-wrapper] PR #2261: fix: use the built-in equality operator in KMS metadata and key SQL on PostgreSQL
@ 2026-11-19 21:27  "aaron-congo (@aaron-congo)" <noreply+aaron-congo@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: aaron-congo (@aaron-congo) @ 2026-11-19 21:27 UTC (permalink / raw)
  To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>

(on wrapper/src/main/java/software/amazon/jdbc/plugin/encryption/key/KeyManagementUtility.java:262)

Good catch - yes, it should. I've updated it so the re-encryption SQL qualifies the primary key comparisons too: the page query now uses `pk OPERATOR(pg_catalog.>) ?` and the update uses `pk OPERATOR(pg_catalog.=) ?`, alongside the value column check that was already qualified.

The catch is that the primary key can be any type the application chose, and forcing the pg_catalog operators isn't safe for every type. For `citext`, it would quietly cast to `text` and compare case-sensitively, while `ORDER BY pk` still sorts case-insensitively, so keyset paging could skip rows. For a custom type with no cast to a built-in type, the query would just fail. So on PostgreSQL, `rotateDataKeyAndReencrypt()` and `reencryptRemainingRows()` now check the primary key column's type before changing anything, and refuse with a clear error if it isn't a built-in type (a domain over a built-in type is fine). MySQL is unchanged.

This only affects those two re-encryption methods. Normal reads and writes, with or without the KMS plugin, work with any primary key type. I added a line in the plugin docs about the requirement.

^ permalink  raw  [nested|flat] 7+ messages in thread

* Re: [aws/aws-advanced-jdbc-wrapper] PR #2261 (approved): fix: use the built-in equality operator in KMS metadata and key SQL on PostgreSQL
@ 2026-11-19 21:41  "sophia-bq (@sophia-bq)" <noreply+sophia-bq@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: sophia-bq (@sophia-bq) @ 2026-11-19 21:41 UTC (permalink / raw)
  To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>



^ permalink  raw  [nested|flat] 7+ messages in thread

* Re: [aws/aws-advanced-jdbc-wrapper] PR #2261 (approved): fix: use the built-in equality operator in KMS metadata and key SQL on PostgreSQL
@ 2026-11-20 17:49  "sophia-bq (@sophia-bq)" <noreply+sophia-bq@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: sophia-bq (@sophia-bq) @ 2026-11-20 17:49 UTC (permalink / raw)
  To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>



^ permalink  raw  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2026-11-20 17:49 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-19 17:24 [aws/aws-advanced-jdbc-wrapper] PR #2261: fix: use the built-in equality operator in KMS metadata and key SQL on PostgreSQL "aaron-congo (@aaron-congo)" <noreply+aaron-congo@github.com>
2026-11-19 19:44 ` "sophia-bq (@sophia-bq)" <noreply+sophia-bq@github.com>
2026-11-19 19:51 ` "sophia-bq (@sophia-bq)" <noreply+sophia-bq@github.com>
2026-11-19 21:27 ` "aaron-congo (@aaron-congo)" <noreply+aaron-congo@github.com>
2026-11-19 21:27 ` "aaron-congo (@aaron-congo)" <noreply+aaron-congo@github.com>
2026-11-19 21:41 ` Re: [aws/aws-advanced-jdbc-wrapper] PR #2261 (approved): fix: use the built-in equality operator in KMS metadata and key SQL on PostgreSQL "sophia-bq (@sophia-bq)" <noreply+sophia-bq@github.com>
2026-11-20 17:49 ` Re: [aws/aws-advanced-jdbc-wrapper] PR #2261 (approved): fix: use the built-in equality operator in KMS metadata and key SQL on PostgreSQL "sophia-bq (@sophia-bq)" <noreply+sophia-bq@github.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox