Postgres Endpoint

Preview

This feature is in preview and is subject to change.

MotherDuck's Postgres endpoint lets you query your databases using any client that speaks the PostgreSQL wire protocol — without installing a DuckDB client library.

For a how-to guide on connecting, see Connect through the Postgres endpoint.

Connection parameters

Parameter	Value
Host	pg.<region>-aws.motherduck.com (for example, pg.us-east-1-aws.motherduck.com)
Port	5432
Database	md: for your default database, or a specific database name
User	postgres
Password	Your MotherDuck access token

Connection string formats

# psql
PGPASSWORD=$MOTHERDUCK_TOKEN psql -h pg.us-east-1-aws.motherduck.com -p 5432 -U postgres "dbname=md: sslmode=verify-full sslrootcert=system"

# libpq URI
postgresql://postgres:$MOTHERDUCK_TOKEN@pg.us-east-1-aws.motherduck.com:5432/md:?sslmode=verify-full&sslrootcert=system

# DSN keyword/value
host=pg.us-east-1-aws.motherduck.com port=5432 dbname=md: user=postgres password=$MOTHERDUCK_TOKEN sslmode=verify-full sslrootcert=system

Use md: as the database name to connect to your default database. You can also specify a database by name, for example sample_data.

SSL and certificate verification

The Postgres endpoint requires encrypted connections. For the best security, verify the server certificate.

SSL modes

Mode	Encryption	Server verification	Recommendation
verify-full	Yes	Yes	Recommended for production
require	Yes	No	Fallback if certificate verification is not possible

Use the system certificate store (recommended)

Set sslmode=verify-full with sslrootcert=system to use your operating system's trusted root certificates. This is supported in libpq 16 and later, and in libraries that wrap libpq (like psycopg v3).

sslmode=verify-full sslrootcert=system

Use a specific certificate file

If your client doesn't support sslrootcert=system, download the ISRG Root X1 certificate from Let's Encrypt and point your client to it:

sslmode=verify-full sslrootcert=/path/to/isrgrootx1.pem

Library-specific SSL handling

Some libraries have their own SSL implementations that don't use libpq directly:

Library	SSL behavior	Workaround
psycopg (v3)	Wraps libpq — sslrootcert=system works	None needed
psycopg2	Bundles its own OpenSSL — sslrootcert=system is not supported	Use sslrootcert=certifi.where() with the certifi package
PostgreSQL JDBC	Looks for ~/.postgresql/root.crt by default	Set sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory to use JVM truststore
node-postgres (pg)	Reads sslrootcert as a file path — system causes ENOENT	Use config object: ssl: { rejectUnauthorized: true }
Cloudflare Workers (pg-cloudflare)	TLS handled by the Workers runtime at the socket level — application-level verification settings are not exposed through the pg client	Use ?sslmode=require in the connection string

Session options

You can pass DuckDB session options using the PGOPTIONS environment variable:

PGOPTIONS="--attach_mode=single --session_name=pg-using-options" psql -h pg.us-east-1-aws.motherduck.com -p 5432 -U postgres md:

Option	Description
--attach_mode=single	Only attach the specified database. Recommended when connecting from IDEs or BI tools to avoid seeing objects from other databases. See Attach Modes.

Supported features and limitations

DuckDB SQL, not PostgreSQL

The Postgres endpoint is a PostgreSQL-wire interface to MotherDuck. You write DuckDB SQL, not PostgreSQL SQL.

Best suited for

• query execution against MotherDuck tables
• DDL and DML that run entirely inside MotherDuck
• metadata inspection
• server-side reads from remote storage

Use a DuckDB client path instead when you need

• local-file workflows such as local-file COPY, EXPORT DATABASE, or IMPORT DATABASE
• local or in-memory attachments such as ATTACH ':memory:' or ATTACH '/path/to/file.duckdb'
• local execution paths such as MD_RUN=LOCAL
• extension-based workflows such as INSTALL, LOAD, or cloud-storage CREATE SECRET
• DuckDB-client session features such as CREATE RESULT

Compatibility notes

• PostgreSQL-specific features such as pg_* functions, PostgreSQL indexes, sequences, and stored procedures are not supported.
• Transaction semantics follow the DuckDB model. Nested transactions are not supported.
• Some commands are further restricted in PG server mode. For example, SET threads and CREATE TEMP TABLE are not supported through the Postgres endpoint.

Operational limitations

• Configuration settings may be restricted. MotherDuck runs in SaaS mode, which limits some configuration changes after connecting. Avoid using SET statements in your client code.
• IDE schema browsers may show extra objects. Some IDEs display tables from all attached databases. Use attach_mode=single to scope the catalog to your target database.
• Use connection pooling in production. Each connection consumes server resources. For applications that need many connections, use a connection pooler (for example, Cloudflare Hyperdrive, PgBouncer) rather than rapidly opening and closing connections.
• Third-party tool support is in early stages. Check the Integrations page for tools that support the Postgres endpoint.