Chapter 24: The PostgreSQL Architecture

Examining the Physical Database Architecture

PostgreSQL is a RDBMS derived from the Berkeley POSTGRES Project. It boasts a number of features offered by commercial databases, but is under the BSD license that allows it to be used and distributed free of charge. The POSTGRES project started out as a DARPA-sponsored initiative and reached version 4.2 before it was officially ended in 1993. Shortly after this, Postgres95 was released as an open source descendant of POSTGRES. Among other changes the query language (PostQUEL) was replaced by SQL and an interactive client (psql) was added. Postgres95 was renamed in 1996 to PostgreSQL and a versioning control was introduced to tie it into the original POSTGRES numbering.

Secure Deployment

PostgreSQL will run under a "modern Unix-compatible system" and has been tested on the following commercial and free Unix clones :

AIX RS6000

FreeBSD

HP-UX

IRIX

Linux

Mac OS X

NetBSD

OpenBSD

Solaris

Tru64 UNIX

UnixWare

PostgreSQL binaries come as standard with many Linux distributions, although many system administrators choose to obtain the latest version and compile it from source or install it from a package (such as an RPM) in order to benefit from functionality and security patches. Alternatively, Red Hat Linux has produced a PostgreSQL package containing an installer and several GUI tools including a query analyzer.

PostgreSQL natively supports Microsoft Windows from version 8.0; it installs pgAdmin III as a query tool and ships with the ODBC and JDBC drivers. Prior to this version, users of Windows systems had to choose a commercial option or "hack up" a Windows port. It is possible to compile PostgreSQL under Cygwin (the Unix emulation layer for Windows), or download a Cygwin binary. In addition, several companies have developed commercial versions:

• Software Research Associates America, Inc. (SRA) has produced a version based on the 7.3.x source tree, named PowerGres.

• dbExperts has produced a version based on the 7.4.1 tree.

• NuSphere has produced a version based on the 7.2.1 tree.

• Pervasive has produced a version based on the 8.0 tree.

Compared with other DBMS, PostgreSQL is "secure out of the box." During an install, the following security measures are taken:

• Network access is disabled by default.

• PostgreSQL will refuse to install or run under the root account on Unix systems, and under accounts who belong to the Local Administrators group on Windows.

• The Windows installer will refuse to create a database account with a username and password that's the same as the service account (that is, the user that the database is running as).

• The Windows installer will check password strength and will suggest replacing weak passwords with a randomly generated one.

Common Deployment Scenarios

PostgreSQL supports the majority of features expected of a commercial DBMS such as ACID compliance, partial roll backs, stored procedures, views, triggers, sequences, cursors , and user-defined data types.

It is typically deployed as the backend database for multi- tier applications. It is supported by common middleware packages (PHP, Java, Python, Tcl/TK, ODBC, JDBC) and has historically been a popular choice of Open Source DBMS for non-Microsoft platforms. The advent of a native Windows port is likely to increase its user base although it may take time before this version is deployed on production systems.

PostgreSQL is widely used in academic environments where support for open source software is strong. It does not have a main sponsor or commercial organization behind it that aggressively promotes it (MySQL AB). However, PostgreSQL has been linked with several high-profile deployments. Both the .org and . info domain registries run from PostgreSQL databases.

In addition, a number of open source packages make use of PostgreSQL. These include intrusion detection systems, web mail systems, and FTP servers.

Terminology

The three major components of PostgreSQL are the frontend (the client), the postmaster, and backend. Throughout the PostgreSQL chapters the terms "front-end" and "client" are used interchangeably. It is important, however, to correctly distinguish between the postmaster and the backend. The postmaster and backend have different roles but may be implemented by the same executable.

The frontend communicates initially with the postmaster, specifying the database to which it wants to connect. The postmaster verifies that access is permitted (based on a number of factors discussed later), requesting that the client authenticates if necessary. Once the frontend has authenticated, the postmaster spawns a backend process and hands off the connection.

Subsequent communication (that is, queries and their results) occurs between the frontend and the backend. The postmaster takes no further part in ordinary query/result communication except for when the frontend wishes to cancel a query currently being executed by a backend.

When the frontend wishes to disconnect it sends an appropriate packet and closes the connection without waiting for a response for the backend.