From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Message-ID: <45B5BC96.8010305@stc.donpac.ru> Date: Tue, 23 Jan 2007 10:43:18 +0300 From: Eugene Prokopiev User-Agent: Mozilla/5.0 (X11; U; Linux i686; ru-RU; rv:1.7.2) Gecko/20040808 X-Accept-Language: ru-ru, ru MIME-Version: 1.0 To: ALT Linux sysadmin discuss References: <200701221741.31881.ashen@nsrz.ru> In-Reply-To: <200701221741.31881.ashen@nsrz.ru> Content-Type: multipart/mixed; boundary="------------010605020308020704050905" X-DSPAM-Result: Whitelisted X-DSPAM-Processed: Tue Jan 23 10:48:55 2007 X-DSPAM-Confidence: 0.9899 X-DSPAM-Probability: 0.0000 X-DSPAM-Signature: 105,45b5bde7153171743312664 X-DSPAM-Factors: 27, Received*Postfix+with, 0.01000, Received*Tue, 0.01000, С, 0.01000, To*ALT+Linux, 0.01000, Content-Type*text+plain, 0.01000, Content-Type*text+plain, 0.01000, User-Agent*ru-RU+rv, 0.01000, Received*ESMTP, 0.01000, Content-Transfer-Encoding*8bit, 0.01000, Received*for+sysadmins, 0.01000, User-Agent*X11+U, 0.01000, это, 0.01000, еще, 0.01000, еще, 0.01000, делать, 0.01000, Subject*Sysadmins, 0.01000, To*sysadmins+lists.altlinux.org, 0.01000, что, 0.01000, что, 0.01000, User-Agent*U, 0.01000, Евгений, 0.01000, чтобы, 0.01000, из, 0.01000, из, 0.01000, Content-Type*plain, 0.01000, Content-Type*plain, 0.01000, Eugene+Prokopiev, 0.01000 Subject: Re: [Sysadmins] dspam & postfix & mysql X-BeenThere: sysadmins@lists.altlinux.org X-Mailman-Version: 2.1.9rc1 Precedence: list Reply-To: ALT Linux sysadmin discuss List-Id: ALT Linux sysadmin discuss List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 23 Jan 2007 07:49:05 -0000 Archived-At: List-Archive: This is a multi-part message in MIME format. --------------010605020308020704050905 Content-Type: text/plain; charset=KOI8-R; format=flowed Content-Transfer-Encoding: quoted-printable =FB=C5=CE=C3=C5=D7 =E1=CC=C5=CB=D3=C5=CA =F7=CC=C1=C4=C9=CD=C9=D2=CF=D7=C9= =DE =D0=C9=DB=C5=D4: > =EB=D4=CF-=CE=C9=C2=D5=C4=D8 =CE=C1=D3=D4=D2=C1=C9=D7=C1=CC =D3=C1=C2=D6= ? =ED=CF=D6=C5=D4=C5 =D2=C1=D3=D3=CB=C1=DA=C1=D4=D8 =CB=C1=CB =C9 =DE=D4=CF= =D5 =D7=C1=D3 =D3=C4=C5=CC=C1=CE=CF? =F0=D2=C1=D7=C9=CC=D8=CE=D9=CA =D0=D5=D4=D8 : postfix -> dspam -> dbmail-= lmtp, =CF=C2=CD=C5=CE =D0=CF lmtp. =F7 2.0=20 =DC=D4=CF =CE=C5 =D2=C1=C2=CF=D4=C1=CC=CF, =C1 =C4=C5=CC=C1=D4=D8 testcas= e =C9 =D0=C9=D3=C1=D4=D8 =C2=C1=C7=D2=C5=D0=CF=D2=D4 =C2=D9=CC=CF =CC=C5=CE= =D8 (=D3=C5=CA=DE=C1=D3=20 =CF=D3=CF=DA=CE=C1=C0, =DE=D4=CF =C2=D9=CC =CE=C5=D0=D2=C1=D7), =D0=CF=DC= =D4=CF=CD=D5 =D3=C4=C5=CC=C1=CC : postfix -> dspam -> postfix=20 -> dbmail-lmtp. =F0=C5=D2=D7=D9=C5 =D4=D2=C9 =CB=CF=CD=D0=CF=CE=C5=CE=D4=C1= =CF=D0=C9=D3=C1=CE=D9 =D7=C5=DA=C4=C5, =D7 =D4.=DE., =CE=C1=D7=C5=D2=CE=CF= =C5, =C9=20 =D7 =D2=CF=C4=CE=CF=CA =C4=CF=CB=D5=CD=C5=CE=D4=C1=C3=C9=C9 dspam, =CE=C1= =D3=D4=D2=CF=CA=CB=C1 dbmail-lmtpd =D4=C1=CB=C1=D1 =D6=C5, =CB=C1=CB =C5=D3= =CC=C9=20 =C2=D9 dspam =C9 =CE=C5 =C2=D9=CC=CF. =EE=D5 =C5=DD=C5 =CD=CF=D6=CE=CF =D3=D7=D1=DA=C1=D4=D8 =C9=C8 =DE=C5=D2=C5= =DA pipe, =CE=CF =D7 =D3=CC=D5=DE=C1=C5 dbmail =DC=D4=C1 =CD=D9=D3=CC=D8 = =CE=C5=20 =D3=C1=CD=C1=D1 =D5=C4=C1=DE=CE=C1=D1 ;) =EF=C2=D2=C1=C2=CF=D4=CB=C1 =D3=D0=C1=CD=C1: =D0=CF=CC=D8=DA=CF=D7=C1=D4=C5= =CC=D8 =D3=CB=CC=C1=C4=D9=D7=C1=C5=D4 =D7 =D0=C1=D0=CB=D5 spam =D7=D3=C5,= =DE=D4=CF =D3=DE=C9=D4=C1=C5=D4=20 =D4=C1=CB=CF=D7=D9=CD, =D3=CB=D2=C9=D0=D4 (=D7 =C1=D4=D4=C1=DE=C5 - =D0=CF= =C4 2.2 =D0=D2=C9=C4=C5=D4=D3=D1 =D0=C5=D2=C5=D0=C9=D3=D9=D7=C1=D4=D8 =C9= =DA-=DA=C1=20 =C9=DA=CD=C5=CE=C5=CE=C9=CA =D7 =D3=D4=D2=D5=CB=D4=D5=D2=C5 =D4=C1=C2=CC=C9= =C3) =D7=D9=C7=D2=C5=C2=C1=C5=D4 =C9=DA =DC=D4=CF=CA =D0=C1=D0=CB=C9 =C9 = =CF=C2=D5=DE=C1=C5=D4 dspam.=20 =EE=D5=D6=CE=CF =C5=DD=C5 =D3=C4=C5=CC=C1=D4=D8 =C1=CE=C1=CC=CF=C7=C9=DE=CE= =D5=C0 =CF=C2=D2=C1=C2=CF=D4=CB=D5 =C4=CC=D1 =CE=C5=D3=D0=C1=CD=C1, =DE=D4= =CF=C2=D9 =D5=D7=C5=CC=C9=DE=C9=D4=D8=20 =D4=CF=DE=CE=CF=D3=D4=D8, =CE=CF =D2=D5=CB=C9 =CE=C5 =C4=CF=C8=CF=C4=D1=D4= . dspam =D7 =D3=C9=DA=C9=C6=C5 =DA=C1=C2=D2=CF=DB=C5=CE, =C4=C1=D6=C5 =D7 b= ackports =CB 2.4 =CF=CE =D7=D9=C7=CC=D1=C4=C9=D4 =DE=D5=D4=D8-=DE=D5=D4=D8= =20 =D0=D2=C9=CC=C9=DE=CE=C5=C5 - =C8=CF=D2=CF=DB=CF =C2=D9, =DE=D4=CF=C2 =C5= =C7=CF =D0=CF=C4=CF=C2=D2=C1=CC =CB=D4=CF-=CE=C9=C2=D5=C4=D8 =C2=CF=CC=C5= =C5=20 =DA=C1=C9=CE=D4=C5=D2=C5=D3=CF=D7=C1=CE=CE=D9=CA, =D4=D9 =CE=C1=D0=D2=C9=CD= =C5=D2 :) =EB=D3=D4=C1=D4=C9, =CB=C1=CB =D4=C1=CD =D3=CF =D3=C2=CF=D2=CB=CF=CA =C9 = =CF=D0=C1=CB=C5=DE=C9=D7=C1=CE=C9=C5=CD web-=CD=CF=D2=C4=D9 =CB dbmail? --=20 =F3 =D5=D7=C1=D6=C5=CE=C9=C5=CD, =F0=D2=CF=CB=CF=D0=D8=C5=D7 =E5=D7=C7=C5= =CE=C9=CA --------------010605020308020704050905 Content-Type: text/plain; name="dspam-learn.py" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="dspam-learn.py" #!/usr/bin/python # Copyright (C) 2006 Eugene Prokopiev # # This program is free software; you can redistribute it and/or # modify it under the terms of the GNU General Public License # as published by the Free Software Foundation; either # version 2 of the License, or (at your option) any later # version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. import sys, os, getopt, email.Parser, re def export(connection): parser = email.Parser.Parser() from_extractor = re.compile(r"[<>]") cursor_mailboxes = connection.cursor() cursor_messages = connection.cursor() cursor_messageblks = connection.cursor() cursor_modify = connection.cursor() sql_mailboxes = """select distinct alias from dbmail_aliases """ # alter table dbmail_messages add dspam_flag smallint not null default 0::smallint; sql_messages = """select message_idnr, internal_date from dbmail_aliases inner join dbmail_users on dbmail_aliases.deliver_to = dbmail_users.user_idnr inner join dbmail_mailboxes on dbmail_users.user_idnr = dbmail_mailboxes.owner_idnr inner join dbmail_messages on dbmail_mailboxes.mailbox_idnr = dbmail_messages.mailbox_idnr inner join dbmail_physmessage on dbmail_messages.physmessage_id = dbmail_physmessage.id where dbmail_messages.dspam_flag=0 and dbmail_messages.status < 2 and dbmail_mailboxes.name = 'Spam' and dbmail_aliases.alias = %s order by message_idnr """ sql_messageblks = """ select messageblk, is_header from dbmail_messageblks inner join dbmail_messages on dbmail_messageblks.physmessage_id = dbmail_messages.physmessage_id where message_idnr = %s order by dbmail_messageblks.messageblk_idnr """ cursor_mailboxes.execute(sql_mailboxes) for alias in cursor_mailboxes.fetchall(): print "Processing mailbox %s ..." % ("%s" % alias) count = 0 cursor_messages.execute(sql_messages, alias) for message_idnr, internal_date in cursor_messages.fetchall(): count = count + 1 dspam = os.popen(("/usr/bin/dspam --class=spam --source=error --user '%s' --mode=teft --feature=chained,noise" % alias), "w") cursor_messageblks.execute(sql_messageblks, (message_idnr,)) for messageblk, is_header in cursor_messageblks.fetchall(): if (is_header == 1): from_header = from_extractor.split(parser.parsestr(messageblk).get("From")) if (len(from_header) == 1): from_header = from_header[0] elif (len(from_header) == 3): from_header = from_header[1] else: from_header = "-" print "Processing message from %s ..." % from_header dspam.write("From "+from_header+" "+internal_date.strftime()+"\n") dspam.write(messageblk+"\n") dspam.close() print "Processed mailbox %s with %s spam messages\n" % (("%s" % alias), count) cursor_modify.execute("update dbmail_messages set dspam_flag=1 where dspam_flag=0") def usage(): usage_text = """ dspam-learn - DBMail Spam mailboxes -> mbox -> DSPAM arguments: -h|--help - show this text -t|--type - database driver type -s|--server - server where DBMail database installed -d|--database - DBMail database name -l|--login - login to database -p|--password - password to database """ print usage_text def main(argv): type = "psycopg" server = "localhost" database = "dbmail" login = "dbmail" password = "dbmailpwd" try: opts, args = getopt.getopt(argv, "ht:s:d:l:p:", ["help", "type=", "server=", "database=", "login=", "password="]) except getopt.GetoptError: usage() sys.exit(2) for opt, arg in opts: if opt in ("-h", "--help"): usage() sys.exit() elif opt in ("-t", "--type"): type = arg elif opt in ("-s", "--server"): server = arg elif opt in ("-d", "--database"): database = arg elif opt in ("-l", "--login"): login = arg elif opt in ("-p", "--password"): password = arg exec "import "+type+" as db" connection = db.connect("host="+server+" dbname="+database+" user="+login+" password="+password) connection.set_isolation_level(1); export(connection) connection.commit() if __name__ == "__main__": main(sys.argv[1:]) --------------010605020308020704050905--