Accidently removed postgres default superuser privileges - can I get it back?

Question

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?

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
– andrefsp, Commented Jan 5, 2015 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
– Dac0d3r, Commented Jan 5, 2015 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
– andrefsp, Commented Jan 5, 2015 at 11:04
@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
– Jasen, Commented Jan 5, 2015 at 11:22

Jasen · Accepted Answer · 2018-10-25 22:33:49Z

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.

edited Oct 25, 2018 at 22:33

answered Jan 5, 2015 at 11:12

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dac0d3r Over a year ago

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;"

Jasen Over a year ago

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.

John O Over a year ago

You're a lifesaver. I had to dig around a little in Postgres.app to find the bin and conf, but this was awesome...

Collectives™ on Stack Overflow

Accidently removed postgres default superuser privileges - can I get it back?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related