[GitHub] [superset] dpgaspar commented on a diff in pull request #20081: fix: database permissions on update and delete (avoid orphaned perms)

[GitBox <gi...@apache.org>]

dpgaspar commented on code in PR #20081:
URL: https://github.com/apache/superset/pull/20081#discussion_r929165965


##########
superset/security/manager.py:
##########
@@ -933,6 +933,167 @@ def _is_granter_pvm(  # pylint: disable=no-self-use
 
         return pvm.permission.name in {"can_override_role_permissions", "can_approve"}
 
+    def database_after_delete(  # pylint: disable=unused-argument
+        self,
+        mapper: Mapper,
+        connection: Connection,
+        target: "Database",
+    ) -> None:
+        view_menu_table = self.viewmenu_model.__table__  # pylint: disable=no-member
+        permission_view_menu_table = (
+            self.permissionview_model.__table__  # pylint: disable=no-member
+        )
+
+        view_menu_name = target.get_perm()
+
+        # Clean database access permission
+        db_pvm = self.find_permission_view_menu("database_access", view_menu_name)
+        if not db_pvm:
+            logger.warning(
+                "Could not find previous database permission %s",
+                view_menu_name,
+            )
+            return
+        connection.execute(
+            permission_view_menu_table.delete().where(
+                permission_view_menu_table.c.id == db_pvm.id
+            )
+        )
+        connection.execute(
+            view_menu_table.delete().where(view_menu_table.c.id == db_pvm.view_menu_id)
+        )
+
+        # Clean database schema permissions
+        schema_pvms = (
+            self.get_session.query(self.permissionview_model)
+            .join(self.permission_model)
+            .join(self.viewmenu_model)
+            .filter(self.permission_model.name == "schema_access")
+            .filter(self.viewmenu_model.name.like(f"[{target.database_name}].[%]"))

Review Comment:
   `database.database_name` is unique, my reasoning here is that:
   - it's possible there are orphaned schema permissions from old schemas of the same db (no problem here)
   - renaming and then creating a new db, for example:
   
   1 - create database named `db1` with schemas `db1_schema1` and `db1_schema2`
   2 - rename the database `db1` -> `db2` (would leave those perms "orphaned")
   3 - create a database named `db1` with schemas `db1_schema1_1`
   4 - delete database `db1`
   
   this would delete schema perms `[db1].[db1_schema1]`, `[db1].[db1_schema2]`, `[db1].[db1_schema1_1]`. No arm done



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