Bug 1908 - ConfSourceSQL: Postgres @GLOBAL overrides user prefs
Summary: ConfSourceSQL: Postgres @GLOBAL overrides user prefs
Status: RESOLVED WORKSFORME
Alias: None
Product: Spamassassin
Classification: Unclassified
Component: Libraries (show other bugs)
Version: 2.53
Hardware: All All
: P1 normal
Target Milestone: 3.0.0
Assignee: SpamAssassin Developer Mailing List
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: 2146
  Show dependency tree
 
Reported: 2003-05-13 11:41 UTC by Daniel W. Halverson
Modified: 2004-03-14 11:17 UTC (History)
1 user (show)


Attachment Type Modified Status Actions Submitter/CLA Status
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Daniel W. Halverson 2003-05-13 11:41:07 UTC 
When using SQL prefs, under postgres @GLOBAL shows up in the result set under
the "g" section.  The result is that anyone with a username < "G" will have
their individual prefs overwritten with global prefs.
Included is a patch that resolves the problem at the expense of 2 individual
look-ups.  Should probably look up the Global prefs once and then only look up
user after that.

--- tmp/Mail-SpamAssassin-2.54/lib/Mail/SpamAssassin/ConfSourceSQL.pm	2003-02-14
12:43:08.000000000 -0600
+++ Mail-SpamAssassin-2.54/lib/Mail/SpamAssassin/ConfSourceSQL.pm	2003-05-13
13:06:14.000000000 -0500
@@ -103,15 +103,39 @@
    my $dbh = DBI->connect($dsn, $dbuser, $dbpass, {'PrintError' => 0});
 
    if($dbh) {
+# Get and set any Global values from SQL
       my $sql = "select $f_preference, $f_value  from $f_table where ". 
-        "$f_username = ".$dbh->quote($username).
-        " or $f_username = 'GLOBAL'".
+        " $f_username = 'GLOBAL'".
         " or $f_username = '\@GLOBAL' order by $f_username asc";
 
+      dbg("Getting prefs with command $sql");
       my $sth = $dbh->prepare($sql);
       if($sth) {
          my $rv  = $sth->execute();
          if($rv) {
+            dbg("retrieving Global Prefs for $username from SQL server");
+            my @row;
+            my $text = '';
+            while(@row = $sth->fetchrow_array()) {
+               $text .= "$row[0]\t$row[1]\n";
+            }
+            if($text ne '') {
+            	$main->{conf}->parse_scores_only(join('',$text));
+            }
+            $sth->finish();
+         } else { warn "SQL Error: $sql\n".$sth->errstr."\n"; }
+      } else { warn "SQL Error: " . $dbh->errstr . "\n"; }
+
+# Get and set any user specific values from SQL
+      $sql = "select $f_preference, $f_value  from $f_table where ". 
+        "$f_username = ".$dbh->quote($username).
+        " order by $f_username asc";
+
+      dbg("Getting prefs with command $sql");
+      $sth = $dbh->prepare($sql);
+      if($sth) {
+         my $rv  = $sth->execute();
+         if($rv) {
             dbg("retrieving prefs for $username from SQL server");
             my @row;
             my $text = '';
@@ -127,6 +151,7 @@
    $dbh->disconnect();
    } else { warn "SQL Error: " . DBI->errstr . "\n"; }
 }
+ 
 
 ###########################################################################
Comment 1 Justin Mason 2004-02-28 14:00:32 UTC 
upping pri since Michael now owns this stuff ;)
Comment 2 Michael Parker 2004-03-03 10:20:18 UTC 
Perhaps I am totally missing something here, but in my version of Postgres
(7.4.1) this doesn't appear to be a problem.  The @GLOBAL is sorted before the
user prefs no matter the username.

What version of Postgres are you running?  Possibly it's not doing the order by
correctly?

Here are my results from a simple query:
select username, preference, value from userpref where username = 'A@foo.bar' or
username = 'GLOBAL' or username = '@GLOBAL' order by username asc;
 username  |   preference   |   value
-----------+----------------+-----------
 @GLOBAL   | whitelist_from | me@me.com
 A@foo.bar | whitelist_from | fong
(2 rows)
Comment 3 Daniel W. Halverson 2004-03-03 11:48:05 UTC 
Subject: Re:  ConfSourceSQL: Postgres @GLOBAL overrides user prefs

bugzilla-daemon@bugzilla.spamassassin.org wrote:

>http://bugzilla.spamassassin.org/show_bug.cgi?id=1908
>
>
>
>
>
>------- Additional Comments From parkerm@pobox.com  2004-03-03 10:20 -------
>Perhaps I am totally missing something here, but in my version of Postgres
>(7.4.1) this doesn't appear to be a problem.  The @GLOBAL is sorted before the
>user prefs no matter the username.
>
>What version of Postgres are you running?  Possibly it's not doing the order by
>correctly?
>
>Here are my results from a simple query:
>select username, preference, value from userpref where username = 'A@foo.bar' or
>username = 'GLOBAL' or username = '@GLOBAL' order by username asc;
> username  |   preference   |   value
>-----------+----------------+-----------
> @GLOBAL   | whitelist_from | me@me.com
> A@foo.bar | whitelist_from | fong
>(2 rows)
>
>
>
>
>------- You are receiving this mail because: -------
>You reported the bug, or are watching the reporter.
>  
>
My results:

