No search results as admin

[Robin Windey]

Hi and first thanks for this great software!

I’m very interessted in using piler for production purposes so i decided to play around with it a little bit by using the more or less official docker image from https://bitbucket.org/jsuto/piler/src/master/docker/. All went fine and i was able to import and index some sample emails from my IMAP account via pilerimport. But when i logged into piler’s web GUI i did not see any emails independent from what i typed in for search (even an empty search did not create results). I checked the sphinx index and saw it was filled with about 4000 emails. Also manual searching inside sphinx was ok and returned results.

I investigated further and saw the the query for an empty search which was logged in /var/log/syslog was invalid:

SELECT id FROM main1,dailydelta1,delta1 
WHERE        MATCH(' ( (@sender ) | (@rcpt ) ) ') 
ORDER BY `sent` 
DESC LIMIT 0,20 OPTION max_matches=1000

I checked the query result in https://bitbucket.org/jsuto/piler/src/4db45674d38f3ef54879848b3b78a2f6b6ef98de/webui/system/database/sphinx.php#lines-42 by applying some sanity code:

   if(!$s->execute($arr)) {
         $errText = $s->errorInfo()[2];
          syslog(LOG_INFO, sprintf("error executing query: %s", $errText));
      }

Then the log gave me

error executing query: index dailydelta1,delta1,main1: syntax error, unexpected ')' near ' ) | (@rcpt ) ) '

I think the invalid query comes from https://bitbucket.org/jsuto/piler/src/4db45674d38f3ef54879848b3b78a2f6b6ef98de/webui/model/search/search.php#lines-90. If the admin users session property emails is empty, the values for @sender and @rcpt are just left empty resulting in the invalid query mentioned above.

Searching with the default auditor user (auditor@local) works as expected.

Not sure what to do here but since this took me a few hours i thought it would be nice to at least add a hint to the docs or something Also i think it would be nice to have some kind of sanity code like drafted above, this would make it easier to figure out what’s going on in the logs.

Btw. i’m using the docker image for piler 1.3.11 with the docker-compose.yml from this repo.

Let me know if i can provide further support

[Janos SUTO]

Hello Robin, thank you for the error report. Well, admin is by design shouldn’t search for emails. Such roles is reserved for admin tasks like setting rules, domains, etc. As you figured it out auditor is the ‘all seeing’ role. However, how is it possible that admin’s emails session property is empty? Anyway, I’ll check the code and see it for myself.

I’ll also see the sanity check you mentioned.

[Robin Windey]

I see. Might be worth to mention in the FAQ because if a “new” user just want’s to play around with piler a little bit this behaviour can be quite confusing

Well regarding your second question: i don’t know why the email-session variable seems to be empty. Like i mentioned i’m working on a fresh docker installation where i just imported a couple of emails from my IMAP email space. But when debugging the php code i noticed that $this->get_all_your_address("emails") (https://bitbucket.org/jsuto/piler/src/4db45674d38f3ef54879848b3b78a2f6b6ef98de/webui/model/search/search.php#lines-84) returns an empty string and $all_your_wildcard_domains in the next line is empty, too. So the filter statement from above is constructed with an empty email which leads to the invalid MATCH statement. So the current behaviour on a fresh docker installation is that the admin user won’t see any search results regardless of what he’s typing because of the silently catched SQL error.

[Janos SUTO]

You are right, and I found the issue. In model/user/user.php in the get_users_all_email_addresses( ) function you have

if($uid > 0) {
   ...
}

“Unfortunately” the built-in admin@local account has uid=0, so fix it to “if($uid >= 0) {“ and the sphinx sql query would be complete.

Also in the Administering piler page (https://www.mailpiler.org/wiki/current:administering-piler) it mentions that you shouldn’t use an admin account for searching emails (though you are allowed to open the search page just as any user), but even the search menu is not shown for admins.

[Robin Windey]

‌

“Unfortunately” the built-in admin@local account has uid=0, so fix it to “if($uid >= 0) {“ and the sphinx sql query would be complete.

Nice that you found the error. Looks like a solvable problem

Also in the Administering piler page (https://www.mailpiler.org/wiki/current:administering-piler) it mentions that you shouldn’t use an admin account for searching emails (though you are allowed to open the search page just as any user), but even the search menu is not shown for admins.

Thanks for the hint, i think i missed this part. Nevertheless it might be worth to mention a section “I cannot see any search results as admin” inside the FAQ, but this is up to you, of course.

Regarding the statement “but even the search menu is not shown for admins“ i’m not sure if i get you right but i can choose the advanced search when logged in as admin. This is a bit misleading, too.

Nevertheless if i use the auditor account everything works as expected, super fast and results are very good. So thanks again for this nice pice of open source software This issue can be closed from my side.

‌

[Janos SUTO]

Fixed uid checking in get_users_all_email_addresses() resolving #1206

Signed-off-by: Janos SUTO [e-mail]

→ <<cset 8f2a6553255b>>