Define secret to minio S3 object storage server, support for use_ssl parameter

[mskyttner]

I want this feature to be able to query parquet files residing on a local minio server:

-- this doesn't work since the "use_ssl" param is unknown
SELECT duckdb.create_simple_secret(
    type := 'S3',
    key_id := '<redacted>',
    secret := '<redacted>',
    endpoint := 'localhost:9000', -- FIXME! when connecting to minio the protocol (http/https) is not specified in the endpoint, its just "domain:port" here
    use_ssl := 'false', -- FIXME! this param is currently not recognized in pg_duckdb but can be used in duckdb CLI for a "secret"
    region := '',
    url_style := 'path',
    scope := 's3://public/'
);

Without the use_ssl parameter, "https" will always be assumed, so connections to internal/local minio servers cannot be made (?)

[JelteF]

You should be able to do that by using the CREATE SERVER syntax instead: https://github.com/duckdb/pg_duckdb/blob/main/docs/secrets.md#secrets-with-credential_chain-provider That supports all secret options that duckdb supports, so you should be able to specify use_ssl there.

[mskyttner]

Thanks for the info. I tried to figure that out... but wouldn't this mean that the AWS SDK would need to be installed inside the container where postgres runs with pg_duckdb? So that environment variables or "aws profiles" can be read for the settings? And additionally the duckdb aws extension would need to be installed?

Normally with duckdb+minio I don't strictly need that.

It would be great to be able to use the simple "config provider"; it would be a home run if the "use_sll" setting could be set (currently it is assumed to be true but not possible to set) from that duckdb.create_simple_secret statement. We're so close now that the "url_style" setting is supported.

Perhaps this comes into play aswell: duckdb/duckdb-aws#108 ?

[Y--]

Hi @mskyttner,

You don't need the AWS SDK for it to work, DuckDB implements everything for it to work.

CREATE SERVER my_minio_secret
TYPE 's3'
FOREIGN DATA WRAPPER duckdb
OPTIONS (
    -- You can add any option supported by `CREATE SECRET` in DuckDB in here:
    ENDPOINT 'localhost:9000',
    USE_SSL 'false',
    REGION '',
    URL_STYLE 'path',
    SCOPE 's3://public/'
);

CREATE USER MAPPING 
FOR CURRENT_USER 
SERVER my_minio_server 
OPTIONS (
  KEY_ID <redacted>,
  SECRET <>,
);

This will then translate to DuckDB in:

CREATE SECRET my_minio_secret (
  TYPE S3,
  ENDPOINT 'localhost:9000',
  USE_SSL 'false',
  REGION '',
  URL_STYLE 'path',
  SCOPE 's3://public/',
  KEY_ID <redacted>,
  SECRET <>,
)

And hopefully work as you expect :-)

Note that instead of providing a USER MAPPING you can also use PROVIDER 'credential_chain' which will then use your environment variable/profiles.

Let me know if you need additional clarifications.

[mskyttner]

Thanks for the info! I tried this approach, which seems to persist the credentials across sessions. It also looks like I can leave out the optional KEY_ID and SECRET options (in this case where the s3 folder on minio has public world readable access), and the duckdb secret will still be created correctly. Nice!

This approach works well for me now that I know about it, and if there is some support in the future for the use_ssl parameter in that other helper function duckdb.create_simple_secret it would of course be convenient for those users that quickly want to get up and running with a connection against a regional/local minio server with low network latency placed close to the database engine (for a quicker path or "shortcut" with no need for ssl termination and dns resolution etc).

Thanks again for the explanation.

[AndrewJackson2020]

Fwiw I ended up getting around this by using the raw_query functionality instead of the pg_duckdb function.

[mskyttner]

Thanks for the pointer! I learned that there is both duckdb.raw_query and duckdb.query.

Following your hint/workaround, I used this sql to query a parquet file from a local minio s3 server:

SELECT * FROM duckdb.raw_query($$
    create secret s3_public (
            type S3,
            endpoint 'minio:9000',
            use_ssl 'false',
            url_style 'path',
            scope 's3://public/'
    )
; $$);

-- check that duckdb secret for reading s3 data is there
SELECT * FROM duckdb.query($$ SELECT name, type FROM duckdb_secrets(); $$);

-- issue a query against a file in the object storage
SELECT * FROM read_parquet('s3://public/topics.parquet') limit 10;

SELECT * FROM duckdb.raw_query($$
    drop secret s3_public;
$$);

[Y--]

Hi all,

The downside of that approach is the lifetime of the secret: you would have to re-create it each time the DuckDB instance is restarted.

Instead, we suggest you use the create_simple_secret function whenever possible and CREATE SERVER/CREATE USER MAPPING syntax when advanced functionalities are needed.

That being said, we're considering adding the use_ssl parameter to create_simple_secret.

[AndrewJackson2020]

That being said, we're considering adding the use_ssl parameter to create_simple_secret

+1 for that idea. I don't think not using ssl is an advanced use case.

Also I will point out that use_ssl is listed as an optional parameter to the duckdb.create_simple_secret function in functions.md.

[JelteF]

If you can create a PR I'll merge it

[AndrewJackson2020]

PR for your review.

#956