Postfix mysql error

When I try to log into roundcube the login fails. Looking at the Dovecot.log file I can see where this auth-worker is looking for a users table in the postfix database. It is correct that there is no users table in the postfix database. There is in the roundcube database. I think a config file is looking in the wrong database. 

Note I have been recovering from a bunch of issues since this weekend. I also had to fix the sieve error related to not writing a file.

May 15 06:07:29 auth-worker(90064): Warning: sqlpool(mysql): Query failed, retrying: Table 'postfix.users' doesn't exist
May 15 06:07:29 auth-worker(90064): Error: conn unix:auth-worker (pid=90039,uid=97): auth-worker<1>: sql(email@myhobby.org,127.0.0.1,<W64l5dpRNMZ/AAAB>): Password query failed: Table 'postfix.users' doesn't exist (using built-in default password_query: SELECT username, domain, password FROM users WHERE username = '%n' AND domain = '%d')
May 15 06:07:29 auth-worker(90064): Warning: sqlpool(mysql): Query failed, retrying: Table 'postfix.users' doesn't exist
May 15 06:07:29 auth-worker(90064): Error: conn unix:auth-worker (pid=90039,uid=97): auth-worker<2>: sql(email@myhobby.org,127.0.0.1,<W64l5dpRNMZ/AAAB>): Password query failed: Table 'postfix.users' doesn't exist (using built-in default password_query: SELECT username, domain, password FROM users WHERE username = '%n' AND domain = '%d')

Correct -- there is no "users" table in the "postfix" DB. But the roundcube DB more pertains to the end user settings -- last login, various options in the Roundcube settings (theme, identities, etc.) -- it doesn't really pertain to auth. Have you been adjusting things in the postfix, dovecot, and/or roundcube configs?

My reply is too long I will have to break it up.

What started all of this was I wanted to install Apache mod_limitipconn. I need some way to stop members from downloading multiple files from our server.

I followed the instructions from here as I couldn't find any helpful information about the mod in this forum. Yes that ended up being a bad idea. I had less trouble setting up the server when we moved from cPanel to CWP. This has been one of those things to put on the list of things to not do again. 
https://installati.one/install-mod_limitipconn-almalinux-8/

I installed mod_limitipconn and it broke apache so I removed mod_limitipconn and it removed the mod and it also removed apache. Aftter I figured out the service was actually gone I opened a ticket with CWP.

After they worked on it for awhile they let me know I should probably just reload the server and restore our sites from back as there was also issues with MySQl etc. I am not sure how that happened.

Not knowing if it was possible I asked them did they mean complete reformat the drive and reinstall?

Their next response was

We have adjusted the configuration settings, kindly reboot the server and check again.

I rebooted and the control panel didn't work, The control panel was throwing the mysql couldn't connect error. I could still get in with SSH and I found MariaDB would not start because of an error message "invalid something open_files_limit=65535"

In the /etc/my.cnf file there was a value "open_files_limit=65535". I put a comment in front of the line and then MariaDB started. THat didn't fix the Control Panel because the root user password had been changed. I knew the password it was in my lists. I found the same link to digital Ocean to reset the root user password that was provided in my MYSQL thread I started. The issue is I reset the password wrong so it forced the password at the console. But I still fixed the control panel.

Once I was in apache sevice would not start. I eventually traced this to the ssl certificates and DNS entries. Some sites were missing a bunch of dns entries. I first figured out I could get apache to run if I removed the SSL certs from anything that had to do with email. Then I found the dns entries missing once those were put back in the ssl certs updated correctly again and those items ran.

Every time the control panel webpage changed I would see an error message just briefly. In addition the postfix and Dovecot IMAP/POP3 Server service would not run.

Warning: mysqli_connect():Access denied for user 'postfix'@'localhost'(using password: YES) in /usr/local/cwpsrv/htdocs/rerources/admin/include/functions.php

I tried to reset the postfix mysql password hoping it would fix the issue but it did not.

At that point I went back to the ticket I had open and asked for help. They let me know the issue had been fixed and it was. Postfix service now ran but Dovecot did not. The error logs kept complaining about a certificate on one of the sites. I removed that domain as it was not being used anyway. That fixed the issue and dovecot ran.

I thought I was almost home free and discovered that round cube could not connect to the database. I chose to ask in the ticket again and their testing broke the control panel (they changed the root password) and they advised me the issue was I had secured the root user with a password on the terminal which is not necessary.

That prompted me to open this post as I didn't know how to set it up how it should be.

https://forum.centos-webpanel.com/mysql/remove-mariadb-root-user-password/

From there I had to fix the root user, fix the control panel, set the roundcube user password, removed a bad entry in Dovecot-mysql.conf (seems when you try to set the password in mysql in the MySQL Manager it writes the password to this file and a few others but in the wrong place.) same issue in the dovecot-token.conf file. I simply commented out the line.

following should all be on one line.
password=somepass

With those fixed I was now faced with a could not log on issue in roundcube.

Looking at the dovecot.log file there was a sieve error message that I fixed by data at the end of this thread.

http://forum.centos-webpanel.com/dovecot/dovecot-sieve-error/msg51022/#msg51022

Now the next thing on the list is this issue.

Most of these issues I have no idea how they happened.

Sounds like you know your server well and are on the right track to resolving this, one turn after another. Sometimes involving outside support introduces new issues or backtracks on the resolution process, as you are discovering! No one knows your server better than you do, so sometimes its best to just muddle through to the final solution.

(But best practice is to take a full snapshot before a major change that can break things... then you can just roll back if things go awry.)

OK that looks like everything is fixed.

This final problem was a missing line of code in the /etc/dovecot/dovecot-mysql.conf file.

Code: [Select]

#password_query = SELECT username as user, password, concat('/var/vmail/', maildir) as userdb_home, concat('maildir:/var/vmail/', maildir) as userdb_mail, 101 as userdb_uid, 12 as userdb_gid FROM mailbox WHERE username = '%u' AND active = '1'
password_query = SELECT username as user, password, concat('/var/vmail/', maildir) as userdb_home, concat('maildir:/var/vmail/', maildir) as userdb_mail, uid as userdb_uid, gid as userdb_gid FROM mailbox WHERE username = '%u' AND active = '1'

Those 2 lines missing are where this code was 

#password=somepass
password=samesomepass

Once I deleted those 2 lines and added back in the missing code (found on one of my other CWP servers) it worked again.

 

Hooray, a DIY sysadmin is born! You passed through the baptism by fire, so now you are probably ready for anything!