You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by "xujipm (via GitHub)" <gi...@apache.org> on 2024/05/20 06:15:46 UTC
[I] Can't access database in SQL lab [superset]
xujipm opened a new issue, #28596:
URL: https://github.com/apache/superset/issues/28596
### 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
1. Create a role `testRole`, and grant `schema access on [examples].[main]`
2. Create an account and give roles `Gamma`, `sql_lab`, `testRole`
3. Use the new account to query sql `SELECT * FROM main.bart_lines`
4. An error occurred
### Screenshots/recordings
### Superset version
3.1.3
### Python version
3.9
### Node version
16
### 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.
--
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: notifications-unsubscribe@superset.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Can't access database in SQL lab [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas commented on issue #28596:
URL: https://github.com/apache/superset/issues/28596#issuecomment-2855453825
Well, OK! Let's call this one resolved, and if anyone finds differently, we can reopen!
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Can't access database in SQL lab [superset]
Posted by "dosubot[bot] (via GitHub)" <gi...@apache.org>.
dosubot[bot] commented on issue #28596:
URL: https://github.com/apache/superset/issues/28596#issuecomment-2119744071
<!-- Greeting -->
Hey there, @xujipm! 🎉 I'm [Dosu](https://dosu.dev), your friendly neighborhood bot here to lend a hand with bugs, answer questions, and get you on the fast track to contributing! While we wait for a human maintainer to chime in, I'll be your go-to for guidance. Let me take a moment to dive into your issue and I'll get back to you with a full answer shortly. Stay tuned! 🤖
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Can't access database in SQL lab [superset]
Posted by "Hen0k (via GitHub)" <gi...@apache.org>.
Hen0k commented on issue #28596:
URL: https://github.com/apache/superset/issues/28596#issuecomment-2364162911
@rusackas @john-bodley This is still an issue for me. Is there anything I can do to help?
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Can't access database in SQL lab [superset]
Posted by "Habeeb556 (via GitHub)" <gi...@apache.org>.
Habeeb556 commented on issue #28596:
URL: https://github.com/apache/superset/issues/28596#issuecomment-2809451577
> The linked PR is pretty much stuck in a state of limbo. If anyone on this thread is still concerned, we would love to see that PR (or one like it!) merged. Contributions here are much appreciated.
I think it fixed in latest `version 4.1.1` as I didn't see it.
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Can't access database in SQL lab [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas closed issue #28596: Can't access database in SQL lab
URL: https://github.com/apache/superset/issues/28596
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Can't access database in SQL lab [superset]
Posted by "dosubot[bot] (via GitHub)" <gi...@apache.org>.
dosubot[bot] commented on issue #28596:
URL: https://github.com/apache/superset/issues/28596#issuecomment-2121629720
<!-- Answer -->
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
1. **Verify Permissions**:
Ensure that the user has the following permissions:
```python
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`.
2. **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.
3. **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.
4. **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.
5. **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:
```python
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,
)
```
6. **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:
```js
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));
}),
);
};
}
```
7. **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.
<!-- Dosu Comment Footer -->
<details>
<summary>
#### Details
</summary>
**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)
<br/>
**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.
</details>
*To continue the conversation, mention @dosu.*
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Can't access database in SQL lab [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas commented on issue #28596:
URL: https://github.com/apache/superset/issues/28596#issuecomment-2802501578
The linked PR is pretty much stuck in a state of limbo. If anyone on this thread is still concerned, we would love to see that PR (or one like it!) merged. Contributions here are much appreciated.
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Can't access database in SQL lab [superset]
Posted by "leoguo1024 (via GitHub)" <gi...@apache.org>.
leoguo1024 commented on issue #28596:
URL: https://github.com/apache/superset/issues/28596#issuecomment-2175484127
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;")
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Can't access database in SQL lab [superset]
Posted by "xujipm (via GitHub)" <gi...@apache.org>.
xujipm commented on issue #28596:
URL: https://github.com/apache/superset/issues/28596#issuecomment-2121624763
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:
>
> 1. **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`.
> 2. **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.
> 3. **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.
> 4. **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.
> 5. **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._
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Can't access database in SQL lab [superset]
Posted by "xujipm (via GitHub)" <gi...@apache.org>.
xujipm commented on issue #28596:
URL: https://github.com/apache/superset/issues/28596#issuecomment-2125233330
DB engine Error
Failed to execute query 'SELECT * FROM main.bart_l:ines ': can not access the query
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Can't access database in SQL lab [superset]
Posted by "dosubot[bot] (via GitHub)" <gi...@apache.org>.
dosubot[bot] commented on issue #28596:
URL: https://github.com/apache/superset/issues/28596#issuecomment-2121665555
<!-- Answer -->
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.
<!-- Dosu Comment Footer -->
<details>
<summary>
#### Details
</summary>
**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)
<br/>
**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.
</details>
*To continue the conversation, mention @dosu.*
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Can't access database in SQL lab [superset]
Posted by "xujipm (via GitHub)" <gi...@apache.org>.
xujipm commented on issue #28596:
URL: https://github.com/apache/superset/issues/28596#issuecomment-2121661360
DB engine Error
Failed to execute query 'SELECT * FROM main.bart_l:ines ': can not access the query
@dosu.
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org