PostgreSQL—the Linux under the Databases

A close look at the PostgreSQL database, including programming interfaces and using it for WWW applications.
PostgreSQL and the WWW

Together with the Apache web server and some separately available Apache modules, PostgreSQL can be used to serve databases to the Web. One of these modules is mod_auth_pg95, which allows user authentication with PostgreSQL databases. To make it work, you have to install the web server with its own user and group ID rather than with the default “nouser” and “nogroup”. This can be done with the configuration statements User and Group in the file httpd.conf. It is then necessary to tell PostgreSQL of this account, so that it accepts connections from the web server with createuser. In the Apache configuration file access.conf, you must tell the web server where to find the authentication data. The following is a sample configuration:

<Directory /DocumentRoot/MySecrets>
Auth_PGhost localhost
Auth_PGport 5432
Auth_PGdatabase www
Auth_PGpwd_table apache_user
Auth_PGuid_field user
Auth_PGpwd_field password
AuthType Basic
AuthName My Secrets
require valid-user
</Directory>

Now, create a database named “www” with a table “apache_user” that contains the fields user and password. I suggest you do not create the table under the web server account for security reasons. If you create the table under another account and grant access with the command:

grant select on apache_user to apache;
then the server can't be used to insert new users or to delete the whole table. To populate the table, you can use the Perl script pg95passwd.pl, which ships with mod_auth_pg95. It also encrypts the passwords for you.

The second Apache module I want to introduce is PHP/FI. It is also available as a stand-alone CGI-Script, but it is more secure to compile it as a module. PHP/FI allows you to embed scripts in your web pages in a similar way to Microsoft's Active Server Pages. It can be configured to allow script access to PostgreSQL. PHP/FI uses its own script language which is easy to learn. The PostgreSQL part is based on libpq. If you know libpq, you should have no problems using PHP/FI for PostgreSQL access. I can't present all the features of PHP/FI, but I will provide an example script in Listing 2, which reads the contents of the cities table and puts it into an HTML table environment.

PHP/FI provides automatic conversion for some data types. Unlike the C interface, table values are returned in their proper format for the types integer, boolean, oid, float and real. Arrays are always returned as strings.

When you compile PHP/FI for use with PostgreSQL, defining the compiler switch MAGIC_QUOTES in php.h makes life easier. This option causes all single and double quote characters in GET and POST data from HTML forms to be automatically escaped.

The Further Development of PostgreSQL

PostgreSQL is now developed by a couple of volunteers, who coordinate their efforts via the Internet. A mailing list is used for discussion of implementation details. At the FTP site (ftp://ftp.postgresql.org/), a tar archive file with the latest sources is provided every night. The programmers use the FreeBSD utility sup to synchronize their source trees. There is also a documentation project where the man pages and the user guide are

It is planned to achieve full SQL92 compliance in the future and to constantly speed up the database operation. Version 6.2.1, which was released in October 1997 brings PostgreSQL a big step nearer to this goal. Now default values and constraints can be specified at table creation, to check newly inserted data for specific conditions. It is also possible to write trigger functions, that are executed whenever a row of table data is selected, inserted or updated. There are also new string functions like trim() substring() and position() which makes manipulating of strings very convenient. A new Server Programming Interface gives the users the aility to write server stored procedures and to implement integrity checks with triggers.

Compared to the widely used mSQL, PostgreSQL is much slower as long as only simple queries are involved. But for queries that include complex joins of multiple tables, PostgreSQL should be faster. mSQL is written as a program that gives programmers or webmasters a small and fast tool for simple database operations. In contrast to this, PostgreSQL is a full featured database system. It provides more data types, better extensibility and, with the two available query tools psql and mpsql, more user friendliness.

There are commercial database systems which are available for free as personal versions, e.g., Solid and Yard-SQL. These systems are fully SQL92 compliant and, in comparison to them, PostgreSQL's functionality is limited. The features that make PostgreSQL stand out among these competitors are that it is freely availabe and anyone who is interested can participate in its further development.

Resources

Rolf Herzog was fascinated by computers since his first steps with the early TRS-80. He came to Linux with kernel version 1.2.3, when searching for an alternative to Windows. Now he is working as a computer consultant for the Steinheim Institute in Duisburg, Germany. He is pursuing a degree in Political Science as the theory of social systems stimulates him as much as computer systems. He can be reached via e-mail at rolf@culthea.gun.de.

______________________

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix