Unrestricted postgresql access remediation

Using Console

To remediate the unrestricted PostgreSQL access issue in AWS, you can follow the below steps:

Go to the AWS Management Console and navigate to the RDS dashboard.
Select the RDS instance that has unrestricted PostgreSQL access.
Click on the “Modify” button.
In the “Network & Security” section, select the “Additional Configuration” tab.
Under “Security Group Rules,” locate the rule that allows unrestricted PostgreSQL access.
Remove the rule by clicking on the “x” icon next to it.
Add a new rule that allows access only from trusted sources.
Click on the “Save Changes” button.

By following these steps, you can remediate the unrestricted PostgreSQL access issue in AWS and ensure that your PostgreSQL database is only accessible from trusted sources.

Using CLI

To remediate unrestricted PostgreSQL access in AWS, you can follow these steps using AWS CLI:

Open the AWS CLI and run the following command to get the security group ID of the security group associated with the PostgreSQL instance:
aws rds describe-db-instances --query 'DBInstances[*].VpcSecurityGroups[*].VpcSecurityGroupId' --output text

Run the following command to get the ID of the security group:

aws ec2 describe-security-groups --filters Name=group-id,Values=<security-group-ID> --query 'SecurityGroups[*].{Name:GroupName,ID:GroupId}' --output table

Run the following command to revoke the unrestricted access to PostgreSQL:
aws ec2 revoke-security-group-ingress --group-id <security-group-ID> --protocol tcp --port 5432 --cidr 0.0.0.0/0
This command will revoke the inbound rule that allows unrestricted access to PostgreSQL.
Run the following command to verify that the unrestricted access has been revoked:
aws ec2 describe-security-groups --filters Name=group-id,Values=<security-group-ID> --query 'SecurityGroups[*].IpPermissions'
This command will show the current inbound rules for the security group. You should see that the rule allowing unrestricted access to PostgreSQL has been removed.

By following these steps, you have successfully remediated the unrestricted PostgreSQL access in AWS.

Using Python

To remediate the unrestricted PostgreSQL access issue in AWS, you can use Python to create a security group that allows access to the PostgreSQL instance only from a specific IP address or range of IP addresses. Here are the steps to do so:

First, you need to create a new security group that will be used to restrict access to the PostgreSQL instance. You can do this using the boto3 library in Python. Here’s an example code snippet that creates a new security group:

import boto3

ec2 = boto3.resource('ec2')

# Create a new security group
security_group = ec2.create_security_group(
    GroupName='PostgreSQLAccess',
    Description='Restrict access to PostgreSQL instance'
)

# Add a rule to allow access to the PostgreSQL port (5432) from a specific IP address
security_group.authorize_ingress(
    IpPermissions=[
        {
            'IpProtocol': 'tcp',
            'FromPort': 5432,
            'ToPort': 5432,
            'IpRanges': [
                {
                    'CidrIp': 'x.x.x.x/32' # Replace x.x.x.x with the specific IP address you want to allow access from
                }
            ]
        }
    ]
)

Once you’ve created the new security group, you need to assign it to the PostgreSQL instance. You can do this using the modify_db_instance method from the boto3 library. Here’s an example code snippet that assigns the new security group to the PostgreSQL instance:

import boto3

rds = boto3.client('rds')

# Modify the PostgreSQL instance to use the new security group
response = rds.modify_db_instance(
    DBInstanceIdentifier='your-instance-id', # Replace with your PostgreSQL instance ID
    VpcSecurityGroupIds=[
        security_group.id
    ]
)

Finally, you can verify that the access to the PostgreSQL instance has been restricted by checking the security group rules associated with the instance. You can do this using the describe_db_instances method from the boto3 library. Here’s an example code snippet that checks the security group rules:

import boto3

rds = boto3.client('rds')

# Get the PostgreSQL instance details
response = rds.describe_db_instances(
    DBInstanceIdentifier='your-instance-id' # Replace with your PostgreSQL instance ID
)

# Get the security group IDs associated with the instance
security_group_ids = response['DBInstances'][0]['VpcSecurityGroups']

# Print the security group rules associated with each security group
for security_group_id in security_group_ids:
    response = ec2.describe_security_groups(
        GroupIds=[
            security_group_id['VpcSecurityGroupId']
        ]
    )
    print(response['SecurityGroups'][0]['IpPermissions'])

This code will print the security group rules associated with each security group, which should show that access to the PostgreSQL port (5432) is only allowed from the specific IP address or range of IP addresses that you specified in the security group rule.

Using Terraform

resource "aws_security_group" "POSTGRES_SG" {
  name        = "POSTGRES_SG_NAME"        # replace with your SG name
  description = "PostgreSQL access from trusted sources only"
  vpc_id      = "VPC_ID"                  # replace with your VPC ID

  # EXAMPLE: allow PostgreSQL only from a trusted IPv4 CIDR
  ingress {
    description = "PostgreSQL from trusted IPv4 range"
    from_port   = 5432
    to_port     = 5432
    protocol    = "tcp"
    cidr_blocks = ["TRUSTED_IPV4_CIDR"]   # e.g. "203.0.113.0/24"
  }

  # OPTIONAL: allow PostgreSQL only from a trusted IPv6 range
  # (omit entirely if you don't need IPv6 access)
  # ingress {
  #   description      = "PostgreSQL from trusted IPv6 range"
  #   from_port        = 5432
  #   to_port          = 5432
  #   protocol         = "tcp"
  #   ipv6_cidr_blocks = ["TRUSTED_IPV6_CIDR"]  # e.g. "2001:db8::/64"
  # }

  # other existing, non-PostgreSQL rules can stay, as long as they
  # do NOT include:
  #   - cidr_blocks      = ["0.0.0.0/0"] with port 5432
  #   - ipv6_cidr_blocks = ["::/0"]      with port 5432

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }

  tags = {
    Name = "POSTGRES_SG_TAG_NAME"        # replace with desired tag
  }
}

This Terraform must not contain any ingress rule with from_port = 5432 / to_port = 5432 and cidr_blocks = ["0.0.0.0/0"] or ipv6_cidr_blocks = ["::/0"]; that matches the CLI revoke-security-group-ingress for those CIDRs.This change updates only the security group rules; it does not replace the security group resource itself, but the affected ingress rules will be destroyed and recreated with the new, restricted CIDRs. This is irreversible in the same sense as the CLI: once applied, the open rule is permanently removed and access will be limited to the trusted ranges you specify.Verification: terraform plan should show ~ (update) on aws_security_group.POSTGRES_SG with - removals of the 0.0.0.0/0 and/or ::/0 ingress rules on port 5432, and + additions (if you defined new restricted rules) for the trusted CIDRs.

AWS Introduction

AWS Pricing

AWS Threats

AWS Misconfigurations

Unrestricted postgresql access remediation

Triage and Remediation

Remediation

​Triage and Remediation

​Remediation

​

Triage and Remediation

Remediation