betodealmeida opened a new pull request, #23356: URL: https://github.com/apache/superset/pull/23356
<!--- Please write the PR title following the conventions at https://www.conventionalcommits.org/en/v1.0.0/ Example: fix(dashboard): load charts correctly --> ### SUMMARY <!--- Describe the change below, including rationale and design decisions --> When a user runs a query in SQL Lab referencing a table without an explicit schema, we check permissions using the **query schema** instead of the **default database schema**. This allows users to query schemas that they shouldn't have access to. In this example, a user has access to only the `secret` schema. They select the `secret` schema in SQL Lab and run the following query: ```sql SELECT * FROM ab_user ``` The database (Postgres in this case, but this applies to most if not all databases) will run the query against the default schema, `public`, returning data. Superset will check if the user has access to the `secret` schema instead, allowing the query to run and return results to the user:  With this fix the user is restricted:  ### TESTING INSTRUCTIONS <!--- Required! What steps can be taken to manually verify the changes? --> 1. Add a schema called `secret` to a database. 1. Create a role called `secret` that has the permission `[schema access on [PostgreSQL].[secret]]`. 1. Add a test user with roles `sql_lab` and `secret`. 1. As the test user, select the `secret` schema in SQL Lab and run `SELECT * FROM ab_user`. ### ADDITIONAL INFORMATION <!--- Check any relevant boxes with "x" --> <!--- HINT: Include "Fixes #nnn" if you are fixing an existing issue --> - [ ] Has associated issue: - [ ] Required feature flags: - [ ] Changes UI - [ ] Includes DB Migration (follow approval process in [SIP-59](https://github.com/apache/superset/issues/13351)) - [ ] Migration is atomic, supports rollback & is backwards-compatible - [ ] Confirm DB migration upgrade and downgrade tested - [ ] Runtime estimates and downtime expectations provided - [ ] Introduces new feature or API - [ ] Removes existing feature or API ### DISCUSSION This problem happens because the query schema is not passed to the connection as the "default schema". SQLAlchemy only supports passing the schema to reflection calls, but not to query execution. In the future, we should add DB-engine-specific methods that allow passing a default schema and catalog, to mutate the SQLAlchemy URI to use the catalog and schemas as the default ones. This would improve the experience in SQL Lab (where the schema dropdown selector does not affect the query, and is used only for the table autocompletion), and would also allow us to support catalogs. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
