upstream/sql/README

   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
   9 able to update the white list of addresses (welcomelist_from, previously whitelist_from) without the need
  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,
  15 welcomelist_from(s) (previously whitelist_from), and required_score.
  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
  78
  79 Requirements
  80 ------------
  81
  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.
  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
  97   preference varchar(50)  # the preference (welcomelist_from (previously whitelist_from), required_score, etc.)
  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