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
  • What distro are you using? – Evan Carroll Jan 12 '18 at 4:50
  • Im using centos 7x – Magick Jan 12 '18 at 6:03
  • Did my answer work for you? – Evan Carroll Jan 12 '18 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 Jan 12 '18 at 19:49
  • Do you mean you do not have passwords? – Basil Bourque Jan 13 '18 at 18:41
1

Try running this,

sudo -u postgres dropdb myDatabase
  • 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 Jan 12 '18 at 6:40
  • So ignore the service part and just run the above command – Evan Carroll Jan 12 '18 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 .

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.