betodealmeida commented on issue #24684: URL: https://github.com/apache/superset/issues/24684#issuecomment-1641136941
A summary of the problem: - Before [my fix](https://github.com/apache/superset/blob/3.0.0rc1/superset/db_engine_specs/postgres.py#L268), when using Postgres or Postgres-like databases (like RDS), in SQL Lab when you selected a schema from the dropdown **the query would not run in that schema**. Because of this, Superset was unable to evaluate if the user had permission to query an unqualified table, eg, `SELECT * FROM my_table`, since it can't know in which schema the table lives. With my fix, the query runs in the selected schema, so any unqualified table name has to be from that schema (it also ensures that there's only one schema in `search_path`, to ensure the default schema is unique). - The way we do this is by passing `options=-csearch_path=my_schema` to the Postgres SQLAlchemy dialect when running a query. Here, `my_schema` comes from the schema dropdown in SQL Lab. - RDS uses the same Postgres SQLAlchemy dialect, but it doesn't accept `options`. The only way to set the default path is by running a query `set search_path=my_schema` when the session starts, but currently Superset has no workflow for that, as far as I know. In summary, I'm not sure that we can fix this for 3.0 in a secure way. To make RDS secure we need to implement a way of running pre-session queries, so that we can set the search path. I'm happy to do that refactoring, but I think cherry-picking it for 3.0 might bring in more risks. -- 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]