select username, preference, value from userpref where username = 'danh'
or username = 'znuff' or username = 'GLOBAL'
or username = '@GLOBAL' order by username asc

Results in the following set:
 username |    preference    |                   value
----------+------------------+--------------------------------------------
 danh     | subject_tag      | !!!SPAM!!!
 danh     | rewrite_subject  | 1
 danh     | whitelist_from   | symantec.com
 danh     | auto_learn       | 1
 danh     | bayes_path       | /home/danh/.spamassassin/bayes
 danh     | whitelist_from   | nagios@mike.tbc.net
 danh     | whitelist_from   | promotions@cigargold.com
 danh     | whitelist_from   | spamcop.net
 danh     | report_safe      | 1
 danh     | use_bayes        | 1
 @GLOBAL  | skip_rbl_checks  | 1
 @GLOBAL  | required_hits    | 6.0
 @GLOBAL  | subject_tag      | [Possible SPAM]
 @GLOBAL  | rewrite_subject  | 0
 @GLOBAL  | spam_level_stars | 1
 @GLOBAL  | use_terse_report | 1
 @GLOBAL  | auto_learn       | 0
 @GLOBAL  | bayes_path       | /etc/mail/spamassassin/.spamassassin/bayes
 @GLOBAL  | use_bayes        | 1
 znuff    | required_hits    | 1.0
 znuff    | rewrite_subject  | 1
 znuff    | report_header    | 0
 znuff    | defang_mime      | 0

Version: postmaster (PostgreSQL) 7.3.4

Anyone with a username before the G's will have their prefs overwritten 
by the global settings.  For example, I have autolearn enabled, but the 
global settings will override my autolearn settings.  Also my bayes path 
will be incorrect.  My patch causes the global prefs to be set first, 
then forces the local prefs to override.  The cost is an extra SQL lookup.

                Thanks for looking at it.

                            Dan
Comment 4 Michael Parker 2004-03-03 12:10:01 UTC 
Subject: Re:  ConfSourceSQL: Postgres @GLOBAL overrides user prefs

Can you please send in the table definition for the userprefs table.

I'm wondering if this is a bug that was perhaps fixed in my version of
postgres, since it's not showing up in 7.4.1, but couldn't find
anything obvious in the changes file.

Michael
Comment 5 Daniel W. Halverson 2004-03-03 12:36:17 UTC 
Subject: Re:  ConfSourceSQL: Postgres @GLOBAL overrides user prefs

horde=# \d userpref
                                      Table "public.userpref"
   Column   |          Type          |                          Modifiers
------------+------------------------+--------------------------------------------------------------
 prefid     | integer                | not null default 
nextval('public.userpref_prefid_seq'::text)
 horde_id   | character varying(255) | not null
 username   | character varying(255) | not null
 preference | character varying(30)  | not null
 value      | character varying(100) | not null
Indexes: userpref_pkey primary key btree (prefid),
         userpref_username btree (username)

Let me know if you need anything else.

bugzilla-daemon@bugzilla.spamassassin.org wrote:

>http://bugzilla.spamassassin.org/show_bug.cgi?id=1908
>
>
>
>
>
>------- Additional Comments From parkerm@pobox.com  2004-03-03 12:10 -------
>Subject: Re:  ConfSourceSQL: Postgres @GLOBAL overrides user prefs
>
>Can you please send in the table definition for the userprefs table.
>
>I'm wondering if this is a bug that was perhaps fixed in my version of
>postgres, since it's not showing up in 7.4.1, but couldn't find
>anything obvious in the changes file.
>
>Michael
>
>
>
>
>
>------- You are receiving this mail because: -------
>You reported the bug, or are watching the reporter.
>  
>
Comment 6 Michael Parker 2004-03-03 13:23:29 UTC 
Subject: Re:  ConfSourceSQL: Postgres @GLOBAL overrides user prefs

What result does:

show lc_collate;

give you?

Michael
Comment 7 Daniel W. Halverson 2004-03-03 13:54:31 UTC 
Subject: Re:  ConfSourceSQL: Postgres @GLOBAL overrides user prefs

horde=# show lc_collate;
ERROR:  Option 'lc_collate' is not recognized
horde=#

Doesn't appear to be a supported option in this version of postgress.

       Thanks

                Dan
bugzilla-daemon@bugzilla.spamassassin.org wrote:

>http://bugzilla.spamassassin.org/show_bug.cgi?id=1908
>
>
>
>
>
>------- Additional Comments From parkerm@pobox.com  2004-03-03 13:23 -------
>Subject: Re:  ConfSourceSQL: Postgres @GLOBAL overrides user prefs
>
>What result does:
>
>show lc_collate;
>
>give you?
>
>Michael
>
>
>
>
>
>------- You are receiving this mail because: -------
>You reported the bug, or are watching the reporter.
>  
>
Comment 8 Michael Parker 2004-03-03 14:04:47 UTC 
Subject: Re:  ConfSourceSQL: Postgres @GLOBAL overrides user prefs

All evidence of similar issues I found via google point to this being
a locale issue.  If you ran initdb and LC_COLLATE was set to anything
but C, which would cause the binary sort we are looking for, then it
could cause a problem with the sort.

I suggest re-running initdb with LC_COLLATE=C and see if that fixes
the problem.

Michael
Comment 9 Justin Mason 2004-03-14 20:17:21 UTC 
marking WORKSFORME -- sounds like the collate issue