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.

______________________

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState