Score:1

Benefits of separate RDBMS server

de flag

I'd like to compare the benefits of two hosting architectures for a small server (meaning a 2-core CPU and 250GB SSD, with 100GB dedicated for data, is more than enough), consisting mostly of a custom application providing web services, with a REST architecture and all the data in a relational database.

  1. Hosted on a single machine in the cloud (e.g. an EC2 instance) with a Relational Database (e.g. mySQL, PostGreSQL) running locally, with frequent encrypted database external backups (e.g. to S3/Glacier).
  2. Hosted on a machine in the cloud (e.g. a similar EC2 instance with less SSD) with the RDBMS a managed service, e.g. Amazon Relational Database Service, perhaps with less frequent encrypted database external backups.

I see as benefits for 1

  • Cheaper (saving on the RDBMS managed service and network) by a factor of like 3, I believe.
  • Possibly better latency of database accesses ?

and for 2

  • If the EC2 instance is obliterated, we won't loose data.
  • No need to copy the DB from one EC2 instance to another when rebuilding the server from scratch, or making a major upgrade like OS change; thus less downtime.
  • Perhaps easier administration of the DB.

What do I miss? Any pointers to authoritative position on this?

pmdba avatar
cn flag
From a security perspective, data should _never_ be hosted on the same server as the application front end - especially so if the front end is exposed to the internet, but also true on private networks. Databases should be protected behind a firewall, separate from the application servers.
fgrieu avatar
de flag
@pmdba: I see your point, and it's interesting. Still, if the server with the application front end is compromised, and since it has the credentials to access the RDBMS, all the data in the RDBMS can in principle be exfiltrated or/and corrupted in scenario 1/2 alike (with the difference that it can be thru the file system in 1 only). Only firm difference I see is that in scenario 2 the exfiltration/corruption is visible in the RDBMS logs, when these can be altered or nuked with the rest in scenario 1.
pmdba avatar
cn flag
A secure app design would have the app use an unprivileged DB account, accessing data through a back-end API (built using stored procedures in the DB). The app should never log directly into a privileged DB account, and especially not the account that owns the data or objects.
Score:1
de flag

Regarding the first point:

  • When your web service is compromised, the database will be compromised as well. The best practice is to separate the presentation layer from the data layer.

  • the latency with the same or even between two AZs is minor and can be omitted from the considerations.

  • I wouldn't say that this solution will be much cheaper than the RDS because the relevant factor impacting the price would be the SSD storage. The price would be approx 30% between RDS and EC2.

  • Regarding the SSD type, you have to decide or maybe you have already known what IOPS and MB/s level at peaks your database would need in the future. That would be also a good candidate to improve the final prices of the solution.

Regarding the second point:

  • You can prevent that situation. By default, when you attach a non-root EBS volume to an instance, DeleteOnTermination attribute is set to false. I assume the database would be located on non-root EBS volume. The flag might be set up to false on root volume as well.

  • you don't have to copy a database from EC2 to EC2, you may attach or detach the EBS volume with the database.

  • as you pointed out in the RDS solution the administrative responsibility is on AWS. However, the more complex schema of a database the more I would opt for a non-managed database service.

You didn't mention what is the key factor for the architecture. The cost or operation management savings? The price will differ significantly when you decide on the type of pricing model: no upfront vs all upfront. I don't know whether this service is going to work within working hours and could be shut down during the night. You didn't also mention what is going to run on the web services. Is it crucial for the business? Maybe a good and cost-effective solution would be to consider the serverless architecture if the web service is an event-based app.

fgrieu avatar
de flag
I don't understand why the situation is much better from a security standpoint with the separate RBS. I reason that if the web service is compromised, since it has R/W access to the database, an adversary can make an extraction or alteration. I do acknowledge one difference: the whole DB can be copied thru the file system only when the RBS is running locally. An encrypted DB mitigates this to some degree.
de flag
Let's assume I am a hacker. I break into your EC2 instance where the web service along with the database is running. Let's also assume that you didn't notice that the security incident had in place. I would install a sniffer program and keylogger and would wait for a notification. When a database administrator logs in or someone performs SQL commands locally by mistake I will intercept credentials. I would log in again and dump your database. This is hard to do when your presentation layer talks to the database layer via HTTPS proxy and public/private keys are not located in the same place.
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.