About

An example Amazon RDS for PostgreSQL database that can be used from an AWS EC2 Ubuntu Virtual Machine.

NB For an Amazon RDS Aurora PostgreSQL example see the rgl/terraform-aws-rds-aurora-postgres-example repository.

This will:

• Use the Amazon RDS for PostgreSQL service.
  • Create a Database Instance.
• Create an example Ubuntu Virtual Machine.
  • Can be used to access the Database Instance.
• Create a VPC and all the required plumbing required for the Ubuntu Virtual Machine to use an Amazon RDS PostgreSQL Database Instance.

Usage (on a Ubuntu Desktop)

Install the tools:

./provision-tools.sh

Set the account credentials using SSO:

# set the account credentials.
# see https://docs.aws.amazon.com/cli/latest/userguide/sso-configure-profile-token.html#sso-configure-profile-token-auto-sso
aws configure sso
# dump the configured profile and sso-session.
cat ~/.aws/config
# set the environment variables to use a specific profile.
export AWS_PROFILE=my-profile
unset AWS_ACCESS_KEY_ID
unset AWS_SECRET_ACCESS_KEY
unset AWS_DEFAULT_REGION
# show the user, user amazon resource name (arn), and the account id, of the
# profile set in the AWS_PROFILE environment variable.
aws sts get-caller-identity

Or, set the account credentials using an access key:

# set the account credentials.
# NB get these from your aws account iam console.
#    see Managing access keys (console) at
#        https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_access-keys.html#Using_CreateAccessKey
export AWS_ACCESS_KEY_ID='TODO'
export AWS_SECRET_ACCESS_KEY='TODO'
# set the default region.
export AWS_DEFAULT_REGION='eu-west-1'
# show the user, user amazon resource name (arn), and the account id.
aws sts get-caller-identity

Review main.tf.

Initialize terraform:

make terraform-init

Launch the example:

rm -f terraform.log
make terraform-apply

Show the terraform state:

make terraform-show

At VM initialization time cloud-init will run the provision-app.sh script to launch the example application.

After VM initialization is done (check the instance system log for cloud-init entries), test the app endpoint:

while ! wget -qO- "http://$(terraform output --raw app_ip_address)/test"; do sleep 3; done

And open a shell inside the VM:

ssh "ubuntu@$(terraform output --raw app_ip_address)"
cloud-init status --wait
less /var/log/cloud-init-output.log
systemctl status app
journalctl -u app
exit

Try accessing the PostgreSQL Database Instance, from within the AWS VPC, using psql:

ssh "ubuntu@$(terraform output --raw app_ip_address)" \
  LC_ALL='C.UTF-8' \
  PGSSLMODE='verify-full' \
  PGHOST="$(printf '%q' "$(terraform output --raw db_address)")" \
  PGDATABASE='postgres' \
  PGUSER="$(printf '%q' "$(terraform output --raw db_admin_username)")" \
  PGPASSWORD="$(printf '%q' "$(terraform output --raw db_admin_password)")" \
  psql \
    --echo-all \
    --no-password \
    --variable ON_ERROR_STOP=1 \
    <<'EOF'
-- show information the postgresql version.
select version();
-- show information about the current connection.
select current_user, current_database(), inet_client_addr(), inet_client_port(), inet_server_addr(), inet_server_port(), pg_backend_pid(), pg_postmaster_start_time();
-- show information about the current tls connection.
select case when ssl then concat('YES (', version, ')') else 'NO' end as ssl from pg_stat_ssl where pid=pg_backend_pid();
-- list roles.
\dg
-- list databases.
\l
EOF

Open an interactive psql session, show the PostgreSQL version, and exit:

ssh -t "ubuntu@$(terraform output --raw app_ip_address)" \
  LC_ALL='C.UTF-8' \
  PGSSLMODE='verify-full' \
  PGHOST="$(printf '%q' "$(terraform output --raw db_address)")" \
  PGDATABASE='postgres' \
  PGUSER="$(printf '%q' "$(terraform output --raw db_admin_username)")" \
  PGPASSWORD="$(printf '%q' "$(terraform output --raw db_admin_password)")" \
  psql
select version();
exit

Destroy the example:

make terraform-destroy

List this repository dependencies (and which have newer versions):

GITHUB_COM_TOKEN='YOUR_GITHUB_PERSONAL_TOKEN' ./renovate.sh

References

• Environment variables to configure the AWS CLI
• Token provider configuration with automatic authentication refresh for AWS IAM Identity Center (SSO)
• Managing access keys (console)
• AWS General Reference
  • Amazon Resource Names (ARNs)
• Connect to the internet using an internet gateway
• Retrieve instance metadata
• How Instance Metadata Service Version 2 works
• Amazon RDS for PostgreSQL service
• Amazon RDS for PostgreSQL resources
• Amazon RDS for PostgreSQL
• Common DBA tasks for Amazon RDS for PostgreSQL
• Using SSL with a PostgreSQL DB instance
• Using SSL/TLS to encrypt a connection to a DB instance or cluster
• PostgreSQL Environment Variables
• PostgreSQL System Information Functions and Operators