Accidentally removed super user privilege from user: postgres on postgres 10. How to get it back without re installing?

Question

I accidentally removed the super user privilege from user: postgres. I don't know how to get another super user. I stopped the postgres service and tried logging in with single-user mode:

/usr/pgsql-10/bin/postgres --single -D /var/lib/pgsql/10/data

I get this error:

"root" execution of the PostgreSQL server is not permitted. The server must be started under an unprivileged user ID to prevent possible system security compromise. See the documentation for more information on how to properly start the server.

In Linux, how can I start Postgres in single-user mode so I can fix the super user privilege?

Edit: I used this link for reference, but I'm getting the error mentioned above. Accidently removed postgres default superuser privileges - can I get it back?

Just do it as user postgres, not root, and it will work like a charm. — Laurenz Albe
– Laurenz Albe, Commented Jan 13, 2020 at 17:41
@LaurenzAlbe: Thanks for your comment. I did a variation of this, and that fixed the issue. — Tim
– Tim, Commented Jan 13, 2020 at 17:58

Tim · Accepted Answer · 2020-01-13 18:07:47Z

2

I combined solutions from these three links:

After stopping the postgresql service, I ran this command:

sudo -u postgres /usr/pgsql-10/bin/postgres --single -D /var/lib/pgsql/10/data

/usr/pgsql-10/bin/postgres is the location of my postgres binary
/var/lib/pgsql/10/data is the location of my postgresql conf

This allowed me to access single user mode, where I just typed this command:

ALTER USER postgres SUPERUSER;

So if anyone is experiencing the same scenario as me, please give the posted command a try.

answered Jan 13, 2020 at 18:07

Tim

6087 silver badges15 bronze badges

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

Comments

Jeremee · Accepted Answer · 2022-09-21 17:19:57Z

1

just adding this for postgresql 12 (Ubuntu) and possibly up

sudo -su postgres /usr/lib/postgresql/12/bin/postgres --single -D /etc/postgresql/12/main

answered Sep 21, 2022 at 17:19

Jeremee

414 bronze badges

Collectives™ on Stack Overflow

Accidentally removed super user privilege from user: postgres on postgres 10. How to get it back without re installing?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related