chrisdutz opened a new pull request, #19350:
URL: https://github.com/apache/iotdb/pull/19350

   ## Description
   
   This draft adds a **PostgreSQL wire protocol service** to IoTDB. PostgreSQL 
clients can then
   query the **table model**, read-only: psql, JDBC tools (pgJDBC, DBeaver), 
Python (psycopg),
   .NET (Npgsql), and BI tools that connect through PostgreSQL connectors, such 
as Microsoft Power BI
   (Import and DirectQuery) and Excel.
   
   It was developed in the ToddySoft DB fork of IoTDB and ported to `master` 
for this PR. The
   community has asked about a PR, so I'm opening it as a **draft**, for two 
reasons:
   
   1. to discuss the design, and the places where this version deliberately 
differs from the fork
      (see "Differences from the fork" below), and
   2. to **ask for help reviewing the golden files** of the SQL translator (see 
"Help wanted").
   
   ### Example
   
   ```properties
   # conf/iotdb-system.properties
   enable_postgresql_service=true
   postgresql_service_address=127.0.0.1
   postgresql_service_port=5432
   ```
   
   Recorded with psql 17 against a standalone server from this branch, bound to 
`0.0.0.0` (so
   TLS is required) with `key_store_path` set (host name and port replaced by 
placeholders):
   
   ```
   $ psql "host=db.example port=5432 user=root dbname=iotdb sslmode=require" -e
   iotdb=> \dn
    List of schemas
    Name  | Owner
   -------+-------
    plant | root
   (1 row)
   
   iotdb=> SELECT device, date_trunc('hour', time) AS hour, avg(temp)
   iotdb->   FROM plant.sensors GROUP BY 1, 2 ORDER BY 1, 2;
    device |          hour          | avg
   --------+------------------------+-----
    d1     | 2026-01-15 09:00:00+00 |  21
    d2     | 2026-01-15 09:00:00+00 |  19
   (2 rows)
   
   iotdb=> INSERT INTO plant.sensors (time, device, temp) VALUES (now(), 'd3', 
1.0);
   ERROR:  cannot execute INSERT in a read-only transaction
   
   $ psql "host=db.example port=5432 user=root dbname=iotdb sslmode=disable"
   psql: error: connection to server at "db.example", port 5432 failed:
   FATAL:  encryption required: connect with SSL (sslmode=require)
   ```
   
   Every IoTDB database appears as a PostgreSQL schema (`plant.sensors` is 
table `sensors` of
   database `plant`). Queries are translated into table-model SQL and executed 
by the normal engine,
   so filters and aggregations stay in the engine. A construct without an exact 
translation is
   refused with SQLSTATE `0A000` and named; it is never answered approximately.
   
   ### Design
   
   **Module and start.** The new module `external-service-impl/postgresql` is a 
built-in external
   service like REST and MQTT: a `POSTGRESQL` entry in 
`BuiltinExternalServices`, enabled by
   `enable_postgresql_service` (default `false`). The jar-with-dependencies 
goes to `lib/` of the
   `all` and `edge` distributions and of the IT nodes. It has to be in `lib/` 
and not in
   `ext/external_service`, because the catalog tables (below) are registered 
through the class path
   when the DataNode starts. The only third-party dependency is JSqlParser 5.4 
(dual LGPL-2.1 /
   Apache-2.0, used under Apache-2.0, added to `LICENSE-binary`). Netty comes 
from the DataNode.
   
   **Core changes (small):**
   - `BuiltinExternalServices.POSTGRESQL`, plus `IoTDBConfig`/`IoTDBDescriptor`:
     `enable_postgresql_service`.
   - `ExternalServiceClientSession`: an `IClientSession` for clients that come 
in through an
     external service speaking another protocol. `IClientSession`'s connection 
methods are
     package-private, so a plugin cannot subclass it itself.
   - `iotdb-system.properties.template`: the "PostgreSQL Service Configuration" 
section.
   - IT framework: typed `DataNodeConfig` setters for the service's keys.
   
   **Protocol** (`server`, `protocol`, `session`, `execution`): PostgreSQL 
protocol 3.0 on Netty:
   - connection start: SSLRequest, GSSENCRequest declined, CancelRequest, 
protocol negotiation;
   - simple and extended query protocol (Parse/Bind/Describe/Execute/Sync, 
portals), text and binary
     results;
   - SQL cursors (`DECLARE`/`FETCH`/`MOVE`/`CLOSE`, as psycopg's server-side 
cursors use them);
   - cancel requests (`57014`) and `statement_timeout`;
   - session commands handled by the service: `SET`/`RESET`/`SHOW`, 
