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

2 Answers 2

1

Try running this,

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

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.