PostgreSQL 14より、新しい認証方式が使われるようになりました。それに対応したv42以上のJDBCドライバを使っていないと、互換性の問題が発生します。
embulk-input-postgresql ではどのようなエラーが起きて、どのように対処すると良いのか、説明します。

結論

(執筆時点での最新版) https://jdbc.postgresql.org/download/postgresql-42.6.0.jar をマシンのローカルに配置して、embulkのinput設定にdriver_pathとoptionsを設定しましょう。

in:
  type: postgresql
  ...
  driver_path: /path/to/postgresql-42.6.0.jar
  options:
    sslmode: require

何がどうなっているのか

embulk-input-postgresql v0.13.2 では、2015年に出た PostgreSQL JDBC Driver 9.4-1205 をバンドルして使っています。

defaultJdbcDriver 'org.postgresql:postgresql:9.4-1205-jdbc41'
https://github.com/embulk/embulk-input-jdbc/blob/master/embulk-input-postgresql/build.gradle#L5

そのドライバを使うとThe authentication type 10 is not supportedというエラーが起きます

Error: java.lang.RuntimeException: org.postgresql.util.PSQLException: The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver.

これは、PostgreSQL v14から標準で採用されたscram-sha-256認証に対応していない事で発生してます。

Change the default of the password_encryption server parameter to scram-sha-256 (Peter Eisentraut)
(from https://www.postgresql.org/docs/release/14.0/)

そこで単にJDBC Driverを指定するだけでは、SSL周りのエラーが起きて少しハマりました。
embulk-input-postgresqlではデフォルトでssl: enables SSL. Data will be encrypted but CA or certification will not be verified (boolean, default: false)という指定があります。
参考 https://github.com/embulk/embulk-input-jdbc/tree/master/embulk-input-postgresql

しかし新しいJDBC Driverでは別のオプションも必要としているので、互換性の問題が発生し、SEVERE: Server name validation failed: エラーが起きてしまいます。

エラーの全容はこちらの通りです。

2023-03-23 06:54:17.014 +0000 [INFO] (0001:transaction): Connecting to jdbc:postgresql://your-cool-server.rds.example.com:5432/your_database options {ApplicationName=embulk-input-postgresql, ssl=true, user=hoge, password=***, tcpKeepAlive=true, sslfactory=org.postgresql.ssl.NonValidatingFactory, loginTimeout=300, socketTimeout=1800}
Mar 23, 2023 6:54:17 AM org.postgresql.ssl.PGjdbcHostnameVerifier verify
SEVERE: Server name validation failed: certificate for host your-cool-server.rds.example.com dNSName entries subjectAltName, but none of them match. Assuming server name validation failed
org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: org.postgresql.util.PSQLException: The hostname your-cool-server.rds.example.com could not be verified by hostnameverifier PgjdbcHostnameVerifier.
	at org.embulk.exec.BulkLoader$LoaderState.buildPartialExecuteException(BulkLoader.java:340)
...

最終的にうまく動作させるには、optionsに、sslmode: requireを記述する必要がありました。
embulk-input-postgresqlでのデフォルトであるssl=true, sslfactory=org.postgresql.ssl.NonValidatingFactoryでは足りないようです。

in:
  type: postgresql
  host: {{ env.DB_HOST }}
  user: {{ env.DB_USER }}
  password: {{ env.DB_PASSWORD }}
  ssl: true
  database: {{ env.DB_DATABASE }}
  default_timezone: UTC
  driver_path: /opt/embulk/postgresql-42.6.0.jar
  options:
    sslmode: require

sslmodeについてはこのあたりの記事が参考になります

なお、JDBC Driverをdigdag workflowと一緒にして送るのはやめましょう。こういうエラーが起きますよ。
error: Status code 400: {"message":"Total size of the archive exceeds limit (2202928 > 2097152 bytes)","status":400}

参考までに、Ansibleの方にはこんな感じの設定を足してます

- name: download PostgreSQL JDBC Driver
  get_url:
    url: https://jdbc.postgresql.org/download/postgresql-42.6.0.jar
    dest: /opt/embulk/postgresql-42.6.0.jar
    owner: embulk
    group: embulk
    mode: 0644

この記事が誰かの役に立てれば幸いです！