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
begin:vcard
fn:Chris Travers
n:Travers;Chris
email;internet:chris@xxxxxxxxxxxxxxxx
tel;work:509-888-0220
tel;cell:509-630-9974
x-mozilla-html:FALSE
version:2.1
end:vcard
