In this third step we create an RDS instance running Postgres in our VPC using our private subnets. We connect to the database using our Bastion server and create dev and production databases.
We are going to run our database in our private subnets - this should ensure maximum security as there is no way that we can accidentally make our database publicly accessible. The first thing we need to do is create a subnet group from our private subnets:
In the advanced settings of our database we place it in our new VPC and we tell it to use the subnet group that we have just created with our private subnets. We also specify the security group we created in step 1 that gives access to the Postgres port (5432) from our Web Severs security group and our Bastion security group.
We can now create or RDS instance - we’ll create our database manually by logging into our Bastion server (see the video for how to launch this server) and connecting to our new RDS instance.
To create a database we use the following commands:
Connect to the database using the following command - the host should be copied from your RDS instance, the username should be the master username you configured when creating the RDS instance. You will be prompted for the master password.
sudo yum install postgresql95 psql -h [HOST] -U [USERNAME] -d postgres
We will create a dev and prod database using the following commands:
CREATE ROLE dev with encrypted password '[STRONG_PASSWORD1]' LOGIN; GRANT dev TO [MASTER_USERNAME]; CREATE ROLE prod with encrypted password '[STRONG_PASSWORD2]' LOGIN; GRANT prod TO [MASTER_USERNAME]; CREATE DATABASE dev with owner dev; CREATE DATABASE prod with owner prod;
[STRONG_PASSWORD1] and [STRONG_PASSWORD2] should be replace by two different passwords that you generate using a strong password generator. [MASTER_USERNAME] should be replaced by the master username that you set the database instance up with.
Our RDS instance is now ready for use from our Rails application. You can now shut down the Bastion server until you need to perform any other database maintenance jobs.