/ WhatILearned

Using pgadmin and default password for postgres

Some people would think that I love using the command line. However, when it comes to working with databases, specifically creating new schemas, tables, etc. I find myself coming back to using a graphical user interface (GUI).

When you install postgresql, you usually get the default Command Line Interface psql.

It's expected that you would "know" that psql is setup with the default user postgres. Meaning the easiest way to get going with the command line is to launch it under the postgres user. In Linux environments this means the following:

sudo -u postgres psql

I created an alias in my .bashrc to overload psql so I don't forget to run it under postgres user:

##Shortcut to launch psql as postgres user
alias psql='sudo -u postgres psql'

This doesn't help you if you're trying to use the GUI pgadmin.

One of the major starting problems with using pgadmin is the fact that the user postgres does not have a password. So in pgadmin, with the New Server Registration dialog form it does not accept an empty password:

View post on imgur.com

An error popup with the message: Error connecting to the server: FATAL: Peer authentication failed for user "postgres"

This means that pgadmin was trying to login with the user postgres but no password.

Unfortunately, the only way around this is to set the password of postgres via psql.

  1. Launch postgres command line interface using sudo -u postgres psql
  2. Run the following command: ALTER USER postgres WITH PASSWORD 'NewPostgresPassword'; REMEMBER TO ADD THE SEMI-COLON
  3. Now setup a new server connection in pgadmin by clicking on File -> New Server...
  4. Make sure the user is set to postgres and the password is set to NewPostgresPassword

Sample screenshot of valid new server details

Note: You could also create a new user in psql but that's for another time. This is simply just how do you get going with as few modifications as possible.