Amazon doesn't talk about this much and the servers are intentionally locked down, so it's hard to be completely sure.
They're EC2 instances that run a custom AMI and have automation tools - in-house, or something like Puppet/Chef/etc. These automation tools communicate with the AWS control panel over web service APIs, SSH push access, etc, and are responsible for managing the PostgreSQL configuration, starting/stopping/reloading the server, etc.
Each EC2 instance runs a single PostgreSQL database server, with its own users, roles, etc.
It's basically just a sealed AWS EC2 instance that you don't have much access to, you just get a locked down non-superuser PostgreSQL connection. Nothing magic.
This isn't the only way to do it. Heroku used to use OpenVZ on top of EC2 to partition EC2 instances into smaller containers, for example. I think these days they always have one EC2 instance per database though.
It sounds like what you want is multi-tenant hosting. You have many options for this:
- One server per user with a single PostgreSQL instance on each server (EC2 or Heroku style)
- one PostgreSQL instance per user on a single host server;
- one database per user on a single PostgreSQL instance;
- one schema per user in a single PostgreSQL database;
- a single set of tables with your application limiting access to data within the tables based on enforced
WHERE
clauses or row-level security policies.
Which to choose depends on trade-offs involving isolation of users, performance, and cost.
There aren't currently any convenient canned recipes to do this that I know of, but searching for "multi-tenant postgresql" will help you find more information.
Best Answer
The Amazon RDS is marketed as
From one point of view, this is true, it relieves you from a lot of maintenance. On the other hand, it means less flexibility, in our case the lack of access to the filesystem.
The PostgreSQL documentation states:
As you cannot put files onto the machine, this simply won't work.
If you have to do your work on some AWS platform, you can choose an EC2 instance, which you can basically handle like any other machine you have root access to.