Decide how to handle whitespace in db entries

deoren commented 6 years ago

Test environment is Ubuntu 16.04 LTS with Postfix 3.1.0.

According to the Postfix 3.2.0 release announcement, the support was added to the postmap command, inline: and texthash: maps for spaces in the left-hand field of lookup tables. I'm not sure if MySQL and SQLite lookup tables already have this support, but when purposefully introducing spaces in the SQL template data file I'm using in development I got back inconsistent behavior.

If the left-hand side of the lookup table (the column I was searching against) had a leading space, I would not get back any results for the query, whether quoting a value not containing a space or not. If I quoted a search string that included a leading space, I got back a solid result.

If the left-hand side of the lookup table had a trailing space, the search string could either include the trailing space (quoted) or leave it out (with or without quoting of the search string) and a successful lookup would occur in both cases.

I suppose it goes without saying, but stray whitespace in the source database is problematic. The workarounds are numerous, but here are a few:

Strip leading/trailing whitespace from all fields when writing to SQLite database
- presumably this would make Postfix happy when actually using the SQLite db, but our new database would no longer reflect the source database
As part of a pre-create validation process, look for those whitespace issues
- skip those fields?
- copy the field values as-is (seems like the safest bet)
- flag rather noisily the problem (also seems like a good initial choice)
As part of the post-validation process, also look for those whitespace issues
- this could help cover potential regressions in the pre-create validation process that might allow spaces to go undetected; though admittedly we would probably share the same functions/code for validation in both places

References:

deoren commented 6 years ago

FWIW, I found an Ubuntu PPA that provides the latest Postfix packages and tried v3.3.1. I received the same response from that version as from an earlier version.

Leading whitespace

postalias queries

ubuntu@ubuntu-1604-virtual-machine:/etc/apt/preferences.d$ postalias -q example.xyz mysql:/etc/postfix/mysql/mysql-access_check_clients.cf

No response from bare search string.

ubuntu@ubuntu-1604-virtual-machine:/etc/apt/preferences.d$ postalias -q ' example.xyz' mysql:/etc/postfix/mysql/mysql-access_check_clients.cf

Response from quoted search string with explicit leading space:

reject Blocked due to history of spam

table entry

ubuntu@ubuntu-1604-virtual-machine:/etc/apt/preferences.d$ mysql -u root -e "SELECT QUOTE(client), QUOTE(action) FROM mailserver.access_check_clients WHERE client LIKE '%example.xyz%'\G"

*************************** 1. row ***************************
QUOTE(client): ' example.xyz'
QUOTE(action): 'reject Blocked due to history of spam'

Trailing whitespace

postalias queries

ubuntu@ubuntu-1604-virtual-machine:/tmp/mysql2sqlite-dev$ postalias -q example.com mysql:/etc/postfix/mysql/mysql-access_check_clients.cf

Response from bare search string without any explicit trailing spaces (even though table entry contains one):

reject Blocked due to history of spam

ubuntu@ubuntu-1604-virtual-machine:/tmp/mysql2sqlite-dev$ postalias -q 'example.com' mysql:/etc/postfix/mysql/mysql-access_check_clients.cf

Response from no trailing space in search string (even though table entry contains one):

reject Blocked due to history of spam

ubuntu@ubuntu-1604-virtual-machine:/tmp/mysql2sqlite-dev$ postalias -q 'example.com ' mysql:/etc/postfix/mysql/mysql-access_check_clients.cf

Response from explicit trailing space in search string:

reject Blocked due to history of spam

table entry

ubuntu@ubuntu-1604-virtual-machine:/tmp/mysql2sqlite-dev$ mysql -u root -e "SELECT QUOTE(client), QUOTE(action) FROM mailserver.access_check_clients WHERE client LIKE '%example.com%'\G"

*************************** 1. row ***************************
QUOTE(client): 'example.com '
QUOTE(action): 'reject Blocked due to history of spam'

deoren commented 6 years ago

related: #6

Because of our specific focus on interoperability with Postfix, those needs will supersede other concerns. I'll try to keep this in mind when evaluating handling of database fields and pre/post db validation.

deoren commented 6 years ago

I've given this some thought and will loop back later with more details, but for the initial code drop the initial workflow can just be to mirror the source database exactly. I'll create a separate PR later to better define the expected workflow.

WhyAskWhy / mysql2sqlite