Installing software needed to work from home in CSE 444

These notes will walk you through installing the programs you need to work from home in CSE 444:

• Java
• PostgreSQL
• JDBC drivers (Java database connection libraries) for:
  • PostgreSQL
  • SQL Server

We assume here that you're using Windows. If you're using Mac OS X or Linux, please help us out by contributing notes for your OS.

Please remember that we only support CSE Instructional Lab-supported computers. If you are having trouble getting your assignment running at home, please try running it on a Lab machine.

General notes

• You will need to access both a local PostgreSQL install and SQL Server on IISQLSRV for project 2. Please see how to tunnel SQL Server using SSH.
• Please download the CSE 444 shell launcher script. You might need to modify the hard-coded paths in the script to use it on a non-Lab machine.
• You must be running as an Administrator to perform many of the tasks described here. You may need to choose the "Run as Administrator" option (right-click the program or shortcut, then click "Run as Adminstrator").
• If you're running a 64-bit version of Windows, you need to change C:\Program Files to C:\Program Files (x86) in the Installing PostgreSQL and Adapting the PostgreSQL instructions sections.

Installing Java

Download and install the Java SDK 6, update 17 from Sun.

Installing PostgreSQL and its JDBC driver

Follow these steps:

1. Download PostgreSQL 8.4.4 for Windows.
2. Install it with the default options. The installer will ask you to create a password for the "database superuser and service account." Remember this password; you may need it later.
3. Then, download the PostgreSQL JDBC driver and save it as C:\Program Files\psqlJDBC\postgresql-8.4-701.jdbc4.jar .
4. To check that you installed the programs correctly, verify that the files C:\Program Files\PostgreSQL\8.4\bin\psql.exe and C:\Program Files\psqlJDBC\postgresql-8.4-701.jdbc4.jar exist.

Installing the JDBC drivers for SQL Server

To install the SQL Server JDBC driver, download it from here, and save it as C:\Program Files\Microsoft SQL Server 2008 JDBC Driver\sqljdbc4.jar .

Adapting the PostgreSQL instructions

With two major exceptions, the PostgreSQL instructions for Lab machines still apply to working from home.

The first exception is that you can completely skip the sections titled "Creating a data folder" and "Starting and stopping PostgreSQL". The installer already created a data folder, so you don't need to create one yourself, and it set up PostgreSQL to start and shut down along with the computer, so you don't need to start it explicitly.

The second exception is the way you log in to the database system. On Lab machines (when following the suggested procedure for creating a data folder), you are not normally asked for a username or password when connecting to the database. This is because your PostgreSQL username is the same as your Windows username (the default username for PostgreSQL client tools and libraries), and PostgreSQL automatically trusts all connections from the local computer, so any local user can connect using any PostgreSQL username without providing a password.

By default, however, PostgreSQL installed using the Windows installer will prompt you for a username and password; you will need to use postgres for the username and the "database superuser password" you previously set for the password. There are two ways to work around this problem:

• Shut down the PostgreSQL service (net stop postgresql-8.4), then run it manually using a data folder created with initdb, as you would do on a Lab computer.
• Modify the data folder created during the PostgreSQL installation to get the same authentication behavior as one created with plain initdb. This is a two-step process:
  1. Allow locally connected users to connect without a password:
     1. Open the pg_hba.conf file in the data folder, typically located at C:\Program Files\PostgreSQL\8.4\data\pg_hba.conf.
     2. Modify the last lines of this file, which read:

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

        by changing "md5" to "trust", so they read:

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

     3. Save and close the file. (If this fails, you may need to re-open the file as an Administrator, and try again.)
     4. Tell PostgreSQL to reread its configuration files: Start->Programs->PostgreSQL 8.4->Reload configuration
  2. Create a PostgreSQL user with the same name as your Windows username:
     1. Open a PostgreSQL shell.
     2. Make sure PostgreSQL is running:

        > net start postgresql-8.4

     3. Run the following command:

        > createuser -U postgres -s "%USERNAME%"

        This command creates a PostgreSQL user with administrative privileges (-s), and the same name as your Windows username (value of the environment variable USERNAME), using the authority of the existing database superuser (-U postgres).