MariaDB through SSH tunnel only possible using admin account

Currently reading
MariaDB through SSH tunnel only possible using admin account

12
1
NAS
DS716+||
Operating system
  1. macOS
Mobile operating system
  1. Android
Hi all, I already posted this in the "official" Synology Community but would also like to post it here because this forum seems more active than the "official" one.

I would like to connect do MariaDB using a SSH tunnel. In DSM I have an account "frank" with full administrator rights. For security reasons I have disabled the accounts admin and guest as a standard practice.

The database client I use is called Navicat for MySQL but the problem seems to be universal to all clients.
  1. Create SSH Tunnel using user frank.
  2. SSH Tunnel is working!
  3. Connect to 127.0.0.1 port 3306 to connect to MariaDB running on the Synology NAS.
  4. Not getting any response from the database.
Somebody gave me the following tip, try using the admin account. It sounds mad but this is working.
  1. Create SSH Tunnel using User admin.
  2. SSH Tunnel is working!
  3. Connect to 127.0.0.1 port 3306 to connect to MariaDB running on the Synology NAS.
  4. Able to connect to the database.
I would like to start the follwing discussion.

For security reasons I would not like to use the user admin but the user frank. Is this possible or should I compromise security and use the user admin and hope for the best? Best solution I think would be to use the user frank and keep the user admin disabled.

What do people think of this topic?

Please excuse me if I started a discussion that was already on this forum. I used the search function extensively and was not able to find anything.
 

fredbert

Moderator
NAS Support
Subscriber
3,706
1,482
NAS
DS1520+, DS218+, DS215j
Router
  1. RT2600ac
  2. MR2200ac
Operating system
  1. macOS
Mobile operating system
  1. iOS
I'm in no way a DB expert (pretty much as far away as one could get) but it sounds like your MariaDB needs to have access rights added for user frank. It's an application permission issue rather than SSH/file system.

At present MariaDB is associating DSM user admin as an authorised DB user. I would look at MariaDB documentation on how to add/associate other OS accounts as authorised DB users.

A pure guess.
 
12
1
NAS
DS716+||
Operating system
  1. macOS
Mobile operating system
  1. Android
At step number 3 I use a database username and password to login. The account admin nor frank are known to the database. The fact is that the DSM admin account is somehow "more administrator" than the administrator account called frank. The DSM user frank somehow has less rights in SSH than the user admin. Both are administrator accounts but one just a bit more than the other?
 

fredbert

Moderator
NAS Support
Subscriber
3,706
1,482
NAS
DS1520+, DS218+, DS215j
Router
  1. RT2600ac
  2. MR2200ac
Operating system
  1. macOS
Mobile operating system
  1. iOS
Ah that wasn't obvious, it read as accessing without supplying any further credentials. The DSM 'admin' account is more like 'root' than other administrator accounts that you add. Often when in a SSH session as one of these new administrators you will have to elevate your privilege using sudo -i. This is certainly true when I've used PostgreSQL to update Video Station's database.

Have you tried explicitly allowing frank to access MariaDB package?
 
12
1
NAS
DS716+||
Operating system
  1. macOS
Mobile operating system
  1. Android
Ah that wasn't obvious, it read as accessing without supplying any further credentials. The DSM 'admin' account is more like 'root' than other administrator accounts that you add. Often when in a SSH session as one of these new administrators you will have to elevate your privilege using sudo -i. This is certainly true when I've used PostgreSQL to update Video Station's database.

Have you tried explicitly allowing frank to access MariaDB package?
No worries.
I indeed get the feeling that admin is a bit more administrator than the account "frank".
I was not able to find anything where I can grant access to the MariaDB package.
 

fredbert

Moderator
NAS Support
Subscriber
3,706
1,482
NAS
DS1520+, DS218+, DS215j
Router
  1. RT2600ac
  2. MR2200ac
Operating system
  1. macOS
Mobile operating system
  1. iOS
I just looked in my NAS's /etc/ssh/sshd_config, needs to use command sudo more /etc/ssh/sshd_config. Looks like there is default deny for most users and explicit allow for root and admin...

Bash:
...
AllowTcpForwarding no
...
Match User root
    AllowTcpForwarding yes
