Persistent S3 secret not used in new connection

[jorritsandbrink]

I'm facing this error when trying to iceberg_scan an Iceberg metadata file on S3 using a persistent secret:

duckdb.duckdb.IOException: IO Error: Cannot open file "s3://my-bucket/my_iceberg_table/metadata/00003-51b3e065-bde2-4d06-af88-ffd14cc8e738.metadata.json": No such file or directory

Reproduce:

import duckdb


duckdb_path = "duck.db"
iceberg_path = "s3://my-bucket/my_iceberg_table/metadata/00003-51b3e065-bde2-4d06-af88-ffd14cc8e738.metadata.json"

conn = duckdb.connect(duckdb_path)
conn.sql("""
CREATE PERSISTENT SECRET my_secret (
    TYPE S3,
    KEY_ID 'my_key_id',
    SECRET 'my_secret',
    REGION 'eu-central-1'
);
""")
conn.close()

conn = duckdb.connect(duckdb_path)
conn.sql("INSTALL iceberg; LOAD iceberg;")
# workaround: uncommenting one of the two lines below makes it work
# conn.sql(f"FROM which_secret('{iceberg_path}', 's3');").show()
# conn.sql("FROM duckdb_secrets();").show()
conn.sql(f"FROM iceberg_scan('{iceberg_path}')").show()

Result:

Traceback (most recent call last):
  File "mre.py", line 23, in <module>
    conn.sql(f"FROM iceberg_scan('{iceberg_path}')").show()
duckdb.duckdb.IOException: IO Error: Cannot open file "s3://my-bucket/my_iceberg_table/metadata/00003-51b3e065-bde2-4d06-af88-ffd14cc8e738.metadata.json": No such file or directory

I've found that, as a workaround, the error can be prevented by executing

conn.sql("FROM duckdb_secrets();").show()

or

conn.sql(f"FROM which_secret('{iceberg_path}', 's3');").show()

before running iceberg_scan.

This seems to only be an issue with iceberg_scan. The error does not occur with delta_scan or read_parquet.