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

    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.orgEnglish
      2·
      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.orgEnglish
          1·
          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.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).