1

Im having some problems with my postgresql server. In particular, some databases cannot be dropped.

On further inspection, I see that the postgres user is not a SuperUser.

There are two other users that are super users:

  • pgsql
  • s6cefdco

However I do have the password for these.

How can I make postgres a SuperUser again?

The operating system is CentOs.

I have tried the following:

sudo -u postgres postgres --single /var/lib/pgsql/9.6/data

Which results in 

sudo: postgres: command not found
Improve this question
5
  • What distro are you using?
    – Evan Carroll
    Commented Jan 12, 2018 at 4:50
  • Im using centos 7x
    – Magick
    Commented Jan 12, 2018 at 6:03
  • Did my answer work for you?
    – Evan Carroll
    Commented Jan 12, 2018 at 16:01
  • How did you get into this state? The existence of a superuser named "s6cefdco" makes me think that perhaps you have been hacked. If so, simply regaining control of the postgres account is probably not enough to clean up the damage.
    – jjanes
    Commented Jan 12, 2018 at 19:49
  • Do you mean you do not have passwords?
    – Basil Bourque
    Commented Jan 13, 2018 at 18:41

2 Answers 2

Reset to default
1

Try running this,

sudo -u postgres dropdb myDatabase
Improve this answer
2
  • I've tried service postgresql-9.6 stop and then dropdb mds but I'm prompted for a password. Im not sure why. I'm logged in as root, but there is no password - I login using ssh key.
    – Magick
    Commented Jan 12, 2018 at 6:40
  • So ignore the service part and just run the above command
    – Evan Carroll
    Commented Jan 12, 2018 at 16:03
1

How can I make postgres a SuperUser again?

A user is made superuser by executing:

  ALTER USER nameofuser SUPERUSER;

It must be executed by another superuser. The question says there are two accounts that are superuser and you do have their passwords, so you should be able to log in with one of them and just issue that SQL command.

If you didn't know these passwords, you could still log in bypassing the password by temporarily changing pg_hba.conf. How to do that is a frequently asked question, see for instance: remove password requirement for user postgres .

Improve this answer

Your Answer

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

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