3

From inside Webmin I accidently unmarked the checkbox "Can create databases?" and "Can create users?"

Stupid, I know.

But since it takes a user with superuser privileges to edit/create a user, is there a way to fix this from the linux terminal?

I know it's possible to reset a password, but I haven't been able to find out how to reset the default superuser postgres' roles/privileges. Is this even possible or do I have to do a reinstall?

Jasen
  • 11,837
  • 2
  • 30
  • 48
Dac0d3r
  • 2,176
  • 6
  • 40
  • 76
  • Do you have access to the server running postgres ? You could login the database in postgres user and alter the role with CREATEDB and CREATEUSER permissions. – andrefsp Jan 05 '15 at 10:24
  • I have full access to my dedicated server where PostgreSQL is running, however I've locked myself out.. The only user there exists is the default one, postgres, but since I was so stupid to remove create user/create database privileges from that user, I have no way to edit/add/do anything :/ Was hoping there were a file or something which I could perhaps edit with sudo in the terminal. When i try to alter the postgres user I get this "ERROR: must be superuser to alter superusers" - so I guess my questions is how to add a superuser without a superuser account. :/ – Dac0d3r Jan 05 '15 at 10:59
  • So if you have full access through the terminal nothing is stopping you to psql into postgres and change it. log in the perminal by "sudo -u postgres psql -h 127.0.0.1" and than type "ALTER USER postgres CREADEDB CREATEUSER". That should do the trick – andrefsp Jan 05 '15 at 11:04
  • 1
    @andrefsp the thing is database user postgres no longer has permission to do that, thus you need to restart in single user mode where permissions are not checked. but for reovering lost passwords that pproach works well. – Jasen Jan 05 '15 at 11:22

1 Answers1

16

there's a single-user mode where you always have superuser capabilities:

as root

service postgresql stop

as user postgres: (you may need to change the version number (here 9.1) Non .deb distros will probably have a different path here too. the first path is the postgres binary, the second one is the directory that contains postgresql.conf.

/usr/lib/postgresql/9.1/bin/postgres --single -D /etc/postgresql/9.1/main/

on the new command line

alter user postgres with superuser;

ctrl-d to exit.

as root:

service postgresql start

A similar strategy can use used to set or reset passwords etc.

Jasen
  • 11,837
  • 2
  • 30
  • 48
  • haha actually I just got it to work and when I checked back I saw your solution which is almost the exact same. Although I just did "alter user postgres SUPERUSER;" – Dac0d3r Jan 05 '15 at 11:18
  • the above should work for windows too, just substitude administrator for "root" and use the gui to start/stop the service, and `runas` to launch to postgresql.exe. – Jasen Jan 05 '15 at 11:49
  • 1
    You're a lifesaver. I had to dig around a little in Postgres.app to find the bin and conf, but this was awesome... – John O Jul 04 '15 at 17:33