## Task 35

The Nautilus DevOps team needs a new private RDS instance for their application. They need to set up a MySQL database and ensure that their existing EC2 instance can connect to it. This will help in managing their database needs efficiently and securely.

1) Task Details:

Create a private RDS instance named xfusion-rds using a sandbox template.
The engine type must be MySQL v8.4.5, and it must be a db.t3.micro type instance.
The master username must be xfusion_admin with an appropriate password.
The RDS storage type must be gp2, and the storage size must be 5GiB.
Create a database named xfusion_db.
Keep the rest of the configurations as default. Ensure the instance is in available state.
Adjust the security groups so that the xfusion-ec2 instance can connect to the RDS on port 3306 and also open port 80 for the instance.
2) An EC2 instance named xfusion-ec2 exists. Connect to this instance from the AWS console. Create an SSH key (/root/.ssh/id_rsa) on the aws-client host if it doesn't already exist. Add the public key to the authorized keys of the root user on the EC2 instance for password-less SSH access.

3) There is a file named index.php under the /root directory on the aws-client host. Copy this file to the xfusion-ec2 instance under the /var/www/html/ directory. Make the appropriate changes in the file to connect to the RDS.

4) You should see a Connected successfully message in the browser once you access the instance using the public IP.


### Solution

# xfusion-rds + EC2 + PHP Task

Big multi-part one — RDS + SSH bootstrap + a PHP app wired to the DB. The engine version is **pinned by the task to 8.4.5**, so use exactly that (no "latest" substitution — the grader wants `8.4.5`, which is a valid 8.4 minor).

Same CLI-template caveat as before: "sandbox" isn't a CLI flag — it's a console wizard preset that maps to single-AZ / minimal / no deletion-protection. You reproduce it with the individual flags.

## Phase 1 — Discover the EC2, build the RDS security group

```bash
REGION=us-east-1

# EC2 identity, its SG, VPC, AZ, public IP
EC2_IID=$(aws ec2 describe-instances \
  --filters "Name=tag:Name,Values=xfusion-ec2" "Name=instance-state-name,Values=pending,running,stopping,stopped" \
  --region $REGION --query 'Reservations[0].Instances[0].InstanceId' --output text)
EC2_SG=$(aws ec2 describe-instances --instance-ids $EC2_IID --region $REGION \
  --query 'Reservations[0].Instances[0].SecurityGroups[0].GroupId' --output text)
VPC_ID=$(aws ec2 describe-instances --instance-ids $EC2_IID --region $REGION \
  --query 'Reservations[0].Instances[0].VpcId' --output text)
EC2_AZ=$(aws ec2 describe-instances --instance-ids $EC2_IID --region $REGION \
  --query 'Reservations[0].Instances[0].Placement.AvailabilityZone' --output text)
EC2_IP=$(aws ec2 describe-instances --instance-ids $EC2_IID --region $REGION \
  --query 'Reservations[0].Instances[0].PublicIpAddress' --output text)

# Dedicated SG for RDS, allow 3306 ONLY from the EC2's SG
RDS_SG=$(aws ec2 create-security-group \
  --group-name xfusion-rds-sg \
  --description "MySQL 3306 from xfusion-ec2" \
  --vpc-id $VPC_ID --region $REGION \
  --query 'GroupId' --output text)

aws ec2 authorize-security-group-ingress \
  --group-id $RDS_SG --protocol tcp --port 3306 \
  --source-group $EC2_SG --region $REGION

# Open port 80 on the EC2 itself (for the web page)
aws ec2 authorize-security-group-ingress \
  --group-id $EC2_SG --protocol tcp --port 80 --cidr 0.0.0.0/0 \
  --region $REGION 2>/dev/null || true
```

The **`--source-group $EC2_SG`** is the clean pattern: RDS accepts 3306 only from instances wearing the EC2's SG, not the whole internet. That's the "adjust SGs so xfusion-ec2 can connect to RDS on 3306" requirement, least-privilege.

## Phase 2 — Create the private RDS instance

```bash
RDS_PASS='Xfusion_Adm1n#2026'   # no / @ " or spaces

aws rds create-db-instance \
  --db-instance-identifier xfusion-rds \
  --engine mysql \
  --engine-version 8.4.5 \
  --db-instance-class db.t3.micro \
  --storage-type gp2 \
  --allocated-storage 5 \
  --master-username xfusion_admin \
  --master-user-password "$RDS_PASS" \
  --db-name xfusion_db \
  --vpc-security-group-ids $RDS_SG \
  --no-publicly-accessible \
  --no-multi-az \
  --region $REGION

# RDS is slow — wait it out
# aws rds wait db-instance-available \
#   --db-instance-identifier xfusion-rds --region $REGION
STATUS=""
until [ "$STATUS" = "available" ]; do
  STATUS=$(aws rds describe-db-instances \
    --db-instance-identifier xfusion-rds --region $REGION \
    --query 'DBInstances[0].DBInstanceStatus' --output text)
  echo "xfusion-rds: $STATUS"
  [ "$STATUS" = "available" ] || sleep 10
done

# Grab the endpoint for the PHP config
RDS_ENDPOINT=$(aws rds describe-db-instances \
  --db-instance-identifier xfusion-rds --region $REGION \
  --query 'DBInstances[0].Endpoint.Address' --output text)
echo "RDS endpoint: $RDS_ENDPOINT"
```

Requirement to flag mapping:

