PostgreSQL on AL2023 EC2

This setup utilizes a Private Subnet to restrict database access exclusively within the VPC. This significantly reduces the risk of unauthorized access to the database.

Launch EC2 Instance for Postgres

Parameter	Value
Region	ap-south-2 (Hyderabad)
Name	Give your preferred name
OS	Amazon Linux (Amazon Linux 2023 as of 2-Oct-2023)
Architecture	64-bit (Arm)
EC2 Type	m6g.large
EC2 KeyPair	PirateDev.pem
VPC	Select PirateDev VPC
Subnet	Select a private subnet since Postgres Server can't be accessed outside the VPC
Auto-assign Public IP	Disable
Security Group	Select Postgres Security Group that allows SSH and PSQL only within VPC
EBS Size	8GB gp3 (Encrypted - using KMS key) (Min size and can't be downgraded after setup)
Advanced	Enable Termination Protection

Create a NAT gateway

Since the Postgres server resides in a Private Subnet, it doesn't have direct internet access to download required packages. However, temporary internet connectivity is necessary to install the relevant Postgres packages.

To achieve this, you need to create a NAT Gateway in a Public Subnet and update the Private Subnet Route Table to direct traffic through the NAT Gateway.

Steps to Configure the NAT Gateway

• Navigate to VPC Console and Select NAT gateways from the sidebar.
• Click Create NAT gateway and configure the following:
  • Name: Give your preferred name
  • Subnet: Choose a Public Subnet from the same VPC as the Postgres Server
  • Connectivity Type: Public
  • Click Allocate Elastic IP to assign a public IP address
• Go to the Route Tables section in the VPC Console. Locate and select the Route Table associated with the Private Subnet of the Postgres server. Add a new route entry:
  • Destination: 0.0.0.0/0
  • Target: The newly created NAT Gateway

Connecting to Postgres Instance

Since the Postgres Server is accessible only within the VPC, you need to first SSH into another server of the VPC, that resides in the Public Subnet.

Steps to Connect

• SSH into the public server.
• From within the public server, SSH into Postgres Server using your .pem file:

# Permissions on pem file has to be 400, else it won't work
chmod 400 "PirateDev.pem"
# Replace <POSTGRES EC2 Private IP address> below
ssh -i "PirateDev.pem" ec2-user@<POSTGRES EC2 Private IP address>

• Upgrade the packages on the server:

sudo yum update

Set timezone

Set the server to IST timezone using:

sudo timedatectl set-timezone 'Asia/Kolkata'

Confirm date by running date command in the terminal.

Disable Root Login

WARNING

A new limited user account is not created and ec2-user is being used, since Apache's mod_wsgi runs into permission errors otherwise.

• Add SSH Public key (of Admin System that needs SSH into this server) to authorized keys of the new user:

sudo vi ~/.ssh/authorized_keys

• Exit and SSH as the new user using password:

exit
ssh ec2-user@<EC2 IP>

• Disable Root login and Password Authentication:

sudo vi /etc/ssh/sshd_config
# Set `PasswordAuthentication` to `no`
# Set `PermitRootLogin` to `no`
# Set `AddressFamily` to `inet` (to disable IPv6 connections)

• Restart SSH service:

sudo systemctl restart sshd

Install and configure Postgres

• Install Postgres:

# Replace 15 with the latest version if applicable.
sudo yum -y install postgresql15-server

• Initialize the database and start Postgres service:

# DB Initialization
sudo postgresql-setup --initdb

# Enabling and starting Postgres service
sudo systemctl enable postgresql
sudo systemctl start postgresql

• Update Authentication method
  • By default, the postgres role is created with the authentication method set to peer for local connections. Update it to trust.

sudo vi /var/lib/pgsql/data/pg_hba.conf
# Change postgres local connections from peer to trust
# Change all other local connections from peer to md5
# Also, add Public Server's Private IP and set the authentication method to md5 (see the below line)
# host piratdev piratdev <Public Server Private IP>/32 md5
# Public Server's private IP will do since they are part of same VPC

• Enable Connections from External Addresses:

sudo vi /var/lib/pgsql/data/postgresql.conf
# Change listen_addresses to '*'

• Restart PostgreSQL Service:

sudo systemctl restart postgresql

Release NAT Gateway

TIP

The NAT Gateway is a paid resource, so it needs to be released.

Now that Postgres is installed and configured, the NAT Gateway is no longer required. Follow these steps to release it:

• Navigate to the NAT Gateways section in the VPC Console.
• Select the NAT Gateway and choose Actions > Delete NAT Gateway.
• Release the associated Elastic IP Address:
  • Go to the Elastic IPs section in the VPC Console.
  • Select the Elastic IP allocated to the NAT Gateway and release it.
• Update the Route Table for the Private Subnet of the Postgres Server:
  • Remove the 0.0.0.0/0 route entry pointing to the NAT Gateway.

Create Database and User

Refer this doc

(Optional) Backup and Restore

This section is applicable only if you need to migrate data from an existing database hosted on another server to the new database.

Refer this doc

🎉 Congrats! You should have a working Postgres Server now.