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

• 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]

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.