Hi, You'll find an ebuild attached for SQLgrey SQLgrey is a Postfix policy service implementing a grey-listing policy. SQLgrey is a fork of postgrey, it is written in Perl and uses DBI to access an SQL database. Grey-listing stops 50 to 90 % junk mails (spam and virus) before they reach the Postfix server mailqueues (saves bandwidth, user time and CPU time). I suggest mail-filter/sqlgrey, it depends on dev-lang/perl, dev-perl/DBI, dev-perl/net-server and dev-perl/IO-Multiplex. In fact it doesn't really depend on dev-perl/net-server but net-server lacks a dependency on dev-perl/IO-Multiplex although Net::Server::Multiplex depends on it (I'll open a separate bug for this). Lionel.
Created attachment 44162 [details] ebuild for sqlgrey-1.3.1
Typo : "doesn't really depend on dev-perl/net-server" should read : "doesn't really depend on dev-perl/IO-Multiplex"
Created attachment 44205 [details] ebuild for 1.3.2 1.3.2 is a bugfix release
latest version is now 1.3.5 (just rename the provided ebuild as sqlgrey-1.3.5.ebuild). It adds numerous enhancements and bugfixes (fix for a database unavailable related crash, support for plaintext files for whitelists on IP and FQDN, change on these files are applied on the fly, a X-Greylist header can be added, prepared statements are used for better performance, ...)
Created attachment 45696 [details] New ebuild for 1.4.0 release 1.4.0 is out and considered stable. x86 arch is now marked stable in the ebuild, other archs should be ok (pure perl script) but weren't tested.
Created attachment 51500 [details] ebuild for 1.4.7 Various cleanups, after re-reading ebuild docs. But I'm sure it would be best if a Gentoo dev would look at it...
Created attachment 51717 [details] Fix to 1.4.7 ebuild Just realised that old ebuilds didn't install documentation...
Changed summary
Created attachment 52427 [details] New ebuild with pkg_config This is a new ebuild with input from another Gentoo user. There's now a pkg_config intended to help users set up their database and their SQLgrey config file. This is for the latest development release but works with the latest stable release (1.4.8) too.
I can confirm that this ebuild works without any problem with SQLgrey 1.5.0 in Gentoo. Is there any reason why this is not in portage yet? What is holding this ebuild from beeing pushed into production? cheers SteveB
ebuild works perfectly for SQLgrey 1.5.1, SQLgrey 1.5.2, SQLgrey 1.5.3 and SQLgrey 1.5.4 (just save/rename the ebuild with the correct version number)
1.5.4 Ebuild works on my system: 2.6.9-gentoo-r12 Athlon XP
Created attachment 57408 [details] 1.5.5+ ebuild New ebuild for 1.5.5, the previous one should be used until 1.5.4. Is there anything holding back its Portage entry?
@Lionel: We would need a Gentoo developer who wants to be responsable and the maintainer for the ebuild. That's all. Technicaly I think there is no reason for holding this ebuild back. But time is shure a big issue for the Gentoo development team. They just can't take every ebuild here in Bugzilla and push it to the portage CVS. They need a developer to take the responsability for the ebuild. And you can imagine that the Gentoo developers have thousend other things to do. As long as no developer is willing to take this responsability, the ebuild will probably stay here in Bugzilla and will not be pushed to portage CVS. That's the way it works with custom Gentoo ebuilds. cheers SteveB
I modified the log output to: log_override = whitelist:1,grey:2 This is what I get when starting sqlgrey: * Shutting down SQLgrey... Use of uninitialized value in substitution (s///) at /usr/sbin/sqlgrey line 1722. Use of uninitialized value in substitution (s///) at /usr/sbin/sqlgrey line 1723. Use of uninitialized value in substitution (s///) at /usr/sbin/sqlgrey line 1722. Use of uninitialized value in substitution (s///) at /usr/sbin/sqlgrey line 1723. [ ok ] * Starting SQLgrey... Use of uninitialized value in substitution (s///) at /usr/sbin/sqlgrey line 1722. Use of uninitialized value in substitution (s///) at /usr/sbin/sqlgrey line 1723. Use of uninitialized value in substitution (s///) at /usr/sbin/sqlgrey line 1722. Use of uninitialized value in substitution (s///) at /usr/sbin/sqlgrey line 1723.
Thanks for the bug report. These warnings are harmless in your case, but there's a bug when spaces are used around "," and ":" in log_override. This is now fixed in my tree and will be in 1.5.6. As this is the dev branch, I'll delay the fix a bit until I finish the optin/optout support. If there is a need for another Gentoo developper, I'd be glad to help (in fact I already requested cvs access for the GWN French translation). I've several other interests in the mail-* ebuilds anyway. I can even test my own ebuilds on sparc64 now :-)
@Linonel: I think developers are always good. I would like as well to join the developer team. But my request send by E-Mail is still not answered. I hope to get soon a replay. Anyway... I would definatly recommend you as a Gentoo developer (if I could). Good luck. cheers SteveB
Created attachment 57802 [details] Ebuild for 1.5.6 Ebuild for version 1.5.6 of sqlgrey.
Version 1.5.6 WFM on my machine. Bug in Comment 15 has been fixed. Thanks Lionel!
SQLgrey 1.5.6 is runing here since this morning (02.05.2002) around 02:00 (after I got a notification from freshmeat.net), without any problems. The new introduced optin/optout possibility is nice. I have to take a closer look at it and maybe replace my current Postfix policy for SQLgrey with the new optin/optout mechanism. Anyway... I just wish that I would have a central place where I could maintain that kind of settings. Currently every service (anti-virus, anti-spam, greylisting, etc) has his own way and place to store information for mail users. All this starts to be a big maintenance issue. cheers SteveB
Created attachment 61384 [details] Ebuild for 1.6.0 Ebuild for 1.6.0
1.6.0 installed and working as needed.
ebuild 1.6.0 works perfectly on my system. thanks
Created attachment 61842 [details] SQLGrey Ebuild for 1.6.1 Ebuild for version 1.6.1
Created attachment 62928 [details] Ebuild for version 1.6.3
Any schedule on having this ebuild added to Portage? If no one from the net-mail herd is willing to add it, I'm going to add it myself I guess :)
Comment on attachment 62928 [details] Ebuild for version 1.6.3 Please, set correct mime type for text attachments.
Wolfram, if you're going to add it, make sure its initscript contains "provide postfix_greylist" in depend(), just like gld or postgrey. Also, be sure to add net-mail as herd into metadata.xml, in addition to you as a maintainer. Thanks for caring for this package, I don't have setup to add and reliably maintain it.
(In reply to comment #26) > Any schedule on having this ebuild added to Portage? > If no one from the net-mail herd is willing to > add it, I'm going to add it myself I guess :) Wolfram! Thank you very much for taking care of this ebuild. Thank you, thank you, thank you. Kind Regards SteveB
Created attachment 68491 [details] SQLGrey Ebuild for 1.6.6
SQLGrey 1.6.6 working perfectly on my system. Portage 2.0.51.22-r2 (default-linux/x86/2005.0, gcc-3.3.5-20050130, glibc-2.3.5-r1, 2.6.12-gentoo-r6 i686) ================================================================= System uname: 2.6.12-gentoo-r6 i686 AMD Athlon(TM) XP 2500+ Gentoo Base System version 1.6.13 dev-lang/python: 2.3.5 sys-apps/sandbox: 1.2.12 sys-devel/autoconf: 2.13, 2.59-r6 sys-devel/automake: 1.4_p6, 1.5, 1.6.3, 1.7.9-r1, 1.8.5-r3, 1.9.6 sys-devel/binutils: 2.15.92.0.2-r10 sys-devel/libtool: 1.5.18-r1 virtual/os-headers: 2.6.8.1-r1, 2.6.11-r2 ACCEPT_KEYWORDS="x86" AUTOCLEAN="yes" CBUILD="i586-pc-linux-gnu" CFLAGS="-march=athlon-xp -O3 -pipe -fomit-frame-pointer" CHOST="i586-pc-linux-gnu"
It's doing just perfect on my x86 gentoo-hardened box. I would love to see it in portage.
Just a ping regarding sqlgrey-1.6.6: Using portage sync from 2005/10/22, ran into one snag that's not directly sqlgrey's fault. Took me quite by surprise when starting the sqlgrey initscript resulted in a "Can't call method "do" on an undefined value at /usr/sbin/sqlgrey line 298." a few seconds later. It depends on DBD-SQLite if sqlite support is expected. Only problem is that this Perl build doesn't actually pull sqlite itself as a dependency. Might be worth mentioning in documentation that the sqlite dependencies need to be emerged by hand.
Without a local sqlite, I can't see any purpose for DBD-SQLite... Shouldn't this be filed as a DBD-SQLite bug?
sqlgrey-1.6.6.ebuild works for me. thank you.
had an issues installing 1.6.6 this week. It would error out with an error when trying to create the username. I changed the following and the ebuild worked. pkg_setup() { has_version dev-perl/IO-Multiplex || die "IO-Multiplex needed. Please emerge it or run g-cpan.pl IO::Multiplex" id sqlgrey 2>/dev/null || enewgroup sqlgrey id sqlgrey 2>/dev/null || enewuser sqlgrey -1 /bin/false /var/spool/sqlgrey sqlgrey } to pkg_setup() { has_version dev-perl/IO-Multiplex || die "IO-Multiplex needed. Please emerge it or run g-cpa n.pl IO::Multiplex" enewgroup ${PN} enewuser ${PN} -1 -1 /var/spool/sqlgrey ${PN} } kashani
Created attachment 76791 [details] mail-filter/sqlgrey-1.7.3.ebuild Fixed the enewuser stuff and changed the config stuff to: emerge --config ${PF}
Created attachment 77107 [details] ebuild for 1.6.7 and 1.7.3 Based on steve's ebuild, only removed the "id sqlgrey 2>/dev/null ||" checks. They aren't needed. enewuser and enewgroup from eutils.class make the proper checks themselves.
This format does not not work and fails with lots of errors. emerge --config sqlgrey-1.7.3 nms01 sqlgrey # emerge --config sqlgrey-1.7.3 Traceback (most recent call last): File "/usr/bin/emerge", line 2817, in ? pkgs = portage.db[portage.root]["vartree"].dbapi.match(myfiles[0]) File "/usr/lib/portage/pym/portage.py", line 4842, in match mymatch=match_from_list(mydep,self.cp_list(mykey,use_cache=use_cache)) File "/usr/lib/portage/pym/portage.py", line 4134, in match_from_list raise KeyError, "Specific key requires an operator (%s) (try adding an '=')" % (mydep) KeyError: "Specific key requires an operator (mail-filter/sqlgrey-1.7.3) (try adding an '=')" whereas this format works fine emerge --config sqlgrey sqlgrey also doesn't work by with Mysql 5.0 because of the whole latin1 vs utf8 thing. I dumped the schema from a Mysql 4.0 install and changed TYPE=MyISAM to TYPE=MyISAM, DEFAULT CHARACTER SET latin1. I'm not sure where that should go in the ebuild or code. Ramin
(In reply to comment #39) > sqlgrey also doesn't work by with Mysql 5.0 because of the whole latin1 vs utf8 > thing. > Well. I can't confirm this. SQLGrey 1.7.3 is runing here on MySQL 5.0.18 without any problems. BTW: This is my DB definition: -- phpMyAdmin SQL Dump -- version 2.7.0-pl2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Mar 01, 2006 at 02:28 AM -- Server version: 5.0.18 -- PHP Version: 5.0.5-pl5-gentoo SET FOREIGN_KEY_CHECKS=0; SET AUTOCOMMIT=0; START TRANSACTION; -- -- Database: `sqlgrey` -- -- -------------------------------------------------------- -- -- Table structure for table `config` -- DROP TABLE IF EXISTS `config`; CREATE TABLE IF NOT EXISTS `config` ( `parameter` varchar(255) NOT NULL, `value` varchar(255) default NULL, PRIMARY KEY (`parameter`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `connect` -- DROP TABLE IF EXISTS `connect`; CREATE TABLE IF NOT EXISTS `connect` ( `sender_name` varchar(64) NOT NULL, `sender_domain` varchar(64) NOT NULL, `src` varchar(64) NOT NULL, `rcpt` varchar(64) NOT NULL, `first_seen` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, KEY `connect_idx` (`src`,`sender_domain`,`sender_name`), KEY `connect_fseen` (`first_seen`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `domain_awl` -- DROP TABLE IF EXISTS `domain_awl`; CREATE TABLE IF NOT EXISTS `domain_awl` ( `sender_domain` varchar(255) NOT NULL, `src` varchar(39) NOT NULL, `first_seen` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `last_seen` timestamp NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`src`,`sender_domain`), KEY `domain_awl_lseen` (`last_seen`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `from_awl` -- DROP TABLE IF EXISTS `from_awl`; CREATE TABLE IF NOT EXISTS `from_awl` ( `sender_name` varchar(64) NOT NULL, `sender_domain` varchar(255) NOT NULL, `src` varchar(39) character set latin1 NOT NULL default '', `first_seen` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `last_seen` timestamp NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`src`,`sender_domain`,`sender_name`), KEY `from_awl_lseen` (`last_seen`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `optin_domain` -- DROP TABLE IF EXISTS `optin_domain`; CREATE TABLE IF NOT EXISTS `optin_domain` ( `domain` varchar(255) NOT NULL, PRIMARY KEY (`domain`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `optin_email` -- DROP TABLE IF EXISTS `optin_email`; CREATE TABLE IF NOT EXISTS `optin_email` ( `email` varchar(255) NOT NULL, PRIMARY KEY (`email`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `optout_domain` -- DROP TABLE IF EXISTS `optout_domain`; CREATE TABLE IF NOT EXISTS `optout_domain` ( `domain` varchar(255) NOT NULL, PRIMARY KEY (`domain`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `optout_email` -- DROP TABLE IF EXISTS `optout_email`; CREATE TABLE IF NOT EXISTS `optout_email` ( `email` varchar(255) NOT NULL, PRIMARY KEY (`email`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; SET FOREIGN_KEY_CHECKS=1; COMMIT;
The ebuild work for me both on x86 and amd64 using mysql use-flag. Added to the tree, with net-mail herd. Thanks all and bug me if it broke.
While sqlgrey is greylisting fine, I get this when I restart the service: shadow mail # /etc/init.d/sqlgrey restart * Shutting down SQLgrey ... Name "DBIx::DBCluster::DEBUG" used only once: possible typo at /usr/sbin/sqlgrey line 2413. Name "DBIx::DBCluster::WRITE_HOSTS_NEVER_READ" used only once: possible typo at /usr/sbin/sqlgrey line 827. Name "DBIx::DBCluster::CLUSTERS" used only once: possible typo at /usr/sbin/sqlgrey line 818. [ ok ] * Starting SQLgrey ... Name "DBIx::DBCluster::DEBUG" used only once: possible typo at /usr/sbin/sqlgrey line 2413. Name "DBIx::DBCluster::WRITE_HOSTS_NEVER_READ" used only once: possible typo at /usr/sbin/sqlgrey line 827. Name "DBIx::DBCluster::CLUSTERS" used only once: possible typo at /usr/sbin/sqlgrey line 818. [ ok ] My logs show all is good. Not sure why I get this error but it apparently isn't hurting anything... or is it? =)
My bad. Using the 1.7.3 ebuild.
well, since I don't have a clustered SQL, I commented out the offending lines from /usr/sbin/sqlgrey and the error is gone. Leaving db_cluster commented out or setting it to off had no effect (sqlgrey.conf).
I was just reviewing my mailserver config preparing to move the pg database to a new quad woodcrest with a terabyte of raid5 and 8 gigs of ram when I noticed the database schema for amavisd-new had changed from the listed example on gentoo-wiki located at http://gentoo-wiki.com/HOWTO_Email:_A_Complete_Virtual_System which I've contributed to on a few occasions. Since this server is mission critical I started looking around for an upstream published/recommended pgsql schema for sqlgrey and found none. Perhaps upstream could publish and or include database schemas for pgsql/mysql/sqlite in docs or contrib for systems admins to make use of. The sql schema offered at http://gentoo-wiki.com/HOWTO_Email:_A_Complete_Virtual_System_-_GreyListing may be unmaintained or incompatible in current and future releases. Often schema changes in cooperating mta applications can be difficult to backtrace if the sql schema isn't published by the developers. Many thanks to Lionel Bouton if he could advise where to find upstream supported sql schemas and if he could add them to docs/contrib in future releases.
(In reply to comment #45) > > [...] Perhaps upstream > could publish and or include database schemas for pgsql/mysql/sqlite in docs or > contrib for systems admins to make use of. The sql schema offered at > http://gentoo-wiki.com/HOWTO_Email:_A_Complete_Virtual_System_-_GreyListing may > be unmaintained or incompatible in current and future releases. Often schema > changes in cooperating mta applications can be difficult to backtrace if the > sql schema isn't published by the developers. Many thanks to Lionel Bouton if > he could advise where to find upstream supported sql schemas and if he could > add them to docs/contrib in future releases. > I'm so aware of database schema problems that I built database schema migrations in SQLgrey itself from the start. You shouldn't ever need to create or update a schema as SQLgrey detects the version of the schema and automatically upgrades it at startup time if it needs to. I like my softs to ask the bare minimum it needs from its users: less maintenance for them, less support to do for me...
(In reply to comment #44) > well, since I don't have a clustered SQL, I commented out the offending lines > from /usr/sbin/sqlgrey and the error is gone. Leaving db_cluster commented out > or setting it to off had no effect (sqlgrey.conf). > This is fixed in 1.7.5 and was only cosmetic: no harm done.
