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]