My production database (PostgresSQL) is actually hosted on a Windows Server that only accepts connections from specific IP addresses.
As long as the database was only accessed by a Web App and a Web Service, that setup worked well. We recently added a complementary desktop application that works along with the Web App. The new Desktop Application also needs to access the database.
The problem is that I now have to remove IP address restrictions to let the different users access the database with the Desktop App.
Is there a way to keep the database safe while making it available widely?
You can manage restrictions at firewall level: specific ip address, and/or specific LAN subnet. Alternatively, you can set your desktop application to go through the web service. You would define specific user names and passwords to be used by your application, that do not correspond to userid’s used to authenticate on the workstations or the LAN.
The idea is to only allow LAN access to the database, either direct or through the webservice (which then acts as a middleware). Any external access has to go through the web application. Also, only specific user/password can access the database, and these are defined specifically for your applications. You may keep them in an encrypted SQLite database that the application can read prior to loging into the PostgreSQL server.
There are probably better approaches, but these ones are fairly quick and easy to implement.
I’m already adding the new users IP addresses to the firewall to give them access. It is manageable since I don’t have so many users for the desktop app. I find it annoying though. Using the web service seems more promising…
We already do that for the companion Mobile App but there is a limited number of calls used for authentication and synchronization. Would using the web service mean that instead of using a database connexion, each and every access to the database would become 2 “API Calls” for each sql request?
The desktop app would authenticate to get a cookie, then each subsequent request would validate against that cookie. There are “best practices” for how to create and manage those cookies that will help ensure the connection is secure.