Unable to import datasources with encrypted_extra params via REST

[withnale]

Bug description

At present it is possible to create a bigquery datasource which uses an explicit key by creating an import_datasources.yaml file that contains something similar to the block below:

databases:
  -
    database_name: mydatabase
    sqlalchemy_uri: bigquery://gcp-project-id/
    cache_timeout: 0
    expose_in_sqllab: true
    allow_run_async: false
    allow_ctas: false
    allow_cvas: false
    allow_dml: false
    allow_file_upload: false
    encrypted_extra: |
      {"credentials_info": {  "type": "service_account",  "project_id": "gcp-project-id",  "private_key_id": "aca359ef9828567aa1f031f367ba547fe26bf54c",  "private_key": "-----BEGIN PRIVATE KEY-----VERY_BIG_PRIVATE_KEY_STRING\n-----END PRIVATE KEY-----\n",  "client_email": "[email protected]",  "client_id": "1149993100000000000",  "auth_uri": "https://accounts.google.com/o/oauth2/auth",  "token_uri": "https://oauth2.googleapis.com/token",  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/service-account%40gcp-project-id.iam.gserviceaccount.com",  "universe_domain": "googleapis.com"} }
    extra: |
      { "allows_virtual_table_explore": true }
    uuid: 00000000-0000-0000-0000-123456789001  

This can be imported using superset import_datasources -p filename.yaml from a running instance.

However, if I wish to import the same data structure using the REST API /api/v1/database/import it will fail.
It seems that the REST version applies additional schema checks on the bundled zipfile and fails because encrypted_extra is not part of ImportV1DatabaseSchema

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.11

Node version

16

Browser

Chrome

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

Hey @withnale! I'm here to help you with your Superset issue. I'm a bot designed to assist with bugs, questions, and contributions. Let's tackle this together!

A similar issue was previously reported and closed. The issue was about importing a Snowflake database with the encrypted_extra field, which was resolved by a pull request that allowed the encrypted_extra field to be imported ^[1].

In your case, the failure when importing a BigQuery datasource with encrypted_extra parameters via the REST API /api/v1/database/import is likely due to the schema validation that renames encrypted_extra to masked_encrypted_extra. The schema expects encrypted_extra to be renamed to masked_encrypted_extra, which is automatically handled in the superset import_datasources -p filename.yaml command. However, when using the REST API, this renaming might not occur, leading to a failure if encrypted_extra is not manually renamed to masked_encrypted_extra in the payload ^[2].

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[withnale]

To comment on the bot notes above. I've seen the masked_encypted_extra section and also tried that. This is another entry that isn't present in ImportV1DatabaseSchema

[geido]

@fisjac @betodealmeida wondering if you might have some ideas about this

[betodealmeida]

Ah, looks like we need to update the schema.

I think the reasoning is that when we export the database we strip passwords and encrypted extra, so the import doesn't expect it. But people can add it manually, and the import should accept it and use it if present.

I'll fix it, it should be quick.

[withnale]

Does it make sense to ensure the same validations take place on the CLI and REST invocations? It seems strange that an object with encrypted_extra can sneak through the schema validation on the CLI version.

[withnale]

Hello. Did this ever get addressed?

[betodealmeida]

Does it make sense to ensure the same validations take place on the CLI and REST invocations? It seems strange that an object with encrypted_extra can sneak through the schema validation on the CLI version.

I think it's OK to have a different validations for CLI and REST, since using the CLI implies direct access to the database. Because of this, allowing more powerful operations than the ones supported by the API makes sense, IMO. That said, I think the API should accept the encrypted extra, since it's not different than sending credentials when you create a DB.

[betodealmeida]

Hello. Did this ever get addressed?

#32339