This is something you will have to deal with at one time or another after managing AWS IAM users for a while. Basically, it’s straight forward with the following code:
Get a list of users
for each user get their access key IDs
(optional) pipe to grep for checking a specific ID
And here’s the `aws-cli` code
for user in $(aws iam list-users --output text --query 'Users[*].UserName'); do
aws iam list-access-keys --user $user --output text;
done
# or
Q_ACCESSKEY=AKIA*************
for user in $(aws iam list-users --output text --query 'Users[*].UserName'); do
aws iam list-access-keys --user $user --output text | grep $Q_ACCESSKEY
done
At work, we wanted to switch from Mandrill/Mailchimp to Amazon SES for a long time. But that was not happening mainly because the tools SES offered to monitor sent mail were, how should I say, DIY.
So, after some deliberation and when I found some time to tackle it, I did it 🙂
The setup is not too complex? Well, it is. But once you understand it, it’s pretty basic.
Let’s start at the source: Amazon
You will see this notice under Notifications for each Email Address you create/verify in SES:
Amazon SES can send you detailed notifications about your bounces, complaints, and deliveries.
Bounce and complaint notifications are available by email or through Amazon Simple Notification Service (Amazon SNS).
Next step is to create the SNS Topic, it’s just a label really.
You will also need an Amazon SQS queue. A standard queue should be good. Once it’s there, copy the ARN as you will need that for the SNS subscription.
Let’s go back to the SNS Topic we created and click on the Create subscription button. Choose Amazon SQS for the Protocol and paste the ARN of the SQS queue you created earlier. You may need to confirm that too? Just click the button if it’s there.
That’s all on the Amazon side! See how easy that was?!
Next you need a Graylog setup.
Where do I start? Well, first choose where do you want to put that Graylog “machine”. For Amazon EC2 I would just go with their ready-made AMIs. Here’s the link/docs to follow: http://docs.graylog.org/en/latest/pages/installation/aws.html (but and I quote: The Graylog appliance is not created to provide a production ready solution)
But since I like doing things the “easy” way, I went with the Ubuntu 16.04 package per http://docs.graylog.org/en/latest/pages/installation/operating_system_packages.html
Seriously, it’s much easier to use and maintain since I know where everything is. Maybe it’s just me …
Anyway, here’s my bash session:
I followed the instructions there, and installed Apache on top of that with the following configuration for the VirtualHost
ServerName example.com
# Letsencrypt it
SSLCertificateFile /etc/letsencrypt/live/example.com/fullchain.pem
SSLCertificateKeyFile /etc/letsencrypt/live/example.com/privkey.pem
Include /etc/letsencrypt/options-ssl-apache.conf
# The needed parts start here
ProxyRequests Off
Order deny,allow
Allow from all
RequestHeader set X-Graylog-Server-URL "https://example.com/api/"
ProxyPass http://127.0.0.1:9000/
ProxyPassReverse http://127.0.0.1:9000/
This will leave you with a Graylog server ready to receive the logs. Now, how do we get the logs over to Graylog? Easy! Pull them from SQS.
Start by adding a GELF HTTP Input in Graylog (System > Inputs > Select Input: GELF HTTP > Launch new input)
Make sure to get the port there right, you will need to configure the script below.
Then download the script, make sure it’s executable. Do run it manually, that way it will tell you what’s missing (BOTO3)
Make sure to configure AWS credentials. The quickest way is:
* to install awscli: apt-get install awscli
* and run its configuration: aws configure
Edit the script with the right configuration vars, add it to cron to run as much as you feel necessary (I use it @hourly)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Well, it’s not… just a bit confusing when you don’t know where to look.
TL,DR: (Ubuntu 16.04, otherwise YMMV, read below)
Add the following to your active configuration file for mysql
[mysqld]
slow_query_log
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
What could go wrong?
Wrong configuration file:
on Ubuntu 16.04 the file can be in /etc/mysql/my.cnf or /etc/mysql/conf.d/mysql.cnf or even /etc/mysql/mysql.conf.d/mysqld.cnf
to find which one is “active”, run: mysqld --verbose --help |grep -A 1 "Default options"
the result is something like the following: Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
if you open /etc/mysql/my.cnf, you will find at the bottom: !includedir /etc/mysql/conf.d/
That’s where the actual files are stored.
Wrong variable names:
If you follow different tutorials on the Internet you will find configurations that mention: log_slow_queries=/var/log/mysql/slow-query.log
DO NOT use that, it’s deprecated.
the new name of the variable is `slow_query_log_file` for the actual log file, and YOU SHOULD have `slow_query_log` as a boolean variable (ON, 1, or just mentioned in the file as per the code above).
Troubleshooting
Tail the /var/log/mysql/error.log in a separate terminal, see what’s failing. Example:
[Warning] option 'slow_query_log': boolean value '/var/log/mysql/mysql-slow.log' wasn't recognized. Set to OFF.
Obviously the variable is set wrongly to the filename. They are now 2 separate variables.
We rely on IP location for many things as sysadmins, like checking where that flood of logins to wordpress is coming from (China?!). Or even checking if a new order is coming from downtown Dubai or from the outskirts of Jakarta…
I usually use ip2location.com/demo/ and it works great from the browser. Recently however I noticed one of my colleagues using db-ip. They have a rest API. So I wrote this small wrapper, that needs plenty of fixes: #!/bin/bash
ADDR=$1
KEY=GETYOURKEYFROMhttp://db-ip.com/api/#key
wget -qO- "http://api.db-ip.com/addrinfo?addr=${ADDR}&api_key=${KEY}"
Here’s a sample session: $ ipinfo 199.59.150.7
{"address":"199.59.150.7","country":"US","stateprov":"California","city":"San Francisco"}
Very simple, but seems enough if I want to check the IP location from the shell. Maybe I’ll add more features (if I need them!)
Talking about mail queues here. Especially when the queue gets filled with hundreds of thousands of spam emails.
Stop the MTA
The first thing you do is stop the MTA before it gets worse. On most Linux servers: ~: # /etc/init.d/exim stop # most WHM/cPanel based servers
~: # /etc/init.d/postfix stop # if you have postfix
Then check the queue
The commands to use are ~: # exim -bp
~: # mailq
I suggest keeping CTRL+C ready to stop the flow. If you have an infestation scrolling through the queue will take a very very long time.
But checking out the first few lines (pages) of the queue may reveal many things. For example, what user account is being exploited for sending out the spam. Sometimes that’s obvious, other times you may need to do some digging. Here’s a sample:
24h 1.1K 1VeKKb-00030t-42 <noreply@yahoo-inc.com>
D alena@example.com
D alenka@example.com
D alepp@example.com
D alerei@example.com
24h 1.1K 1VeKKb-00030u-5R <noreply@yahoo-inc.com>
D alex@example.com
D alex.b@example.com
D alex.d@example.com
D alex.f@example.com
24h 1.1K 1VeKKb-00030v-1R <noreply@yahoo-inc.com>
D alex.t@example.com
[...]
The lines above are from an exim queue output. Obviously noreply@yahoo-inc.com is not an account on this server. Let’s find the username then clean up. A nice Exim utility is exigrep (basically it’s grep with exim ties!) :~# exigrep 1VeKKb-00030t-42 /var/log/exim/mainlog
+++ 1VeKKb-00030t-42 has not completed +++
2013-11-08 21:18:14 1VeKKb-00030t-42 <= noreply@yahoo-inc.com H=(User) [XXX.XX.XX.XXX] P=esmtpa A=login:test@mydomain.com S=1193 T="Authenticate Your Email" from <noreply@yahoo-inc.com> for alena@example.com alenka@example.com alepp@example.com alerei@example.com [...]
2 things interest us here: the H=(User) [XXX.XX.XX.XXX] part tells us where that intruder is logging from (probably another exploited server, so it’s nice to alert the owners of that server as well. And A=login:test@mydomain.com tells us which user they are logged in as, so we can lock down that user, change their password, etc.
Cleaning up
Emptying the queue is usually an easy way out: ~: # postsuper -d ALL # for postfix
~: # exim -bp | exiqgrep -i | xargs exim -Mrm # for exim
A more subtle approach is needed when some of that mail in the queue is actually legit. If you checked out the queue earlier you might do something like the following: ~: # exiqgrep -i -f noreply@yahoo-inc.com | exim -Mrm # that will remove all mail sent by noreply@yahoo-inc.com
It’s a bit harder in postfix, here’s one recipe: ~: # mailq | tail +2 | awk 'BEGIN { RS = "" } { if ($7 == "noreply@yahoo-inc.com") print $1 }' | tr -d '*!' | postsuper -d -
remove the tail +2 | if that doesn’t work for you.
There are plenty of other things to check and do to fix your server when someone is abusing it to spam. Cleaning up is one way to start.