Issue Reappearing in pg_duckdb Context Discussed in MotherDuck Community

I'm now seeing errors like this when querying via postgres with pg_duckdb set up.

postgres=# select * from read_csv('r2://gain-site-register/2026-03-25.csv');
ERROR:  (PGDuckDB/CreatePlan) Prepared query returned an error: HTTP Error: Permission error: Missing or invalid credentials. Please check registered secrets by running FROM DUCKDB_SECRETS().

LINE 1: SELECT read_csv.* FROM system.main.read_csv('r2://gain-site-register/2026-03-25...
                               ^

But the same query works in a direct Motherduck query

wrapping it in duckdb.raw_query also fails in the same way

select duckdb.raw_query('select * from DUCKDB_SECRETS();'); shows the secret correctly

Hey Matt, can you verify which secret DuckDB tries to use while trying to access your R2 bucket? I think FROM which_secret('r2://gain-site-register/2026-03-25.csv', 'r2') would do it.

Output is a bit messed up - not the main issue - seems to be finding the secret okay

postgres=# select * FROM duckdb.raw_query('SELECT * FROM which_secret(''r2://gain-site-register/2026-03-25.csv'', ''r2'')');
NOTICE:  result: name   persistent      storage
VARCHAR VARCHAR VARCHAR
[ Rows: 1]
r2      PERSISTENT      motherduck


 raw_query
-----------

(1 row)

r2 is the name of the secret configured in motherduck

although in Motherduck there is

name	persistent	storage
r2	TEMPORARY	memory

Ah right, so I've not persisted the secret properly

Yeah that could be it

Actually in our logs I'm not seeing any query from today trying to read/write to that bucket, but I see from yesterday. Can you verify whether select duckdb.raw_query('select * from read_csv("r2://gain-site-register/2026-03-25.csv")'); works?

It did not work today at all, which is why I'm here!

But I've fixed it now, with your help

ah, cool. weird that the attempt didn't reach our logging even if the secret authentication failed 🤔

perfe, let us know if we can help with anything else 😌