- **`--db-name xfusion_db`** creates the initial database at provision time (req #5 — don't skip it, adding a DB later means a separate mysql connection).
- **`--storage-type gp2 --allocated-storage 5`** = gp2 / 5 GiB.
- **`--no-publicly-accessible`** = private.
- **`--no-multi-az`** + minimal flags = the sandbox template shape.
- **`--vpc-security-group-ids $RDS_SG`** attaches the SG from Phase 1 so the EC2 can reach it.

## Phase 3 — SSH key + passwordless root access to the EC2

On `aws-client`:

```bash
# 1. Generate id_rsa only if missing
[ -f /root/.ssh/id_rsa ] || ssh-keygen -t rsa -b 4096 -f /root/.ssh/id_rsa -N "" -q
PUBKEY=$(cat /root/.ssh/id_rsa.pub)

# 2. Push the key to ec2-user via EC2 Instance Connect (60s TTL)
aws ec2-instance-connect send-ssh-public-key \
  --instance-id $EC2_IID \
  --instance-os-user ec2-user \
  --ssh-public-key file:///root/.ssh/id_rsa.pub \
  --availability-zone $EC2_AZ \
  --region $REGION

# 3. Within 60s: hop in as ec2-user, plant the key into ROOT's authorized_keys
ssh -o StrictHostKeyChecking=no -i /root/.ssh/id_rsa ec2-user@$EC2_IP "
  sudo mkdir -p /root/.ssh && sudo chmod 700 /root/.ssh
  echo '$PUBKEY' | sudo tee -a /root/.ssh/authorized_keys >/dev/null
  sudo chmod 600 /root/.ssh/authorized_keys
  echo 'PermitRootLogin prohibit-password' | sudo tee /etc/ssh/sshd_config.d/99-root.conf >/dev/null
  sudo systemctl restart sshd
"

# 4. Now passwordless root SSH works:
ssh -i /root/.ssh/id_rsa root@$EC2_IP hostname
```

"Connect from the AWS console" = EC2 Instance Connect, which is exactly what `send-ssh-public-key` does programmatically — pushes a short-lived key so you can get on the box the first time, then you persist your real key into **root's** authorized_keys (the task specifically wants root, not ec2-user).

## Phase 4 — Install the web stack, deploy & wire index.php

The EC2 needs Apache + PHP + the MySQL PHP driver, or the page won't render or connect. Do it over the root SSH you just set up:

```bash
ssh -i /root/.ssh/id_rsa root@$EC2_IP "
  apt-get update -y
  apt-get install -y apache2 php libapache2-mod-php php-mysql
  systemctl enable --now apache2
"

# Stage a local copy of index.php and look at what placeholders it uses
cp /root/index.php /tmp/index.php
cat /tmp/index.php
```

KodeKloud's `index.php` uses `$dbhost`, `$dbuser`, `$dbpass`, `$dbname` as single-quoted placeholders (`'<dbhost>'`, etc.). Patch the staged copy in-place — then upload the finished file:

```bash
sed -i \
  -e "s/\$dbhost *= *.*/\$dbhost = '$RDS_ENDPOINT';/" \
  -e "s/\$dbuser *= *.*/\$dbuser = 'xfusion_admin';/" \
  -e "s/\$dbpass *= *.*/\$dbpass = '$RDS_PASS';/" \
  -e "s/\$dbname *= *.*/\$dbname = 'xfusion_db';/" \
  /tmp/index.php

# Upload the already-patched file — no remote editing needed
scp -i /root/.ssh/id_rsa /tmp/index.php root@$EC2_IP:/var/www/html/index.php

ssh -i /root/.ssh/id_rsa root@$EC2_IP "systemctl restart apache2"
```

If the file's variable names differ, don't fight sed — just rewrite the connection block directly. A known-good minimal version matching this file's actual structure:

```php
<?php
$dbname = 'xfusion_db';
$dbuser = 'xfusion_admin';
$dbpass = 'REPLACE_RDS_PASS';
$dbhost = 'REPLACE_RDS_ENDPOINT';

$link = mysqli_connect($dbhost, $dbuser, $dbpass) or die("Unable to Connect to '$dbhost'");
mysqli_select_db($link, $dbname) or die("Could not open the db '$dbname'");

echo "Connected successfully<br />\n";
?>
```

Keep whatever surrounding HTML the original had; only the connection params must be real.

## Verify

```bash
# RDS up, correct spec
aws rds describe-db-instances --db-instance-identifier xfusion-rds --region $REGION \
  --query 'DBInstances[0].{Status:DBInstanceStatus,Ver:EngineVersion,Class:DBInstanceClass,Storage:StorageType,Size:AllocatedStorage,Public:PubliclyAccessible,DB:DBName}'

# The actual end-to-end proof — hit the page
curl -s "http://$EC2_IP/index.php"
```

Want the describe showing `available`, `8.4.5`, `db.t3.micro`, `gp2`, `5`, `Public: false`, `DB: xfusion_db` — and `curl` returning **`Connected successfully`**. That last string is the whole task's success signal: it means PHP on the EC2 reached the private RDS over 3306 through the SG you wired. Open `http://<EC2_IP>` in a browser for the same.

## Failure-mode debug order

If you *don't* see "Connected successfully":

1. **`Connection failed` with a timeout** → SG issue: confirm `RDS_SG` allows 3306 from `EC2_SG` and RDS actually has `RDS_SG` attached (`describe-db-instances ... VpcSecurityGroups`).
2. **`Connection failed` with access-denied** → wrong username/password in index.php, or you fat-fingered `$RDS_PASS`.
3. **Blank page / PHP source shown** → `php`/`php-mysqlnd` not installed or httpd not restarted after install.
4. **Can't reach the page at all** → port 80 not open on `EC2_SG`, or httpd not running.

Wired as above it resolves clean.