Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to make superuser from non superuser in PostgreSQL

How to make superuser from non superuser in PostgreSQL

From
Sunil Jadhav
Date:
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 

Re: How to make superuser from non superuser in PostgreSQL

From
"David G. Johnston"
Date:


On Thursday, September 19, 2024, Sunil Jadhav <sunilbjpatil@gmail.com> wrote:

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.

Re: How to make superuser from non superuser in PostgreSQL

From
Holger Jakobs
Date:
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


Am 19. September 2024 17:06:57 MESZ schrieb "David G. Johnston" <david.g.johnston@gmail.com>:


On Thursday, September 19, 2024, Sunil Jadhav <sunilbjpatil@gmail.com> wrote:

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.

Re: How to make superuser from non superuser in PostgreSQL

From
Muhammad Usman Khan
Date:
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
      

On Thu, 19 Sept 2024 at 18:31, Sunil Jadhav <sunilbjpatil@gmail.com> wrote:
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