This document has these sections:
Documents have bugs!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.
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.
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.
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.
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.
Access to PostgreSQL from perl uses DBD::Pg, which works with the DBI database abstraction module. Documentation is here
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.]
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.
Please see the Client Interfaces section of the PostgreSQL manual.
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:
frnswrth— both a PostgreSQL role
named frnswrth and a PostgreSQL database named
frnswrth are created.To tell if you have a Cubist PostgreSQL account, visit
CSE
Account Resources and look for the host class (AKA "password
class") cub-pgsql.
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:
Last modified: Thursday, 24-Oct-2013 23:39:40 PDT.
webmaint at cs.washington.edu