Database Access and Configuration

The server is setup on the following server. Note that you do not need to login to the database server to access the database. You can access the command line SQL utility from any machine on Computer Science remote pool -as explained below. If you are accessing the database from a program located on another machine (for example when using Rails), the connecting machine has to be on the RPI or CS network. If this is not the case, you need to setup and use VPN. It is your responsibility to make sure VPN is working properly if you are accessing the database server from an outside machine.

Postgres database server(s)

class	server host	port	notes
csc4380	csc4380.cs.rpi.edu	5432 (default)	Database class server

Postgres SQL Documentation

url	Description
http://www.postgresql.org/docs	Postgres Documentation

Database access

Two user accounts are created for each user, one has username identical to your CS username (we will call it userid). This user connects to the database using Kerberos authentication and has the same password as your CS account. The second user account is not tied to your CS account/password, we will call these usernames md5 users. The username will start with csc4380_ and has an assigned password. This password is mailed to you and cannot be changed. We recommend that you only use your md5 account for simplicity.

In addition to the user accounts, three different databases are created for each user: userid, userid_dev, userid_test. Note that if you do not supply the database name when you connect to postgresql, it will assume that the database name is the same as the connecting user.

Command line access

• ssh remote.cs.rpi.edu
• psql -h csc4380.cs.rpi.edu [-p 5432] [-f sql_input_file] [-U userid] -d database_name
  • Using a secure connection program (putty, SSH, crt, etc) connect to your favorite CS department host.
  • Note: There should be no need to login directly on the class server for database access.
  • Connecting to the database using a remote host will impose a lighter load on the server since it will not have to handle interactive user sessions.
  • Create a remote connection to the database using the psql command.
  • Port 5432 is the default server port, -p 5432 is an optional argument.
  • If you do not specify a database name, then you will be taken to the default database with your user id.
  • If you do not specify a username, then it will use your Kerberos user id which is the user id you used to connect to the remote.cs.rpi.edu machine and the accompanying password.
  • If you want to use the additional usernames we provided you, simply include: -U csc4380_userid where user id is the one you used to connect to the remote.cs.rpi.edu machine.

Common problems when connecting at the command line

Access denied or postgres will not prompt for a password

This should only happen if you simply typed psql without specifying a username, connecting with your Kerberos userid.

• Run 'kinit' after logging in to authenticate your session with the kerberos server.
• For individual commands psql --password can be used to force prompting.

psql command fails with "not found"

• Run the command "which psql" if a command path is not reported
  • Check your environment searchpath (PATH=) and verify /usr/local/bin is present.
• Connecting from a computer science host will correct the problem:
  • Campus machines outside the computer science department may not have a postgres client installed.
  • Login on remote.cs.rpi.edu and the psql command can be used.

psql: pg_krb5_init: krb5_cc_get_principal: No credentials cache found

• Your kerberos credentials have expired.
• Run the kinit command to re-authenticate

Connecting to the database from a Ruby on Rails program

• Create a RubyOnRails project and modify the file
  • ${project}/config/database.yml
  • Add your database name, your md5 user name (starts with csc4380_) and its corresponding password, the hostname. The details are provided in the Rails Tutorials. * If you are connecting from your own computer, you have to be on the CS or RPI network to be able to connect. In this case, supply your md5 username and password.

Testing Ruby on Rails with the database

Edit the config/database.yml file as follows:

development:
  adapter: postgresql
  database: <userid>
  username: <csc4380_userid>
  password: <your md5 password>
  host: csc4380.cs.rpi.edu

where

<userid>

is your CS user id and

<md5_password>

is the password mailed to you for the database.

To test that your connection to the database is working, create a new project

rails test1

Change directory to test1. Now, create a new model:

ruby script/generate model order

and edit the file named "db/migrate/001_create_orders.rb. You will see the following line:

# t.column :name, :string

this line is commented out. You can uncomment it by removing the first character, leaving:

t.column :name, :string

Now, save this file and try the following command:

rake db:migrate

If your system is setup correctly, you will get a success message. To undo the changes to the database, you can type:

rake db:migrate VERSION=0

-- SibelAdali - 31 Jan 2010