Can't access database in SQL lab

Bug description

I have authorized the permissions of sample.main to this user, but this account still cannot be queried in sql_lab

How to reproduce the bug

Create a role testRole, and grant schema access on [examples].[main]
Create an account and give roles Gamma, sql_lab, testRole
Use the new account to query sql SELECT * FROM main.bart_lines
An error occurred

Screenshots/recordings

Superset version

3.1.3

Python version

3.9

Node version

Browser

Chrome

Additional context

No response

Checklist

[X] I have searched Superset docs and Slack and didn't find a solution to my problem.
[x] I have searched the GitHub issue tracker and didn't find a similar bug report.
[x] 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.

It sounds like you're encountering a permissions issue or a problem with database connectivity or table existence. Here are some targeted steps to troubleshoot and hopefully resolve the issue:

Verify Role and Permissions: Ensure the testRole you created and assigned to the user indeed has the necessary permissions for SQL Lab and the examples.main schema. Specifically, check for permissions related to database and datasource access, such as DATABASE_SECURITY_ACCESS_ERROR or DATASOURCE_SECURITY_ACCESS_ERROR.
Check Database Permissions: Confirm that the database access is explicitly granted to the user's role. This is crucial for non-Admin users and involves adding the database to the list of permitted databases for the role.
Review Custom Roles: If you've created custom roles, make sure they include the correct permissions for SQL Lab and database access. It's recommended to create new roles with the necessary permissions rather than altering base roles.
Database Connectivity: Ensure there's no issue with the database connection itself. Superset uses SQLAlchemy engines for database connections, and issues here could lead to the errors you're experiencing.
Table Existence: Verify that the table main.part_lines actually exists and that the user has permissions to access it. The error message suggests there might be an issue with accessing this specific table.

If these steps don't resolve the issue, providing the exact error message from the Superset logs could offer more insight into the problem.

#### Details

