Skip to main content

MultiProjectSharedDBInstance

"Multi-App, Single RDS Instance: A Cost-Effective & Scalable Approach"​

1. Introduction​

  • When running multiple projects, spinning up a new RDS instance for every app is overkill and expensive.
  • Instead, you can use one RDS instance while keeping each app's data completely separate with its own database, users, and permissions.
  • This approach allows easy cost optimization while preserving clean database isolation.

2. Architecture Overview: One Instance, Many Independent Databases​

  • ✅ One RDS instance hosts multiple databases.
  • ✅ Each database has its own admin user
    • This is the user you will user you run migrations, while you could just do this with the user for the instance, its safer to create an admin user per db. (Just to prevent mixups)
  • ✅ Each database has its own app user with tailored permissions.
    • This is the user your application will connect with
  • ✅ Each app runs in its own EC2 instance (or multiple Go apps per EC2).
  • ✅ Reverse proxy directs traffic at the entry point.

Example Setup​

RDS InstanceDatabaseApp UserAdmin User
rds-shared-instanceproject_a_dbproject_a_appproject_a_admin
rds-shared-instanceproject_b_dbproject_b_appproject_b_admin
rds-shared-instanceproject_c_dbproject_c_appproject_c_admin
  • No cross-access between databases.
  • Each app connects only to its assigned database using its dedicated app user.

Quick Notes About How PostgreSQL Handles Users in an RDS Instance​

  • Users (Roles) are global to the RDS instance, not just to individual databases.
  • But you can grant different privileges per database to restrict access.
  • A user with CREATEDB permission can create new databases but won’t automatically have access to them.

How To Create an Admin User Per Database​

Details
1. Create a New Database (if not already created)​
CREATE DATABASE project_a_db;
2. Create an Admin User for That Database​
CREATE USER project_a_admin WITH PASSWORD 'supersecurepassword';
3. Grant Full Control Over Just That Database​
GRANT ALL PRIVILEGES ON DATABASE project_a_db TO project_a_admin;
4. Ensure the Admin User Can Manage Schema & Tables​

Log in as the postgres (superuser) and run:

\c project_a_db  -- Switch to the project_a_db database
ALTER DEFAULT PRIVILEGES FOR ROLE project_a_admin IN SCHEMA public GRANT ALL ON TABLES TO project_a_admin;
GRANT ALL ON SCHEMA public TO project_a_admin;

At this point: ✅ project_a_admin has full control over project_a_db.
✅ But it cannot access any other database in the instance unless explicitly granted access.

Limitations & Considerations​

  1. Users Are Global in PostgreSQL

    • Even though project_a_admin can only access project_a_db, it still exists in the pg_roles table at the instance level.
    • This means someone could list all users (SELECT * FROM pg_roles;), even if they don’t have access to other DBs.

  2. RDS Superuser Restrictions

    • AWS does not give you a full postgres superuser on RDS.
    • Some commands like CREATE EXTENSION require extra permissions (handled via rds_superuser role).

  3. Connection Limits

    • RDS has a max total connection limit across the instance.
    • If every database has its own admin user, ensure they don’t hold open unnecessary connections.

Creating An App User (Obviously also each database needs this)​

To create an app user with a reasonable set of privileges, check out each of these articles:

  • Pk_ToLink - Project Template - create app user (setup_db.sh)
  • Pk_ToLink - Project Tepalte - apply_priv (already have article in PostgreSQL folder)

3. Why This Approach Works Well​

✅ Cost-efficient – One RDS instance instead of many, reducing AWS costs.
✅ Security & Isolation – Separate databases, app users, and admin users ensure no unwanted cross-access.
✅ Simplifies management – Easier to track DB usage, permissions, and access.
✅ Scalable – If a database needs its own instance later, it’s easy to migrate.

4. Scaling Strategy: Moving an App to a Dedicated RDS Instance​

When an app outgrows the shared RDS instance, follow this simple migration plan:

1️⃣ Provision a new RDS instance

CREATE DATABASE project_a_db_new;

2️⃣ Copy data from the shared instance

pg_dump -h shared-rds -U project_a_admin -d project_a_db | psql -h new-rds -U project_a_admin -d project_a_db_new

3️⃣ Update the app configuration to point to the new database.
4️⃣ Test and verify all queries and user permissions.
5️⃣ Decommission the old database once everything is stable.

🚀 No app downtime if done correctly (can be done with read replicas or a temporary cutover strategy).

5. Alternative Approaches & Trade-offs​

ApproachProsCons
One RDS, multiple databases (this model)✅ Cost-efficient ✅ Easy to migrate ✅ Database-level isolation❌ Shared compute/I/O resources
Multiple RDS instances (one per app)✅ Full isolation ✅ No shared resources❌ More Expensive

6. Conclusion​

  • Using one RDS instance with multiple isolated databases is a smart, cost-efficient strategy for running multiple projects.
  • Scaling is simple—when an app needs its own instance, migrate and detach.
  • This approach avoids premature over-engineering while keeping future growth in mind.