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:
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.
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!