Data Management

Why PostgreSQL is a better enterprise database than MySQL

Vincent Danen demonstrates some of the features that make PostgreSQL a standout database. While it's a bit more challenging to set up and use, he thinks it offers much more security, reliability, and data integrity than MySQL.

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.

About

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 and is a veteran Mac user.

2 comments
Curtis Michelson
Curtis Michelson

>PostgreSQL offers much more security, reliability, >and data integrity than MySQL does. You addressed (partly) the security issue with PostgreSQL vs MySQL, but what about data integrity and reliability - do you have data to back up those claims? What are you basing the comparisons on?

stephen.sandifer
stephen.sandifer

Thanks for the article. It was a good introduction to authentication methods for PostgreSQL. Authentication does not, however, make a better enterprise database alone. The title of the article was misleading. Furthermore, PostgreSQL has offloaded authentication to the underlying OS. This may or may not be more secure; it depends on the skill of the sysadmin and hardening of the OS. And when the author speaks of "granularity" I wonder if he has conflated authentication with authorization. Both are important parts of AAA but they aren't the same. Good article. Looking forward to more information in this vein.

Editor's Picks