How to identify IP address and executed query of the connection in the past.

Hello.

Is your database hosted on RDS?
And what database engine are you using?
For example, if you are using MySQL, you can obtain audit logs and investigate IP addresses and the queries that were actually executed.
If you configure the system to collect audit logs before a security breach occurs, you can investigate the issue.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.MySQL.LogFileSize.html

Does the database have a fixed IP address? like is it a RDS database or a database running on EC2. If so, you can VPC flow logs to find all the connections to this database.

https://docs.aws.amazon.com/vpc/latest/userguide/flow-logs-records-examples.html

To find out what happened, you need to check the logs that were active before the breach. If they weren't on, the data is gone.

Here's where to look:

1. RDS Audit Log: This is your best shot. If you had it enabled in your database parameter group, it will show you the exact queries and the source IP addresses. You'll find these logs in CloudWatch.

2. RDS Error Log: This log is on by default and will show you all successful and failed connection attempts, including the source IP. It won't show the queries, but it proves who got in.

3. VPC Flow Logs: If you have these enabled for your VPC, you can see all network traffic attempts to your database's IP address. This can help you confirm a suspicious IP even if the database logs are missing.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.html https://docs.aws.amazon.com/prescriptive-guidance/latest/amazon-rds-monitoring-alerting/database-logs.html https://www.datasunrise.com/knowledge-center/amazon-rds-audit-log/

Start with the Audit Log. If it's not there, use the Error Log and VPC Flow Logs to piece together what happened.