pg.ddx.io aws/aws-advanced-jdbc-wrapper GitHub issues and pull requestshelp / 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