**Sources** - [Can't access database in SQL lab ](https://github.com/apache/superset/issues/13689) - [SqlLab preview permissions error](https://github.com/apache/superset/issues/20511) - [docs/docs/security/security.mdx](https://github.com/apache/superset/blob/master/docs/docs/security/security.mdx) - [superset/security/manager.py](https://github.com/apache/superset/blob/master/superset/security/manager.py) - [superset/errors.py](https://github.com/apache/superset/blob/master/superset/errors.py) - [tests/integration_tests/security/migrate_roles_tests.py](https://github.com/apache/superset/blob/master/tests/integration_tests/security/migrate_roles_tests.py) - [tests/integration_tests/security_tests.py](https://github.com/apache/superset/blob/master/tests/integration_tests/security_tests.py) - [CHANGELOG/3.1.3.md](https://github.com/apache/superset/blob/master/CHANGELOG/3.1.3.md) - [superset/sql_lab.py](https://github.com/apache/superset/blob/master/superset/sql_lab.py) - [superset/commands/database/test_connection.py](https://github.com/apache/superset/blob/master/superset/commands/database/test_connection.py) - [superset/views/utils.py](https://github.com/apache/superset/blob/master/superset/views/utils.py) - [superset-frontend/src/dashboard/util/permissionUtils.test.ts](https://github.com/apache/superset/blob/master/superset-frontend/src/dashboard/util/permissionUtils.test.ts)
**About Dosu** This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.

I've tried your steps but still can't solve my problem @dosu.

It sounds like you're encountering a permissions issue or a problem with database connectivity or table existence. Here are some targeted steps to troubleshoot and hopefully resolve the issue:

Verify Role and Permissions: Ensure the testRole you created and assigned to the user indeed has the necessary permissions for SQL Lab and the examples.main schema. Specifically, check for permissions related to database and datasource access, such as DATABASE_SECURITY_ACCESS_ERROR or DATASOURCE_SECURITY_ACCESS_ERROR.

Check Database Permissions: Confirm that the database access is explicitly granted to the user's role. This is crucial for non-Admin users and involves adding the database to the list of permitted databases for the role.

Review Custom Roles: If you've created custom roles, make sure they include the correct permissions for SQL Lab and database access. It's recommended to create new roles with the necessary permissions rather than altering base roles.

Database Connectivity: Ensure there's no issue with the database connection itself. Superset uses SQLAlchemy engines for database connections, and issues here could lead to the errors you're experiencing.

Table Existence: Verify that the table main.part_lines actually exists and that the user has permissions to access it. The error message suggests there might be an issue with accessing this specific table.

If these steps don't resolve the issue, providing the exact error message from the Superset logs could offer more insight into the problem.

Details

To continue the conversation, mention @dosu.

To address the issue where a user with the necessary permissions cannot query a database in SQL Lab and receives an error indicating a failure to execute the SQL query, let's go through a detailed checklist and some potential solutions:

Checklist and Potential Solutions

Verify Permissions: Ensure that the user has the following permissions:

def test_sql_lab_permissions(self):
   sql_lab_set = get_perm_tuples("sql_lab")
   self.assertEqual(
       sql_lab_set,
       {
           ("can_activate", "TabStateView"),
           ("can_csv", "Superset"),
           ("can_delete_query", "TabStateView"),
           ("can_delete", "TabStateView"),
           ("can_execute_sql_query", "SQLLab"),
           ("can_export", "SavedQuery"),
           ("can_export_csv", "SQLLab"),
           ("can_get", "TabStateView"),
           ("can_get_results", "SQLLab"),
           ("can_migrate_query", "TabStateView"),
           ("can_sqllab", "Superset"),
           ("can_sqllab_history", "Superset"),
           ("can_put", "TabStateView"),
           ("can_post", "TabStateView"),
           ("can_write", "SavedQuery"),
           ("can_read", "Query"),
           ("can_read", "Database"),
           ("can_read", "SQLLab"),
           ("can_read", "SavedQuery"),
           ("menu_access", "Query Search"),
           ("menu_access", "Saved Queries"),
           ("menu_access", "SQL Editor"),
           ("menu_access", "SQL Lab"),
       },
   )

Specifically, ensure the user has can_execute_sql_query for SQLLab and can_read for Database.

Database Connection Configuration: Verify that the database connection is properly configured. Ensure that the connection string and credentials are correct and that the database is accessible from Superset.
Check SQL Query Validity: Ensure that the SQL query being executed is valid. The error message suggests a potential issue with the query or permissions. Verify that the table main.bart_lines exists and that the user has access to it.
Review Logs for Specific Errors: Check the Superset logs for any specific error messages that might provide more insight into the problem. Look for any security-related errors or issues with the database connection.

Security Restrictions: If the error message indicates a security access issue, it might be related to the QueryIsForbiddenToAccessException in superset/sqllab/exceptions.py. This exception is raised when a query is forbidden to access due to security reasons. The error message will include a reason and possibly a suggestion to help resolve the issue:

QUERY_IS_FORBIDDEN_TO_ACCESS_REASON_MESSAGE = "can not access the query"
class QueryIsForbiddenToAccessException(SqlLabException):
   def __init__(
       self,
       sql_json_execution_context: SqlJsonExecutionContext,
       exception: Exception | None = None,
   ) -> None:
       super().__init__(
           sql_json_execution_context,
           SupersetErrorType.QUERY_SECURITY_ACCESS_ERROR,
           QUERY_IS_FORBIDDEN_TO_ACCESS_REASON_MESSAGE,
           exception,
       )

Frontend Actions: Ensure that the frontend actions related to SQL Lab are functioning correctly. The runQuery function in superset-frontend/src/SqlLab/actions/sqlLab.js handles the execution of SQL queries from the frontend and includes error handling for various scenarios, such as CSRF token issues and session timeouts:

export function runQuery(query) {
 return function (dispatch) {
   dispatch(startQuery(query));
   const postPayload = {
     client_id: query.id,
     database_id: query.dbId,
     json: true,
     runAsync: query.runAsync,
     catalog: query.catalog,
     schema: query.schema,
     sql: query.sql,
     sql_editor_id: query.sqlEditorId,
     tab: query.tab,
     tmp_table_name: query.tempTable,
     select_as_cta: query.ctas,
     ctas_method: query.ctas_method,
     templateParams: query.templateParams,
     queryLimit: query.queryLimit,
     expand_data: true,
   };
   const search = window.location.search || '';
   return SupersetClient.post({
     endpoint: `/api/v1/sqllab/execute/${search}`,
     body: JSON.stringify(postPayload),
     headers: { 'Content-Type': 'application/json' },
     parseMethod: 'json-bigint',
   })
     .then(({ json }) => {
       if (!query.runAsync) {
         dispatch(querySuccess(query, json));
       }
     })
     .catch(response =>
       getClientErrorObject(response).then(error => {
         let message =
           error.error ||
           error.message ||
           error.statusText ||
           t('Unknown error');
         if (message includes('CSRF token')) {
           message = t(COMMON_ERR_MESSAGES.SESSION_TIMED_OUT);
         }
         dispatch(queryFailed(query, message, error.link, error.errors));
       }),
     );
 };
}

Superset Security Manager: Ensure that the user has the appropriate permissions set up in the SupersetSecurityManager class in superset/security/manager.py, particularly for accessing databases and executing SQL queries. The can_access method and related methods in this class are crucial for determining if a user has the necessary permissions to perform certain actions.

Additional Steps

If the above steps do not resolve the issue, please provide the exact error messages from the Superset logs. This will help in diagnosing the problem more accurately. Additionally, ensure that there are no network issues or firewall rules blocking access to the database.

By following these steps, you should be able to identify and resolve the issue preventing the user from querying the database in SQL Lab.

#### Details

**Sources** - [Unable to use sql lab](https://github.com/apache/superset/discussions/27377) - [Can't access database in SQL lab ](https://github.com/apache/superset/issues/13689) - [SqlLab preview permissions error](https://github.com/apache/superset/issues/20511) - [tests/integration_tests/security_tests.py](https://github.com/apache/superset/blob/master/tests/integration_tests/security_tests.py) - [superset/sql_lab.py](https://github.com/apache/superset/blob/master/superset/sql_lab.py) - [superset/sqllab/exceptions.py](https://github.com/apache/superset/blob/master/superset/sqllab/exceptions.py) - [superset-frontend/src/SqlLab/actions/sqlLab.js](https://github.com/apache/superset/blob/master/superset-frontend/src/SqlLab/actions/sqlLab.js) - [superset/security/manager.py](https://github.com/apache/superset/blob/master/superset/security/manager.py)
**About Dosu** This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.

DB engine Error Failed to execute query 'SELECT * FROM main.bart_l:ines ': can not access the query @dosu.

The error message "Failed to execute query 'SELECT * FROM main.bart_lines': can not access the query" indicates a security access issue, specifically a QueryIsForbiddenToAccessException. Here are some additional steps to resolve this issue:

Verify Role Permissions

Ensure that the testRole has the necessary permissions to access the main.bart_lines table. Check the role's permissions in the Superset UI or using the Superset CLI.

Review Dataset Permissions

Make sure the dataset permissions are correctly configured to allow access to the main.bart_lines table. This can be done in the Superset UI under the "Datasets" section.

Inspect Security Policies

Check for any security policies or rules that might be restricting access to the query. This includes reviewing the SupersetSecurityManager class in superset/security/manager.py for any custom security logic.

Check the `_validate_access` Method

Ensure the _validate_access method in the ExecuteSqlCommand class (located in superset/commands/sql_lab/execute.py) is not being overridden or modified in a way that restricts access.

Review the `SupersetSecurityManager` Methods

Ensure that the SupersetSecurityManager methods like can_access, can_access_all_queries, can_access_all_datasources, and can_access_database are correctly implemented and that the user has the appropriate permissions.

Check Superset Logs

Review the Superset logs for detailed error messages that could provide additional insights into the cause of the access restriction. The logs can be found in the Superset server logs directory.

Validate Access in `execute_sql_query`

Ensure that the execute_sql_query method in superset/sqllab/api.py is not being modified in a way that restricts access.

By following these steps, you should be able to identify and resolve the issue causing the "can not access the query" error. If the problem persists, please provide more details about your configuration and any custom security settings you might have in place.

#### Details

**Sources** - [Can't access database in SQL lab ](https://github.com/apache/superset/issues/13689) - [superset/sqllab/exceptions.py](https://github.com/apache/superset/blob/master/superset/sqllab/exceptions.py) - [superset/commands/sql_lab/execute.py](https://github.com/apache/superset/blob/master/superset/commands/sql_lab/execute.py) - [superset/security/manager.py](https://github.com/apache/superset/blob/master/superset/security/manager.py) - [superset/sqllab/api.py](https://github.com/apache/superset/blob/master/superset/sqllab/api.py)
**About Dosu** This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.

DB engine Error Failed to execute query 'SELECT * FROM main.bart_l:ines ': can not access the query

I encountered the same problem and tried to print some logs to investigate the issue. The code is as follows: superset/sql_parse.py

class ParsedQuery:
    def __init__(
        self,
        sql_statement: str,
        strip_comments: bool = False,
        engine: str | None = None,
    ):
        if strip_comments:
            sql_statement = sqlparse.format(sql_statement, strip_comments=True)

        self.sql: str = sql_statement
        self._dialect = SQLGLOT_DIALECTS.get(engine) if engine else None
        self._tables: set[Table] = set()
        self._alias_names: set[str] = set()
        self._limit: int | None = None

        logger.debug("Parsing with sqlparse statement: %s", self.sql)
        self._parsed = sqlparse.parse(self.stripped())
        for statement in self._parsed:
            self._limit = _extract_limit_from_query(statement)

def stripped(self) -> str:
        return self.sql.strip(" \t\r\n;")

I try to print log: logger-info (self.sql) The output is Template (body=[Output (nodes=[TemplateData (data='SELECT * from xxxx. xxxx limit 100; ')]) The code will not continue to execute until this point return self.sql.strip(" \t\r\n;")

Awesome! @leoguo1024 #29311 fixed my problem as well. I was experiencing the same issue when upgrading from version 2.1.3 to version 4.0.1.

Hopefully we can merge that PR soon and close out this issue. I think the PR has a couple of comments/requests that need to be addressed by @leoguo1024, and then we'll be all good!

@rusackas @john-bodley This is still an issue for me. Is there anything I can do to help?

apache / superset