0

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!

2

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.

  • 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 '18 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 '18 at 12:27
  • That was it! Thank you @Colin 't Hart you've saved my day :) – forvas Jan 10 '18 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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