Subject: How to make superuser from non superuser in PostgreSQL

From: Sunil Jadhav <[email-masked]>
Date: Thu, 19 Sep 2024 19:01:15 +0530

Hello Team,

I have login with non super user in PostgreSQL instance.
How to become a super user
I tried and getting a below error
 "must be  superuser to alter superuser"
 How to resolve this?
I don't know the superuser password?

Thanks for your help in advance

Thanks
Sunil jadhav

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 19 Sep 2024 08:06:57 -0700

> I don't know the superuser password?
You will need to figure out a way to login as the (an?) existing
superuser.  Typically a password is not required since the pg_hba.conf file
usually has a “local postgres postgres peer” (or equivalent) entry which
lets the DBA become the OS postgres (or whatever user owns the service)
user and then just connect to the cluster using the postgres role.
Regardless, your ability to becomes superuser depends at this point on
being able to become the OS user that owns the PostgreSQL service so you
can modify pg_hba.conf to let you login.

David J.

---

From: Holger Jakobs <[email-masked]>
Date: Thu, 19 Sep 2024 17:50:27 +0200

If the database system is running on a machine you have access to, you might start it in single user mode and establish a new superuser role or change the password. 

Directly from SQL this isn't possible as it would make all restrictions useless. 

-- 
Holger Jakobs, Bergisch Gladbach 
Tel. +49 178 9759012

---

From: Muhammad Usman Khan <[email-masked]>
Date: Fri, 20 Sep 2024 09:18:34 +0500

Hi,
You can try the following options

   - Edit pg_hba.conf file and change authentication method to trust
   Reload postgres iand login without password
   psql -U postgres
   And then you can use alter command
   ALTER USER your_username WITH SUPERUSER;
   - If you have root access to the server than you can directly switch to
   postgres superuser
   sudo -i -u postgres
   psql
   ALTER USER your_username WITH SUPERUSER;
   - If you can't log in with sudo but have access to the PostgreSQL data
   directory, you can start PostgreSQL in single-user mode to reset the
   password
   sudo -u postgres postgres --single -D /var/lib/pgsql/data  (Change your
   path accordingly)
   ALTER USER postgres WITH PASSWORD 'new_password';
   Exit single user mode and start postgres normally