Connecting to PostgreSQL on Linux for the first time

On Windows and OS X, PostgreSQL is configured to be accessed immediately. No further configuration is required. The user name is postgres and password is postgres.

However, on Linux, both on Ubuntu and Red Hat-based systems, additional work needs to be undertaken. This is because the default PostgreSQL configuration on both Ubuntu and Red Hat-based systems has connections turned off for the postgres user by default.

So after install of OpenGeo Suite, if you try to connect to PostgreSQL via the psql command-line utility or through pgAdmin, you will get the following connection error:

psql: FATAL:  peer authentication failed for user "postgres"

There are two steps to allow connections to PostgreSQL:

  • Set a password for the postgres user
  • Allow local connections to PostgreSQL

For more information, please see the Ubuntu documentation on PostgreSQL.

Setting a password for the postgres user

On Windows and OS X, the default password is postgres. But on Linux systems, there is no default password set.

To set the default password:

  1. Run the psql command from the postgres user account:

    sudo -u postgres psql postgres

  2. Set the password:

    \password postgres

  3. Enter a password.

  4. Close psql.

    \q

Allowing local connections

The file pg_hba.conf governs the basic constraints underlying connection to PostgreSQL. By default, these settings are very conservative. Specifically, local connections are not allowed for the postgres user.

To allow this:

  1. As a super user, open /etc/postgresql/9.3/main/pg_hba.conf (Ubuntu) or /var/lib/pgsql/9.3/data/pg_hba.conf (Red Hat) in a text editor.

  2. Scroll down to the line that describes local network. It may look like this:

    # IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident

  3. Change the ident method to trust.

    Note

    For more information on the various options, please see the PostgreSQL documentation on pg_hba.conf.

    Warning

    Using trust allows the all local users to connect to the database without a password. This is convenience, but insecure. If you want a little more security, replace trust with md5, and use the password you set in the previous section to connect.

  4. Save and close the file.

  5. Restart PostgreSQL:

    sudo service postgresql-9.3 restart

  6. To test your connection using psql, run the following command:

    psql -U postgres -W

    and enter your password when prompted. You should be able to access the psql console.

  7. To test your connection using pgAdmin, connect to the database at localhost:5432 using the user name postgres and the password supplied.

    ../../_images/firstconnect_pgadmin_ubuntu.png

    Testing the connection in pgAdmin

If you encounter errors, make sure that the postgres password is set correctly, and that the correct line was edited in pg_hba.conf.