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!

There are a few “gotchas” if you are trying to setup MySQL on Linux server, Ubuntu in this case, and connect to it using MySQL Administrator from somewhere else. Here is what worked for me…

Install MySQL server and MySQL GUI Tools

• To install MySQL on a Ubuntu server use:
  sudo apt-get install mysql-server
• If you are going to be using MySQL with PHP then you will need the PHP module for MySQL 5:
  sudo apt-get install php5-mysql
• Download and install the MySQL GUI Tools Bundle for the OS you will be connecting from.
• Start MySQL Administrator. Enter any name you like for “Stored Connection:”, the IP or DNS name of your MySQL Server for “Server Host:”, “root” for the “Username:” and the root password for “Password:”. The default MySQL port is 3306.

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

The problem is that by default MySQL server is only bound to the localhost/loopback IP of 127.0.0.1. This was probably done so MySQL is locked down by default which is a good security feature.

Configure MySQL bindings

• To bind MySQL to the real IP address on your server, you will need to comment out the “bind-address = 127.0.0.1” line in the /etc/mysql/my.cnf file using your favorite text editor. 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/mysql/my.cnf
  or
  sudo vi /etc/mysql/my.cnf

  Change:

  bind-address = 127.0.0.1
  to
  #bind-address = 127.0.0.1
• Save the file and then restart MySQL Server:
  sudo /etc/init.d/mysql restart

Now MySQL Administrator will make a connection but will likely return the following error:

It still doesn’t work but we are a bit closer. MySQL associates IP addresses and/or DNS names with users. By default, root can only connect from the localhost so we will need to fix this.

Grant users remote access

• From your MySQL server enter the following to start up the command line MySQL client:
  mysql -u root -p

  When prompted, enter your root password and then you should see an “mysql>” prompt.

• Now we need to grant root the ability to connect from the host you want to use MySQL Administrator on. You can do this by DNS or by IP address with ‘%’ as a wildcard. I like to do it by IP so I would use ‘192.168.%.%’. This will allow me to connect from any computer with an IP that starts with 192.168 which is the typical default private IP range used by most broadband routers that geeks have in their homes. Here is an example (replace ‘yourpassword’ with your password):
  mysql> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’192.168.%.%’ IDENTIFIED BY ‘yourpassword’ WITH GRANT OPTION;

  The GRANT statement is granting all permission to the ‘root’ user connecting from the IP range or DNS name you have defined. If you wanted to do this by a DNS name you might do something like:

  mysql> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%.example.com’ IDENTIFIED BY ‘yourpassword’ WITH GRANT OPTION;
• Now if everything is configured correctly you should be able to connect using MySQL Administrator.

All this is well and good but usually it is bad practice to do everything with your root 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.

• Assuming you are still in the MysQL command line client on your server, here is how you would add a user named ‘web_user’ with a password of ‘yourpassword’ with enough privileges to do nearly everything:
  GRANT ALL PRIVILEGES ON *.* TO ‘web_user’@’192.168.%.%’ IDENTIFIED BY ‘yourpassword’;

  Note the IP range. This is set so that you can connect via this user from MySQL Administrator running on your remote host if you like. Depending on what you are going to do with this user, you may want to restrict the permissions further to a specific database or even specific tables. You can also restrict the user to only read. It is best practice to only give your SQL user the minimum permissions needed to the job.

Create your first database and table

Now that you are able to connect with MySQL Administrator you can close out of the command client on the server (“\q” -> Enter) and use MySQL Administrator to create your first database and table.

• In MySQL Administrator click on “Catalogs” in the menu on the left. This should reveal the default internal databases that MySQL uses for itself. To create your own database right click on the empty area where the current databases are showing and choose “Create New Schema”. Type in the name for your new database and press enter and you should now see your database below the others.
• To create a table, right click on your newly create database/schema/catalog (or whatever it is called) and click “Create New Table”. From there you can enter a table name, columns, etc. On the “Table Options” tab there is a “Storage Engine” option. MySQL supports several different storage engines that are often trade offs between data integrity and performance. The two engines that are probably most well known are MyISAM and InnoDB. MyISAM sacrifices data integrity features like foreign key constraints for performance. InnoDB sacrifices performance for data integrity features such as FK constraints and ACID compliant transactions. You will need to decide which is best for your application.

Well that’s it for this post. I hope someone finds it useful.