Databases

In this section, I configure and secure the PostgreSQL database infrastructure that will support the Threat Intelligence Application. I deploy a private Amazon RDS instance, validate connectivity and configure the required database objects and access controls, then transition PostgreSQL to the production EC2 environment. Database credentials are secured using AWS Secrets Manager and least-privilege IAM access, preparing the infrastructure for later application integration.

#RDS PostgreSQL — Network & Security Setup - Part 1

In this section, I prepared the AWS network environment for an Amazon RDS PostgreSQL database. The database is designed to remain private, with connectivity restricted to the production web server rather than being directly accessible from the internet.

1.Verify Existing Database Subnet

Before creating the RDS DB subnet group, I verified that the existing Private DB Subnet was deployed in eu-north-1a. Because an RDS DB subnet group must span multiple Availability Zones, I needed an additional private database subnet in a different AZ.

-Existing subnet: Private DB Subnet

-Availability Zone: eu-north-1a

-CIDR: 10.0.2.0/24

2.Create the Second Private DB Subnets

I created Private DB Subnet 2 inside the existing projectzero-prod-vpc and placed it in eu-north-1b, a different Availability Zone from the original database subnet. This provides the multi-AZ subnet coverage required for the RDS DB subnet group.

-VPC: projectzero-prod-vpc

-Subnet name: Private DB Subnet 2

-Availability Zone: eu-north-1b

-CIDR: 10.0.3.0/24

3.Configure the RDS Security Group

I created a dedicated security group for the PostgreSQL database and restricted inbound database connectivity to the production web server security group.

-Security group: projectzero-prod-rds-SG

-Protocol: PostgreSQL / TCP

-Port: 5432

-Source: projectzero-prod-websvr-SG

Using the web server's security group as the source instead of an IP range ensures that only EC2 resources associated with the approved security group can initiate PostgreSQL connections to the database. This keeps the database isolated from direct internet access.

##Create RDS PostgreSQL Instance

In this section, I deployed an Amazon RDS PostgreSQL database within the existing private network architecture. I configured the required DB subnet group, selected the PostgreSQL engine, and restricted database connectivity to the production web server through dedicated security controls.

1.Create the RDS DB Subnet Group

I created an RDS DB subnet group using the two private database subnets across separate Availability Zones. This allows RDS to deploy database resources within the private network while meeting AWS subnet group requirements.

-Name: projectzero-prod-db-subnet-group

-VPC: projectzero-prod-vpc

-Subnets: Private DB Subnet and Private DB Subnet 2

-Availability Zones: eu-north-1a and eu-north-1b

2.Configure the PostgreSQL Database

I created an Amazon RDS PostgreSQL instance using the Free Tier template and a Single-AZ deployment for the lab environment.

-Engine: PostgreSQL

-Version: PostgreSQL 17.6-R2

-Template: Free Tier

-Deployment: Single-AZ

4.Configure Private Database Connectivity

I configured the RDS instance to use the existing production VPC and associated it with the previously created private DB subnet group and RDS security group. The database was configured without public access so connectivity remains restricted to approved resources inside the VPC.

-VPC: projectzero-prod-vpc

-DB subnet group: projectzero-prod-db-subnet-group

-Public access: No

-Security group: projectzero-prod-rds-SG

-Database port: TCP 5432

#RDS PostgreSQL — Database Configuration & Secure Access - Part 2

In this section, I configured the private RDS PostgreSQL database for the threat intelligence application. I validated connectivity from the production web server, created the application schema and database user, and secured database credentials using AWS Secrets Manager and least-privilege IAM access.

1.Validate Database Connectivity

I connected from projectzero-prod-websvr to the private RDS PostgreSQL endpoint and executed a test query to confirm that the network path, security group rules, and database authentication were functioning correctly.

I used a temporary .pgpass file with 600 permissions to provide credentials securely during the connectivity test.

chmod 600 ~/.pgpass

psql -h projectzero-prod-rds.c5kuusk8yc3p.eu-north-1.rds.amazonaws.com \
-U pz_dbadmin -d postgres -c "select now();"

2.Create Application Database Objects

I connected to the RDS PostgreSQL instance as pz_dbadmin to configure the database objects required by the threat intelligence application.

psql-h projectzero-prod-rds.c5kuusk8yc3p.eu-north-1.rds.amazonaws.com -U pz_dbadmin -d postgres

I then created the dashboard schema and panel_feed table to store structured threat intelligence data collected for the application dashboard.

