Question: Auto-attach DuckLake catalog on connection to avoid per-session attach_ducklake() call

[JC1738]

We're using pg_duckdb with DuckLake to query Parquet files stored in Cloudflare R2 (S3-compatible). We've successfully implemented a hybrid warehouse where:

• 58 tables are stored as Parquet files in R2
• A DuckLake catalog (ducklake_catalog.duckdb) maintains table metadata locally
• PostgreSQL views wrap duckdb.query() calls for transparent access
• R2 credentials are stored in a DuckDB secret during initialization

Current Implementation

Users must call attach_ducklake() once per session before querying:

CREATE OR REPLACE FUNCTION attach_ducklake()
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
catalog_path text := '/var/lib/postgresql/data/ducklake_catalog.duckdb';
bucket_name text := 'duckdb';
BEGIN
-- Load extensions
PERFORM duckdb.install_extension('httpfs');
PERFORM duckdb.load_extension('httpfs');
PERFORM duckdb.install_extension('aws');
PERFORM duckdb.load_extension('aws');
PERFORM duckdb.install_extension('ducklake');
PERFORM duckdb.load_extension('ducklake');

  -- Attach DuckLake catalog (uses R2 secret created during init)
  PERFORM duckdb.raw_query(
      'ATTACH ''' || catalog_path || ''' AS my_lake (TYPE DUCKLAKE, READ_ONLY TRUE, DATA_PATH ''r2://' || bucket_name || '/'')'
  );

  RETURN 'DuckLake catalog attached - tables available in my_lake schema';

END;
$$;

Typical user workflow:
-- Every new connection requires this
SELECT attach_ducklake();

-- Then queries work via views
SELECT COUNT(*) FROM "Order"; -- View wraps duckdb.query('SELECT * FROM my_lake."Order"')

Problem

ATTACH is session-scoped - it doesn't persist across connections. This creates friction for:

• BI tools like Metabase (requires initialization query setting)
• Connection poolers (each new connection needs manual attachment)
• Application code (must remember to call attach_ducklake() after connect)

Question

Is there a way to make the DuckLake catalog attachment persistent across all connections?

Options we're considering:

1. PostgreSQL event trigger on connection?
2. pg_duckdb configuration setting to auto-attach a catalog?
3. Session initialization hook in postgresql.conf?
4. Different attachment method that persists across sessions?

Ideal Behavior

We'd love for the catalog to be automatically available to all connections without requiring manual attach_ducklake() calls, similar to how foreign data wrappers or schemas work in PostgreSQL.

Environment

• PostgreSQL 17.6
• pg_duckdb (DuckDB 1.4.1 embedded)
• DuckLake catalog managing 58 tables
• Cloudflare R2 for Parquet storage
• Docker deployment

Any guidance would be greatly appreciated! We're happy to contribute documentation or examples if we find a good solution.

[JC1738]

I actually solved this using pgbouncer to attach datalake each time, not sure if there any better solutions. Also looking to see best way to have the clients of postgres to see the datalake tables naturally in public schema. Right now I just wrapped each table with a view selecting r[''] so that the view has the actual column definitions. Hoping for a better way?