There are several factors to consider before creating a new RDS instance including which region to create the RDS in and the VPC to use. The difference between regions will affect latency and how the data is transmitted. For example having your RDS in US-west-1 and your ECS and/or Cloud Services in US-east-1 will experience higher latency than having all services under the same region (Under most use cases this is negligible).

To avoid using VPC peering connections,  the RDS should share the same VPC with the ECS and/or Cloud Services.

PostgreSQL RDS

Create RDS Instance

Follow the console instructions to setup a new RDS instance. After the instance is running, you will need to configure the white-list to allow the ECS instances to connect to this database. Below shows examples of what this looks like.

Creating a new PostgreSQL RDS instance, The Internal Endpoint is generated once the White-list is configured.

Configure White-list

Add the IPs of any ECS instance that you want to allow to connect to the RDS instance. Note that you can also grant permissions using Security Group which we are not covering in this example.

Configuring the Whitelist, add all ECS instances that that you want to grant access. Note that the instance should be on the same VPC (assuming there is no VPC peering)

Setup PostgreSQL User

To connect to the PostgreSQL instance, you will also need a user account for the database instance. You will be able to create one "Privileged Account", which has elevated permissions and "Standard Accounts". When running applications, you want to use "Standard Accounts" to create and connect applications with.

Example of a "Standard Account" user. This user represents the PostgreSQL database user.
Once you have a valid PostgreSQL database user, you can create databases under for that user.

At this point you will have configured an functioning RDS instance. The next step is to attempt to connect to the RDS instance.

Setup pgAdmin

PostgreSQL database has a IDE for connecting and managing PostgreSQL databases known as pgAdmin. This IDE is browser based and can be run as a Docker container. Below we will go through the process of how to setup such instance.

The traffic between pgAdmin and the RDS is occurring internally over the Internal Endpoint within the VPC. Regardless, we will setup Transport Layer Security (TLS) for connections between the IDE and RDS. To achieve this we will first create a self-signed certificate for pgAdmin.

Certificate for pgAdmin

To generate certificates for pgAdmin, use the following script:

#!/bin/bash

BASEDIR=$(dirname "$1")
LOGGING_PREFIX="gen_cert.sh >> "

PASSKEY=[certificate_pass_key]

rm -f ${BASEDIR}/server.crt
rm -f ${BASEDIR}/server.csr
rm -f ${BASEDIR}/server.key
rm -f ${BASEDIR}/rootCA.crt
rm -f ${BASEDIR}/rootCA.csr
rm -f ${BASEDIR}/rootCA.key
rm -f ${BASEDIR}/rootCA.srl

# generate a key for our root CA certificate
echo "${LOGGING_PREFIX} Generating key for root CA certificate"
openssl genrsa -des3 -passout pass:${PASSKEY} -out ${BASEDIR}/rootCA.pass.key 2048
openssl rsa -passin pass:${PASSKEY} -in ${BASEDIR}/rootCA.pass.key -out ${BASEDIR}/rootCA.key
rm ${BASEDIR}/rootCA.pass.key

# create and self sign the root CA certificate
echo "${LOGGING_PREFIX} Creating self-signed root CA certificate"
openssl req -x509 -new -nodes -key ${BASEDIR}/rootCA.key -sha256 -days 1024 -out ${BASEDIR}/rootCA.crt -subj "/C=US/OU=sigma/CN=prod-env-CA"
echo "${LOGGING_PREFIX} Self-signed root CA certificate (${BASEDIR}/rootCA.crt) is:"
openssl x509 -in ${BASEDIR}/rootCA.crt -text -noout

# generate a key for our server certificate
#echo "${LOGGING_PREFIX} Generating key for server certificate"
openssl genrsa -des3 -passout pass:${PASSKEY} -out ${BASEDIR}/server.pass.key 2048
openssl rsa -passin pass:${PASSKEY} -in ${BASEDIR}/server.pass.key -out ${BASEDIR}/server.key
rm ${BASEDIR}/server.pass.key

# create a certificate request for our server. This includes a subject alternative name so either aios-localhost, localhost or postgres_ssl can be used to address it
echo "${LOGGING_PREFIX} Creating server certificate"
openssl req -new -key ${BASEDIR}/server.key -out ${BASEDIR}/server.csr -subj "/C=US/OU=sigma/CN=postgres_ssl" 
echo "${LOGGING_PREFIX} Server certificate signing request (${BASEDIR}/server.csr) is:"
openssl req -verify -in ${BASEDIR}/server.csr -text -noout

# use our CA certificate and key to create a signed version of the server certificate
echo "${LOGGING_PREFIX} Signing server certificate using our root CA certificate and key"
openssl x509 -req -sha256 -days 365 -in ${BASEDIR}/server.csr -CA ${BASEDIR}/rootCA.crt -CAkey ${BASEDIR}/rootCA.key -CAcreateserial -out ${BASEDIR}/server.crt -extensions SAN 
chmod og-rwx ${BASEDIR}/server.key
echo "${LOGGING_PREFIX} Server certificate signed with our root CA certificate (${BASEDIR}/server.crt) is:"
openssl x509 -in ${BASEDIR}/server.crt -text -noout

# done output the base64 encoded version of the root CA certificate which should be added to trust stores
echo "${LOGGING_PREFIX} Done. Next time the postgres_ssl docker image is rebuilt the new server certificate (${BASEDIR}/server.crt) will be used."
echo "${LOGGING_PREFIX} Use the following CA certificate variables:"
B64_CA_CERT=`cat ${BASEDIR}/rootCA.crt | base64`
echo "POSTGRES_SSL_CA_CERT=${B64_CA_CERT}"
Replace 'certificate_pass_key' with an actual pass key of your choice.

The above script will generate the following files:

Certificate files generated from the above script.In this example, the files are generated in the demo directory.

Setup Container

Assuming you are not using a reverse proxy and that port 443 is not already used by another application. pgAdmin should start using the Docker command below.

The Certificate generated is used to create a new pgAdmin Docker instance. In this example 'pgadmin' is a custom directory where the certificate files are located. This will allow the user to select the referenced certificate to use for SSL connections to the RDS.

docker run -p 443:443 \
-v ~/pgadmin:/var/lib/pgadmin \
-v ~/pgadmin/server.crt:/certs/server.cert \
-v ~/pgadmin/server.key:/certs/server.key \
-e 'PGADMIN_DEFAULT_EMAIL=[pgadmin_user_email]' \
-e 'PGADMIN_DEFAULT_PASSWORD=[pgadmin_user_pw]' \
-e 'PGADMIN_ENABLE_TLS=True' \
-d dpage/pgadmin4:latest
https://www.pgadmin.org/docs/pgadmin4/4.13/container_deployment.html
The 'Client Certificate' and 'Client Certificate Key' are referencing to the certificate that was generated using the script above.

Connect to instance

If pgAdmin is running on port 443, you should be to navigate to the application. Login to pgAdmin using the pgAdmin credentials. This will then allow you to login to the RDS database instance.

When using a reverse proxy check out the following article on how to configure pgAdmin as a sub-domain.

https://mycloudology.com/create-reverse-proxy-for-domain-and-sub-domain/