GitHub user dosubot[bot] added a comment to the discussion: Set database transaction params
<!-- Greeting --> Hi @baptisteaden! I'm [Dosu](https://go.dosu.dev/dosubot) and Iām helping the superset team. <!-- Answer --> Superset doesn't natively support running a dynamic SQL statement like `set local app.user_id = ...` before each query to set session variables for database-level RLS, especially in embedded or guest_token scenarios. There are a few hooks and workarounds, but each has limitations: - You can use the SQL_QUERY_MUTATOR config hook to prepend SQL (like `set local ...`) to queries, but you'd need to implement logic to extract user context from the request/session, which is not straightforward with guest_token embedding. Also, this approach can get tricky with multi-statement queries and may not work seamlessly for all use cases or databases [(reference)](https://github.com/apache/superset/blob/master/superset/config.py). - The DB_CONNECTION_MUTATOR hook lets you alter connection parameters per user/session, which works for things like impersonation or static session parameters, but it doesn't natively support injecting arbitrary per-query session variables from guest_token payloads [(reference)](https://github.com/apache/superset/blob/master/superset/config.py). - Some database engine specs (like StarRocks) can run pre-session queries for impersonation, but this is database-specific and not a general solution for arbitrary session variables [(reference)](https://github.com/apache/superset/blob/master/superset/db_engine_specs/starrocks.py). - For PostgreSQL, you can set static session parameters in the connection settings (e.g., `{"connect_args":{"options":"-c work_mem=32768"}}`), but these can't be set dynamically per user/session [(reference)](https://github.com/apache/superset/discussions/33879). Currently, there is no built-in way to extract values from guest_token and inject them as session variables in the database connection for each query. If you need this, the closest workaround is a custom SQL_QUERY_MUTATOR, but integrating it with guest_token user context would require significant custom development. Let me know if you want more details on any of these hooks or possible custom approaches. <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/34bb42d3-bde8-4ace-84e2-982bd306019b?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/34bb42d3-bde8-4ace-84e2-982bd306019b?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/34bb42d3-bde8-4ace-84e2-982bd306019b?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/34bb42d3-bde8-4ace-84e2-982bd306019b?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/34bb42d3-bde8-4ace-84e2-982bd306019b?feedback_type=hallucination) | [Report š](https://app.dosu.dev/response-feedback/34bb42d3-bde8-4ace-84e2-982bd306019b?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/34bb42d3-bde8-4ace-84e2-982bd306019b?feedback_type=other)</sup> [](https://app.dosu.dev/a28d3c7e-a9d3-459e-9fb6-3a6f9ff4f357/ask?utm_source=github) [](https://cursor.com/link/prompt?text=Superset%20doesn%27t%20natively%20support%20running%20a%20dynamic%20SQL%20statement%20like%20%60set%20local%20app.user_id%20%3D%20...%60%20before%20each%20query%20to%20set%20session%20variables%20for%20database-level%20RLS%2C%20especially%20in%20embedded%20or%20guest_token%20scenarios.%20There%20are%20a%20few%20hooks%20and%20workarounds%2C%20but%20each%20has%20limitations%3A%0A%0A-%20You%20can%20use%20the%20SQL_QUERY_MUTATOR%20config%20hook%20to%20prepend%20SQL%20%28like%20%60set%20local%20...%60%29%20to%20queries%2C%20but%20you%27d%20need%20to%20implement%20logic%20to%20extract%20user%20context%20from%20the%20request/session%2C%20which%20is%20not%20straightforward%20with%20guest_token%20embedding.%20Also%2C%20this%20approach%20can%20get%20tricky%20wi th%20multi-statement%20queries%20and%20may%20not%20work%20seamlessly%20for%20all%20use%20cases%20or%20databases%20%5B%28reference%29%5D%28https%3A//github.com/apache/superset/blob/master/superset/config.py%29.%0A-%20The%20DB_CONNECTION_MUTATOR%20hook%20lets%20you%20alter%20connection%20parameters%20per%20user/session%2C%20which%20works%20for%20things%20like%20impersonation%20or%20static%20session%20parameters%2C%20but%20it%20doesn%27t%20natively%20support%20injecting%20arbitrary%20per-query%20session%20variables%20from%20guest_token%20payloads%20%5B%28reference%29%5D%28https%3A//github.com/apache/superset/blob/master/superset/config.py%29.%0A-%20Some%20database%20engine%20specs%20%28like%20StarRocks%29%20can%20run%20pre-session%20queries%20for%20impersonation%2C%20but%20this%20is%20database-specific%20and%20not%20a%20general%20solution%20for%20arbitrary%20session%20variables%20%5B%28reference%29%5D%28https%3A//github.com/apache/superset/blob/master/superset/db_engine_specs/starrocks .py%29.%0A-%20For%20PostgreSQL%2C%20you%20can%20set%20static%20session%20parameters%20in%20the%20connection%20settings%20%28e.g.%2C%20%60%7B%22connect_args%22%3A%7B%22options%22%3A%22-c%20work_mem%3D32768%22%7D%7D%60%29%2C%20but%20these%20can%27t%20be%20set%20dynamically%20per%20user/session%20%5B%28reference%29%5D%28https%3A//github.com/apache/superset/discussions/33879%29.%0A%0ACurrently%2C%20there%20is%20no%20built-in%20way%20to%20extract%20values%20from%20guest_token%20and%20inject%20them%20as%20session%20variables%20in%20the%20database%20connection%20for%20each%20query.%20If%20you%20need%20this%2C%20the%20closest%20workaround%20is%20a%20custom%20SQL_QUERY_MUTATOR%2C%20but%20integrating%20it%20with%20guest_token%20user%20context%20would%20require%20significant%20custom%20development.%0A%0ALet%20me%20know%20if%20you%20want%20more%20details%20on%20any%20of%20these%20hooks%20or%20possible%20custom%20approaches.) [](https://go.dosu.dev/discord-bot) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/superset/discussions/37498) GitHub link: https://github.com/apache/superset/discussions/37498#discussioncomment-15621552 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
