[mythtv-users] Remote frontend access denied
Don Brett
dlbrett at zoominternet.net
Fri Nov 22 05:17:45 UTC 2019
On 11/21/2019 9:15 PM, Stephen Worthington wrote:
> On Thu, 21 Nov 2019 13:42:48 -0500, you wrote:
>
>> On 11/21/2019 1:04 PM, Greg Oliver wrote:
>>> On Thu, Nov 21, 2019 at 11:24 AM Don Brett <dlbrett at zoominternet.net
>>> <mailto:dlbrett at zoominternet.net>> wrote:
>>>
>>> On 11/21/2019 3:32 AM, Stephen Worthington wrote:
>>> > On Wed, 20 Nov 2019 23:01:50 -0500, you wrote:
>>> >
>>> >> I had a working 18.04 Ubuntu-Mate box that worked fine; after a
>>> >> self-induced catastrophic error, I ended up rebuilding it
>>> (fe/be box
>>> >> with one remote frontend). The rebuild also works pretty well,
>>> but I
>>> >> haven't gotten the remote front-end working yet. I haven't
>>> made any
>>> >> changes to the remote, but I'm getting database connection errors.
>>> >> Tried lots of things, but no luck. Any suggestions on what to
>>> look for?
>>> >>
>>> >> Don
>>> >>
>>> >> *From the logs:*
>>> >>
>>> >> *Excerpts from remote box: (/var/log/mythtv/mythfrontend.log)*
>>> >> Nov 20 12:49:34 jax mythfrontend.real: mythfrontend[2459]: E
>>> CoreContext
>>> >> mythdbcon.cpp:239 (OpenDatabase) Driver error was
>>> [1/1045]:#012QMYSQL:
>>> >> Unable to connect#012Database error was:#012Access denied for user
>>> >> 'mythtv'@'jax' (using password: YES)
>>> >>
>>> >> Nov 20 12:49:34 jax mythfrontend.real: mythfrontend[2459]: E
>>> CoreContext
>>> >> mythdb.cpp:646 (GetSettingOnHost) Database not open while
>>> trying to load
>>> >> setting: backendserverport
>>> >>
>>> >> *Excerpts from fe/be box:**(/var/log/mysql/error.log)*
>>> >> 2019-11-20T17:49:34.057263Z 544 [Note] Access denied for user
>>> >> 'mythtv'@'jax' (using password: YES)
>>> >> 2019-11-20T18:00:01.606694Z 545 [Note] Got an error reading
>>> >> communication packets
>>> >> 2019-11-21T00:01:41.387690Z 721 [Note] Got an error reading
>>> >> communication packets
>>> >> 2019-11-21T00:15:52.630339Z 156 [Note] Aborted connection 156
>>> to db:
>>> >> 'mythconverg' user: 'mythtv' host: 'localhost' (Got timeout reading
>>> >> communication packets)
>>> >>
>>> >>
>>> >> *Things I've checked:**
>>> >> **
>>> >> **/home/don/don.my.cnf*
>>> >> secure_file_priv=/var/lib/mysql
>>> >>
>>> >> */home/don/.mythtv/config.xml (same as it was before rebuild)*
>>> >> <LocalHostName>my-unique-identifier-goes-here</LocalHostName>
>>> >> <Database>
>>> >> <PingHost>1</PingHost>
>>> >> <Host>localhost</Host>
>>> >> <UserName>mythtv</UserName>
>>> >> <Password>mythtv</Password>
>>> >> <DatabaseName>mythconverg</DatabaseName>
>>> >> <Port>3306</Port>
>>> >> </Database>
>>> >>
>>> >> */home/mythtv/.mythtv/config.xml (symlinked to
>>> /etc/mythtv/config.xml)*
>>> >> <Configuration>
>>> >> <Database>
>>> >> <PingHost>1</PingHost>
>>> >> <Host>localhost</Host>
>>> >> <UserName>mythtv</UserName>
>>> >> <Password>mythtv</Password>
>>> >> <DatabaseName>mythconverg</DatabaseName>
>>> >> <Port>3306</Port>
>>> >> </Database>
>>> >>
>>> >> */etc/mysql/mysql.conf.d/mysqld.cnf*
>>> >> # removed 11/17/2019
>>> >> #bind-address = 127.0.0.1
>>> >>
>>> >> # trying this 11/18/2019...didn't help
>>> >> #bind-address = 0.0.0.0
>>> >>
>>> >> */etc/mysql/mysql.conf.d/mythtv.cnf*
>>> >> [mysqld]
>>> >> #bind-address=::
>>> >> max_connections=100
>>> >> #sql_mode=NO_ENGINE_SUBSTITUTION
>>> > If you are doing external access to the database, you do need either
>>> > "bind-address = 0.0.0.0" or "bind-address=::". Use :: if you want
>>> > IPv6 to work. Make sure that all other "bind-address=" lines in all
>>> > the MySQL/MariaDB config files are commented out. You need to
>>> restart
>>> > MySQL or MariaDB before it will see the change:
>>> >
>>> > sudo systemctl restart mysql
>>> > or
>>> > sudo systemctl restart mariadb
>>> >
>>> > However, the first error message you are reporting is "Access
>>> denied".
>>> > So that may mean that you have not done the right GRANT command to
>>> > allow that user access from that location. I think doing this
>>> on the
>>> > backend PC should fix that problem:
>>> >
>>> > sudo mysql
>>> > GRANT ALL PRIVILEGES ON mythconverg to 'mythtv'@'jax';
>>> > FLUSH PRIVILEGES;
>>> > exit
>>>
>>>
>>> Should be :
>>>
>>> GRANT ALL PRIVILEGES ON mythconverg.* to 'mythtv'@'jax';
>>>
>>> > If that does not work, or you want to allow access from all
>>> devices on
>>> > your network, try:
>>> >
>>> > sudo mysql
>>> > GRANT ALL PRIVILEGES ON mythconverg to 'mythtv'@'%';
>>> > FLUSH PRIVILEGES;
>>> > exit
>>> >
>>> > The above presumes that you have actually created the user 'mythtv'.
>>> > If not, then you may need to do the following before the above GRANT
>>> > commands:
>>> >
>>> > GRANT ALL PRIVILEGES ON mythconverg TO 'mythtv'@'localhost'
>>> IDENTIFIED
>>> > BY 'mythtv' WITH GRANT OPTION;
>>> >
>>> > Then you need to make sure the config.xml file being used by the
>>> > remote frontend has its <Host></Host> value set to the IP address or
>>> > hostname of the backend PC. Both the config.xml files you
>>> posted have
>>> > it set to "localhost", which will not work for a remote frontend.
>>> >
>>> > Once you have database access working, you also need to ensure that
>>> > mythbackend only starts after the network is fully up. The default
>>> > systemd file for mythbackend only waits for localhost to be up, and
>>> > mythbackend then never binds to the external IP address. If that is
>>> > the case, just restarting mythbackend after booting is complete will
>>> > fix that until the next reboot:
>>> >
>>> > sudo systemctl restart mythtv-backend
>>> >
>>> > If the above fixes things, then you need to create an appropriate
>>> > systemd override file for mythbackend. There are other threads on
>>> > this mailing list about how to do that - it is modestly
>>> complicated so
>>> > I do not want to repeat it here again unless you can not find the
>>> > proper thread. A google search for "wait-until-pingable.py" (in
>>> > double quotes) should find it.
>>>
>>> Did the changes for bind-address=::, no difference. Tried granting
>>> privileges and got:
>>>
>>> mysql> GRANT ALL PRIVILEGES ON mythconverg to 'mythtv'@'jax';
>>> ERROR 1046 (3D000): No database selected
>>> mysql>
>>> mysql> use mysql;
>>> Reading table information for completion of table and column names
>>> You can turn off this feature to get a quicker startup with -A
>>>
>>> Database changed
>>> mysql> GRANT ALL PRIVILEGES ON mythconverg to 'mythtv'@'jax';
>>> ERROR 1133 (42000): Can't find any matching row in the user table
>>> mysql>
>>> mysql> GRANT ALL PRIVILEGES ON mythconverg TO 'mythtv'@'localhost'
>>> IDENTIFIED
>>> -> BY 'mythtv' WITH GRANT OPTION;
>>> Query OK, 0 rows affected, 1 warning (0.00 sec)
>>> mysql>
>>> mysql> GRANT ALL PRIVILEGES ON mythconverg to 'mythtv'@'%';
>>> Query OK, 0 rows affected (0.00 sec)
>>>
>>> Then rebooted, still no difference.
>>>
>>> Did I use the correct database?
>>>
>>> Checked /etc/mythtv/config.xml on the remote, it was using
>>> localhost, so
>>> changed to 192.168.0.177 (backend address). No difference.
>>>
>>> During the rebuild, I used your systemd procedure, so
>>> "wait-until-pingable.py" was already in place.
>>>
>>>
>>> Still getting access denied messages from mysql:
>>>
>>> *From /var/log/mysql/error.log (on backend)*
>>> 2019-11-21T16:46:18.681726Z 525 [Note] Access denied for user
>>> 'mythtv'@'jax' (using password: YES)
>>>
>>>
>>> By the way, should this be working? It's from the remote box to the
>>> backend box.
>>>
>>> don at jax:~$ sudo mysql -h192.168.0.177 -umythtv -p
>>> Enter password:
>>> ERROR 1045 (28000): Access denied for user 'mythtv'@'jax' (using
>>> password: YES)
>>>
>>>
>>> Don
>> Am I using the correct database?
> Yes.
>
>> mysql> use mysql;
>> Database changed
>> mysql> GRANT ALL PRIVILEGES ON mythconverg.* to 'mythtv'@'jax';
>> ERROR 1133 (42000): Can't find any matching row in the user table
>> mysql>
>> mysql> select user, host from user;
>> +------------------+-----------+
>> | user | host |
>> +------------------+-----------+
>> | mythtv | % |
>> | debian-sys-maint | localhost |
>> | mysql.session | localhost |
>> | mysql.sys | localhost |
>> | mythtv | localhost |
>> | root | localhost |
>> +------------------+-----------+
>> 6 rows in set (0.00 sec)
> That makes it clear that the 'mythtv'@'jax' user has not been created.
> But the 'mythtv'@'%' user is there, and that should have matched
> 'mythtv'@'jax' and allowed access. So what do these commands show?
>
> SHOW GRANTS FOR 'mythtv'@'localhost';
> SHOW GRANTS FOR 'mythtv'@'%';
>
> Don is right - it needs "mythconverg.*", not "mythconverg" in the
> GRANT command, so I put you wrong there, and that is likely the
> problem.
>
> You could try creating the specific 'mythtv'@'jax' user with the
> correct GRANT command:
>
> CREATE USER 'mythtv'@'jax' IDENTIFIED BY 'mythtv';
> GRANT ALL PRIVILEGES ON mythconverg.* TO 'mythtv'@'jax';
> FLUSH PRIVILEGES;
>
> If that works, then you probably should delete the 'mythtv'@'%' user
> (unless you really want to allow logins from anywhere):
Yep, that did it, it works now.
> DROP USER 'mythtv'@'%';
Dropped
Doubt we still need them, but here are the grants results you asked
for. Thanks a bunch for the help,
Don
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> SHOW GRANTS FOR 'mythtv'@'localhost';
+-----------------------------------------------------------------------------------------+
| Grants for mythtv at localhost |
+-----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO
'mythtv'@'localhost' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO
'mythtv'@'localhost' |
| GRANT ALL PRIVILEGES ON `mysql`.`mythconverg` TO 'mythtv'@'localhost'
WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)
mysql> SHOW GRANTS FOR 'mythtv'@'%';
+---------------------------------------------------------------+
| Grants for mythtv@% |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mythtv'@'%' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO 'mythtv'@'%' |
| GRANT ALL PRIVILEGES ON `mysql`.`mythconverg` TO 'mythtv'@'%' |
+---------------------------------------------------------------+
3 rows in set (0.00 sec)
More information about the mythtv-users
mailing list