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:

1. become the “postgres” system user (either via the root user, sudo or through SSH public key authentication)
2. 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.

You might also like: Case Study – Scaling Postgres infrastructure with Performance Tuning

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!