I also created the dedicated webapp_rw database user and granted it the permissions required to read and modify application data within the dashboard schema.

CREATE SCHEMA dashboard AUTHORIZATION pz_dbadmin;

CREATE TABLE dashboard.panel_feed (
    id bigserial PRIMARY KEY,
    panel_name text NOT NULL,
    source_feed text NOT NULL,
    payload jsonb NOT NULL,
    collected_at timestamptz NOT NULL DEFAULT now()
);

CREATE USER webapp_rw WITH PASSWORD '<STRONG_RANDOM_PASSWORD>';
GRANT CONNECT ON DATABASE projectzerodb TO webapp_rw;
GRANT USAGE ON SCHEMA dashboard TO webapp_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA dashboard TO webapp_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA dashboard 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO webapp_rw;

Security: The application uses the dedicated webapp_rw account rather than the administrative pz_dbadmin account, separating application access from database administration.

This table will eventually store the output of each open-source threat feed for the dashboard panels.

##Seed Reference Data

I populated dashboard.panel_feed with placeholder records representing the threat intelligence feeds that will be consumed by application dashboard, including security news, domains, IP addresses, geographic IP data, malware hashes, and IOCs.

INSERT INTO dashboard.panel_feed (panel_name, source_feed, payload)
VALUES
('security_news_rss', 'placeholder', '{"items": []}'),
('top_100_domains', 'placeholder', '{"items": []}'),
('top_ips', 'placeholder', '{"items": []}'),
('top_10_countries_by_ip', 'placeholder', '{"items": []}'),
('top_malware_hashes', 'placeholder', '{"items": []}'),
('top_iocs', 'placeholder', '{"items": []}');

#PostgreSQL on EC2 (Production Configuration) - Part 3

Instead of relying on the temporary RDS environment, I deployed PostgreSQL directly on the production web server to provide a persistent database backend for the WebApp Lab.

2.Verify PostgreSQL Service

sudo systemctl status postgresql

##Create Application Database Objects

I connected to the local PostgreSQL instance as the postgres superuser and created the database objects required by the WebApp Lab.

The configuration included:

I configured webapp_rw with only the permissions required to interact with the application schema rather than using the PostgreSQL superuser for application access.

##Seed Reference Data

I populated dashboard.panel_feed with placeholder records representing the threat intelligence feeds that will be consumed by the WebApp Lab dashboard, including security news, domains, IP addresses, geographic IP data, malware hashes, and IOCs.

Keep the SQL block here because it shows exactly how the database is structured for the different dashboard feeds:

INSERT INTO dashboard.panel_feed (panel_name, source_feed, payload)
VALUES
('security_news_rss', 'placeholder', '{"items": []}'),
('top_100_domains', 'placeholder', '{"items": []}'),
('top_ips', 'placeholder', '{"items": []}'),
('top_10_countries_by_ip', 'placeholder', '{"items": []}'),
('top_malware_hashes', 'placeholder', '{"items": []}'),
('top_iocs', 'placeholder', '{"items": []}');

##Secure Database Credentials with AWS Secrets Manager

I stored the credentials for pz_dbadmin and webapp_rw securely in AWS Secrets Manager, allowing the EC2 instance to retrieve them when required without hardcoding credentials in the application. The logic for retrieving these secrets will be integrated into the Astro web application later in this lab.

1.Create Secrets in Secrets Manager

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ReadProjectZeroDbSecrets",
      "Effect": "Allow",
      "Action": [
        "secretsmanager:GetSecretValue",
        "secretsmanager:DescribeSecret"
      ],
      "Resource": [
        "arn:aws:secretsmanager:eu-north-1:YOUR_ACCOUNT_ID:secret:projectzero-prod/postgres/pz-dbadmin-*",
        "arn:aws:secretsmanager:eu-north-1:YOUR_ACCOUNT_ID:secret:projectzero-prod/postgres/webapp-rw-*"
      ]
    }
  ]
}

3.Attach IAM Role to the Web Server

I attached the IAM role to projectzero-prod-websvr-public, allowing the Astro application to securely retrieve the required PostgreSQL secrets through the EC2 instance profile without hardcoding credentials.

#Why This Matters

This section reinforced how database security extends beyond the database itself. Protecting application data requires network isolation, controlled database permissions, secure credential storage, and least-privilege IAM access working together. These controls reduce credential exposure and limit access to the PostgreSQL infrastructure supporting the Threat Intelligence Application.