I’ve installed Lemmy via ansible, but I figured out that there are problems with emails and after I’ve created a user, no verification email came, no login is possible and now I have a ghost account I want to delete.

Because I couldn’t find an UI element to purge users in the admin panel, I want to do it over postgresql, but I have no idea how to connect to the Lemmy database over a docker container, only thing I’ve found is to backup/restore the whole database:

docker-compose exec postgres pg_dumpall -c -U lemmy | gzip > lemmy_dump_date +%Y-%m-%d""%H%M_%S.sql.gz

Thank you for your help.

  • beeng@discuss.tchncs.de
    link
    fedilink
    arrow-up
    4
    ·
    1 year ago

    Try to get a bash prompt inside the dB container, then you can run CLI postgres commands from inside the container.

    • maxmoon@lemmy.mlOP
      link
      fedilink
      English
      arrow-up
      1
      ·
      edit-2
      1 year ago

      How exactly? The recommendations of other people here didn’t work.

      EDIT: Found a solution. Check other posts to see how it works.

  • FuckyWucky [none/use name]@hexbear.net
    link
    fedilink
    English
    arrow-up
    2
    ·
    1 year ago

    if you already have the container up you can run “docker exec -it postgres bash”. this will open the shell of the postgres container

    in that i think you can run postgres commands with “psql”

    • maxmoon@lemmy.mlOP
      link
      fedilink
      English
      arrow-up
      1
      ·
      1 year ago

      docker exec -it postgres bash

      After executing this command and executing psql, I got this error:

      psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "root" does not exist

        • maxmoon@lemmy.mlOP
          link
          fedilink
          English
          arrow-up
          1
          ·
          1 year ago

          psql -U postgres

          No luck with this comman :(

          psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "postgres" does not exist

          But is it the right docker container? The name of the container I try to connect is lemmymydomaintld_postgres_1

          Seems pretty hard to delete a user this way. Do you know an easier way by any chance?

            • maxmoon@lemmy.mlOP
              link
              fedilink
              English
              arrow-up
              3
              ·
              edit-2
              1 year ago

              I found a solution here

              A warning to people who have the same problem:

              On the website there is a command, which will purge all unverified account. If your admin account wasn’t verified, it will be purged, too.

              Thanks a lot for your help :)

  • ikornaselur@lemm.ee
    link
    fedilink
    arrow-up
    2
    ·
    edit-2
    1 year ago

    You might be able to just run docker-compose exec postgres psql to drop into a shell (you may still need to define the user with -U lemmy)

    If you’re trying to connect with ane external tool though, you’ll need to expose the ports temporarily, which I’m not sure if you can do without restarting the container (after adding the exposed ports to the docket compose file, like 127.0.0.1:5432:5432)

    • maxmoon@lemmy.mlOP
      link
      fedilink
      English
      arrow-up
      1
      ·
      1 year ago

      After executing docker exec -it lemmymydomain_postgres_1 psql

      I’ve got the error:

      psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "root" does not exist

      • ikornaselur@lemm.ee
        link
        fedilink
        arrow-up
        3
        ·
        edit-2
        1 year ago

        Oh yeah, try specifying the hostname as localhost (I think it’s -h, but --help will confirm it). Psql tries by default to connect to that socket, but in docker it does use it.

        Basically, you’ll need to specify the hostname and user (and if port, which should just be default, then -p 5432)

        It should be

        docker exec -it lemmymydomain_postgres_1 psql -h localhost -U lemmy (dbname)

        Keeping in mind that you’ll need to have the right user and database name, assuming (based on your original comment) lemmy being the username and the db name might also be lemmy

        Edit: this should btw just get you into the db, I have no idea of its a good idea or not to delete a user directly from the db, it might cause issues, but I don’t have experience with it, but just generally use postgres in docker with other projects

        • maxmoon@lemmy.mlOP
          link
          fedilink
          English
          arrow-up
          2
          ·
          edit-2
          1 year ago

          I found a solution here

          A warning to people who have the same problem:

          On the website there is a command, which will purge all unverified account. If your admin account wasn’t verified, it will be purged, too.

          Thanks a lot for your help :)

          • ikornaselur@lemm.ee
            link
            fedilink
            arrow-up
            1
            ·
            1 year ago

            Glad you solved it! Definitely sounds like a better solution than playing around in the db

            • maxmoon@lemmy.mlOP
              link
              fedilink
              English
              arrow-up
              1
              ·
              1 year ago

              Well, it is playing around with the db 😀 … and it could even destroy the whole instance if people don’t see the warning I’ve posted there.

  • Lugh@futurology.today
    link
    fedilink
    English
    arrow-up
    1
    ·
    1 year ago

    I hope the Lemmy devs sort out email verification soon. We had so many problems we switched it off at futurology.today & went with limiting new sign-ups to a set amount per hour to combat bots instead.

    • maxmoon@lemmy.mlOP
      link
      fedilink
      arrow-up
      1
      ·
      1 year ago

      What exactly was the issue with your verification system?

      Currently the emails get out. At least I can see people creating accounts and posting stuff.

      • Lugh@futurology.today
        link
        fedilink
        English
        arrow-up
        2
        ·
        1 year ago

        What exactly was the issue with your verification system?

        Numerous issues the site dev couldn’t fully understand after hours of investigating. We’ve switched off email verification until the Lemmy devs fix this in an update. Additionally 2FA is often broken too & awaiting a fix.

        https://ani.social/post/46684

        • maxmoon@lemmy.mlOP
          link
          fedilink
          English
          arrow-up
          2
          ·
          1 year ago

          What a big no-go, if you could lock out yourself as an admin.

          That’s such a sloppy craftsmanship.