CentOS - Jun 2006 - Mysql login-problem after recent 4.1.20 upgrade (CentOS4.3, x86_64)

Hi all,

I've upgraded my mysql-installation from the previous 4.1.12 version to the 
new 4.1.20 version, and I've found that a daemon I'm using suddenly
can't
log into the database anymore.

I've verified the problem with the mysql command;
[root at sciream ~]# mysql -upostfix -p mysql
Enter password:
ERROR 1045 (28000): Access denied for user 'postfix'@'localhost'
(using
password: YES)
[root at sciream ~]#

I've changed the users password in the mysql database to make sure the 
encrypted version is compatible with the current server, but to no avail

Rolling back to the 4.1.12 version fixed it for me (if not, I'd still be 
sweating about this ;-), but I hope this mail comes in time to assist others.

# rpm -Uvh --oldpackage mysql-4.1.12-3.RHEL4.1.* \
mysql-server-4.1.12-3.RHEL4.1.x86_64.rpm \
mysql-devel-4.1.12-3.RHEL4.1.x86_64.rpm

fixed it again.

This was with a current CentOS 4.3 install, running in x86_64 mode. Can 
anyone confirm this?

I'll see if I can do this on i386 as well.

Yours,
-S
-- 
Simen Thoresen, Dolphin ICS

Simen Thoresen wrote:
> Hi all,
> 
> I've upgraded my mysql-installation from the previous 4.1.12 version to
> the new 4.1.20 version, and I've found that a daemon I'm using
suddenly
> can't log into the database anymore.
I think I'll append this one myself (I'm on digest, so I have not yet
seen
any outraged refutations of this error-report yet ;-) )

There is an issue here, but it is less critical than I made it out to be. I 
have performed the upgrade, and both mysql and the service I use if for work 
well on mysql 4.1.20 as shipped in the CentOS update.

The daemon I ran into this issue with (Policyd, a Postfix greylisting 
filter) specifies to create an mysql user with

     GRANT ALL ON policyd.* TO postfix at 127.0.0.1 IDENTIFIED by
'p0stf1x';

Policyd then connects to mysql on '127.0.0.1'.

I can't speculate on why, but the database contained a '%' instead
of
'localhost'. I've learned that % as the hostname is matched in mysql
by
anything /but/ 'localhost'.

This means that functionality up until the upgrade relied on a 'bug', or
at
least a mis-configuration.
Further, the updated version seems to have changed this behaviour, and 
suddenly enforced the '% is everything but localhost' mysqlism, or
suddenly
started treating '127.0.0.1' as matching 'localhost' instead of
'%'. When
googling, I found references to mysql being picky on which of localhost and 
localhost.localdomain actually should match 'localhost', and I've
verified
that the failing command below may succeed with added '-h 
localhost.localdomain' or '-h 127.0.0.1' or similar (I'm a bit
hazy on the
details there).

> I've verified the problem with the mysql command;
> [root at sciream ~]# mysql -upostfix -p mysql
> Enter password:
> ERROR 1045 (28000): Access denied for user
'postfix'@'localhost' (using
> password: YES)
> [root at sciream ~]#
In short,

The mysql manual is correct. A better way of setting the above account up 
would be
     GRANT ALL ON policyd.* TO 'postfix'@'localhost' IDENTIFIED
by 'p0stf1x';
     GRANT ALL ON policyd.* TO 'postfix'@'%' IDENTIFIED by
'p0stf1x';

...as using '127.0.0.1' or only one of the above is not always unique 
between mysql builds.

Please feel free to correct me if I have mis-learned something from this 
experience, and sorry about the bother.

Yours,
-S
-- 
Simen Thoresen, Dolphin ICS