Error in SQL lab using RLS for dataset on PostgreSQL

[propellerjet]

Bug description

The bug is reproduced for datasets on PostgreSQL with columns containing capital letters.
Steps:

1. Create RLS
   
2. Run select query for this dataset
   

Identifiers (including column names) that are not double-quoted are folded to lower case in PostgreSQL.
Identifiers created with double quotes retain upper case letters.

Analyzing the log we've found that even we are using double quotes for column name in RLS, query that are send to DB from SQL lab doesn't contain double quotes.

This bug is not reproduced on chart

The query is generated correctly

Screenshots/recordings

No response

Superset version

4.1.1

Python version

3.9

Node version

16

Browser

Chrome

Additional context

flag RLS_IN_SQLLAB is applied

---

Worker container log
[2025-03-10 16:44:05,738: INFO/ForkPoolWorker-7] Task sql_lab.get_sql_results[28448e7f-351f-45c4-9624-441718fa923a] succeeded in 0.19986815191805363s: {'query_id': 12018, 'status': 'failed', 'error': 'postgresql error: column flights.airline does not exist

LINE 3: FROM (SELECT * FROM public.flights WHERE (flights.AIRLINE li...

                                              ^', 'errors': [{'message': 'postgresql error: column flights.airline does not exist

LINE 3: FROM (SELECT * FROM public.flights WHERE (flights.AIRLINE li...

                                              ^

', 'error_type': 'GENERIC_DB_ENGINE_ERROR', 'level': 'error', 'extra': {...}}]}

---

DB container log:
2025-03-10 16:44:05.720 [27282] ERROR: column flights.airline does not exist at character 54
2025-03-10 16:44:05.720 [27282] STATEMENT: SELECT
*
FROM (SELECT * FROM public.flights WHERE (flights.AIRLINE like 'A%')) AS flights
LIMIT 101

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]

The issue you're encountering seems to be related to how Superset's SQL Lab handles column name casing when Row Level Security (RLS) is applied. In PostgreSQL, unquoted identifiers are automatically converted to lowercase, which can lead to issues if your column names contain uppercase letters and are not properly quoted in the SQL query.

In your case, it appears that even though you are using double quotes for column names in the RLS configuration, the SQL Lab query sent to the database does not include these quotes, resulting in a casing mismatch and the error you're seeing.

This behavior is consistent with the known handling of SQL query generation in Superset, where column names might not be quoted correctly, especially when RLS is involved. The RLS_IN_SQLLAB flag attempts to apply RLS rules to SQL Lab queries, but this can lead to issues if the query parsing and manipulation do not preserve the necessary quoting for column names ^[1].

To address this issue, you might consider:

1. Ensuring that all column names in your queries are explicitly quoted to preserve their casing.
2. Reviewing the RLS configuration to ensure that it aligns with how SQL Lab processes queries.
3. Checking if there are any updates or patches in newer versions of Superset that address this specific issue.

If the problem persists, you may need to explore alternative ways to handle RLS or adjust your dataset schema to avoid casing issues.

To continue talking to Dosu, mention @dosu.

---

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

[rusackas]

Maybe we can automatically double quote these using SQLGlot? I'm not sure, but at least there's a workaround of doing so manually for now. CC @betodealmeida

[rusackas]

Can you check on master and see if the SQLGlot migration resolved this issue?