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.comOP
      link
      fedilink
      English
      arrow-up
      2
      ·
      1 year ago

      Thanks! That’s exactly what I was looking for :) I have two questions though.

      1. Should I stop lemmy before running this script?
      2. -- Easiest way to disable triggers for a transaction. If you know of a better way, please share. SET session_replication_role = replica; How do I enable it back?
      • Admiral Patrick@dubvee.org
        link
        fedilink
        English
        arrow-up
        3
        ·
        1 year ago

        I stopped Lemmy to run it, but that was because I had already stopped it to take a full backup first. I was also removing a very large, active community. I don’t think it was necessary, and I’ve removed other communities with Lemmy running. Just see the caveat about restarting Lemmy to make sure it “forgets” the community.

        The triggers will only be disabled for the run of that script in that session. So you don’t have to worry about turning them back on because they were never turned off for any other DB sessions.

          • Admiral Patrick@dubvee.org
            link
            fedilink
            English
            arrow-up
            1
            ·
            edit-2
            1 year ago

            sudo docker run --network [network name] curlimages/curl --no-progress-meter --write-out '\n' -X POST -H 'x-api-token: [API TOKEN HERE]' pictrs:8080/internal/purge?alias=[image name/alias]

            Basically, call the /internal/purge endpoint of pict-rs by sending the API token as a header and the filename of the image as a URL param. You have to do this against the pict-rs service directly since Lemmy-UI doesn’t proxy that /internal route.

            Unfortunately, as stated in my guide, I don’t have any facilities in the script (yet) to pull out any pict-rs URLs that are local to your instance. After the script runs, those references in the DB are gone and there’s no way to delete them from pict-rs without the filenames/aliases. You can, however, get those from a database backup if you’re so inclined.

            e.g. restore a backup to another postgres instance, write a query to look for https://{your-instance-domain}/pictrs/image in the post and comment tables that are linked to the given community ID. The table links should be in the bottom of the guide.

        • pinkolik@random-hero.comOP
          link
          fedilink
          English
          arrow-up
          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.org
            link
            fedilink
            English
            arrow-up
            1
            ·
            edit-2
            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.comOP
              link
              fedilink
              English
              arrow-up
              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.org
                link
                fedilink
                English
                arrow-up
                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).

  • BlackEco@lemmy.blackeco.com
    link
    fedilink
    English
    arrow-up
    2
    ·
    1 year ago

    My guess is remote instances still consider the bots to be subscribed and thus kept sending new posts and comments. I don’t know how to fix this situation though.