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!