Destination Postgres: Postgres destination connector modifies data in transit

[sukolenvo]

Connector Name

destination-postgres

Connector Version

every

What step the error happened?

Other

Revelant information

Byte array after saving doesn't match original values in the source database.

Here is sample json of airbyte message generated by MS SQL source connector:

{
  "_airbyte_ab_id": "29ca0b24-7671-4903-a144-e992620e4d77",
  "_airbyte_data": {
    "ATTORNEYID": "BIL",
    "Password": "�\u0010\"��P�\u0018",
    "Salt": "%\n��\u001d��]\u0000���z�ˢ�����D**jp\\`k\u0006��",
    "Language": null,
    "acclaim": null,
    "_ab_cdc_updated_at": "2023-09-27T06:51:22.100Z",
    "_ab_cdc_lsn": "0000657a:00001201:0003",
    "_ab_cdc_event_serial_no": "null",
    "_ab_cdc_cursor": 169579747800006560,
    "_ab_cdc_deleted_at": null
  },
  "_airbyte_emitted_at": 1695797478906
}

Value of Salt in _airbyte_raw_attorneys is

%\n��\u001d��]���z�ˢ�����D**jp\\`k\u0006��

"\u0000" is removed from it by:

airbyte/airbyte-integrations/connectors/destination-postgres/src/main/java/io/airbyte/integrations/destination/postgres/PostgresDataAdapter.java

Line 15 in 6ba89fe

final String textValue = jsonNode.textValue().replaceAll("\\u0000", "");

This affects any bytearray column - tokens, salt, password, a image or zip file saved in database will be corrected after running through airbyte-destination-postgres. This cannot be fixed by transforms as I understand as raw value in database is already altered.

Relevant log output

No response

Contribute

• Yes, I want to contribute

[tom-haines]

A related issue is that the default JDBC source encodes binary values as Base 64 via jackson binary node, whereas mssql source marshalls binary arrays into a UTF-8 Java string? (thus creating an invalid string from postgres jsonb type point of view).

See override in source-mssql MssqlSourceOperations.java.

It's also not clear how you can detect whether a given string is base64 or not?