+39 0574 159 3000
How to safely change the “postgres” user password via “psql”
One of the most recurring questions I get from users that are starting their long and prosperous journey with PostgreSQL involves connecting with the “postgres” user.
I personally use the “postgres” user just to manage global objects (roles, tablespaces, databases) and to perform regular administration duties. Therefore, my favourite way of connecting is:
- become the “postgres” system user (either via the root user, sudo or through SSH public key authentication)
- connect to the local server using “psql”
However, a lot of our customers and students love graphical interfaces (especially if you come from databases with advanced tools in this area – I on the other hand am a console guy). These tools need to connect through the network and work as a standard client application.
By default, when you create a PostgreSQL cluster, password authentication for the database superuser (“postgres”) is disabled. The simplest and safest way to add a password to the “postgres” user is to connect to the local server using “psql” (see steps #1 and #2 above), then type the “\password” meta command of psql. You will be asked to enter a password and confirm it.
postgres=# \password Enter new password: Enter it again: postgres=#
This command won’t leave any track of your password anywhere in the system or log. Simple, secure, safe.
Remember:
- use the “postgres” database user solely for administration purposes;
- never use it for your standard/user applications.
PostgreSQL allows you to define groups, roles and users and to associate privileges to each of them in a very flexible and powerful way. Welcome to Postgres!
merci pour ce post m’as été bien utile
Telling us the command to change the password via psql, would have been nice.
I’m confused, it does. \password. Do you mean how to reset the password if you have lost it? Because that’s quite different.
1- Enter this command and press enter
sudo -u postgres psql
2- You will see postgres=# in reply of command prompt write \password an press enter
3 – Command prompt will show
Enter new password:
Enter your new password here and press enter.
4- Then it will show
Enter it again:
enter your password again and press enter
Once you have followed these steps your password will be changed sucessfully