1

By default configurations, why is mysql server accepting nonlocal request, while postgresql server is not?

$ nmap -p0-65535 192.168.1.97

Starting Nmap 7.60 ( https://nmap.org ) at 2019-03-20 14:34 EDT
Nmap scan report for ocean.fios-router.home (192.168.1.97)
Host is up (0.00036s latency).
Not shown: 65533 closed ports
PORT      STATE SERVICE
22/tcp    open  ssh
3306/tcp  open  mysql
33060/tcp open  mysqlx

Nmap done: 1 IP address (1 host up) scanned in 4.50 seconds

Can netstat tell me the reason?

$ sudo netstat -ap | grep postg
tcp        0      0 localhost:postgresql    0.0.0.0:*               LISTEN      1567/postgres       
udp        0      0 localhost:57504         localhost:57504         ESTABLISHED 1567/postgres       
unix  2      [ ACC ]     STREAM     LISTENING     27116    1567/postgres        /var/run/postgresql/.s.PGSQL.5432

$ sudo netstat -ap | grep mysql
tcp6       0      0 [::]:mysql              [::]:*                  LISTEN      23683/mysqld        
tcp6       0      0 [::]:33060              [::]:*                  LISTEN      23683/mysqld        
unix  2      [ ACC ]     STREAM     LISTENING     1169294  23683/mysqld         /var/run/mysqld/mysqld.sock
unix  2      [ ACC ]     STREAM     LISTENING     1169513  23683/mysqld         /var/run/mysqld/mysqlx.sock
unix  2      [ ]         DGRAM                    1169211  23683/mysqld

How can I configure mysql work like postgresql, so that it is running but not accepting nonlocal request?

Thanks.

Improve this question
  • It will be in the config. This is why you should run a local firewall on your machine. It gives you one place to allow incoming connections. Some say it is pointless, but it does help when you miss-configure a service. – ctrl-alt-delor Mar 20 '19 at 18:48
  • Thanks. May I ask what commands you would use to run a local firewall for this purpose? – Tim Mar 20 '19 at 18:49
  • ufw is a simple one, gufw is its graphical-user-interface. (It is a front end to ip-tables.) On any debian based system just do apt-get install gufw, then you can do gufw &. At the top you specify the default rules e.g. incoming = Deny. The you can override by allowing services/ports – ctrl-alt-delor Mar 20 '19 at 18:54
  • That's a different question which, if submitted, would probably be closed as "primarily opinion-based" – DopeGhoti Mar 20 '19 at 19:33
2

There is no need of a firewall to such task. Note that I'm not saying firewalls are useless but you just need to disable all address listen(0.0.0.0). Least privilege on what addresses your software is able to wait for connections.

PostgreSQL and MySQL are different softwares and thus, can have different default behaviors of network, user access, installation path, database files path, filesystems supported, etc. You can't correlate those just because they are DBs. Other thing that could influence default configuration is what the responsibles for this packages inside your distribution decided the software should use as defaults.

MySQL defaults is to listen at all network addresses(0.0.0.0) unless configured differently by the distribution as explained above.

Back to your question, to make MySQL answer to localhost only, access it's config file(most cases at /etc/my.cnf or /etc/mysql/my.cnf) and add or edit the following line:

bind-address = 127.0.0.1

After that, restart your mysql service(depends on the distro, generally systemctl restart mysql or systemctl restart mysqld)

Double check with ss command:

ss -lntu | grep mysql
Improve this answer
  • Thanks. If not looking at nmap, can you tell that mysql accepts nonlocal request, while postgresql doesn't, by for example netstat? – Tim Mar 20 '19 at 21:39
  • With ss -lntu as root you can see the process and port it is listening at – user34720 Mar 20 '19 at 21:51
  • Thanks. May I ask why ss -lntu doesn't show postgreql, while netstat -ap does? How can I tell from the output of netstat -ap that postgresql is not accepting a nonlocal connection request, while mysql is? – Tim Mar 20 '19 at 22:00
  • if you are grepping by the mysql word, it will not show postgres related stuff. You need also to execute that command as root. – user34720 Mar 20 '19 at 22:03
  • Maybe I wasn't clear. Take a look at the output of sudo netstat -ap | grep postg in my post, does postgresql listen at localhost:postgresql? If so, why doesn't nmap not reports postgresql is listening at some tcp port? What does that udp established connection between localhost:57504 and itself mean? – Tim Mar 20 '19 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.