PostgreSQL for Instructional Use on Cubist

Obsoleted by this page.
Basic notes on how to administer PostgreSQL for instructional use on cubist.cs.washington.edu, targetted at the instructional team.

This document has these sections:

Documents have bugs!
Please report any you find
in this document here.

Introduction

PostgreSQL is an open source client-server database package that is installed on Cubist for instructional use. Currently (writing November 2010), we are running version 8.4. Here is a local version of PostgreSQL Documentation. Key sections of interest to those administering a PostgreSQL installation are:

The PostgreSQL home page is here.

Readers of this fine document may also endure Notes on Instructional Use of Cubist.

Concepts

Users

There is the concept in PostgreSQL of roles. You can think of a PostgreSQL user as a flavor of role, and the most common flavor, so we use the terms interchangeably. PostgreSQL users are distinct from operating system users, though there is, of course, nothing that stops them from being created with similar attributes. Users have names, credentials, and varying degrees of privilege. Key per-user privileges are the rights to login, to create databases, and to create other users. (The ability to create other users is the moral equivalent of being the database superuser.) Every database is owned by some user. Our convention is to create PostgreSQL users with the same name as the corresponding Unix user whenever there is such a correspdance.

There is also the concept of the PostgreSQL superuser, who has a higher degree of privilege than other users. This user corresponds to a particular Unix user that owns the files that constitute the database installation; at our site we use the username postgres, which is the default.

Finally, there is the concept of groups- a flavor of role that consists of collections of PostgreSQL users that share a set of permissions.

Access Control

A single access control file determines on (a potentially per-database basis) what hosts, users are granted access, and which authentication mechanisms are to be used.

A PostgreSQL server offers services on one or both of a Unix and a TCP socket. Cubist does both.

Currently, PostgreSQL on Cubist is configured to use MD5 authentication on all databases. This adds up to the fact that clients must provide a username and password to connect to a database.

Per-database Permissions

Besides per-user permissions to create databases and other users, users have a set of per-database permissions— such as select, update, and insert, and delete— which are granted and revoked by the database owner or superuser.

The per-database permissions are only applied after a connection is forged— that is, a user must pass the access control barrier to connect to a database before their permissions to execute specific operations come into play.

Accessing PostgreSQL

psql

psql is a command-line client, provided with the basic PostgreSQL distribution. At our site, it's installed as /usr/bin/psql. Documentation is available in a manual page and, in the Reference Manual, here. Once connected, you can issue SQL commands to the psql command prompt.

perl

Access to PostgreSQL from perl uses DBD::Pg, which works with the DBI database abstraction module. Documentation is here

PHP

PHP is a web scripting language that works much like the familiar ASP technology: one embeds programming language constructs directly in an HTML document, using special tags to direct interpretation of the code by a web server module. At this writing, we run version 5.2 on Cubist. A key advantage of PHP over perl CGI is that it is possible to specify a persistant connection to a database, which significantly improves application performance. Documentation for PHP is available at the PHP web site. An example of using PHP to access PostgreSQL is here.

phpPgAdmin is a PHP application for administering PostgreSQL, modeled after the well-known phpMyAdmin utility that is useful for administering MySQL. We have it installed on Cubist at https://cubist.cs.washington.edu/phpPgAdmin/. [The HTTPS URL is required to keep your PostgreSQL password from being sent in the clear over the network. The SSL certificate on Cubist is signed by the University of Washington-administered "UWServicesCA." If— likely— you encounter a warning when you access phpPgAdmin, you can load the root certificate for UWServicesCA into your browser's certificate store by browsing to https://www.washington.edu/computing/ca/ and following the instructions. Total time investment: 30 seconds.]

Java

Two JDBC drivers for PostgreSQL (postgresql-9.0-801.jdbc3.jar and postgresql-9.0-801.jdbc4.jar) are installed in /usr/share/java/. Example source code is at http://cubist.cs.washington.edu/~rose/pgsql.java

Documentation is here.

C and C++ Bindings

Please see the Client Interfaces section of the PostgreSQL manual.

Administering PostgreSQL

For the most part, staff will take care of administering the PostgreSQL service. As of October 2009, PostgreSQL account management tasks are integrated into the regular account management process. Note that Cubist PostgreSQL accounts (password class cub-pgsql) are distinct from both Cubist login accounts (password class cubist) and Cubist MySQL accounts (password class cub-mysql).

Cubist PostgreSQL accounts that staff creates have the following characteristics:

username
The username of the account matches the Unix username.
password
Passwords are randomly-generated.
privileges
Accounts are created without privileges, including the ability to create databases. Therefore, staff creates a database named for the user and assigns them ownership. For example, imagine a user named frnswrth— both a PostgreSQL role named frnswrth and a PostgreSQL database named frnswrth are created.
longevity
Accounts are created for the duration of the academic term. Shortly after the end of the term, a database backup is taken and the account and database are silently dropped. Sometime later, the database backup is silently discarded. To request an extension, users should contact support before the end of the term.

To tell if you have a Cubist PostgreSQL account, visit CSE Account Resources and look for the host class (AKA "password class") cub-pgsql.

Routine Administration

Users— who may not like the initial passwords we assign to them— can change their own passwords:

  % psql
  Password: 
  Welcome to psql, the PostgreSQL interactive terminal.

  Type:  \copyright for distribution terms
	 \h for help with SQL commands
	 \? for help on internal slash commands
	 \g or terminate with semicolon to execute query
	 \q to quit

  frnswrth=> ALTER USER frnswrth PASSWORD 'rancid666';
  ALTER USER
  frnswrth=> \q

(Note that user frnswrth was able to invoke psql without command line arguments; that's because psql defaults to the user and database with the same names as the current Unix user.)

phpPgAdmin also provides a means of changing passwords, and it's accessible to users. To do so, a user would:

  1. Click on the server name in the lefthand navigation pane.
  2. Select the "Account" tab (privileged PostgreSQL users see the "Users" tab instead of the "Account" tab).
  3. Click on the "Change password" link.
  4. Enter and confirm the new password.
  5. Press "OK."

Last modified: Saturday, 26-Oct-2013 13:06:45 PDT.

webmaint at cs.washington.edu