AWS
GCP
Security
Tutorials
How to Inventory All Public IP Addresses Across AWS and GCP using CloudQuery
That public IP address you assigned to a test EC2 instance last Tuesday? It's probably already getting hammered by SSH brute-force attempts. Public IPs get indexed by Shodan within minutes of exposure. Bots start scanning them immediately. Your security team needs to know every single one exists.
The problem isn't tracking one IP address. The problem is tracking all of them when they're scattered across EC2 instances, RDS databases, load balancers, NAT Gateways, ECS tasks, API Gateways, and then you remember you also have GCP resources with Compute Engine instances, Cloud SQL databases, and Load Balancers.
No single command exists to list them all. Each cloud provider has different services. Each service has different API calls. You'd need to write custom scripts, maintain them as APIs change, and somehow combine results from two different cloud platforms into one view.
We'll show you how to inventory all your public IP addresses across AWS and GCP using CloudQuery. One configuration file. One sync command. One SQL query to see everything.
The Public IP Visibility Problem #
Public IP addresses exist in over 10 different AWS services. EC2 instances have them. RDS databases might have them (yours shouldn't, but let's check). Elastic Load Balancers definitely have them. ECS tasks can get them. NAT Gateways and Internet Gateways route traffic through public IPs. ElastiCache clusters might be exposed. Redshift clusters could be public. API Gateway custom domains use them.
GCP adds its own complexity. Compute Engine instances use external IPs. Cloud SQL instances might be publicly accessible. Cloud Load Balancing uses forwarding rules with external IPs. Cloud Run services can have public endpoints. Google Kubernetes Engine nodes might have external IPs.
Each service requires different API calls to extract IP information. AWS EC2 uses
DescribeInstances to list instances with public IPs. DescribeAddresses shows Elastic IPs. DescribeNetworkInterfaces reveals network interface details. RDS requires DescribeDBInstances. Load balancers need DescribeLoadBalancers.GCP uses completely different APIs.
compute.instances.list shows Compute Engine instances. compute.addresses.list lists reserved external IP addresses. compute.forwardingRules.list shows load balancer IPs.Security teams need this inventory for attack surface mapping. Every public IP represents potential exposure. OWASP Cloud Security guidelines recommend maintaining accurate asset inventories. CIS AWS Foundations Benchmark requires knowing which resources face the internet.
Cost teams need it too. Since February 2024, AWS charges $0.005 per hour for every public IPv4 address—attached or not. That's $3.65 per month per IP. An organization with 100 public IPs pays $365 monthly just for IP addresses. Unattached Elastic IPs deserve scrutiny—some are intentionally reserved for scaling or allowlisting, but many are simply forgotten.
Compliance frameworks demand it. PCI DSS requires documenting all system components that store, process, or transmit cardholder data. SOC 2 auditors ask for network diagrams showing internet-facing systems. You need the actual list of public IPs to answer these questions accurately.
Comparing Your Options for Multi-Cloud IP Inventory #
How CloudQuery Solves Multi-Cloud IP Inventory #
CloudQuery calls all the relevant AWS and GCP APIs automatically. It syncs the data to PostgreSQL (or any destination you choose). You write one SQL query to find every public IP address across both cloud platforms.
The CloudQuery AWS source plugin calls AWS APIs like EC2's
DescribeInstances, DescribeNetworkInterfaces, and DescribeAddresses. It handles pagination automatically when you have thousands of resources. It respects AWS rate limits. It extracts public IP information from EC2 instances, Elastic IPs, network interfaces, RDS databases, and load balancers.The CloudQuery GCP source plugin calls GCP APIs like
compute.instances.list, compute.addresses.list, and compute.forwardingRules.list. It authenticates using service account keys. It handles GCP's quota system. It extracts external IP addresses from Compute Engine instances, reserved addresses, and load balancing rules.Both plugins sync data to your destination database. PostgreSQL gives you full SQL query capabilities. The data arrives in normalized tables. AWS EC2 instances land in
aws_ec2_instances. GCP Compute instances go to gcp_compute_instances. Elastic IPs populate aws_ec2_eips.You query this data using standard SQL. Join tables. Filter by tags. Group by region or project. Export to CSV. Build dashboards. The data sits in your database, under your control.
Implementation: Tracking Public IPs Across AWS and GCP #
You'll need Docker installed and cloud credentials configured. AWS credentials go in
~/.aws/credentials or environment variables. GCP requires a service account key JSON file.Create a CloudQuery configuration file that defines your AWS and GCP sources plus a PostgreSQL destination:
kind: source
spec:
name: aws
path: cloudquery/aws
version: 'v32.58.1' # Check hub.cloudquery.io for latest
tables:
- aws_ec2_instances
- aws_ec2_eips
- aws_ec2_security_groups
- aws_rds_instances
- aws_elbv1_load_balancers
- aws_elbv2_load_balancers
skip_dependent_tables: false # Include child tables like security group rules
destinations: ['postgresql']
spec:
regions: ['us-east-1', 'us-west-2'] # Add your regions
---
kind: source
spec:
name: gcp
path: cloudquery/gcp
version: 'v19.13.4' # Check hub.cloudquery.io for latest
tables:
- gcp_compute_instances
- gcp_compute_addresses
- gcp_compute_forwarding_rules
destinations: ['postgresql']
spec:
project_ids: ['your-gcp-project-id']
---
kind: destination
spec:
name: postgresql
path: cloudquery/postgresql
version: 'v8.13.3' # Check hub.cloudquery.io for latest
send_sync_summary: true
spec:
connection_string: 'postgresql://user:password@localhost:5432/cloudquery'
Run the sync command to extract data from AWS and GCP:
cloudquery sync config.yml
First sync takes 5-10 minutes for environments with 1000+ resources. Subsequent syncs run faster because CloudQuery tracks what changed. You'll see progress output showing which tables sync and how many rows insert.
Query for all public IP addresses across both clouds:
-- AWS EC2 instances with public IPs
SELECT
account_id,
region,
instance_id,
public_ip_address,
'EC2 Instance' as resource_type,
tags
FROM aws_ec2_instances
WHERE public_ip_address IS NOT NULL
UNION ALL
-- AWS Elastic IPs
SELECT
account_id,
region,
allocation_id as instance_id,
public_ip AS public_ip_address,
'Elastic IP' as resource_type,
tags
FROM aws_ec2_eips
UNION ALL
-- AWS RDS instances with public endpoints
SELECT
account_id,
region,
db_instance_identifier as instance_id,
endpoint_address as public_ip_address,
'RDS Database' as resource_type,
tags
FROM aws_rds_instances
WHERE publicly_accessible = true
UNION ALL
-- AWS Classic Load Balancers (ELBv1)
SELECT
account_id,
region,
name as instance_id,
dns_name as public_ip_address,
'Classic Load Balancer' as resource_type,
tags
FROM aws_elbv1_load_balancers
WHERE scheme = 'internet-facing'
UNION ALL
-- AWS Application/Network Load Balancers (ELBv2)
SELECT
account_id,
region,
name as instance_id,
dns_name as public_ip_address,
CASE
WHEN type = 'application' THEN 'Application Load Balancer'
WHEN type = 'network' THEN 'Network Load Balancer'
ELSE 'Gateway Load Balancer'
END as resource_type,
tags
FROM aws_elbv2_load_balancers
WHERE scheme = 'internet-facing'
UNION ALL
-- GCP Load Balancers (Forwarding Rules)
SELECT
project_id as account_id,
region,
name as instance_id,
ip_address as public_ip_address,
'GCP Load Balancer' as resource_type,
labels as tags
FROM gcp_compute_forwarding_rules
WHERE load_balancing_scheme = 'EXTERNAL'
UNION ALL
-- GCP Compute instances with external IPs
SELECT
project_id as account_id,
zone as region,
name as instance_id,
(network_interfaces->0->'accessConfigs'->0->>'natIP') as public_ip_address,
'GCP Compute' as resource_type,
labels as tags
FROM gcp_compute_instances
WHERE (network_interfaces->0->'accessConfigs'->0->>'natIP') IS NOT NULL;
This query returns every resource with a public IP address. The
resource_type column tells you what kind of resource owns each IP. Tags help you identify which team or environment owns it.Expected output:
account_id | region | instance_id | public_ip_address | resource_type | tags
--------------+------------+------------------------+--------------------------+--------------------------+---------------------------
123456789012 | us-east-1 | i-0abc123def456789 | 54.123.45.67 | EC2 Instance | {"Team": "Platform", ...}
123456789012 | us-east-1 | eipalloc-0123abcd | 52.45.67.89 | Elastic IP | {"Environment": "prod"}
123456789012 | us-west-2 | mydb-prod | mydb.abc.rds.amazonaws..| RDS Database | {"Team": "Data"}
123456789012 | us-east-1 | prod-api-lb | prod-api.us-east-1.elb...| Application Load Balancer| {"Team": "Platform"}
123456789012 | us-west-2 | legacy-lb | legacy.us-west-2.elb... | Classic Load Balancer | {"Environment": "prod"}
my-gcp-proj | us-central1| frontend-lb | 35.190.45.67 | GCP Load Balancer | {"team": "frontend"}
my-gcp-proj | us-central1| gcp-instance-1 | 35.123.45.67 | GCP Compute | {"team": "backend"}
Public IP Terminology
AWS Public IP: Temporary IP assigned to EC2 instance, changes when you stop and start the instance. Costs $0.005/hour ($3.65/month) since February 2024.
AWS Elastic IP: Static IP you own that persists across instance restarts. Same $0.005/hour cost whether attached or not.
AWS NAT Gateway IP: Static IP for outbound traffic from private subnets, cannot be assigned to instances directly.
GCP External IP: Can be ephemeral (temporary like AWS Public IP) or static (reserved like AWS Elastic IP).
GCP Static External IP: Reserved IP address that persists until you delete it. Costs $0.004/hour when in use ($2.92/month), $0.010/hour when unused ($7.30/month).
Finding Security Risks and Cost Waste #
Security teams use this inventory to map their attack surface. This query often reveals RDS databases with public endpoints that shouldn't be internet-accessible. Teams frequently discover resources they didn't know were exposed.
Here's how to find RDS instances that probably shouldn't be public:
SELECT
db_instance_identifier,
endpoint_address,
engine,
region,
tags
FROM aws_rds_instances
WHERE publicly_accessible = true
ORDER BY region, db_instance_identifier;
Every row returned represents a database accepting connections from the internet. Check if each one actually needs public access. Most production databases sit behind application servers in private subnets.
Cost optimization teams use this to find potential savings. While all public IPs now cost $0.005 per hour, unattached Elastic IPs are worth reviewing. Some may be intentionally reserved for scaling events or allowlisting, but many are forgotten allocations that can be safely released.
Find unattached Elastic IPs costing you money:
SELECT
account_id,
region,
allocation_id,
public_ip,
tags,
ROUND(0.005 * 730, 2) as monthly_cost_usd -- 730 hours average per month
FROM aws_ec2_eips
WHERE association_id IS NULL;
Expected output showing waste:
account_id | region | allocation_id | public_ip | monthly_cost_usd
--------------+------------+---------------------+--------------+------------------
123456789012 | us-east-1 | eipalloc-abc123 | 52.1.2.3 | 3.65
123456789012 | us-east-1 | eipalloc-def456 | 52.4.5.6 | 3.65
123456789012 | us-west-2 | eipalloc-ghi789 | 54.7.8.9 | 3.65
Each unattached Elastic IP costs $3.65 monthly. Organizations commonly find dozens of forgotten test IPs allocated months ago and never released. Even a handful of these add up to hundreds of dollars annually in unnecessary spend.
Find which security groups allow SSH from anywhere:
SELECT
i.instance_id,
i.public_ip_address,
i.region,
sg.group_id,
sg.group_name,
sgr.from_port,
sgr.to_port
FROM aws_ec2_instances i
JOIN aws_ec2_instance_security_groups isg
ON i.arn = isg.instance_arn
JOIN aws_ec2_security_groups sg
ON isg.group_id = sg.group_id
JOIN aws_ec2_security_group_ip_permissions sgr
ON sg.arn = sgr.security_group_arn
WHERE i.public_ip_address IS NOT NULL
AND sgr.from_port <= 22
AND sgr.to_port >= 22
AND sgr.ip_protocol = 'tcp'
AND EXISTS (
SELECT 1
FROM jsonb_array_elements(sgr.ip_ranges) as ip_range
WHERE ip_range->>'cidr_ip' = '0.0.0.0/0'
);
This query joins EC2 instances with their security groups and examines ingress rules. Any instance with a public IP that allows port 22 from
0.0.0.0/0 shows up in results. You'll probably want to restrict those to your corporate VPN or specific IP ranges.Organizing Public IPs by Team and Environment #
Most teams tag their cloud resources with ownership metadata. Tags like
Team, Environment, Project, or CostCenter help attribute resources to the right group.Group public IP counts by team using tags:
WITH all_public_ips AS (
SELECT
account_id,
region,
instance_id,
public_ip_address,
'EC2 Instance' as resource_type,
tags
FROM aws_ec2_instances
WHERE public_ip_address IS NOT NULL
UNION ALL
SELECT
account_id,
region,
allocation_id,
public_ip AS public_ip_address,
'Elastic IP' as resource_type,
tags
FROM aws_ec2_eips
UNION ALL
SELECT
account_id,
region,
db_instance_identifier,
endpoint_address,
'RDS Database' as resource_type,
tags
FROM aws_rds_instances
WHERE publicly_accessible = true
UNION ALL
SELECT
account_id,
region,
name,
dns_name,
'Classic Load Balancer' as resource_type,
tags
FROM aws_elbv1_load_balancers
WHERE scheme = 'internet-facing'
UNION ALL
SELECT
account_id,
region,
name,
dns_name,
CASE
WHEN type = 'application' THEN 'Application Load Balancer'
WHEN type = 'network' THEN 'Network Load Balancer'
ELSE 'Gateway Load Balancer'
END as resource_type,
tags
FROM aws_elbv2_load_balancers
WHERE scheme = 'internet-facing'
UNION ALL
SELECT
project_id,
region,
name,
ip_address,
'GCP Load Balancer' as resource_type,
labels as tags
FROM gcp_compute_forwarding_rules
WHERE load_balancing_scheme = 'EXTERNAL'
UNION ALL
SELECT
project_id,
zone,
name,
(network_interfaces->0->'accessConfigs'->0->>'natIP'),
'GCP Compute' as resource_type,
labels as tags
FROM gcp_compute_instances
WHERE (network_interfaces->0->'accessConfigs'->0->>'natIP') IS NOT NULL
)
SELECT
tags->>'Team' as team,
COUNT(*) as public_ip_count,
array_agg(DISTINCT region) as regions,
array_agg(DISTINCT resource_type) as resource_types
FROM all_public_ips
WHERE tags->>'Team' IS NOT NULL
GROUP BY tags->>'Team'
ORDER BY public_ip_count DESC;
Expected output:
team | public_ip_count | regions | resource_types
-------------+-----------------+------------------------+-------------------------------------------------------------
Platform | 50 | {us-east-1,us-west-2} | {EC2 Instance,Elastic IP,Application Load Balancer}
Data | 12 | {us-east-1,us-central1}| {RDS Database,GCP Compute,GCP Load Balancer}
Mobile | 8 | {us-east-1} | {EC2 Instance,Classic Load Balancer}
This shows you which teams have the most internet-facing resources. The platform team might have 50 public IPs for infrastructure services. The data science team might have 12 for notebook servers. The mobile team might have 8 for API endpoints.
Track environment exposure separately:
SELECT
tags->>'Environment' as environment,
COUNT(*) as public_ip_count,
COUNT(CASE WHEN resource_type LIKE '%RDS%' THEN 1 END) as public_databases
FROM (
-- Same union query as above
) all_public_ips
WHERE tags->>'Environment' IS NOT NULL
GROUP BY tags->>'Environment'
ORDER BY public_ip_count DESC;
Production environments should have far fewer public IPs than development or staging. Most production resources sit behind load balancers. If you see 100 public IPs in production but 20 in development, investigate why production has so much direct internet exposure.
Compliance Reporting with Public IP Inventory #
PCI DSS Section 1.1.4 requires maintaining accurate network diagrams. PCI Security Standards Council publishes the full requirements. Network diagrams must show all connections between the cardholder data environment and other networks, including internet connections.
Generate a compliance report showing all internet-facing systems:
WITH all_public_ips AS (
-- Same union query from earlier
)
SELECT
resource_type,
account_id,
region,
instance_id,
public_ip_address,
CASE
WHEN tags->>'PCI_Scope' = 'true' THEN 'In Scope'
WHEN tags->>'PCI_Scope' = 'false' THEN 'Out of Scope'
ELSE 'Not Tagged'
END as pci_scope,
tags->>'Owner' as owner,
tags->>'Environment' as environment
FROM all_public_ips
ORDER BY pci_scope, resource_type, region;
This report shows auditors exactly which systems can be reached from the internet. The
pci_scope tag identifies which resources handle payment data. Resources in scope need additional security controls.SOC 2 Type II audits require documenting your security monitoring. Auditors want to know how you track new internet-facing resources. Set up scheduled CloudQuery syncs (every 6 hours or daily) and track when public IPs first appear using CloudQuery's sync timestamp:
SELECT
instance_id,
public_ip_address,
MIN(_cq_sync_time) as first_seen,
MAX(_cq_sync_time) as last_seen,
COUNT(*) as times_observed
FROM aws_ec2_instances
WHERE public_ip_address IS NOT NULL
GROUP BY instance_id, public_ip_address
HAVING MIN(_cq_sync_time) > NOW() - INTERVAL '7 days'
ORDER BY first_seen DESC;
This shows public IPs that appeared in the last week. New IPs from the most recent sync appear at the top.
Setting Up Automated Tracking #
Schedule CloudQuery syncs using cron or your task scheduler:
# Run every 6 hours
0 */6 * * * /usr/local/bin/cloudquery sync /path/to/config.yml
Store results with timestamps to track changes over time. Modify your destination configuration to append data instead of replacing it:
kind: destination
spec:
name: postgresql
path: cloudquery/postgresql
version: 'v8.13.3'
spec:
connection_string: 'postgresql://user:password@localhost:5432/cloudquery'
write_mode: append # Don't replace data, append it
Each sync adds rows with a
_cq_sync_time timestamp column. Query historical data to track trends.Build dashboards using Grafana, Metabase, or any PostgreSQL-compatible visualization tool. Track public IP counts over time. Graph new IPs per day. Show distribution across teams and environments.
Key Takeaways: Multi-Cloud Public IP Inventory #
Implementation & Results:
- CloudQuery syncs all AWS and GCP public IPs to PostgreSQL in minutes with a single configuration file
- One SQL query shows every public-facing resource across both clouds instead of dozens of separate API calls
- Security teams find exposed databases and overly permissive security groups that manual reviews miss
- Cost teams identify unused Elastic IPs wasting $30-70 monthly per organization
Technical Architecture:
- AWS plugin calls
DescribeInstances,DescribeAddresses,DescribeNetworkInterfacesand related APIs automatically - GCP plugin uses
compute.instances.list,compute.addresses.list,compute.forwardingRules.listfor comprehensive coverage - Handles pagination, rate limiting, and exponential backoff without custom code
- Normalized PostgreSQL tables enable joins across services, clouds, and resource types
Security & Compliance:
- PCI DSS and SOC 2 compliance requires knowing all internet-facing systems - this provides the actual inventory
- Track when new public IPs appear using timestamped incremental syncs
- Cross-reference security groups and firewall rules to find high-risk exposure
- Historical tracking shows attack surface changes over weeks and months
Looking to implement public IP tracking for your multi-cloud environment? Contact our team to discuss your specific security and compliance requirements, or explore our security solutions to see how CloudQuery fits into your broader cloud governance strategy.
Frequently Asked Questions #
What cloud providers does CloudQuery support for public IP inventory? #
CloudQuery supports AWS, GCP, and Azure for public IP tracking. The AWS plugin extracts IPs from EC2, RDS, Load Balancers, and other services. The GCP plugin covers Compute Engine, Cloud SQL, and Load Balancing. The Azure plugin handles Virtual Machines, Load Balancers, and Application Gateways. Over 70 total integrations are available for different cloud platforms and SaaS services.
How often should I sync my cloud infrastructure data? #
Sync frequency depends on how quickly your infrastructure changes. Development environments with frequent deployments benefit from syncs every 2-4 hours. Production environments with stable infrastructure work fine with daily syncs. Security teams tracking attack surface might sync every hour. Cost optimization teams tracking Elastic IP waste typically sync weekly. CloudQuery handles incremental syncs efficiently, so frequent syncs don't resync everything each time.
Can CloudQuery track public IP changes over time? #
Yes, using append mode instead of replace mode. Configure your destination to append data with timestamps rather than replacing it. Each sync adds rows with a
_cq_sync_time column showing when CloudQuery captured the data. Query historical data to see when resources appeared, changed, or disappeared. Track trends like "public IPs per team over the last 6 months" or "new public databases each week."Does CloudQuery support Azure for public IP inventory? #
CloudQuery's Azure plugin extracts public IP information from Azure Virtual Machines, Load Balancers, Application Gateways, and other services. Query Azure data using the same SQL approach shown for AWS and GCP. Combine all three clouds in a single query using
UNION ALL to see your complete multi-cloud public IP inventory.Can I export the public IP inventory to a CSV or JSON file? #
Yes. The simplest approach is to use CloudQuery's File destination plugin to sync directly to CSV or JSON files instead of PostgreSQL. Alternatively, if you're using PostgreSQL, export query results using
COPY or \copy commands for CSV, or row_to_json() functions for JSON. Most SQL clients also have built-in export features.What are the main differences between AWS and GCP public IP management? #
Since February 2024, AWS charges $0.005/hour for all public IPv4 addresses—attached or not. AWS separates ephemeral public IPs (change on instance restart) from Elastic IPs (static). GCP uses external IPs that can be either ephemeral or static (reserved). GCP static IPs cost $0.010/hour when unused but only $0.004/hour when in use. AWS automatically assigns public IPs unless you disable this. GCP requires explicitly requesting external IPs. Both clouds support bringing your own IP addresses (BYOIP) for specific use cases.
How does CloudQuery handle API rate limits when syncing thousands of resources? #
CloudQuery implements exponential backoff and automatic retries when hitting rate limits. AWS API throttling gets handled transparently - CloudQuery pauses briefly and retries. GCP quota limits work the same way. Large accounts with 10,000+ resources see longer initial sync times (15-20 minutes) but subsequent syncs complete much faster because CloudQuery only fetches changed resources. You don't need to tune rate limiting parameters; the plugins handle this automatically.
