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 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:

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:

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!