1. Janos SUTO Avatar Janos SUTO
  2. Piler
  3. piler
  4. Issues

sphinx database access denied after post install

Issue #119 resolved
Former user created an issue

When attempting to run piler on centos 6 I receive

SQLSTATE[42000] [1044] Access denied for user ''@'localhost' to database 'sphinx' on database: sphinx

when attempting to access the gui. It appears that something isn't going right with the post install and the database rights aren't being set for sphinx. I have tried changing the user to piler in the webgui config file but then it gives for user piler wrong password password=no, so I'm not really sure where or how I can set the password. Any help would be appreciated! Thanks!

Comments (40)

  1. Janos SUTO repo owner

    Please make sure that searchd is running, and listening on port 9306. It actually doesn't use any authentication, so no username / password is required to access sphinx.

  2. datapharmer

    Ok, so I apologize! Searchd was indeed running, but when I checked piler it wasn't (even though it said piler starting and everything seemed fine). Looking around I realized postifx was the problem per this issue: https://bitbucket.org/jsuto/piler/issue/21/centos-63-piler-not-starting is there a log somewhere that would make this easier to identify should something similar happen?

    Also, thank you very much for working on such a needed piece of software - keep up the great work! Thanks!

  3. Janos SUTO repo owner

    yes, the maillog (/var/log/maillog or mail.log) should help to figure out what's wrong. The piler daemon and the gui logs whenever an email comes in or some sort of problem happens. Btw. is everything ok now?

  4. datapharmer

    Well I still am getting piler: ERROR under smtp status (related to the postfix problem I suppose?) and need to look into that more, also I am struggling with the sso on centos (getting that particular ntlm auth module for apache compiled and running is a beast on RHEL variants), but even with it installed and running I'm getting permission denied. Do you have any additional pointers on troubleshooting the sso? What are the user/group/domain setup requirements on the backend prior to enabling SSO? In our case our internal domain for authentication is domain.local and our email domain is domain.com (yes, I know using .local is bad practice but the AD domain was setup well before I got here). Thanks for the quick response and follow-up!

  5. Janos SUTO repo owner

    Edit config-site.php, and fix PILER_HOST, set the IP-address of the host running piler.

    Regarding SSO, I recommend you to set the loglevel to debug, then restart apache, then try to login again. I assume you are already logged in to the Active Directory.

    Also make sure the user running apache is in the winbindd_priv (or equivalent) group.

    I found the following that might help in the troubleshooting:

    http://blog.netnerds.net/2009/10/enable-windows-ntlm-pass-through-authentication-in-linux-based-apache/ https://github.com/remicollet/remirepo/blob/master/mod/mod_auth_ntlm_winbind/auth_ntlm_winbind.conf

    I'll get the ubuntu version of apache config stuff to make sure you have the same.

  7. Janos SUTO repo owner

    OK, if you manage to make SSO working on centos, then I'll add it to the documentation.

  9. datapharmer

    Thank you, it is much appreciated. I dropped back to Debian and thought I would work through that first to make sure I have all the issues ironed out since the documentation matches more closely and then work through centos so I'm not running up against too many potential pitfalls. On Debian 7 I have ldap authentication working fine, but sso gives me nothing but "permission denied". I can't see any relevant errors in the various logs I've checked (mail, messages, apache2, samba) and wbinfo and kinit both seem to work fine from the command line. Also I got a hit in the AD logs for a query with no SID when there were no ldap settings but nothing now that I do have ldap settings in config-site, so I'm probably missing something in the ldap portion still (permissions issue between ldap and ntlm maybe?) Anyhow, if you have any suggestions I'm all ears, but I can deal with just using ldap otherwise. The sso is just a bonus. Also I wanted to mention that LDAP_BASE_DN is a required parameter as there is an error in ldap.php logged otherwise that prevents it from working. Thanks again for all your help!

  10. Janos SUTO repo owner

    Please set the LogLevel to debug in apache config. Then visit http://..../sso.php again, and verify that you are accepted by (AD + winbind), and let me see the error log.

    Then check if you have created the helper account. It's required to query the user email addresses, list membership, etc.

  11. datapharmer

    I have sanitized some of the data for security reasons, but it should give you everything you need. I've also included the config-site info in case something stands out as being wrong.

    root@[myhostname]:~# grep LogLevel /etc/apache2/apache2.conf

    LogLevel: Control the number of messages logged to the error_log.

    LogLevel debug root@[myhostname]:~# grep ErrorLog /etc/apache2/apache2.conf

    ErrorLog: The location of the error log file.

    If you do not specify an ErrorLog directive within a <VirtualHost>

    ErrorLog ${APACHE_LOG_DIR}/error.log root@[myhostname]:~# root@[myhostname]:~# cat /var/www/piler/config-site.php <?php

    $config['SITE_NAME'] = 'piler.mydomain.local'; $config['SITE_URL'] = 'http://piler.mydomain.com/'; $config['DIR_BASE'] = '/var/www/piler/'; $config['PILER_HOST'] = '192.168.1.5'; $config['ENABLE_SYSLOG'] = 1;

    $config['SMTP_DOMAIN'] = 'piler.mydomain.local'; $config['SMTP_FROMADDR'] = 'no-reply@piler.mydomain.local'; $config['ADMIN_EMAIL'] = 'admin@piler.mydomain.local';

    $config['DB_DRIVER'] = 'mysql'; $config['DB_PREFIX'] = ''; $config['DB_HOSTNAME'] = 'localhost'; $config['DB_USERNAME'] = 'piler'; $config['DB_PASSWORD'] = 'pilerdbpassword'; $config['DB_DATABASE'] = 'piler';

    $config['SMARTHOST'] = 'mailserver.mydomain.local'; $config['SMARTHOST_PORT'] = 25; $config['ENABLE_SSO_LOGIN'] = 1; $config['ENABLE_LDAP_AUTH'] = 0; $config['LDAP_HOST'] = 'domaincontroller.mydomain.local'; $config['LDAP_HELPER_DN'] = 'CN=piler,CN=Users,DC=mydomain,DC=local'; $config['LDAP_HELPER_PASSWORD'] = 'pileruserpassword'; $config['LDAP_MAIL_ATTR'] = 'mail'; $config['REWRITE_MESSAGE_ID'] = '1'; $config['LDAP_BASE_DN'] = 'OU=Users,OU=domain user ou,DC=mydomain,DC=local'; $config['LDAP_AUDITOR_MEMBER_DN'] = 'CN=Auditors,CN=Users,DC=mydomain,DC=local'; ?> root@[myhostname]:~# tail /var/log/apache2/error.log root@[myhostname]:~# root@[myhostname]:~# tail -f /var/log/apache2/error.log [Tue Aug 20 14:16:35 2013] [notice] Apache/2.2.22 (Debian) PHP/5.4.4-14+deb7u3 configured -- resuming normal operations [Tue Aug 20 14:16:35 2013] [info] Server built: Mar 4 2013 22:05:16 [Tue Aug 20 14:16:35 2013] [debug] prefork.c(1023): AcceptMutex: sysvsem (default: sysvsem) [Tue Aug 20 14:16:44 2013] [debug] mod_deflate.c(615): [client 192.168.1.2] Zlib: Compressed 482 to 325 : URL /sso.php [Tue Aug 20 14:16:44 2013] [debug] mod_auth_ntlm_winbind.c(1019): [client 192.168.1.2] doing ntlm auth dance [Tue Aug 20 14:16:44 2013] [debug] mod_auth_ntlm_winbind.c(483): [client 192.168.1.2] Launched ntlm_helper, pid 5451 [Tue Aug 20 14:16:44 2013] [debug] mod_auth_ntlm_winbind.c(653): [client 192.168.1.2] creating auth user [Tue Aug 20 14:16:44 2013] [debug] mod_auth_ntlm_winbind.c(704): [client 192.168.1.2] parsing reply from helper to YR TlRMTNVTUAAABAAAB4IIogAAAAAAAAAAAAAAAAAAAAAGAbEdAAAADw==\n [Tue Aug 20 14:16:44 2013] [debug] mod_auth_ntlm_winbind.c(742): [client 192.168.1.2] got response: TT TlRMTVNTUAACAAAACAAIADgAAAAFgomi92USKV9wOJMAAAAAAAAAAF4AXgBAAAAABgEAAAAAAA9BAEIAQwBSAAIACACCAEIAQwBSAAEADABBAEIAQwBNAFAANQAEABQAYQBiAGMAcgAuAGwAbwBjBGEAbAADAICAYBQiAGMAbQBwADEALgBhAGIAYwByAC4AbABvAGJUNQBsAAAAAAA= [Tue Aug 20 14:16:44 2013] [debug] mod_auth_ntlm_winbind.c(412): [client 192.168.1.2] sending back TlRMTVNTUAACAAAACAAIADgAAAAFgomi92USKV9wOJMAAAAAAAAAAF4AXgBAAAAABgEAAAAAAA9BAEIAQwBSAAIACABBAEIAQwBSAAEADABBAEIAQwBNAFAAMQAEABQAYQBiAGMAcgAuAGwAbwBjAGEAbAADACIAYQBiAGMAbQBwADEALgBhAGIAYwByAC4AbABvAGMAYQBsAAAAAAA= [Tue Aug 20 14:16:44 2013] [debug] mod_deflate.c(615): [client 192.168.1.2] Zlib: Compressed 482 to 325 : URL /sso.php [Tue Aug 20 14:16:44 2013] [debug] mod_auth_ntlm_winbind.c(1019): [client 192.168.1.2] doing ntlm auth dance [Tue Aug 20 14:16:44 2013] [debug] mod_auth_ntlm_winbind.c(485): [client 192.168.1.2] Using existing auth helper 5451 [Tue Aug 20 14:16:44 2013] [debug] mod_auth_ntlm_winbind.c(704): [client 192.168.1.2] parsing reply from helper to KK TlRMTVNTUAADAAAAGAAYAHgAAAAIAQgBkAAAAAgACABYAAAACgAKAGAAAAAOAA4AagAAAAAAAACYAQAABYKIogYBsR0AAAAQcrviZjI013Ib8FlTfJ7yJ0EAQgBDAFIASwBlAHYAaQBuAEkAVABMAFQAMPAzAEsAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAsEFoqUFd+fegOHoqlCTJeAEBAAAAAAAAQPvabNGdzgF21PaCB8UjgAAAAAACAAgAQQBCAEMAUgAAAAwAQQCBAEMATQBQADEABAAUAGEAYgBjAHIALgBsAG8AYwBhAGwAAwAiAGEAYgBjAG0AcAAxAC4AYQBiAGMAcgAuAGwAbwBjAGEAbAAIADAAMAAAAAAAAAABAAAAACAAAC15kUFTm07JpXl+SJHu36u/WLanCBy6I3pOvGpwun9wCgAQAAAAAAAAAAAAAAAAAAAAAAAJAC4ASABUAFQAUAAvAGEAYgBjAG0AcAAxAC4AYQBiAGMAcgAuAGMAbwBtADoAOAAwAAAAAAAAAAAA\n [Tue Aug 20 14:16:44 2013] [debug] mod_auth_ntlm_winbind.c(742): [client 192.168.1.2] got response: AF myusername [Tue Aug 20 14:16:44 2013] [debug] mod_auth_ntlm_winbind.c(788): [client 192.168.1.2] authenticated myusername [Tue Aug 20 14:16:44 2013] [debug] mod_deflate.c(615): [client 192.168.1.2] Zlib: Compressed 17 to 19 : URL /sso.php [Tue Aug 20 14:16:45 2013] [debug] mod_auth_ntlm_winbind.c(962): [client 192.168.1.2] retaining user myusername [Tue Aug 20 14:16:45 2013] [debug] mod_auth_ntlm_winbind.c(963): [client 192.168.1.2] keepalives: 3 [Tue Aug 20 14:16:45 2013] [error] [client 192.168.1.2] File does not exist: /var/www/piler/favicon.ico [Tue Aug 20 14:16:45 2013] [debug] mod_deflate.c(615): [client 192.168.1.2] Zlib: Compressed 290 to 221 : URL /favicon.ico

  12. datapharmer

    I also verified that #kinit username is accepted after entering a password at the prompt and a ticket is issued. wbinfo -u returns a list of all users including the username I am testing with.

  13. Janos SUTO repo owner

    According to the log (authenticated myusername) the browser <-> winbind/samba part is done properly.

    Now we need some troubleshooting. In model/user/auth.php there's a line:

    $u = explode("\", $_SERVER['REMOTE_USER']);

    write the following line after that:

    print_r($u); exit;

    than try to login at /sso.php at show me what you got.

  15. Janos SUTO repo owner

    hmm, no [1] => value? Normally the REMOTE_USER should be in DOMAIN\username format.

    Ok, now try the following:

    replace $u[1] with $u[0] in the check_ntlm_auth() function, and let's try again. Please remove the print_r(); .... line.

  17. Janos SUTO repo owner

    ok, then please write the following to the beginning of the check_ntlm_auth function:

    print "aaa:" . $_SERVER['REMOTE_USER']; exit;

    and let's try again.

  19. Janos SUTO repo owner

    I've updated the master branch. Please download, and update model/user/auth.php in the documentroot. It also syslogs the sso username.

  20. datapharmer

    ok, sso login appears to succeed now according to mail.log but apache2 error log shows:

    [Wed Aug 21 08:08:50 2013] [error] [client 192.168.1.2] PHP Fatal error: Call to a member function get_your_all_domains_by_email() on a non-object in /var/www/piler/model/user/auth.php on line 209

  22. datapharmer

    whoops that's my fault. All is ok. I was using sAMAccountName for ldap instead of mail. Switching back to make made everything happy. Thanks!

    On another note I am trying to get some old exchange journals that were saved as .pst into piler. I converted to mbox with readpst and pilerimport with -m switch appears to run for a very short period and then exits with nothing but a line break, but nothing makes it into the database.

    mail.log shows: Aug 21 08:14:50 myhostname pilerimport[15149]: added mydomain: 'domain.com' Aug 21 08:14:50 myhostname pilerimport[15149]: added mydomain: 'local' Aug 21 08:16:52 myhostname piler[10312]: processed 0 messages Aug 21 08:16:52 myhostname piler[10313]: connection from 127.0.0.1 Aug 21 08:16:52 myhostname piler[10313]: 400000008214afbe26da6f1cd059bfd2352d: sent: 220 piler.domain.com ESMTP#015

    Any thoughts on how to get more info on what is wrong here?

    Thanks you so much!

  23. datapharmer

    oh, I already tried toggling the archive message-id variable between 0 and 1 and reloading piler. Sorry, meant to mention that above.

  24. Janos SUTO repo owner

    Please chdir to a directory where user piler has write permissions, then try running pilerimport again.

  25. datapharmer

    ok, I checked that, but the problem appears to be that I processed the pst with the recursive option in readpst and pilerimport didn't like that. It says processing and is listing tons of messages, so I think all is well now!

    I know documentation can be a nightmare to keep up with (I'm bad at it with my own projects). That said, this install is fresh for me, and If I review what is there now and give you some updates is that useful to you? If so, what is the best form to provide them in? I don't want to be annoying to you!

    One other really minor thing I noticed is that the health statistics don't seem to be loading on the latest master branch (I recall them working on .23 when I tried it). Perhaps something changed in the branch that is referring to graph and it has changed to chart or something? Anyhow it is really not a big deal to me but thought you might want to check it before the final release. The apache error log shows a 500 for index.php?route=stat/graph&timespan=daily&uid=

  26. datapharmer

    I also happened to notice that LDAP_AUDITOR_MEMBER_DN works for SSO but is seemingly ignored now by LDAP sign-on (at least for me). This isn't a problem for me, so if it is specific to my configuration feel free to ignore me :-)

  27. Janos SUTO repo owner

    If you enabled sso, then for a regular ldap login go to /login.php directly. The site url redirects you to sso.php performing an sso.

  29. datapharmer

    what I meant is if I set sso to off and ldap to on, when I logged in with ldap the audit button is missing (even though I am in the auditors group). I will recheck after rebuilding from the master branch. As for the charts, that is what I expect but I get a broken image where the chart should be (again I will check back after the next rebuild).

  30. datapharmer

    as for the charts it looks like I was missing php5-gd as the error started appearing for imagecreatetruecolor() in the apache logs after updating the master branch so that was a quick fix. I must have had it installed on centos and missed it on debian.

    $config['LDAP_AUDITOR_MEMBER_DN'] still seems to not work for ldap logins with the 835 build. That said, it is recognized when sso is enabled instead, so go figure.

  33. Janos SUTO repo owner

    I tested against my AD server using ordinary LDAP auth, and it worked. Can you help me with some debugging?

    Please open model/user/auth.php and replace the check_ldap_membership() function with the attached version. (it basically prints some ldap attributes, then exit). Then try to login, and show me the results.

  35. datapharmer

    Ok, I am traveling this week so I won't be able to test it until next week, but I will update the ticket as soon as I get a chance to test this.

  36. datapharmer

    Ok, so sorry about not getting this to you sooner, but here it is:

    CN=Auditors,CN=Users,DC=abcr,DC=local bbbCN=[My Name Redacted],OU=IT,OU=Office Admin,OU=Users,OU=[Company Name Redacted],DC=abcr,DC=local vs. CN=Auditors,CN=Users,DC=[domain redacted],DC=local

  38. Janos SUTO repo owner

    So, you say that when SSO is turned on or used, then the same user account is recognized as auditor, however when using ldap auth, then not.

    If this is the case, then try to locate the following line in model/user/auth.php (around 115th line):

    $query = $ldap->query($ldap_base_dn, "(|(&(objectClass=$ldap_account_objectclass)($ldap_mail_attr=$username))(&(objectClass=$ldap_distributionlist_objectclass)($ldap_distributionlist_attr=$ username)" . ")(&(objectClass=$ldap_distributionlist_objectclass)($ldap_distributionlist_attr=" . stripslashes($a['dn']) . ")))", array("mail", "mailalternateaddress", "proxyaddresses", "zimbraMailForwardingAddre ss", "member", "memberOfGroup"));

    then replace it with the following line:

    $query = $ldap->query(LDAP_BASE_DN, "(|(&(objectClass=$ldap_account_objectclass)($ldap_mail_attr=$username))(&(objectClass=$ldap_distributionlist_objectclass)($ldap_distributionlist_attr=$user name)" . ")(&(objectClass=$ldap_distributionlist_objectclass)($ldap_distributionlist_attr=" . $a['dn'] . ")))", array());

    It appears that I query all ldap attributes using SSO, but only few when LDAP auth. Probably I miss some, so let's figure it out.

  41. Log in to comment
Assignee
Type
bug
Priority
major
Status
resolved
Votes
0
Watchers
2
Jira: the preferred issue tracker for Bitbucket. Join the team!