/savedqueryviewapi/api/read throws 401 {"message":"Access is Denied","severity":"danger"} when /api/v1/saved_query works fine

[rajivpatki]

A clear and concise description of what the bug is.

How to reproduce the bug

1. Use admin role user (using auth: Google OAuth) to log in to Superset UI in your browser. Navigate to https://superset.example.com/savedqueryviewapi/api/read
2. Observe the response json: all saved queries, also from other users, show up
3. Use admin credentials (provider: db, admin user created during superset init) to make a GET request to /api/v1/saved_query
4. Own saved queries are returned
5. Use admin credentials (provider: db, admin user created during superset init) to make a GET request to /savedqueryviewapi/api/read
6. Response is status code 401 with content {"message":"Access is Denied","severity":"danger"}

Expected results

Same behaviour as browser UI

Actual results

401 with Access Denied

Environment

• browser type and version: Microsoft Edge Version 120.0.2210.91 (Official build) (64-bit)
• superset version: 3.0.2 from official docker image
• any feature flags active:

TALISMAN_ENABLED = True
SECRET_KEY = "SUPERSET_SECRET_KEY"
WTF_CSRF_ENABLED = True

FEATURE_FLAGS = {
    "ENABLE_TEMPLATE_PROCESSING": True,
}

SCHEDULED_QUERIES = {<from URL in Additional context below>}

Logs

127.0.0.1 - - [30/Dec/2023:12:03:39 +0000] "GET /api/v1/security/csrf_token/ HTTP/1.1" 200 105 "-" "python-requests/2.31.0"
127.0.0.1 - - [30/Dec/2023:12:03:39 +0000] "GET /login/ HTTP/1.1" 200 48874 "-" "python-requests/2.31.0"
127.0.0.1 - - [30/Dec/2023:12:03:39 +0000] "POST /api/v1/security/login HTTP/1.1" 200 280 "-" "python-requests/2.31.0"
127.0.0.1 - - [30/Dec/2023:12:03:39 +0000] "GET /api/v1/security/csrf_token/ HTTP/1.1" 200 105 "-" "python-requests/2.31.0"
127.0.0.1 - - [30/Dec/2023:12:03:40 +0000] "POST /api/v1/security/login HTTP/1.1" 200 280 "-" "python-requests/2.31.0"
127.0.0.1 - - [30/Dec/2023:12:03:40 +0000] "GET /api/v1/saved_query HTTP/1.1" 308 269 "-" "python-requests/2.31.0"
127.0.0.1 - - [30/Dec/2023:12:03:40 +0000] "GET /api/v1/saved_query/ HTTP/1.1" 200 377 "-" "python-requests/2.31.0"
127.0.0.1 - - [30/Dec/2023:12:03:40 +0000] "GET /health HTTP/1.1" 200 2 "-" "curl/7.88.1"
127.0.0.1 - - [30/Dec/2023:12:03:57 +0000] "POST /api/v1/security/login HTTP/1.1" 200 280 "-" "python-requests/2.31.0"
127.0.0.1 - - [30/Dec/2023:12:03:57 +0000] "GET /api/v1/security/csrf_token/ HTTP/1.1" 200 105 "-" "python-requests/2.31.0"
127.0.0.1 - - [30/Dec/2023:12:03:57 +0000] "GET /login/ HTTP/1.1" 200 48875 "-" "python-requests/2.31.0"
127.0.0.1 - - [30/Dec/2023:12:03:58 +0000] "POST /api/v1/security/login HTTP/1.1" 200 280 "-" "python-requests/2.31.0"
127.0.0.1 - - [30/Dec/2023:12:03:58 +0000] "GET /api/v1/security/csrf_token/ HTTP/1.1" 200 105 "-" "python-requests/2.31.0"
127.0.0.1 - - [30/Dec/2023:12:03:58 +0000] "POST /api/v1/security/login HTTP/1.1" 200 280 "-" "python-requests/2.31.0"
127.0.0.1 - - [30/Dec/2023:12:03:58 +0000] "GET /savedqueryviewapi/api/read HTTP/1.1" 401 51 "-" "python-requests/2.31.0"

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• [✓] I have checked the superset logs for python stacktraces and included it here as text if there are any.
• [✓] I have reproduced the issue with at least the latest released version of superset.
• [✓] I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

I am trying to implement SCHEDULED_QUERIES as referenced here:

This information can then be retrieved from the endpoint /savedqueryviewapi/api/read and used to schedule the queries that have scheduled_queries in their JSON metadata. For schedulers other than Airflow, additional fields can be easily added to the configuration file above.

The objective is to use Airflow to implement users' scheduled queries. The UI works fine - saved queries with scheduling data show up at https://superset.example.com/savedqueryviewapi/api/read on my browser. But I am not able to fetch these queries using a Python script due to the 401.

Using /api/v1/saved_query doesn't help since it shows only your own saved queries, similar to @matt-lessig in #21769 (comment)

Will this fix also apply to the REST API? We are trying to use an administrative service account to pull all saved queries for all users via the REST API.

Reference: #20604, #21769

[rajivpatki]

It seems that authentication can either be only OAUTH or DB, not both. Could someone help with how we can authenticate as an admin user or using a Google Service account? I shall happily convert that to proper documentation.

[rusackas]

@rajivpatki are you still facing this, or have you moved on in some way? Tempted to close as stale, but don't want to do so if you still need help. We'll have a hard time supporting any custom security managers if that's part of the problem here, but if you think it's an underlying Superset bug, we can attempt to get more attention on this.

[rusackas]

Meanwhile, let's see what our new bot says....

@dosu-bot

[dosubot]

To address the issue of accessing the /savedqueryviewapi/api/read endpoint with a 401 error and to facilitate authentication using either an admin user or a Google Service account, here's a consolidated guide:

1. Ensure Proper Permissions: Verify that the admin credentials or the Google Service account have the necessary permissions to access the endpoint. This includes checking if the admin user has the correct role and permissions within Superset's security manager.

2. Authentication Method Configuration: For DB authentication, confirm the admin user created during Superset initialization is correctly set up with the necessary permissions. For Google OAuth, ensure that OAuth credentials are properly configured in superset_config.py, which involves setting up OAuth 2.0 credentials in the Google API Console and configuring Superset to use these credentials.

3. Hybrid Authentication Support: If you aim to support both DB and OAuth authentication, you may need to customize your Superset configuration. This could involve modifying the authentication backend to allow for both methods, which might require custom development.

4. Service Account for API Access: When using a Google Service account for programmatic access, make sure the service account is granted access within Superset. This process typically involves generating a JWT for the service account and using it to obtain an access token for API requests.

5. Check Endpoint and Server Configuration: Confirm the endpoint URL is correct and that there are no server-side configurations or middleware that might be blocking the request. Also, ensure you're using the correct method to pass the authentication token in your request headers.

6. Use Supported API Endpoints: Consider using REST API endpoints under /api/v1/ for more consistent and supported access, as the /savedqueryviewapi/api/read might not be supported in your version of Superset or could require different permissions.

7. Review Documentation and Community Forums: For specific configurations, especially when setting up a hybrid authentication system or using Google Service accounts, refer to Superset documentation and community forums for guidance and examples.

By following these steps, you should be able to configure the authentication method that best suits your needs and resolve the 401 error when accessing the /savedqueryviewapi/api/read endpoint. If issues persist, examining application logs and the authentication provider's logs can offer further insights.

Details

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.