Vincent Danen demonstrates some of the features that make PostgreSQL a standout database, despite being a bit more challenging to set up and use.

By Vincent Danen, Special to ZDNet Asia  (Monday, February 22, 2010 11:25 AM)

When it comes to open source databases, MySQL gets the lion's share of attention. MySQL is an easy-to-use database, and a lot of open source Web applications are geared towards it.

The other primary open source database is PostgreSQL which, while widely known, doesn't have the same mindshare that MySQL has obtained. This is unfortunate, because out of the two, PostgreSQL offers much more security, reliability and data integrity than MySQL does.

This does, however, come at a cost. PostgreSQL is a little more challenging to set up and use; it leverages privileges and security of the underlying operating system as well as roles and privileges provided within the database. This can make PostgreSQL more difficult to use if you are unaware of these issues.

Once you are aware of them, using PostgreSQL is just as easy as using MySQL.

Similar to MySQL, PostgreSQL operates on the principle that certain users have certain types of access to data. In PostgreSQL, these are called "roles" and can be created or managed using CREATE ROLE, ALTER ROLE, and DROP ROLE.

Unlike MySQL, these can also be mapped and tied to system users, which means it can leverage different forms of system authentication: ident server authentication, LDAP server authentication, PAM, and Kerberos.

For local connections, you can also use filesystem permissions by changing who can access the UNIX domain socket, and where it is located.

The meat of the access controls in PostgreSQL is in the pg_hba.conf. For ident authentication, the pg_ident.conf is used as well; this is used to map database users to local users. Assume that user "joe" is allowed to access the database as PostgreSQL users "joe" and "ecommerce". The pg_hba.conf file would contain:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

local   all         all                               ident map=esite

host    all         all         127.0.0.1/32          ident map=esite

And pg_ident.conf would contain:

# MAPNAME     SYSTEM-USERNAME    PG-USERNAME

esite         joe                joe

esite         joe                ecommerce

esite         postgres           joe

This allows the system user "joe" to access the database as either "joe" or "ecommerce". It also allows the system "postgres" user to connect to the database as "joe". It also enforces the map type for the ident method with the name "esite", as defined in pg_ident.conf. What this means is that on the local type (UNIX domain socket) and on the local TCP/IP address (127.0.0.1), only joe and postgres can connect to the database. No other user has privileges to do so.

The ident method is a nice way to control which local users can connect to which database. This method only works for localhost (TCP/IP or UNIX domain socket) connections; it does not work for remote connections.

While this may seem a little confusing to those coming from MySQL, there is real desire for databases to have this kind of granular authentication mechanism. MySQL only supports authentication based on login credentials; these credentials are stored and managed in the database itself.

PostgreSQL on the other hand, can also allow this type of authentication, using the password mechanism. Beyond that, it can allow password-less authentication (trust), the ident mechanism as discussed, PAM (which allows for a lot of interesting authentication scenarios), and finally both LDAP and Kerberos.

Kerberos support is something that has been desired for MySQL for years (in fact, bug #6733, opened November 2004, in the MySQL bug database is a feature request for Kerberos support). Kerberos support and LDAP directories for password storage are very much at the heart of many companies' preferences, which makes PostgreSQL a compelling database to use.

There are many more features that make PostgreSQL well-suited to the enterprise. Security is huge, but PostgreSQL's support and focus on data integrity, granular access controls, ACID compliance, and other core focuses, really explain why PostgreSQL is so highly favoured amongst many database administrators.

Vincent Danen works on the Red Hat Security Response Team and lives in Canada. He has been writing about and developing on Linux for over 10 years.