Cloudwrxs - PostgreSQL on Microsoft Windows Server 2022

Prerequisite: IAM Role Requirement (Before Launching the Instance) Before launching the EC2 instance from this AMI, you must ensure that an IAM Role with AWS Secrets Manager permissions is attached.

During first boot, the PostgreSQL postgres password is: a. Automatically generated b. Securely stored in AWS Secrets Manager If the instance does not have proper IAM permissions, the password will not be stored.

Option 1: Use Existing Role with SecretsManagerReadWrite (Quick Setup) If you already have an IAM role with the AWS managed policy: SecretsManagerReadWrite You may attach that role to the EC2 instance during launch.

Option 2: Recommended (Least Privilege Policy) Step 1: Create Custom IAM Policy Go to: IAM, Policies, Create Policy, JSON Paste: { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "secretsmanager:CreateSecret", "secretsmanager:PutSecretValue", "secretsmanager:DescribeSecret" ], "Resource": "arn:aws:secretsmanager:<region>:<account-id>:secret:/awsmarketplace/postgres/*" } ] } Save the policy.

Step 2: Create IAM Role for EC2

1. Go to IAM, Roles, Create Role
2. Select AWS Service
3. Choose EC2
4. Attach: a. The custom policy you created b. (Recommended) AmazonSSMManagedInstanceCore
5. Name the role (example: PostgresSecretsRole)
6. Create the role

Step 3: Attach Role During EC2 Launch When launching the instance from the AMI:

1. Go to Advanced Details

2. Under IAM Instance Profile

3. Select the IAM role you created

4. Launch the instance

5. Launch EC2 Instance from AMI Create Instance from AMI. Retrieve the password from the AWS EC2 console by selecting your instance, Click on Connect for selected Instance, select choosing RDP Client and Get Windows Password, and providing your EC2 key pair to decrypt and log in.

6. Connect to the Instance via RDP: Use Remote Desktop Connection (mstsc) on your Windows or Mac machine. In the Computer field, enter: <your-ec2-public-ip> Use the Administrator username and the retrieved password from the AWS EC2 console.

7. Retrieve PostgreSQL Password (Required) The PostgreSQL administrator password is automatically generated during first boot and securely stored in AWS Secrets Manager. The installation-time password is NOT valid.

To Retrieve the Password: a. Go to AWS Console b. Open AWS Secrets Manager c. Locate the secret: /awsmarketplace/postgres/<instance-id> d. Click Retrieve secret value e. Copy the password field Example: { "username": "postgres", "password": "AutoGeneratedSecurePassword", "engine": "postgres", "host": "localhost", "port": 5432 }

4. Verify PostgreSQL Installation After connecting via RDP: Check Version Open PowerShell: psql --version

Connect Using psql & "C:\Program Files\PostgreSQL\17\bin\psql.exe" -U postgres -h localhost Enter the password retrieved from AWS Secrets Manager when prompted.

Connect Using pgAdmin (Optional GUI) a. Open pgAdmin from Start Menu b. Add Server: Host: localhost Username: postgres Password: (retrieved from AWS Secrets Manager)

5. Changing the PostgreSQL Password (Optional) If you change the password manually: a. Open PowerShell as Administrator b. Connect: & "C:\Program Files\PostgreSQL\17\bin\psql.exe" -U postgres -h localhost c. Run: ALTER USER postgres WITH PASSWORD 'NewStrongPasswordHere'; d. Exit: \q If you change the password manually, update the value in AWS Secrets Manager to keep it synchronized.