`BEGIN`/`COMMIT`/`ROLLBACK`,
     `DISCARD`, `DEALLOCATE`;
   - streaming with back pressure: rows are sent block by block, and a client 
that stops reading
     for `postgresql_service_write_timeout_in_ms` loses its query and its 
connection;
   - a connection limit (`53300`), an idle timeout and a startup timeout;
   - engine errors mapped to SQLSTATEs (`PgErrors`).
   
   Writes are refused with `25006`. The reported `server_version` is `14.0`, so 
clients take their
   standard code paths.
   
   **Security:**
   - On any address other than loopback the service accepts **only TLS 
1.2/1.3** connections with
     AEAD suites; a plain connection gets `FATAL 28000 encryption required`.
   - The certificate comes from `key_store_path`/`key_store_pwd`. Without a key 
store the service
     refuses to start off loopback and logs why.
   - Logins use PostgreSQL's *cleartext password* authentication. It is only 
ever offered inside
     TLS or on loopback, and the password is checked by `SessionManager.login`, 
so lockout and the
     other login rules apply unchanged. See "Differences" for why it isn't 
SCRAM.
   
   **Translation** (`sql`): JSqlParser parses the statement. A check 
(`ConstructCheck`) allows only
   constructs with an exact table-model equivalent, and a writer 
(`PgToTableSql`) prints table-model
   SQL. Among the rules:
   - PostgreSQL output column names (`count`, `?column?`, ...);
   - `::` casts and typed literals;
   - `ILIKE`;
   - anchored regular expressions as `LIKE`/`IN`;
   - `NULLIF`, and `||`/`concat` with PostgreSQL's NULL semantics;
   - typed NULLs;
   - `date_trunc`/`extract`/`date_part` and `timestamp ± interval` in the 
session time zone
     (`DateTimeRules`; day truncation is exact across DST changes, and units 
that would be wrong in
     the session zone are refused);
   - window functions without explicit frames;
   - scalar sub-queries;
   - the catalog functions clients call (`format_type`, `pg_get_expr`, 
`obj_description`,
     `current_schemas`, `regclass`/`regtype` casts, ...).
   
   **Catalog** (`catalog`): the `pg_catalog` tables clients read 
(`pg_namespace`, `pg_class`,
   `pg_attribute`, `pg_type`, `pg_proc`, `pg_description`, `pg_database`, 
`pg_settings`, ...) and
   PostgreSQL's `information_schema` views. They are implemented as 
`information_schema.pg_*` /
   `pg_is_*` tables through the existing `InformationSchemaExtension` and
   `AdditionalInformationSchemaProvider` SPIs, so catalog queries, joins 
included, run in the normal
   engine. They show only the databases and tables the user may read. OIDs are 
stable (CRC32 of the
   name, at least 16384), and column categories (TAG/ATTRIBUTE/FIELD) appear as 
column comments.
   
   ### Tests
   
   - **Unit and golden tests** (72): codec, startup on an in-memory channel, 
parameters of every type,
     value encoding, catalog rows, translation rules, and 
`TranslatorGoldenTest`. The golden test
     translates every recorded client statement and compares the result with
     `corpus/expected/<client>/<n>.sql` (or the expected `0A000`).
   - **Integration tests** (`org.apache.iotdb.postgresql.it`, `TableSimpleIT`):
     - `IoTDBPostgreSqlEngineIT` runs every translated corpus statement on the 
engine and checks
       psql's catalog results, Npgsql's type-loading queries, date rules around 
both 2026 DST changes
       in Europe/Berlin, pgJDBC `DatabaseMetaData`, SQL cursors, cancel, 
`statement_timeout` and
       prepared statements with every parameter type;
     - `IoTDBPostgreSqlClientsIT`: pgJDBC simple/extended mode, metadata;
     - `IoTDBPostgreSqlImportIT`: 1,000,000 rows of every type through pgJDBC, 
compared with the
       session API, plus the write timeout;
     - `IoTDBPostgreSqlSecurityIT`: login, refusals, connection limit, 
read-only;
     - `IoTDBPostgreSqlTlsIT`: TLS-only off loopback, TLS 1.2/1.3, TLS 1.1 
refused;
     - `IoTDBPostgreSqlNoKeyStoreIT` and `IoTDBPostgreSqlPortInUseIT`: the 
service fails cleanly and
       the DataNode keeps running.
   
   ### Differences from the fork (each available as a separate PR if wanted)
   
   The fork has a few things this PR deliberately leaves out, to keep the core 
