duckdb / duckdb_mysql

MIT License
45 stars 10 forks source link

Implement credential management using secrets #64

Closed Mytherin closed 4 months ago

Mytherin commented 4 months ago

Implements the same as https://github.com/duckdb/postgres_scanner/pull/217 but for MySQL

This allows credentials to be managed using secrets. The secret to be used can be selected with the SECRET setting that is passed into ATTACH. There is also a default secret - this is the secret that is created without specifying a name. When no secret is provided to ATTACH, and such a secret is present, this secret is used.

Secrets can provide the following set of options.

Name Description
database Database name
user MySQL user name
password MySQL password
host Name of host to connect to
port Port number
socket Socket

Note that not all of these need to be provided. For options that are not provided, the system falls back to the regular set of default options as specified in the configuration.

Implicit (Default) Secret With All Options
CREATE SECRET (
    TYPE MYSQL,
    HOST '127.0.0.1',
        PORT 0,
    DATABASE mysqlscanner,
        USER 'mysql',
    PASSWORD ''
);
ATTACH '' AS secret_attach (TYPE MYSQL);
Explicit (Named) Secret With Limited Options
CREATE SECRET mysql_secret(
    TYPE MYSQL,
    HOST '127.0.0.1',
        PORT 0,
    DATABASE mysqlscanner,
        USER 'mysql',
    PASSWORD ''
);

ATTACH '' AS secret_attach (TYPE MYSQL, SECRET mysql_secret);
Overriding Options

Secrets essentially provide a new set of default options. The individual options can be overridden in the attach string, similar to how they can be overridden for the standard set of default options. For example:

CREATE SECRET (
    TYPE MYSQL,
    HOST '127.0.0.1',
        PORT 0,
    DATABASE mysqlscanner,
        USER 'mysql',
    PASSWORD ''
);
-- override the dbname option, but use the rest of the options provided in the secret
ATTACH 'dbname=unknown_db_name' AS secret_attach (TYPE MYSQL);
-- Unknown database 'unknown_db_name'