Re: Advantages of PostgreSQL over MySQL 5.0

On Sun, 2006-03-26 at 16:00, Robert Treat wrote:

> mysql's various user permissions / connection tables are often seen as being
> more featurefull than postgresql pg_hba system, due to its closer likeness to
> using sql, potentially simpler syntax, and ability to use remote admin tools.
> That said some people also consider the mysql system an abomination, and much
> proffer the internal user/group management you cando with sql compliant roles
> that postgresql has. I think generally it is a wash, but the one important
> point I think is that alot of mysql installation run as root, so any exploits
> mysql has are potentially root level, which is something you don't have to
> worry about in postgresql. This is more of a culture thing though than an
> actual software issue.

Well, first and foremost, most mysql installations no longer run as
root. That was once a very real problem, but the mysql_safe script does
much the same thing apache does, i.e. start a master daemon that then
starts the children under another account with limited access.

The MySQL security setup is kind of designed to be simple and easy to
use. It allows all kinds of fun things like "grant select on * to
whomever" which seems really great. Until you realize that you'll be
doing that over and over, again and again, your whole life, because, as
mentioned before, there are no groups.

With PostgreSQL, you have the harder time of having to iterate over all
the tables you want to grant access to, but since you can do this on a
group level, you only ever have to do that once. Then, you can simply
add / remove users from that group as needs be. From a database /
normalization perspective, this is far superior. But, if you're used to
the way MySQL does things, PostgreSQL seems horrific at first glance,
but you soon realize that this is a better way.

OTOH, if you're used to doing it the PostgreSQL way, MySQL seems
horrific at first glance, and never really stops seeming horrific.