1

Someone logged in my PostgreSQL as the user postgres, because a lack of security in my configuration files. What he did is to create a lot of superusers, and then, he revoked SUPERUSER privilege from postgres.

I've corrected the configuration files to avoid more problems, but now the harm is done and I can only log in as postgres, who is no more a superuser.

I would like to grant SUPERUSER privileges again to postgres user, but of course as I can't log in as a superuser I can't do it. Which is the best way to achieve this? I prefer to avoid reinstalling postgresql, is there other way?

I've also glanced the PostgreSQL log and I've seen the moment when the users were created, and their passwords, but these ones are encrypted through MD5 and I'm not able to desencrypt them.

What would you do in this case? Thank you!

Improve this question
0

1 Answer 1

Reset to default
3

If you edit your pg_hba.conf to allow login as one of the other superusers, then you can login as one of those and then alter role postgres superuser.

Improve this answer
3
  • Do you mean to modify the line local : all : all : md5 and wrtite local : all : all : trust, restart PostgreSQL service and log in as a superuser whose password I don't know, don't you?
    – forvas
    Jan 10, 2018 at 12:23
  • 2
    It's more secure to add a special line for one account, like local all <user> trust. Or change to trust to peer if you're able to add an OS user with the same name.
    – Colin 't Hart
    Jan 10, 2018 at 12:27
  • That was it! Thank you @Colin 't Hart you've saved my day :)
    – forvas
    Jan 10, 2018 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.