CSL PostgreSQL Database Service
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
. Here's how to connect with a bunch of different programs:
psql (Command Line Interface)
/s/postgresql/bin/psql -h postgres.cs.wisc.edu -p 5432 <database name>
S:\postgresql\bin\psql -h postgres.cs.wisc.edu -p 5432 <database name>
Please see the
page for psql for more information.
$dbh = DBI->connect("dbi:Pg:dbname=<database name>;host=postgres.cs.wisc.edu;port=5432");
Please see the
pages for DBI and DBD::Pg for more information.
$dbh = pg_connect("host=postgres.cs.wisc.edu dbname=<database> port=5432");
from pyPgSQL import PgSQL
db=PgSQL.Connection("host='postgres.cs.wisc.edu' port='5432' dbname='<database>'")
For More information
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
using Java and the JDBC.
Collaboration and Sharing the Database
By default, your database will have public usage rights on the default schema
, 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
the appropriate access to others. Please research the
commands on the PostgreSQL website
. For instance, if you would like to give a web script (running as
) 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
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
(read) access to your tables and
access to your databases and schemas to
(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.
There are no official disk space or bandwidth quotas on
. 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
, 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
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 email@example.com
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.
The CSL is now offering support for users with password-based authentication on
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
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 firstname.lastname@example.org
. 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