Package: sqlgrey
Version: 1.8.0rc2-1
Severity: important
SQLGrey's PostgreSQL database connection crashes when the sender gives
a non-UTF-8 encoded e-mail address.
We recently noticed, for a SQLGrey setup with ~2000 email accounts,
that the databqse connection spuriously restarted (once every 15
minutes, it looked quite concerning...). It stemmed from the fact that
a sender used a latin1-encoded email address which made the PgSQL
connection barf.
This is caused by the fact that, by default and following the
instructions in /usr/share/doc/sqlgrey/HOWTO.gz, the database is
created with and expects an UTF-8 encoding. For PostgreSQL, the
database should be created using encoding SQL_ASCII, which is a
special encoding in which no character set conversion is done
whatsoever.
I tried patching the SQL client side, setting the CLIENT_ENCODING to
SQL_ASCII, but it didn't work, I guess because my Perl-fu is rusty and
I lack perl-DBI knowledge. :)
You will find below how to check if the installation is affected, and
how to correct it.
------
Steps to reproduce :
python -c 'print """
EHLO cartan.example.com
MAIL FROM:nícol...@example.com
RCPT TO:nicol...@example.com
""".decode("utf-8").encode("iso-8859-15")' | nc mx.example.com 25
(notice the accent on the 'i' in the MAIL FROM)
Expected output:
450 4.7.1 <nicol...@example.com>: Recipient address rejected: Greylisted for 6
minutes
Output:
250 2.1.0 Ok
And a log entry in /var/log/mail.log:
sqlgrey: dbaccess: warning: couldn't do query:#012UPDATE from_awl SET last_seen
= NOW(), first_seen = first_seen WHERE sender_name = 'nícolasd' AND
sender_domain = 'example.com' AND src = '88.190.13':#012ERREUR: séquence
d'octets invalide pour l'encodage « UTF8 » : 0xed636f#012HINT: Cette erreur
peut aussi survenir si la séquence d'octets ne correspond pas#012au jeu de
caractères attendu par le serveur, le jeu étant contrôlé par#012«
client_encoding »., reconnecting to DB
(Yep, PgSQL server is speaking French, sorry about that! ;) )
------
How to fix the issue:
Create the database in the SQL_ASCII encoding, using 'createdb -O sqlgrey -E
SQL_ASCII sqlgrey'
For a running SQLGrey setup, you can't change database encoding on the
fly, so you have to dump the database, drop it, and recreate it:
[... disable and stop SQLGrey ...]
sudo -u postgres pg_dump -E SQL_ASCII sqlgrey > sqlgrey.dump
sudo -u postgres dropdb sqlgrey
sudo -u postgres createdb -O sqlgrey -E SQL_ASCII sqlgrey
sudo -u postgres psql -d sqlgrey -f sqlgrey.dump
[... restart and re-enable SQLGrey ...]
-- System Information:
Debian Release: wheezy/sid
APT prefers unstable
APT policy: (990, 'unstable'), (500, 'testing'), (500, 'stable'), (1,
'experimental')
Architecture: amd64 (x86_64)
Kernel: Linux 2.6.37-1-amd64 (SMP w/2 CPU cores)
Locale: LANG=fr_FR.UTF-8, LC_CTYPE=fr_FR.UTF-8 (charmap=UTF-8) (ignored: LC_ALL
set to fr_FR.UTF-8)
Shell: /bin/sh linked to /bin/dash
Versions of packages sqlgrey depends on:
ii adduser 3.112+nmu2 add and remove users and groups
ii libdate-calc-perl 6.0-2 Perl library for accessing dates
ii libdbd-mysql-perl 4.016-1 Perl5 database interface to the My
ii libdbd-pg-perl 2.17.2-1 Perl DBI driver for the PostgreSQL
ii libdbd-sqlite3-perl 1.29-3 Perl DBI driver with a self-contai
ii libnet-server-perl 0.97-1 An extensible, general perl server
ii perl 5.10.1-17 Larry Wall's Practical Extraction
Versions of packages sqlgrey recommends:
ii libdbd-pg-perl 2.17.2-1 Perl DBI driver for the PostgreSQL
ii postfix 2.7.1-1 High-performance mail transport ag
sqlgrey suggests no packages.
-- no debconf information
--
To UNSUBSCRIBE, email to debian-bugs-dist-requ...@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmas...@lists.debian.org
