]>
Commit | Line | Data |
---|---|---|
37ef5775 SI |
1 | |
2 | Loading SpamAssassin User Preferences From An SQL Database | |
3 | ---------------------------------------------------------- | |
4 | ||
5 | SpamAssassin can now load users' score files from an SQL database. The concept | |
6 | here is to have a web application (PHP/perl/ASP/etc.) that will allow users to | |
7 | be able to update their local preferences on how SpamAssassin will filter their | |
8 | e-mail. The most common use for a system like this would be for users to be | |
ae52237f | 9 | able to update the white list of addresses (welcomelist_from, previously whitelist_from) without the need |
37ef5775 SI |
10 | for them to update their $HOME/.spamassassin/user_prefs file. It is also quite |
11 | common for users listed in /etc/passwd to not have a home directory, therefore, | |
12 | the only way to have their own local settings would be through an RDBMS system. | |
13 | ||
14 | Note that this will NOT look for test rules, only local scores, | |
ae52237f | 15 | welcomelist_from(s) (previously whitelist_from), and required_score. |
37ef5775 SI |
16 | |
17 | In addition, any config options marked as Admin Only will NOT be parsed from | |
18 | SQL preferences. | |
19 | ||
20 | SpamAssassin will check the global configuration file (ie. any file matching | |
21 | /etc/mail/spamassassin/*.cf) for the following settings: | |
22 | ||
23 | user_scores_dsn DBI:driver:connection | |
24 | user_scores_sql_username dbusername | |
25 | user_scores_sql_password dbpassword | |
26 | ||
27 | The first option, user_scores_dsn, describes the data source name that will be | |
28 | used to create the connection to your SQL server. It MUST be in the format | |
29 | as listed above. <driver> should be the DBD driver that you have installed | |
30 | to access your database. <connection> can differ depending on which | |
31 | database you are using. | |
32 | ||
33 | For MySQL, connection should take the format | |
34 | ||
35 | database:hostname[:port] | |
36 | ||
37 | <database> must be the name of the database that you created to store the user | |
38 | preference table. <hostname> is the name of the host that contains the SQL | |
39 | database server. <port> is the optional port number where your database server | |
40 | is listening. | |
41 | ||
42 | user_scores_dsn DBI:mysql:spamassassin:localhost | |
43 | ||
44 | Would tell SpamAssassin to connect to the database named spamassassin using | |
45 | MySQL on the local server, and since <port> is omitted, the driver will use the | |
46 | default port number. | |
47 | ||
48 | For PostgreSQL, connection should take the following format: | |
49 | ||
50 | dbname=database;[host=hostname;[port=port;] | |
51 | ||
52 | user_scores_dsn DBI:Pg:dbname=spamassassin;host=localhost | |
53 | ||
54 | would do the same as the previous example. | |
55 | ||
56 | For additional information, please refer to the DBD::* documentation | |
57 | for your particular driver. | |
58 | ||
59 | The spamd server will not pay attention to SQL preferences by default, | |
60 | even with user_scores_dsn set in the config files. You must startup | |
61 | spamd with the proper options (ie -q or -Q, see perldoc spamd for more | |
62 | information). If the user_scores_dsn option does not exist, | |
63 | SpamAssassin will not attempt to use SQL for retrieving users' | |
64 | preferences. | |
65 | ||
66 | While scanning a message if spamd is unable to connect to the server | |
67 | specified in user_scores_dsn or an error occurs when querying the SQL | |
68 | server then spam checking will not be performed on that message. | |
69 | ||
70 | The user_scores_sql_username and user_scores_sql_password options are | |
71 | required if your database server requires a username and password to | |
72 | be sent on connect. | |
73 | ||
74 | If you have a table layout that differs from the default, please | |
75 | review the documentation for user_scores_sql_custom_query for | |
76 | information on how deal with a custom layout. | |
77 | ||
ae52237f | 78 | |
37ef5775 SI |
79 | Requirements |
80 | ------------ | |
81 | ||
ae52237f SI |
82 | In order for SpamAssassin to work with your SQL database, you must have the |
83 | perl DBI module installed, AS WELL AS the DBD driver/module for your | |
84 | specific database. For example, if using MySQL/MariaDB as your RDBMS, you | |
85 | must have the DBD::mysql or DBD::MariaDB module installed. For PostgreSQL | |
86 | use the DBD::Pg module. Check CPAN for the latest versions of DBI and your | |
87 | database driver/module. | |
37ef5775 SI |
88 | |
89 | ||
90 | Database Schema | |
91 | --------------- | |
92 | ||
93 | The database must contain a table, default name "userpref", with at | |
94 | least three fields: | |
95 | ||
96 | username varchar(100) # this is the username whose e-mail is being filtered | |
ae52237f | 97 | preference varchar(50) # the preference (welcomelist_from (previously whitelist_from), required_score, etc.) |
37ef5775 SI |
98 | value varchar(100) # the value of the named preference |
99 | ||
100 | You can add as many other fields you wish as long as the above three fields are | |
101 | contained in the table. | |
102 | ||
103 | Note that you can either use just the mail recipient's username for the | |
104 | "username" field, in which case a varchar(8) should suffice. Alternatively, | |
105 | you can use the entire recipient's email address, e.g. "user@example.com", and | |
106 | use the full varchar(100). | |
107 | ||
108 | Included is a default table that can be safely used in your own setup. To use | |
109 | the default table, you must first create a database, and a username/password | |
110 | that can access that database. | |
111 | ||
112 | If you wish to use a table that differs from the included default you | |
113 | should review the user_scores_sql_custom_query config option for | |
114 | information on making it work correctly. | |
115 | ||
116 | To create a database, if one does not already exist, see "Creating A Database" | |
117 | below. | |
118 | ||
119 | To install the table to a mysql database, use the following command: | |
120 | ||
121 | mysql -h <hostname> -u <adminusername> -p <databasename> < userpref_mysql.sql | |
122 | Enter password: <adminpassword> | |
123 | ||
124 | This will create the following table: | |
125 | ||
126 | CREATE TABLE userpref ( | |
127 | username varchar(100) default NOT NULL, | |
128 | preference varchar(50) default NOT NULL, | |
129 | value varchar(100) default NOT NULL, | |
130 | prefid int(11) NOT NULL auto_increment, | |
131 | PRIMARY KEY (prefid), | |
132 | INDEX (username) | |
133 | ) TYPE=MyISAM; | |
134 | ||
135 | For PostgreSQL, use the following command: | |
136 | ||
137 | psql -U <username> -f userpref_pg.sql <databasename> | |
138 | ||
139 | This will create a table similar to above. | |
140 | ||
141 | Once you have created the database and added the table, just add the required | |
142 | lines to your global configuration file (local.cf). Note that you must be | |
143 | running spamc/spamd in order for this to work, and the current username must | |
144 | be passed to spamd. This can be done from spamc using the following | |
145 | .procmailrc recipe: | |
146 | ||
147 | :0fw | |
148 | | /usr/local/bin/spamc -f | |
149 | ||
150 | (watch out; spamc could be installed as /usr/bin/spamc instead.) | |
151 | If you are using this from /etc/procmailrc, you must include DROPPRIVS=yes | |
152 | before spamc. An example /etc/procmailrc: | |
153 | ||
154 | DROPPRIVS=yes | |
155 | ||
156 | :0fw | |
157 | | /usr/local/bin/spamc -f | |
158 | ||
159 | Also note that spamd may need the "-q" switch so it knows to look up users in | |
160 | the SQL table instead of /etc/passwd. See "man spamd". | |
161 | ||
162 | ||
163 | Creating A Database | |
164 | ------------------- | |
165 | ||
166 | Here's the command to create a MySQL database, and user/password pair to access | |
167 | it: | |
168 | ||
169 | mysql -h <hostname> -u <adminusername> -p | |
170 | Enter password: <adminpassword> | |
171 | mysql> use mysql; | |
172 | mysql> insert into user (Host, User, Password) values('localhost','<username>', password('<password>')); | |
173 | mysql> insert into db (Host, Db, User, Select_priv) values('localhost','<databasename>','<username>','Y'); | |
174 | mysql> create database <databasename>; | |
175 | mysql> quit | |
176 | ||
177 | NOTE: If you intend to use this database for Bayes and/or AWL data you | |
178 | may need to grant additional privs (ie Insert_priv, Update_priv and | |
179 | Delete_priv). Please refer to the MySQL documentation for the proper | |
180 | method of adding these privs. | |
181 | ||
182 | To create the database for PostgreSQL, with a username/password: | |
183 | ||
184 | psql -U <adminuser> template1 | |
185 | template1=# CREATE USER <username> PASSWORD '<password>'; | |
186 | template1=# CREATE DATABASE <databasename> OWNER = <username>; | |
187 | ||
188 | ||
189 | Testing SpamAssassin/SQL | |
190 | ------------------------ | |
191 | ||
192 | To test your SQL setup, and debug any possible problems, you should start | |
193 | spamd with the -D option, which will keep spamd in the foreground, and will | |
194 | output debug message to the terminal. You should then test spamd with a | |
195 | message by calling spamc. You can use the sample-spam.txt file with the | |
196 | following command: | |
197 | ||
198 | cat sample-spam.txt | spamc | |
199 | ||
200 | Watch the debug output from spamd and look for the following debug line: | |
201 | ||
202 | retrieving prefs for <username> from SQL server | |
203 | ||
204 | If you do not see the above text, then the SQL query was not successful, and | |
205 | you should see any error messages reported. <username> should be the user | |
206 | that was passed to spamd and is usually the user executing spamc. | |
207 | ||
208 | Note that under the default configuration any prefs stored under the | |
209 | username '@GLOBAL' are used as defaults for all users. | |
210 | ||
211 | This code has only been tested using MySQL as the RDMS, but it has been written | |
212 | with the utmost simplicity using DBI, and any database driver that conforms to | |
213 | the DBI interface should work without problems. | |
214 | ||
215 | Web Interfaces | |
216 | -------------- | |
217 | ||
218 | Several web interfaces have been created for per user configurations. | |
219 | You can find more information about these on the SpamAssassin wiki: | |
220 | http://wiki.apache.org/spamassassin/WebUserInterfaces |