CSL PostgreSQL Database Service

Overview

The CSL PostgreSQL database service lets you use databases for personal, class or research projects without having to worry about administering them. The CSL keeps the server running, upgrades it and makes daily backups.

Requesting a Database

To request a database, fill out the "Request a PostgreSQL database" form on https://www-auth.cs.wisc.edu.

Using a Database

The server is running on postgres.cs.wisc.edu on port 5432. Here's how to connect with a bunch of different programs:

psql (Command Line Interface)

On Unix:

/s/postgresql/bin/psql -h postgres.cs.wisc.edu -p 5432 <database name>

On Windows:

S:\postgresql\bin\psql -h postgres.cs.wisc.edu -p 5432 <database name>

Please see the man page for psql for more information.

Perl

#!/s/std/bin/perl

use DBI;
use CSL::Pg;
$dbh = DBI->connect("dbi:Pg:dbname=<database name>;host=postgres.cs.wisc.edu;port=5432");

Please see the man pages for DBI and DBD::Pg for more information.

PHP

<?php
$dbh = pg_connect("host=postgres.cs.wisc.edu dbname=<database> port=5432");
?>

Python

#!/s/python-2.5.2/bin/python
from pyPgSQL import PgSQL
db=PgSQL.Connection("host='postgres.cs.wisc.edu' port='5432' dbname='<database>'")

For More information

• PostgreSQL website: All sorts of PostgreSQL-related information.
• Comprehensive Perl Archive Network: Information on the DBI and DBD::Pg Perl modules.
• PHP PostgreSQL pages: Information on using PHP with your database.
• pyPgSQL project page: Using the pyPgSQL Python module

Notes about Java

JDBC support is now included with the CSL's PostgreSQL database server, with one primary caveat.

JDBC support is only available for password-based users. The JDBC drivers do not currently support Kerberos authentication. The upshot is that you cannot connect "as yourself" to the database server using a Java/JDBC program. However, this does allow you to connect and use the database server with Java.

Please see the page on password-based users for more information and a sample program on how to connect to and query postgres.cs.wisc.edu using Java and the JDBC.

Collaboration and Sharing the Database

By default, your database will have public usage rights on the default schema public, but if a user is not granted the proper access to a particular table, he/she/it may not access it. Likewise, if you create a new schema (a way of organizing and keeping tables...a schema is like a directory and tables are like files), only you will initially have access rights. In order to share your tables, schemas, and other database objects, you will have to GRANT the appropriate access to others. Please research the GRANT and REVOKE commands on the PostgreSQL website. For instance, if you would like to give a web script (running as www-user) all access to read and write a specific table in your database, run the following after connecting to the database:

• GRANT ALL ON [table] TO "www-user"; (The quotes are important)

Please keep in mind that anyone running a script as www-user will be able to access your database. Keep these caveats in mind when managing your database. For these reasons, we highly recommend that you do not give anything more than SELECT (read) access to your tables and USAGE access to your databases and schemas to PUBLIC (which is a PostgreSQL alias for "any user who can connect to your database"). Access should only be given to those who need it.

The scope of the PostgreSQL authorization and authentication scheme is too grand for this document. Please, as always, consult the PostgreSQL website for the most accurate, up to date information.

Database Quotas

There are no official disk space or bandwidth quotas on postgres.cs.wisc.edu. However, please remember that many people and projects use this server. Therefore, please try to keep your database size and I/O bandwidth usage low (about 10 GB of raw data is a practical limit). If you have any specific questions or if you need to run intensive queries on a very large database, please contact the lab and we will do our best to resolve the issue. If you are creating problems for other users on postgres.cs.wisc.edu, the lab will contact you and work with you to resolve the issue.

Additional features in PostgreSQL

PostgreSQL is a very feature-rich database system, even in its base set. Included in the distribution are a number of useful, though non-default and occasionally unverified modules and server-side languages which can be activated on a per-database basis. For security and space considerations, we only activate the built-in PostgreSQL server-side language plpgsql by default. All other languages and modules are not activated by default, and must often be activated by an administrator.

If you need or would like one of these modules or languages in your database, please mail lab@cs.wisc.edu specifying the database, the module, and your reason for needing or wanting said module. Requests will be handled on a case-by-case basis and we will do what we can to get it installed in your database.

Password-Based Users

The CSL is now offering support for users with password-based authentication on postgres.cs.wisc.edu.

For more information and specifics on CSL policy, please read this page.

To request a password-based user, please fill out the "Request a Password-Based PostgreSQL User" webform on https://www-auth.cs.wisc.edu/forms.

PostgreSQL Servers

Users may not install PostgreSQL database servers on the CSL network. Research projects that have special needs for a private PostgreSQL database server should contact the CSL by sending email to lab@cs.wisc.edu. We will work with you to install and maintain a server on an appropriate computer.

For More Information

Unfortunately, the scope of PostgreSQL is far too great for this document. As always, if you have questions, if you want to learn something, or if you just want to see official documentation, please consult the PostgreSQL website.