Unable to set role permissions for new schema unless a new connection is created.

[MialLewis]

Bug description

I have a Trino database which is made available through connection1. If I create a new schema on this database, I cannot give users permission to access this schema via their role.

When navigating to Settings > List Roles > Edit Record, in the Permissions field I cannot see the new schema, only schema that existed before the creation of connection1.

If I now create a new connection, connection2, which is exactly the same as connection1, I can see the new schema,

This bug was discussed last year here: https://lists.apache.org/thread/bqogccdg3wdjkjsgytsqw1ylsmps756v

And now here: https://github.com/konzainc/konza-kube/issues/375

How to reproduce the bug

1) Create a database connection to a Trino database, test_connection_1 (other db's not tested). 2) On this database create a new schema test_schema through SQL Lab. 3) Navigate to Settings > List Roles > Edit Record. In the permissions field search for test_schema, which will not be found 4) Create a new database connection configured in the same way as the first, test_connection_2. 5) Repeat step 3, this time see test_schema is found through test_connection_2.

Screenshots/recordings

No response

Superset version

3.1.3

Python version

3.10

Node version

I don't know

Browser

Firefox

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.

[dosubot[bot]]

Hey @MialLewis! I'm here to help you with bugs, questions, and becoming a contributor. Let's squash some bugs together! 🐛

To set role permissions for a new schema in Apache Superset without creating a new connection, you can use the add_permission_view_menu and add_permission_role methods from the SupersetSecurityManager class. This approach allows you to directly add the necessary permissions to a role.

Here is an example of how you can set schema access permissions for a role:

from superset import security_manager
from superset.models.core import Database
from superset.models.sql_lab import SqlaTable

# Define the role name and the schema permission
role_name = "your_role_name"
database_name = "your_database_name"
schema_name = "your_schema_name"
schema_permission = security_manager.get_schema_perm(database_name, None, schema_name)

# Create the schema permission view menu
security_manager.add_permission_view_menu("schema_access", schema_permission)

# Find the permission view
permission_view = security_manager.find_permission_view_menu("schema_access", schema_permission)

# Add the permission to the role
role = security_manager.find_role(role_name)
security_manager.add_permission_role(role, permission_view)

# Commit the changes to the database
db.session.commit()

In this example:

1. Define the role name and the schema permission.
2. Create the schema permission view menu using add_permission_view_menu.
3. Find the permission view for the schema access.
4. Add the permission to the role using add_permission_role.
5. Commit the changes to the database.

This approach allows you to set role permissions for a new schema without creating a new connection. Additionally, the get_schema_perm method is used to generate the schema permission string, ensuring consistency with how permissions are managed in Superset ^[1][2][3].

Additionally, Apache Superset handles schema caching through the schema_cache_timeout property. If schema caching is enabled and the cache timeout is set, it could affect the visibility of new schemas in the role permissions. You can force refresh the schema list using the useSchemas hook in superset-frontend/src/hooks/apiResources/schemas.ts:

export function useSchemas(options: Params) {
  const { dbId, catalog, onSuccess, onError } = options || {};
  const [trigger] = useLazySchemasQuery();
  const result = useSchemasQuery(
    { dbId, catalog: catalog || undefined, forceRefresh: false },
    {
      skip: !dbId,
    },
  );

  const fetchData = useEffectEvent(
    (
      dbId: FetchSchemasQueryParams['dbId'],
      catalog: FetchSchemasQueryParams['catalog'],
      forceRefresh = false,
    ) => {
      if (dbId && (!result.currentData || forceRefresh)) {
        trigger({ dbId, catalog, forceRefresh }).then(
          ({ isSuccess, isError, data }) => {
            if (isSuccess) {
              onSuccess?.(data || EMPTY_SCHEMAS, forceRefresh);
            }
            if (isError) {
              onError?.();
            }
          },
        );
      }
    },
  );

  useEffect(() => {
    fetchData(dbId, catalog, false);
  }, [dbId, catalog, fetchData]);

  const refetch = useCallback(() => {
    fetchData(dbId, catalog, true);
  }, [dbId, catalog, fetchData]);

  return {
    ...result,
    refetch,
  };
}

This hook allows you to force refresh the schema list by calling the refetch method, which can help in making new schemas visible without waiting for the cache to expire ^[4].

To continue talking to Dosu, mention @dosu.

[Habeeb556]

Hello @MialLewis, I am facing the same issue. However, I worked around it by going to the database connections where you configure your database and clicking on "Test Connection" This acts like a refresh instead of creating a new data source connection.

Additionally, I think this bug could be resolved by implementing an auto-refresh feature in Superset.

[MialLewis]

Test Connection didn't work to refresh schema for me, wonder if it depends on the connection type.

[Habeeb556]

@MialLewis What type of database connection did you use?

[algardas-sca]

Yeah, also had this problem - it's difficult to make superset refresh the schema_access permissions after updating the database schema. I have used this script as part of my provisioning setup:

import superset
from superset import security_manager as sm

def refresh_db_schemas(dbname):
    from superset.daos.database import DatabaseDAO

    # superset does not automatically refresh the schema_access permissions after DB schema changes,
    # let's do it manually for now (code parts from upstream superset/superset/databases/api.py)
    database = DatabaseDAO.get_database_by_name(dbname)
    schemas = database.get_all_schema_names(cache=False)
    for schema in schemas:
        sm.add_permission_view_menu("schema_access", sm.get_schema_perm(database, schema)) 

app = superset.create_app()
with app.app_context():
  refresh_db_schemas("mydb")

[Habeeb556]

++ I encountered the same issue, and using "Test Connection" did not resolve it. The problem is related to role permissions. The schema name appeared in SQL Lab after refreshing the list from button, but the role permission issue persists with the new schema name not showing.

[rusackas]

Pinging the illustrious Trino review crew from the rolodex: @giftig @sujiplr @nytai @cccs-tom @villebro

[Habeeb556]

Yeah, also had this problem - it's difficult to make superset refresh the schema_access permissions after updating the database schema. I have used this script as part of my provisioning setup:

import superset
from superset import security_manager as sm

def refresh_db_schemas(dbname):
    from superset.daos.database import DatabaseDAO

    # superset does not automatically refresh the schema_access permissions after DB schema changes,
    # let's do it manually for now (code parts from upstream superset/superset/databases/api.py)
    database = DatabaseDAO.get_database_by_name(dbname)
    schemas = database.get_all_schema_names(cache=False)
    for schema in schemas:
        sm.add_permission_view_menu("schema_access", sm.get_schema_perm(database, schema)) 

app = superset.create_app()
with app.app_context():
  refresh_db_schemas("mydb")

I believe the issue isn't just with forcing the retrieval of new schemas, but also with the role section being cached. For example, even after deleting and creating a new Datasource, the old Datasource still appears in role_permission.

[cccs-tom]

Fwiw, we've been working around this by opening the Database configuration dialog and clicking Finish. No need to make any changes. That seems to re-fetch the list of schemas.

[Habeeb556]

Hello @cccs-tom, I understand the workaround. However, I was referring to another issue where the old deleted database configuration is still cached. It appears when I open the list of roles and attempt to add role permissions for a specific schema.