12

Okay, so I'm completely new to the server stuff, but I have a VPS running with MySql.

I want to create a mobile app with Python, but I need access to a database that's available for everyone.

So now I was thinking to open port 3006 on my VPS so we can access the database from the python app. But this feels like it's not a good practice.

Any advice?

Refilon
  • 229
  • 2
  • 5
  • 13
    Not a good idea at all. – Orphans Aug 19 '22 at 09:08
  • You can, *iff* you know how to lock down your database properly. But you're still open to DoS attacks that make a lot of connections, or execute specifically crafted long-running queries. – Bergi Aug 19 '22 at 22:09
  • 1
    "*a database that's available for everyone*" - do you mean a static, public dataset, or really a database where everyone can edit whatever they want? – Bergi Aug 20 '22 at 02:59
  • @Bergi I want my Python app to be connected to this external database. – Refilon Aug 21 '22 at 16:14
  • 1
    @Refilon Yes, but what does it do with the database? Is it a readonly database? What does your app do, who are its users? Do you need to limit each user's permissions on what they can do with (their part of) the data? Would you allow your users to run arbitrary SQL commands on the database? – Bergi Aug 21 '22 at 16:54
  • @Bergi Yes, they should be able to create/read/update/delete data for certain actions. – Refilon Aug 22 '22 at 14:42

5 Answers5

30

Do not make your database publicly accessible. One bug in the database or weak passwords and someone deletes / steals / changes everything.

Your mobile app shouldn't access the database directly, it should access an API that in your case is probably written in Python sitting behind Nginx. Your API calls need to be authenticated to avoid abuse, and ideally rate limited to avoid DDOS attacks.

Tim
  • 31,888
  • 7
  • 52
  • 78
  • 1
    "*One bug in the code or weak passwords*" could also be said about the API. But sure it's simpler to implement arbitrary authentication methods or rate limiting in a python HTTP server than in a database. – Bergi Aug 19 '22 at 22:11
  • 10
    If you open your database port, people can extract the database username/password from the mobile app, then use those to do whatever they want on the database - `update users set password='123456' where name='admin'`. Getting the API to do this, if it doesn't allow SQL injection, and doesn't have a `update users` statement anywhere, is **much** harder. – Guntram Blohm Aug 19 '22 at 22:37
  • 2
    @GuntramBlohm You could give every user their own database account. And/or you only give SELECT privileges to the db user (a read-only account). Databases have a lot of this stuff built-in, you just need to know how to use it… – Bergi Aug 19 '22 at 23:24
  • 5
    Here's a great recent example of someone that exposed their database directly for end user consumption, and illustrates why it's such a bad idea: https://kernal.eu/posts/linuxfx/ – 4oo4 Aug 20 '22 at 00:47
  • 1
    @Bergi still a bad idea even then. Apart from the hideous configuration you’d have to do and all the aforementioned other reasons, it also won’t allow fine-grained access on individual rows, e.g. ‘you cannot set this ‘active’ boolean to true when field x is lower than 10’. – Sebastiaan van den Broek Aug 20 '22 at 02:01
  • @SebastiaanvandenBroek I'm not saying it's a good idea, only that this isn't the reason. The hideous and unusual configuration is, as well as scalability for firewalling connections. And actually yes, databases do allow configuring fine-grained access controls, using constraints, row-level security, or as a last resort triggers - they're built for this purpose! – Bergi Aug 20 '22 at 02:55
  • 1
    RDBMS have sessions, views, stored procedures, schemes, roles... But yes RDBMS dosnt scales for thousands connections – gapsf Aug 21 '22 at 09:04
  • Don't worry about bugs, someone will reverse engineer you app. Al Queida were using unsecured fitness tracker data to locate american soldiers. – Jasen Aug 22 '22 at 05:16
11

Is it a good idea to open up a database port for everyone?
... this feels like it's not a good practice.

No it isn't.

Access control and security are of course big issues there.

From an application design perspective: most things database related die a horrible death when there is significant latency between the database and application.

That is one of the reasons why most phone applications are designed to connect to API's instead.

HBruijn
  • 77,029
  • 24
  • 135
  • 201
5

Very bad idea. It's quite easy to decompile an app. Then anyone have credentials for your db. Lets say the app uses the same login for every install then you have a massive data leak. Even if you use different logins for each user of your app you have one hell of a task settings up correct privileges on your tables on row level which I don't think is even possible.

Much easier and more correct to build an api through which all communication is done

Esben Skov Pedersen
  • 197
  • 1
  • 8
3

You need to set up a zero trust interface that establishes the user's identity and gives them access to only their own data.

Possibly this can be done by using different database roles for each user account, but I don't think that approach scales well. The usual approach is to set up an API on an encrypted channel (eg: HTTPS), and have the API perform permission checks on every request.

Jasen
  • 826
  • 6
  • 12
1

Recently there have been exploits for authentication bypass on MySQL, letting attackers access and modify/delete all data. I would therefore highly advise against publicly exposing a database server!

If it's only for maintenance use a SSH tunnel, like ssh -L 3306:127.0.0.1:3306 myuser@myserver.example.com

If you absolutely need to access it from across the internet, limit access to the IP of the server you are accessing from (and enable SSL in the database server configuration).

ctm
  • 11
  • 1