Setting up PostgreSQL on Linux and connecting using pgAdmin III

In my previous post I covered setting up/installing MySQL and connecting via MySQL Administrator from a remote host. In this post I will talk about the PostgreSQL equivalent.

As a side note, any database system should always be behind a firewall of some kind. Putting up a database server on a public IP using the default port (5432 for PostgreSQL and 3306 for MySQL) is begging for trouble. Most web applications should only require revealing the web server to the world and even then only ports 80 and 443 (for SSL). Ideally database servers should never see the light of a port scanner.

Install PostgreSQL server and pgAdmin III

  • To install PostgreSQL on a Ubuntu server use:
    sudo apt-get install postgresql
  • If you are going to be using PostgreSQL with PHP then you will need the PHP module for PostgreSQL:
    sudo apt-get install php5-pgsql
  • Download and install the pgAdmin III Client for the OS you will be connecting from.
  • Start pgAdmin III. Click on the plugPostgreSQL Connect Plugin the upper left hand corner to pull up the “New Server Registration” window. Enter any name you like for “Name”, the IP or DNS name of your PostgreSQL Server for “Host”, “postgres” for the “Username” and nothing for the password.

If you try to connect from a remote host now without configuring your PostgreSQL server bindings you will likely see something similar to the following error:

Server doesn’t listen

The server doesn’t accept connections: the connection library reports

could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "192.168.1.100" and accepting TCP/IP connections on port 5432?

The problem is that by default PostgreSQL does not listen on or bind to any IP addresses (not even localhost). Like MySQL, this was probably done so PostgreSQL is locked down by default which is a good security feature.

Configure PostgreSQL Connection Settings

  • To configure PostgreSQL to listen on all IP addresses on your server, you will need to modify the “#listen_addresses” line in the /etc/postgresql/8.3/main/postgresql.conf file using your favorite text editor (also look for it in /var/lib/postgresql/data or /var/lib/pgsql/data if you are not using Ubuntu). This file is usually locked down so only someone with root can write to it. To work around this use “sudo” before your text editor command:
    sudo nano /etc/postgresql/8.3/main/postgresql.conf
    or
    sudo vi /etc/postgresql/8.3/main/postgresql.conf

    Change:

    #listen_addresses = ‘localhost’
    to
    listen_addresses = ‘*’
  • Save the file and then restart PostgreSQL Server using whichever of the following methods that works on your distro:
    sudo /etc/init.d/postgresql-8.3 restart
    or
    sudo /sbin/service postgresql restart
    or
    sudo /etc/rc.d/init.d/postgresql restart
    or
    /etc/init.d/postgresql restart

Now pgAdmin III will make a connection but will likely return the following error:

Access to database denied
The server doesn’t grant access to the database: the server reports
FATAL: no pg_hba.conf entry for host "192.168.1.100", user "postgres", database "postgres", SSL off

It still doesn’t work but we are a bit closer. PostgreSQL checks that users are connecting from allowable IP addresses or IP address ranges. By default, the “postgres” default user can only connect locally so we will need to fix this.

Grant users remote access

  • There should be a pg_hba.conf file in the same directory as the postgresql.conf file you edited previously. Open this file in your favorite text editor using sudo:
    sudo nano /etc/postgresql/8.3/main/pg_hba.conf

    Path names vary between Linux distributions so this may take a little hunting.

  • In this example we would like to grant all users access from the 192.168.0.0 subnet so we will add the following line:
    host all all 192.168.0.0/16 md5

    Note that the IP subnet is specified in CIDR notation. 192.168.0.0/16 specifies that any host with an IP address that starts with 192.168 will have access.

  • Before we can connect remotely we will need to be able to connect locally to set a password for the “postgres” user. Change the following line:
    local all postgres md5 sameuser

    To:

    local all postgres trust
  • Comment the following two lines with a “#” character:
    #local all all ident sameuser
    #host all all 127.0.0.1/32 md5
  • Save the file and restart PostgreSQL using one of the methods described above.
  • Use the following command to start the PostgreSQL command line client on the server. Note that psql may also be “postgres” or “pgsql” depending on your distribution/installation:
    psql -U postgres -d template1
  • Now set a new password for the “postgres” account using the server command line client:
    template1=# ALTER USER postgres with encrypted password ‘yourpassword’;
  • Now try connecting to your server using pgAdmin III from your remote host. Use “postgres” for the username and whatever password you chose for the password. If all went well you should be able to connect. This will give you a tree hierarchy menu on the left that allows you to drill down to your databases, groups, login roles, etc.

Usually it is bad practice to do everything with your default account so you might want to create another user. This is particularly the case if you are going to use it for connecting from PHP. In PostgreSQL, the concept of a user translates to a “Login Role”.

  • From the pgAdmin III client, right click on “Login Roles” and choose “New Login Roles…”. You should now see the “New Login Role…” dialog.
  • Enter a “Role name” (I.e. web_user), a password (twice), check applicable role privileges, and click “OK”. In this case, I put a check next to “Can create database objects” so I could use this account to create databases and tables.
  • You should now have new user. You can test this user by disconnecting from the server in pgAdmin III and then reconnecting using the credentials for your newly created account.

Create your first database and table

Now you can use pgAdmin III to create your first database and table.

  • With your server tree open in pgAdmin III right click on “Databases” and choose “New Database…”. Enter a name for your database and click “OK”. You should now see your new database nested under “Databases”.
  • Your databases tables are a bit nested in the pgAdmin III hierarchy. To get there, double click your database, “Schemas”, “public” and then you should see “Tables”.
  • Right click on “Tables” and choose “New Tables…”. Enter your table name on the “New Table…” dialog and then click OK to create your first table.

That’s it for this post. Good luck!

3 thoughts on “Setting up PostgreSQL on Linux and connecting using pgAdmin III”

  1. Thanks a lot! I was having problems connecting using pgadmin3 and your tutorial showed me how to setup the pg_hba.conf file. All is well now!

Leave a Reply

Your email address will not be published. Required fields are marked *