Deprecated permissions are not deleted after upgrade

Bug description

Hello

after upgrade, deprecated permissions are not deleted. It's maybe normal (a downgrade is possible) but how clean deprecated permissions ?

How to reproduce the bug

'can select star on Superset' is a deprecated permission.

I install an old version (1.5.2)

I create a role with 'can select star on Superset' permission

I create an user with the role

I stop my superset (I work with docker compose) I upgrade to 4.0.2 I run superset db upgrade I run superset db init I restart superset

The upgrade is done

The role have the deprecated permission

It's possible to create a new role with the deprecated permission

I count the permission in the 'ab_permission' table :

72 for a version 4.0.2 from scratch
112 for the version 1.5.2 upgraded to 4.0.2

Best regards

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.9

Node version

Browser

Chrome

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.

To clean up deprecated permissions after upgrading Apache Superset from version 1.5.2 to 4.0.2, you can use the cleanup_permissions function provided in the scripts/permissions_cleanup.py script. This function handles the cleanup of duplicate permissions, permissions with None values, and empty roles.

Here is the relevant code snippet for cleaning up permissions:

from collections import defaultdict
from superset import security_manager
from superset.utils.decorators import transaction

@transaction()
def cleanup_permissions() -> None:
    # 1. Clean up duplicates.
    pvms = security_manager.get_session.query(
        security_manager.permissionview_model
    ).all()
    print(f"# of permission view menus is: {len(pvms)}")
    pvms_dict = defaultdict(list)
    for pvm in pvms:
        pvms_dict[(pvm.permission, pvm.view_menu)].append(pvm)
    duplicates = [v for v in pvms_dict.values() if len(v) > 1]

    for pvm_list in duplicates:
        first_prm = pvm_list[0]
        roles = set(first_prm.role)
        for pvm in pvm_list[1:]:
            roles = roles.union(pvm.role)
            security_manager.get_session.delete(pvm)
        first_prm.roles = list(roles)

    pvms = security_manager.get_session.query(
        security_manager.permissionview_model
    ).all()
    print(f"Stage 1: # of permission view menus is: {len(pvms)}")

    # 2. Clean up None permissions or view menus
    pvms = security_manager.get_session.query(
        security_manager.permissionview_model
    ).all()
    for pvm in pvms:
        if not (pvm.view_menu and pvm.permission):
            security_manager.get_session.delete(pvm)

    pvms = security_manager.get_session.query(
        security_manager.permissionview_model
    ).all()
    print(f"Stage 2: # of permission view menus is: {len(pvms)}")

    # 3. Delete empty permission view menus from roles
    roles = security_manager.get_session.query(security_manager.role_model).all()
    for role in roles:
        role.permissions = [p for p in role.permissions if p]

    # 4. Delete empty roles from permission view menus
    pvms = security_manager.get_session.query(
        security_manager.permissionview_model
    ).all()
    for pvm in pvms:
        pvm.role = [r for r in pvm.role if r]

cleanup_permissions()

To use this function, simply run the cleanup_permissions function. This will handle the deletion of duplicate permissions, permissions with None values, and empty roles, ensuring that your permissions are properly cleaned up after the upgrade ^[1].

To continue talking to Dosu, mention @dosu.

apache / superset