CODEWITHSUNDEEP
linuxpostgresqlpostgresql-10
Tim
Tim

Reputation: 588

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

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?

Upvotes: 0

Views: 1584

Answers (2)

Jeremee
Jeremee

Reputation: 41

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

Upvotes: 1

Tim
Tim

Reputation: 588

I combined solutions from these three links:

  1. Accidently removed postgres default superuser privileges - can I get it back?
  2. Restoring the superuser account on the postgres server
  3. “root” execution of the PostgreSQL server is not permitted

After stopping the postgresql service, I ran this command: 

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

  1. /usr/pgsql-10/bin/postgres is the location of my postgres binary

  2. /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.

Upvotes: 2

Related Questions