Connecting to PostgreSQL on Linux for the first time¶
This section uses the command line utility psql and optionally the graphical utility pgAdmin. These tools may not be automatically present, depending on the type of installation of OpenGeo Suite. Please see the Installation section for information on how to install these tools for your platform.
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:
Run the psql command from the postgres user account:
sudo -u postgres psql postgres
Set the password:
Enter a password.
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:
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.
Scroll down to the line that describes local socket connections. It may look like this:
local all all peer
Change the peer method to md5.
For more information on the various options, please see the PostgreSQL documentation on pg_hba.conf.
To allow connections using pgAdmin, find the line that describes local loopback connections over IPv6:
host all all ::1/128 ident
Change the ident method to md5.
Save and close the file.
sudo service postgresql restart
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.
To test your connection using pgAdmin, connect to the database at localhost:5432 using the user name postgres and the password supplied.
If you encounter errors, make sure that the postgres password is set correctly, and that the correct line was edited in pg_hba.conf, as many look alike.