Match User admin
    AllowTcpForwarding yes
Match User anonymous
    AllowTcpForwarding no
    GatewayPorts no

It's been many years since I used SSH port forwarding but AllowTcpForwarding seems to be the most obvious config command.
 
12
1
NAS
DS716+||
Operating system
  1. macOS
Mobile operating system
  1. Android
I just looked in my NAS's /etc/ssh/sshd_config, needs to use command sudo more /etc/ssh/sshd_config. Looks like there is default deny for most users and explicit allow for root and admin...

Bash:
...
AllowTcpForwarding no
...
Match User root
    AllowTcpForwarding yes
Match User admin
    AllowTcpForwarding yes
Match User anonymous
    AllowTcpForwarding no
    GatewayPorts no

It's been many years since I used SSH port forwarding but AllowTcpForwarding seems to be the most obvious config command.
Thank you very much! This does indeed solve my issue.

After making the change I had to restart the SSH service in order for the changes to take effect.
  1. In the Synology DSM Control Panel go to Terminal & SNMP.
  2. Uncheck SSH service and press apply.
  3. Check Enable SSH service and press apply.
The change should now be implemented.

Again, thank you for your answer!

P.S.
The "unofficial" Synology Forum is faster than the "official" forum.
 
12
1
NAS
DS716+||
Operating system
  1. macOS
Mobile operating system
  1. Android
Great!

I would note down the change made as it may be overwritten when DSM is updated.
I have indeed documented this change. They will definitely overwrite this custom setting when a new update / patch of DSM is installed. Currently on DSM 7.0.1-42218 version. Quite happy with the current version.

I was looking for setting this topic to "Solved" but am not able to find this function. Maybe I am too new here and this is not available yet for me?
 

jeyare

Subscriber
2,424
806
NAS
Synology, TrueNAS
Operating system
  1. Linux
  2. Windows
Is there necessary to use the Synology native DB (deployed with DSM)? Because better and more under control is deployment of the official MariaDB docker container.
I have also MySQL in this scenario, managed by MySQL Workbench.
Flexible, safe and independent from Synology DSM upgrades.
 
12
1
NAS
DS716+||
Operating system
  1. macOS
Mobile operating system
  1. Android
I did have the docker container running during a test but after a while I noticed some weird network traffic from outside being able to connect to the MariaDB Docker container. No matter what firewall settings I set in the DSM firewall. I disabled the MariaDB Docker container and the traffic stopped. I removed the container.
 
12
1
NAS
DS716+||
Operating system
  1. macOS
Mobile operating system
  1. Android
There was a continuous stream of data, something like 20 mbit, going out. I stopped every single service and in the end I disabled the Docker container that contained MariaDB what stopped this outbound data stream.

I am not a network engineer and would have to look up how to make a tcpdump and how to make sure it only contains information from the docker container. My objective was to stop that unknown outbound stream as soon as possible. After it stood out that this came from Synology Docker, I uninstalled the whole lot and marked the test as failed.
 

jeyare

Subscriber
2,424
806
NAS
Synology, TrueNAS
Operating system
  1. Linux
  2. Windows
When you have official MariaDB container deployed, no need to be worried.
Be sure what you use, many unofficial sources there (out of my interest).
 
12
1
NAS
DS716+||
Operating system
  1. macOS
Mobile operating system
  1. Android
I thought I used the official one, I can't verify this anymore because I completely uninstalled Docker. Something was misbehaving very badly and I do not trust it.
 

fredbert

Moderator
NAS Support
Subscriber
3,706
1,482
NAS
DS1520+, DS218+, DS215j
Router
  1. RT2600ac
  2. MR2200ac
Operating system
  1. macOS
Mobile operating system
  1. iOS
I am not a network engineer and would have to look up how to make a tcpdump
Do a google for 'man tcpdump'. Since you're no longer progressing down the Docker path then treat this as an FYI but you may want to use it on something else :)

tcpdump is a Unix/Linux command line executable that will monitor packets on designated interfaces. It's a common tool to diagnose network connections. Depending how you construct the command's filter parameters you may want to | grep -v "<your client device IP/name>" its output to filter out any noisy packets going to/from, e.g., your SSH'd in client device.
 
