Install Postgres and create the DB

Switch to the Postgres user (the only user who can login at
this point)

> sudo su
postgres

Create a script called script.sql

> cd

> vi script.sql

And place the following in it

-- Add Users

CREATE USER app_ro WITH PASSWORD 'myPassword';

CREATE USER app_rw WITH PASSWORD 'myPassword';

-- Create DB

CREATE DATABASE myapp;

-- login to the new DB

\c
myapp

--
Revoke all Privileges

REVOKE
ALL ON DATABASE myapp FROM PUBLIC;

REVOKE
ALL ON SCHEMA public FROM PUBLIC;

-- Set up privileges for app_ro

GRANT
CONNECT ON DATABASE myapp to app_ro;

GRANT
SELECT ON ALL TABLES IN SCHEMA public TO app_ro;

GRANT
SELECT ON ALL SEQUENCES IN SCHEMA public TO app_ro;

GRANT
USAGE ON SCHEMA public to app_ro;

-- Set up privileges for app_rw

GRANT CONNECT ON DATABASE myapp to app_rw;

GRANT
SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;

GRANT
SELECT ON ALL SEQUENCES IN SCHEMA public TO app_rw;

GRANT
USAGE ON SCHEMA public to app_rw;

GRANT
SELECT ON ALL SEQUENCES IN SCHEMA public TO app_ro;

GRANT
USAGE ON SCHEMA public to app_ro;

-- Set up privileges for app_ro (for new tables)

ALTER
DEFAULT PRIVILEGES IN SCHEMA public

GRANT SELECT ON TABLES TO app_ro;

ALTER
DEFAULT PRIVILEGES IN SCHEMA public

GRANT SELECT ON SEQUENCES TO app_ro;

-- Set up privileges for app_rw (for new tables)

ALTER
DEFAULT PRIVILEGES IN SCHEMA public

GRANT SELECT, UPDATE, INSERT, DELETE ON
TABLES TO app_rw;

ALTER
DEFAULT PRIVILEGES IN SCHEMA public

GRANT SELECT, UPDATE ON SEQUENCES TO
app_rw;

--Create
tables

CREATE
TABLE message (id serial, msg varchar(1024));

--Insert
some test data

INSERT
INTO message (msg) VALUES ('First Test Message');

INSERT INTO message (msg) VALUES ('Second Test
Message');

This script create a database called myapp with a table
called message and populates that table with two messages. It also creates two users for that
database. app_ro is a read only user and
app_rw is a read write user. Each user has a password of
"myPassword".

Run the following command to run the script.

> psql -U
postgres -d postgres -a -f script.sql

Run the following command to confirm the database has been
created, the table is populated, and the app_ro can read from the database.

Why did I do all that?
Well, I think now that I have an app working in Heroku and talking to
it's database I think it may only be a matter of changing the config file
config/application.json to point the RDS database I am about to make.

Before RDS

I have a Postgres database running locally at home… What if I just open up a port and route it to
this box? Can I get Heroku to use my
database running at home? I am guessing
so, let me try it out.

You need to expose your server to the internet. In my case
route port 5432 from my router to this virtual machine.

After I got this set up I tried to login to Postgres
remotely. (I changed the IP address here
to protect the innocent, in fact I just found out 203.0.113.0 - 203.0.113.253
are example IP addresses and don't exist J much like example.com)

Heroku push

I should get an error, as I am pretty sure Heroku cannot
access my RDS.

Yep.

OK I need to update my Security group for my Database.

Security Group

Click on Security Groups

Click on VPC page.
(The database was put in a VPC network)

Click on Security Groups and then on Create Security Group

Name it. Then select
the VPC the RDS is a part of. (In my
case its easy I have no other VPCs so I just choose the one, if you are unsure
you can check the RDS instance and it will list the VPC it is in)

Click Yes, Create.

Select Inbound Rules and click Edit.

Select Custom TCP Rule.
Set the port Range to 5432 and the Source to 0.0.0.0/0

This effectively says I accept connections from anyone. (They still need a username and password
though)

Click Save

The security group is made and now needs to be applied to
the Database Instance.

Click on Instances.
Select the Database, then from the pull down menu Select Modify.

Select the New Security Group.

Checkbox Apply Immediately
(If you don't the changes will take effect during the next scheduled
maintenance window)