Re: Advantages of PostgreSQL over MySQL 5.0

Scott Marlowe wrote:

>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.
>>
>>
>
>
>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.
>
>
And their idea of host-based authentication leads to some very odd
issues occasionally.  If Joe logs in from computer1.mydomain make him
use this password, but if he logs in from computer2.mydomain, make him
use that password and give him different permissions....

 From the MySQL Manual:
"A MySQL account is defined in terms of a username and the client host
or hosts from which the user can connect to the server. The account also
has a password."

Also from the manual:
" MySQL usernames can be up to a maximum of 16 characters long. This
limit is hard-coded in the MySQL servers and clients, and trying to
circumvent it by modifying the definitions of the tables in the |mysql|
database /does not work/."

In PostgreSQL, usernames are defined as a type that allows for up to 63
characters.  Evidently the fact that there is a mention in the MySQL
manual about the limit and the inability to modify the table definition
is something people have run up against, but not in PostgreSQL ;-)

Out of curiosity, how hard would it be to change the default maximum
length on the name type in PostgreSQL?  I would assume that it would be
easier than in MySQL, where both the client and the server need to be
modified.

Finally, from the MySQL manual a statement that really alarms me:
" MySQL encrypts passwords using its own algorithm."  Yet nowhere
(outside of reading the code) can I actually find out what that
algorythm is.  Way to make you feel secure.

>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.
>
>
There ought to be a simple way to create a PLPGSQL function that would
do this for you.  I just haven't been motivated to do it which gives you
an idea of how seldom the problem actually comes up.

>OTOH, if you're used to doing it the PostgreSQL way, MySQL seems
>horrific at first glance, and never really stops seeming horrific.
>
>
Well said.  I would change that to "once you are used to doing it the
PostgreSQL way...."

My main CRM app (HERMES) used to support MySQL and I wrote a few hundred
lines of PHP code to manage permissions so that I could emulate groups.

Best Wishes,
Chris Travers
Metatron Technology Consulting