When connecting to PostgreSQL on Linux for the first time many admins have questions, especially if those admins are from the MySQL world.
By default, when PostgreSQL is installed, a postgres user is also added.
If you run the command:
cat /etc/passwd
… you’ll see the postgres user.
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
The first question many ask is, “What is the default password for the user postgres?” The answer is easy… there isn’t a default password. The default authentication mode for PostgreSQL is set to ident.
cat /var/lib/pgsql/9.3/data/pg_hba.conf
… you’ll see the authentication mode is ident.
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident
What is the ident authentication method? Well, it works by taking the OS username you’re operating as, and comparing it with the allowed database username(s). There is optional username mapping.
This means that in order to connect to PostgreSQL you must be logged in as the correct OS user. In this case, I am logged into the server as root. When I try to connect to PostgreSQL:
psql
… I get the following error:
psql: FATAL: role "root" does not exist
However, if I become the default PostgreSQL user, postgres:
su - postgres
… then attempt a connection to PostgreSQL:
psql
… I get the correct, valid response!
psql (9.3.9)
Type "help" for help.
postgres=#