Database connection

6 posts / 0 new
Last post
SP2L
SP2L's picture
Database connection

Greetings.

LAN IP addres of my Debian system is 192.168.0.14

From "Database connection" window I can connect using:
- "Server Name": localhost, 127.0.0.1, public IP of my server
- "User name": cqrlog, pma, sp2l, root

Using as "Server name" 192.168.0 14 I can NOT connect at all.
Why is that, please?
Is RFC1918 16-bit block somewhere disabled in CQRLog?

From CLI and PhpMyAdmin webpage I can connect
to CQRLog database using all above listed "User names"
and all "Server names", including 192.168.0.14

There are no any iptables rules running.

sp2l@buster:~$ ip route show all
default via 192.168.0.1 dev enp5s0 proto dhcp metric 100
192.168.0.0/24 dev enp5s0 proto kernel scope link src 192.168.0.14 metric 100
sp2l@buster:~$

Take care.

Best regards.
Tom - SP2L
https://www.sp2l.ampr.org

oh1kh
Database connection

How is the server's bind address?

If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.

Is connection allowed from all addresses?

GRANT ALL PRIVILEGES ON *.* TO 'cqrlog'@'%' IDENTIFIED BY 'cqrlog';
(This is very open definition!)

--
Saku
OH1KH

SP2L
SP2L's picture
Database connection

Hello Saku.

Thank you for quick response.

Debian-10.5 LAN IP is 192.168.0.13.

Temporarily and very insecure setup is as follow:

MariaDB [(none)]> SHOW GRANTS FOR 'cqrlog'@'%';
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for cqrlog@% |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `cqrlog`@`%` IDENTIFIED BY PASSWORD 'OBFUSCATED' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO `cqrlog`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql`.* TO `cqrlog`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `cqrlog001`.* TO `cqrlog`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `cqrlog_common`.* TO `cqrlog`@`%` WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.000 sec)

MariaDB [(none)]>

MariaDB [(none)]> SHOW GRANTS FOR 'pma'@'%';
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for pma@% |
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `pma`@`%` IDENTIFIED BY PASSWORD 'OBFUSCATED' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO `pma`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql`.* TO `pma`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `cqrlog001`.* TO `pma`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `cqrlog_common`.* TO `pma`@`%` WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.000 sec)

MariaDB [(none)]>

MariaDB [(none)]> SHOW GRANTS FOR 'root'@'%';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD 'OBFUSCATED' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO `root`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql`.* TO `root`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `cqrlog001`.* TO `root`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `cqrlog_common`.* TO `root`@`%`WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.000 sec)

MariaDB [(none)]>

MariaDB [(none)]> SHOW GRANTS FOR 'sp2l'@'%';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for sp2l@% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `sp2l`@`%` IDENTIFIED BY PASSWORD 'OBFUSCATED' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO `sp2l`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql`.* TO `sp2l`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `cqrlog001`.* TO `sp2l`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `cqrlog_common`.* TO `sp2l`@`%` WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.000 sec)

MariaDB [(none)]>

So, users crqlog, pma, root and sp2l have maximum possible credentials.
From CLI any of them can connect to any existing MariaDB database.

See attached picture of connection using 0.0.0.0 IP address for phpMyAdmin.
Other possible hosts are: localhost and 127.0.0.1

If in CQRLog's "Database connection" window
I put running system legitimate IP address 192.168.0.13
none of authorized users can connect to CQRLog database.

See attached combined picture.

Take care.

File: 

Best regards.
Tom - SP2L
https://www.sp2l.ampr.org

oh1kh
Database connection

H! !

I did mean my.cnf or mariadb-server.cnf should have server's bind address setting as 0.0.0.0 otherwise server will not listen on all interfaces.
You can not put a list of bind addresses there. Only one specified, or 0.0.0.0 that means any.

WIthout firewall, bind to 0.0.0.0 and privileges as shown there should not be any restrictions.

But I made some testing. I have two servers in local network running cqrlog database. In addition I have server here at local pc running cqrlog database. All "real" mysql servers at port 3306.

I can connect to external servers with their IPs. And local pc server with "localhost" (or 127.0.0.1), but I can not connect local server with local network card IP address.
How ever I can connect local server from same pc with telnet to local network card ip address and port 3306.

So this must be Lazarus/FPC sql unit property. It seems to restrict loop connection from own IP address to own IP address if both are at same physical interface. I did even create new virtual interface for network card with fixed IP. Cqrlog can not connect to that IP either.
If localhost is used then connection is made from own network card to localhost interface (that is not physical loop). I think.

But this is ok as localhost (or 127.0.0.1) works fine with local sql server.

--
Saku
OH1KH

SP2L
SP2L's picture
Database connection

Hello Saku.

Perfect catch - thank you!

That was the place I did not looked at, Hi!
Relevant configuration in my Debian-10.5 is here:

/etc/mysql/mariadb.conf.d/50-server.conf

Changed
bind-address = 127.0.0.1
to
bind-address = 0.0.0.0

Now using LAN IP 192.168.0.13,
connection to CQRLog database works O.K.
Simple consistency of MySQL settings.

Have a nice day.

Best regards.
Tom - SP2L
https://www.sp2l.ampr.org

oh1kh
Database connection

Hi!
OK, fine if that was solved.

Now It left me thinking why I can not connect sql server of this PC with netcard address and cqrlog, but can connect with telnet to netcard address and can nonnect to localhost with cqrlog. And I do have bind-address as 0.0.0.0

I have to test with other computer to see what then happens.

How ever it is not problem as localhost works anyway and this is a "kitchen laptop" used for cqrlog seldom. Only when travelling, not at home.

I have just another problem with fldigi. It has been there since version 4.0.13 (now latest is 4.1.14.51). Dave has already found first problem after we managed to find a way to reproduce it also with his setup.
It is in other way round as cqrlog. Here usually Ubuntu users have problem Fedora not. There now other ways.

Fixed bug is now setting up hamlib with "Hamlib net rigctld" as rig. It finally works. Now it just remains problem for me that when I save that kind of setup, stop fldigi, and try to start again it has immediately "fatal error" and does not start.
Dave can not reproduce it with his F32 setup. I can. Even with clean virtual machine F32.

So cqrlog is not the only program having problems, hi.

--
Saku
OH1KH