PostgreSQL on AL2023 (AWS EC2)

This guide explains how to configure PostgreSQL on an AWS EC2 Instance running Amazon Linux (AL2023).

NOTE

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

NOTE

You can follow the steps in this guide as written, but replace the following placeholders with your own names:

• <POSTGRES EC2 Private IP Address>: Your EC2 Instance's Private IP Address
• <Other EC2 Private IP>: Your other EC2 Instance's Private IP Address
• non_root: Your non-root username
• your-database-name: Your database name
• your-db-username: Your database username

Setup AWS EC2 (AL2023) for PostgreSQL

Launch an EC2 Instance

Navigate to the EC2 Dashboard in your AWS Console and launch a new instance with the following parameters:

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	Select your Key Pair (.pem)
VPC	Create and select a 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 and Configure a NAT Gateway

Since the PostgreSQL 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 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

Connect and Upgrade Packages

Since the PostgreSQL server is accessible only within the VPC, you must first SSH into another server within the same VPC that resides in a Public Subnet.

From within that public server, ensure your .pem file has the correct permissions and SSH into the Postgres server:

# Permissions on pem file has to be 400, else it won't work
chmod 400 /path/to/your-key-pair.pem
# Replace <POSTGRES EC2 Private IP Address> below
# Notice the user for AL2023-based instances is `ec2-user` and not `admin`
ssh -i /path/to/your-key-pair.pem ec2-user@<POSTGRES EC2 Private IP Address>

Upgrade the packages on the server:

sudo yum update -y

Set Timezone

All new EC2 servers are set to UTC time by default. To change it to IST, use:

sudo timedatectl set-timezone 'Asia/Kolkata'

Confirm the date by running the date command in the terminal.

Set Up Non-Root User

AWS AMIs disable root password login by default. However, it is still best practice to create your own dedicated limited user account.

First, create a limited user account:

sudo adduser non_root
# You'll be prompted to provide password

Add the new user to the wheel group for administrative privileges (Amazon Linux uses wheel instead of sudo):

sudo usermod -aG wheel non_root

To allow the new user to log in using the same SSH key you used for the ec2-user account, copy the authorized keys directory and update the ownership:

sudo rsync --archive --chown=non_root:non_root ~/.ssh /home/non_root

Exit the session and SSH back into the server as your new user:

exit
ssh -i /path/to/your-key-pair.pem non_root@<POSTGRES EC2 Private IP Address>

Create an SSH directory and add the public key from the other EC2 instance in your VPC to the authorized keys file:

mkdir ~/.ssh && vi ~/.ssh/authorized_keys

AWS already disables root login and password authentication by default, but you can confirm this by checking the SSH configuration file:

sudo vi /etc/ssh/sshd_config
# Confirm that `PasswordAuthentication` is set to `no`
# Confirm that `PermitRootLogin` is set to `no`
# Confirm that `AddressFamily` is set to `inet` (to disable IPv6 connections)

Finally, restart the SSH service to apply the changes, if required:

sudo systemctl restart sshd

Install and Configure PostgreSQL

Install the Postgres packages:

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

Initialize the database and start the 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 scram-sha-256
# Also, add Public Server's Private IP and set the authentication method to scram-sha-256 (see the below line)
# host your-database-name your-db-username <Other EC2 Private IP>/32 scram-sha-256
# You may need to allow SSH from the other EC2 Instance in the private server's security group.

Enable Connections from External Addresses:

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

Restart the PostgreSQL Service:

sudo systemctl restart postgresql

Release NAT Gateway

TIP

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

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 to this guide to set up your specific database and user credentials.

(Optional) Backup and Restore

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

Refer to this guide for instructions on transferring your data.

You should now have a working PostgreSQL database server securely isolated within your VPC.