12
1
NAS
DS716+||
Operating system
  1. macOS
Mobile operating system
  1. Android
The Synology command line interface does not offer man pages. But I do know grep. In tcpdump I see a lot of things happening at the moment, but nothing that looks out of place at first glance.

I am a developer, not a network engineer. The promise of Docker is that you do not have to go down that path but my experience is quite different. Many many issues using Docker in projects, slowing projects down a lot. Not a fan.

I uninstalled the Docker stuff because some party was obviously in the process of downloading my entire database resulting in a continuous outgoing stream of data from the Docker container. Which I was not happy about. Not very critical data but still not a good thing to happen.

I would love to use the flexibility of a Docker instance of the MariaDB database. For the moment I will stick with what does work and that is using the builtin DSM package because that one stood out to be reliable.
 

jeyare

Subscriber
2,424
806
NAS
Synology, TrueNAS
Operating system
  1. Linux
  2. Windows
Official image from Postgres at Docker Hub:
include detailed steps to deployment

--------------------------
How to check the container 'network activities' ... for the docker newbies:
NAS Docker GUI: check a name of the docker network ... e.g. 'bridge'
1639648372882.png


SSH into the NAS:
Bash:
docker network inspect bridge
find records for the 'postgre' container name:

"Name": "postgres1",
"IPv4Address": "172.17.0.4/16",

Same and faster, when you use Portainer for container management:
container IP address: 172.17.0.4 ..... two clicks within Portainer

Then simple:
tcpdump src 172.17.0.4
and you can see all the communication, you can use more filters for the tcpdump to define more precise interpretation

.... in my case just known IPs in the evaluated output


The Synology command line interface does not offer man pages. But I do know grep. In tcpdump I see a lot of things happening at the moment, but nothing that looks out of place at first glance.

I am a developer, not a network engineer. The promise of Docker is that you do not have to go down that path but my experience is quite different. Many many issues using Docker in projects, slowing projects down a lot. Not a fan.

I uninstalled the Docker stuff because some party was obviously in the process of downloading my entire database resulting in a continuous outgoing stream of data from the Docker container. Which I was not happy about. Not very critical data but still not a good thing to happen.

I would love to use the flexibility of a Docker instance of the MariaDB database. For the moment I will stick with what does work and that is using the builtin DSM package because that one stood out to be reliable.
something must be wrong in your setup:
- usage of the original Syno db pckg is out of your control
- with docker, I can clone all my DB instances immediately, including all possible IPTABLES customisation (better than in the Firewall GUI). But with the Firewall GUI you are still able to define IP address/range/subnet/country 'allowed' to use the container instance. Then it is up to you how to harden the security. There is also a network level (router/firewall).
- The fact that the 3rd party had the opportunity to 'reach' for your resources in your NAS is not a mistake on the part of the NAS or the container.
- you can't get a secure environment for the original Syno db pckg, just because it is preinstalled. This is a big mistake.
 
12
1
NAS
DS716+||
Operating system
  1. macOS
Mobile operating system
  1. Android
Thank you for the information. What works for Postgres should also work for MariaDB?

I only want the database to be reachable on port 3306 on the private network. Class-C 192.168...
 

jeyare

Subscriber
2,424
806
NAS
Synology, TrueNAS
Operating system
  1. Linux
  2. Windows
works also for:
- MySQL, MariaDB, Postgre, Mongo, ...
or
all containers

First:
- use just official containers (especially for DB)
- then community containers for another source
- hands off from containers w/o description or few downloads from the docker hub
- best to use the container CVE scanner. In my case
Bash:
https://github.com/anchore/grype
... hands off from Critical CVEs findings, unsolved
 

Create an account or login to comment

You must be a member in order to leave a comment

Create account

Create an account on our community. It's easy!

Log in

Already have an account? Log in here.

Similar threads

Replies
1
Views
5,767
I would not trust secure erase, without a visual surface check of the entire drive. There is no...
Replies
1
Views
903
I use mRemoteNG (still uses putty under the hood for ssh connections), which organizes the connections...
Replies
4
Views
700

Welcome to SynoForum.com!

SynoForum.com is an unofficial Synology forum for NAS owners and enthusiasts.

Registration is free, easy and fast!

Top