Initial Release
PostgreSQL wire protocol server for InterSystems IRIS. Connects BI tools, Python frameworks, data pipelines, and any PostgreSQL-compatible client to IRIS databases β no IRIS-specific drivers needed.
Connection string: postgresql://user:pass@localhost:5432/USER
git clone https://github.com/intersystems-community/iris-pgwire.git cd iris-pgwire docker compose up -dTest it (PGWire runs on port 5432 inside the IRIS container).
Clients log in with an IRIS username and password; the development container accepts the
password over a plain connection because it is published on localhost only.
PGPASSWORD=SYS psql -h localhost -p 5432 -U _SYSTEM -d USER -c "SELECT 'Hello from IRIS!'"
pip install iris-pgwire psycopg[binary]export IRIS_HOST=localhost IRIS_PORT=1972
IRIS_USERNAME=_SYSTEM IRIS_PASSWORD=SYS IRIS_NAMESPACE=USER
python -m iris_pgwire.server --allow-plaintext-password
Clients must log in with an IRIS username and password, and the password is sent in the clear, so
the server refuses to start unless you choose how it is protected β see
Authentication. --allow-plaintext-password is for a network you trust.
import psycopg
with psycopg.connect("host=localhost port=5432 dbname=USER user=_SYSTEM password=SYS") as conn: with conn.cursor() as cur: cur.execute("SELECT COUNT(*) FROM MyTable") print(cur.fetchone()[0])
Tested against real IRIS instances via the wire protocol:
| Language | Clients |
|---|---|
| Python | psycopg3, asyncpg, SQLAlchemy (sync + async) |
| Node.js | pg (node-postgres) |
| Java | PostgreSQL JDBC |
| .NET | Npgsql |
| Go | pgx v5 |
| Ruby | pg gem |
| Rust | tokio-postgres |
| PHP | PDO PostgreSQL |
| Arrow | ADBC PostgreSQL driver (queries only; see ADBC) |
ORMs: SQLAlchemy, Prisma, Drizzle, Sequelize, Hibernate
BI tools: Apache Superset, Metabase, Grafana (standard PostgreSQL driver)
See Client Compatibility Guide for setup examples.
pgvector operators β <=> (cosine), <#> (dot product), <-> (L2) auto-translate to IRIS VECTOR_COSINE/VECTOR_DOT_PRODUCT. HNSW indexes give 5Γ speedup at 100K+ vectors. See Vector Operations.
DDL compatibility β Automatic public β SQLUser schema mapping; strips fillfactor, GENERATED columns, USING btree, IF NOT EXISTS guards, and other PostgreSQL-specific DDL so ORM migrations run cleanly. See DDL Compatibility.
SQL translation β RETURNING emulation, ON CONFLICT, boolean literals, pg_catalog β INFORMATION_SCHEMA rewrites, JSON operators (-> / ->> β JSON_EXTRACT), parameterized queries.
Authentication β Required by default. The client sends its IRIS username and password and the
server checks them by logging in to IRIS with them, so IRIS remains the only store of users and
passwords. Because the password crosses the wire, TLS is required unless you say otherwise:
| You want | Run with |
|---|---|
| TLS (production) | PGWIRE_SSL_ENABLED=true PGWIRE_SSL_CERT=β¦ PGWIRE_SSL_KEY=β¦ |
| Plain connection on a trusted network, or behind a TLS-terminating proxy | --allow-plaintext-password (PGWIRE_ALLOW_PLAINTEXT_PASSWORD=true) |
| No authentication, local development only | --trust (PGWIRE_AUTH_MODE=trust) |
| Let users other than the service account log in | --allowed-users alice,bob (PGWIRE_ALLOWED_USERS) |
A server started with none of these refuses to start and says so.
What authentication does and does not do: it proves the client knows the password of an IRIS user.
Statements still run as the service account configured with IRIS_USERNAME, so every user who can
log in has that accountβs privileges. That is why only the service account may log in unless you
list others; a list replaces the default, so list only people you would trust with that account.
SCRAM-SHA-256, OAuth 2.0 and IRIS Wallet credentials are
not available: earlier versions advertised them, but the SCRAM exchange never checked the clientβs
proof, and PGWIRE_ENABLE_SCRAM was never read. Asking for SCRAM now fails at startup. Login
attempts are checked by IRIS itself, so its lockout and password policies apply β repeated failures
can lock an account.
Dual backend β Embedded Python (irispython, lowest latency) or external DBAPI (standard TCP connection). Selectable via IRIS_BACKEND env var.
COPY protocol β Bulk load via COPY β¦ FROM STDIN (~600 rows/sec on DBAPI path).
import psycopg
with psycopg.connect("host=localhost port=5432 dbname=USER user=_SYSTEM password=SYS") as conn: with conn.cursor() as cur: cur.execute("SELECT * FROM MyTable WHERE id = %s", (42,)) row = cur.fetchone()
query_vector = [0.1, 0.2, 0.3]
with conn.cursor() as cur:
cur.execute("""
SELECT id, embedding <=> %s::vector AS score
FROM vectors
ORDER BY score
LIMIT 5
""", (query_vector,))
results = cur.fetchall()
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker from sqlalchemy import textengine = create_async_engine("postgresql+psycopg://localhost:5432/USER") SessionLocal = async_sessionmaker(engine, class_=AsyncSession)
async def query(): async with SessionLocal() as session: result = await session.execute(text("SELECT * FROM MyTable")) return result.fetchall()
with conn.cursor() as cur:
with cur.copy("COPY MyTable (col1, col2) FROM STDIN") as copy:
for row in data:
copy.write_row(row)
| Guide | Description |
|---|---|
| Installation | Docker, PyPI, Embedded Python deployment |
| Architecture | System design, dual backend, request flow |
| DDL Compatibility | PostgreSQL DDL transformations |
| Vector Operations | pgvector syntax, HNSW indexes |
| Client Compatibility | Per-language setup and caveats |
| Deployment | Production setup, SSL/TLS, auth |
| Performance | Benchmarks, tuning |
| Developer Guide | Development setup, contribution guidelines |
# Install dependencies uv sync --frozenRun unit + contract tests (no IRIS needed)
pytest tests/unit/ tests/contract/ -v
Run with live IRIS (container must be up)
docker compose up -d PGWIRE_BACKEND_TYPE=dbapi PGWIRE_POOL_SIZE=1 pytest tests/ -v
Code quality check
python -m iris_pgwire.quality
Test coverage: 92% (5349 tests)
Code quality: black (formatter), ruff (linter), bandit (security)
See https://github.com/intersystems-community/iris-pgwire/blob/main/KNOWN_LIMITATIONS.md for the full list. Key items:
REPEATABLE READ and SERIALIZABLE are rejected rather than silently downgraded; IRIS READ COMMITTED is narrower than PostgreSQLβs, and dirty reads are reachable through aggregates, GROUP BY and DISTINCT. Lock conflicts arrive as retryable 40001. Version-guarded (optimistic-locking) updates can report false conflicts β read the isolation section before porting an ORM that uses them.PGWIRE_POOL_SIZE=1 for devPGWIRE_SSL_ENABLED); behind a TLS-terminating proxy, use --allow-plaintext-passwordINFORMATION_SCHEMA only, no pg_catalog tables (translated automatically)git clone https://github.com/intersystems-community/iris-pgwire.git
cd iris-pgwire
uv sync --frozen
docker compose up -d
pytest tests/unit/ tests/contract/ -v
Open an issue or PR on GitHub.
MIT License β see https://github.com/intersystems-community/iris-pgwire/blob/main/LICENSE