changes small. If the
   project is interested in any of them, I'm happy to send it as a separate PR:
   
   1. **SCRAM-SHA-256 login.** In the fork, built-in users log in with 
SCRAM-SHA-256, so the
      password never crosses the network, even inside TLS. That requires 
storing passwords as salted
      SCRAM verifiers 
(`$scram-sha-256$<iterations>:<salt>$<StoredKey>:<ServerKey>`, PBKDF2-SHA256)
      instead of the current unsalted SHA-256 hash. Existing hashes are 
converted when the ConfigNode
      starts or at the next login. That is an on-disk format change with no way 
back, so it needs its
      own discussion. Without it, this PR uses the password inside TLS.
   2. **Catalog queries at high priority.** With a small memory budget (we test 
with 256 MB
      DataNodes), catalog queries with two or more joins can fail with "not 
enough memory", because
      every sort is estimated at its full sort buffer. The fork marks queries 
that read only
      `information_schema` as highest priority, so they use the existing 
high-priority memory
      fallback. It's a small change in `Analysis`/`QueryExecution`. With the 
default memory settings
      I haven't seen the problem.
   3. **Plugin discovery instead of an enum entry.** In the fork, optional 
services are found
      through `ServiceLoader` (`META-INF/services/...IExternalService`), and 
each plugin reads its own
      keys, so the core has no `enable_postgresql_service` and no `POSTGRESQL` 
entry. The fork also
      guards the start of discovered services against undeclared checked 
exceptions: Netty rethrows
      `BindException` undeclared, and the plugin catches it itself here too.
   4. **Service status, settings and endpoints SPI.** In the fork, external 
services report their
      status and endpoints to an embedding host (the fork has an embedded 
in-JVM mode). That isn't
      needed in IoTDB.
   5. **Host user backends.** The fork lets an embedding host plug in its own 
user management.
      PostgreSQL logins for such users use the password inside TLS as here, 
unless the backend
      supplies SCRAM verifiers.
   6. **Configuration keys.** The fork uses `postgresql_service.*` keys with 
ISO-8601 durations,
      because its plugins declare their settings through the SPI in item 4. 
This PR uses IoTDB's style
      (`enable_postgresql_service`, `postgresql_service_*_in_ms`).
   
   7. **Uniform login refusals.** In the fork, a wrong password and an unknown 
user get the same
      message (and a minimum response time), so logins don't reveal which users 
exist. This PR passes
      IoTDB's own login messages through ("The user ... does not exist"), with 
SQLSTATE `28P01` in
      both cases.
   
   ### Help wanted: reviewing the golden files
   
   `external-service-impl/postgresql/src/test/resources/corpus/` contains SQL 
recorded from real
   clients against PostgreSQL 14:
   - 21 statements from psql (`\l`, `\dn`, `\dt`, `\d table`, `\d+`);
   - 20 from pgJDBC (`DatabaseMetaData` calls as DBeaver makes them);
   - 14 from psycopg (named cursors, parameters).
   
   `corpus/expected/<client>/<n>.sql` holds the expected translation of each 
statement, or the
   expected `0A000` refusal. **The translator itself generated these files**
   (`-Dgolden.update=true`), so for now they only prove that its output doesn't 
change, not that it
   is right. `IoTDBPostgreSqlEngineIT` already proves that every translated 
statement runs on the
   engine, and checks the rows for a few of them (psql's `\dn` and `\d`, pgJDBC 
metadata, the date
   rules).
   
   What we'd like reviewers to check, file by file: is the translation 
**semantically the same** as
   the PostgreSQL statement, and is each refusal acceptable? A typical case is 
`expected/psql/5.sql`
   (psql's `\d table`):
   - `format_type(oid, NULL)` becomes a `CASE` over the type OIDs;
   - `pg_get_expr(...)` becomes `CAST(NULL AS STRING)` (IoTDB has no column 
defaults);
   - `relhasoids` becomes `FALSE`.
   
   Comments on individual files in this PR are very welcome. Recordings from 
Power BI (DirectQuery)
   will follow, and those are the translations that matter most.
   
   <hr>
   
   This PR has:
   - [x] been self-reviewed.
   - [x] added documentation for new or modified features or behaviors
     (`external-service-impl/postgresql/README.md`, configuration template).
   - [x] added Javadocs for most classes and all non-trivial methods.
   - [x] added or updated version, __license__, or notice information 
(JSqlParser in
     `LICENSE-binary`).
   - [x] added comments explaining the "why" and the intent of the code 
wherever would not be
     obvious for an unfamiliar reader.
   - [x] added unit tests or modified existing tests to cover new code paths.
   - [x] added integration tests.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to