Can't purge communities - eviltoast

Hello everyone!

Recently my instance have become a target of community subscribers bots, which subscribed my instance to hundreds of communities. I’ve deleted all those bot accounts manually and enabled captcha and application for registration.

Then I stopped all containers except for lemmy-postgres and ran a following query to purge all empty communities

delete from community where id in (select community_id from community_aggregates ca where ca.subscribers = 0 for update skip locked);

At first I thought that it worked because all communities and related posts have disappeared, but when I started all the other containers, lemmy for some reason again started adding back those communities with 0 subscribers to my database.

What am I doing wrong? How do I purge them? Also, I would appreciate on any guide how to remove all dangling images (which don’t have any reference from db) in pict-rs.

Thanks!

  • pinkolik@random-hero.comOPEnglish
    1·
    1 year ago

    Actually, after some time, some of communities reappear again. Even though lemmy was shut off during this script. Idk why

    • Admiral Patrick@dubvee.orgEnglish
      1·
      1 year ago

      I think it may be because the remote instance community never received the “unsubscribe” and still sends post updates to your instance.

      One of the communities I removed was from a dead instance (vlemmy), one was from a locked community that wasn’t receiving updates (the old Android community), and the last one was a big meme community that was very active. The first two removed easily and without hassle. For the meme community, I had to re-sub and unsub a couple of times before I stopped getting updates. After that, it stayed removed after running the DB script.

      At the very least, it did clean up all the large number of post/comment/like entries in the DB.

      I may need to update my doc to add a note about that and making sure the unsubscribe is effective first.

      • pinkolik@random-hero.comOPEnglish
        2·
        1 year ago

        The thing is I’ve never been subscribed to those. Probably it was those bots who subscribed to those communities. And since I already deleted them from person table, I don’t know what I can do about it. Is there a way to globally send unsubscribe requests? I even tried removing community first and then purging

        • Admiral Patrick@dubvee.orgEnglish
          1·
          1 year ago

          Is there a way to globally send unsubscribe requests? I even tried removing community first and then purging

          Not that I know of, and we’re reaching the limits of my knowledge here.

          I think the problem in your case is that the remote communities still have entries in their community_follower table for those bot accounts on your instance. I’m not sure if there is a good solution, TBH, and this seems to be a rough edge of either Lemmy or ActivityPub. Basically, those instances are sending updates to your server because they still have entries in their DB telling them someone is subscribed from your side. They’re unaware those users no longer exist on your end.

          I can only think of one good solution (not saying this is the only way, just all I can think of):

          Reach out to the admins of those instances. Let them know the actor id of each bot (you might need to pull up a DB backup to get that info) and the community on their instance they were subscribed to. Ask if they would be able/willing to remove those entries from their community_follower table. They’d need to get the person ID and community ids from their own database (they’re different for each instance) and remove them. I’m not sure if banning those users from the community via the UI merely prevents them from posting or stops them from getting updates (I’m unclear and have never been banned, so I dunno how it works).