Tag Archives: RHEL

Linux Administration Tools & Tricks

Introduction

All *nux administrators have tools they use to make their job easier. This blog will be an ongoing one where every time I find or create a handy tool, I’ll share it. I’ll do my best to provide a quick write up with it and some simple examples as to how it may be useful to you too.
Currently, this blog touches on the following:

Applications

Please note that most of these tools can be directly retrieved from my repository I’m hosting. You may wish to connect to it to make your life easier.


genssl: SSL/TLS Certificate Generator and Certificate Validation

genssl is a simple bash script I created to automatically generate Private/Public key pairs that can be used by all sorts of applications requiring SSL/TLS encryption. This could be used by any number of servers you’re hosting (Web, Mail, FTP, etc). It will also test previously created keys (validating them). It supports generating keys that are to be signed by Certificate Authority as well as self signed and test keys too.

# Assuming you've connected to my repository,
# just type the following:
yum install -y --enablerepo=nuxref genssl

What You’ll Need To Know
It’s really quite simple, SSL/TLS keys are usually assigned to domain names. Thus this is the only input to the tool in addition to the key type you want to generate (self-signed, test, or registered):

# Generate a simple self-signed Certificate Private/Public
# key pair for the domain example.com you would just type the
# following:
genssl -s example.com
# ^-- creates the following:
#   - example.com.key
#   - example.com.crt
#   - example.com.README

# You can specify as many domain names as you want to
# generate more then one:
genssl -s example.com nuxref.com myothersite.com
# ^-- creates the following:
#   - example.com.key
#   - example.com.crt
#   - example.com.README
#   - nuxref.com.key
#   - nuxref.com.crt
#   - nuxref.com.README
#   - myothersite.com.key
#   - myothersite.com.crt
#   - myothersite.com.README

You can verify the keys with the following command:

# The following scans the current directory
# for all .key, .crt, and/or .csr files and tests that
# they all correctly match against one another.
genssl -v

If you need a signed certificate by a registered Certificate Authority, genssl will get you half way there. It will generate you your public key and an unsigned certificate (.csr). You’ll then need to choose a Certificate Authority that works best for you (cost wise, features you need). You will provide them your unsigned certificate you just generated and in return, they’ll produce (and provide) for you the private key you need (.key). Signed (registered) certificates cost money because you’re paying for someone else to confirm the authenticity of your site when people visit it. This is by far the absolute best way to publicly host a secure website!

# Generate an unsigned certificate and public key
# pair for the domain example.com with the following:
genssl -r example.com
# ^-- creates the following:
#   - example.com.key
#   - example.com.crt
#   - example.com.README

You’ll notice that you also generate a README file for every domain you create keys for. Some of us won’t generate keys often; so this README provides some additional information that will explain how you can use the key. It lists a few Certificate Authoritative locations you can contact, and provides examples on how you can install the key into your system. Depending on the key pair type you generate, the README will provide you with information specific to your cause. Have a look at the contents yourself after you generate a key! I find the information in it useful!

The last thing worth noting about this tool is to create a key pair, you usually provide information about yourself or the site you’re creating the key for. I’ve defaulted all these values to ones you’re probably not interested in using. You can over-ride them by specifying additional switches on the command line. But the absolute easiest (and better) way of doing it, is to just provide a global configuration file it can reference which is /etc/genssl. This file is always sourced (if present). The next file that is checked and sourced is ~/.config/genssl and then ~/.genssl.
Set this with information pertaining to your hosting to ease the generation of of the key details. Here is an example of what any one of these configuration files might look like:

# The country code is represented in it's 2 letter abbreviated version:
# hence: CA, US, UK, etc
# Defaults to "7K" if none is specified
GENSSL_COUNTRY="7K"
# Your organization/company Name
# Defaults to "Lannisters" if none is specified
GENSSL_ORG="Lannisters"
# The Province and or State you reside in
# Defaults to "Westerlands" if none is specified
GENSSL_PROVSTATE="Westerlands"
# Identify the City/Town you reside in
# Defaults to "Casterly Rock" if none is specified
GENSSL_LOCATION="Casterly Rock"
# Define a department; this is loosely used. Some
# just leave it blank
# Defaults to "" (blank) if not is specified
GENSSL_DEPT=""

There are other variables you can override, but the above are the key ones. The default cipher strength is rsa:2048 and keys expire after 730 days (which equates to 2 years). This is usually the max time Certificate Authoritative sites will sign your key for anyway (if registering with them).

You don’t need a configuration file at all for this script to work, there are switches for all of these variables too that you can pass in. Its important to note that passed in switches will always over-ride the configuration file too, but this is the same with most applications anyway.

Some Further Reading


fencemon: A System Monitoring Tool

fencemon is a beautiful creation by Red Hat, the only thing I did was package it as an RPM. I don’t think it’s advertised or used enough by system administrators. It simply runs as a cron and constantly gathers detailed system information. Information such as the current programs running, the system memory remaining, network statistics, disk i/o, etc. It captures content every 10 seconds but does so by using tools that are not i/o intensive. So running this will not slow your machine down enough to justify not using it.

Where this information becomes vital is during a system failure (in the event one ever does occur). It will allow you to see exactly what the last state of the system was (processes in memory, etc) prior to your issue. The detailed information can be used with everyday troubleshooting as well too such as identifying that process that is going crazy overnight and other anonymities that you’re never around to witness first hand.

Why is it called fencemon?
It got it’s name because it’s initial purpose was to run in clustered environments which do a think called ‘fencing’. It’s when another cluster node sees that another is violating some of the simple cluster rules put in place, or it simply isn’t responding anymore. Fencing is effectively the power cycling of the node (so an admin doesn’t have to). In almost all cases (unless there is seriously something wrong with the fenced node), it will reboot and rejoin the cluster correctly. This tool would have allowed an admin to see the final state of the cluster node before it was lost. But any server can crash or go crazy when deploying software in a production environment. We all hope it never happens, but it can. The logging put in place by fencemon can be a life saver!

# Assuming you've connected to my repository,
# just type the following:
yum install -y --enablerepo=nuxref fencemon

What You’ll Need To Know
The constant system capturing that is going on will report itself to /var/log/fencemon/.

The format of the log files is:
hostnameYYYYMMDDHHMMSS-info-capturetype.log

Once an hour elapses, a new file is written to. Each file contains system statistics in 20 second bursts; as you can imagine, there is A LOT of information here.

the following capturetype log files (with their associated commands) are gathered in 20 second increments:

  • hostnameYYYYMMDDHHMMSS-info-ps-wchan.log:
    ps -eo user,pid,%cpu,%mem,vsz,rss,tty,stat,start,time,wchan:32,args
  • hostnameYYYYMMDDHHMMSS-info-vmstat.log:
    vmstat
  • hostnameYYYYMMDDHHMMSS-info-vmstat -d.log:
    vmstat-d
  • hostnameYYYYMMDDHHMMSS-info-netstat.log:
    netstat -s
  • hostnameYYYYMMDDHHMMSS-info-meminfo.log:
    cat /proc/meminfo
  • hostnameYYYYMMDDHHMMSS-info-slabinfo.log:
    cat /proc/slabinfo
  • hostnameYYYYMMDDHHMMSS-info-ethtool-$iface.log:
    # $iface will be whatever is detected on your machine
    # you can also define this variable in /etc/sysconfig/fencemon
    # ifaces="lo eth0"
    # The above definition would cause the following to occur:
    /sbin/ethtool -S lo >> 
       /var/log/fencemon/hostname-YYYYMMDD-HHMMSS-info-ethtool-lo.log
    /sbin/ethtool -S eth0 >> 
       /var/log/fencemon/hostname-YYYYMMDD-HHMMSS-info-ethtool-eth0.log
    

The log files are kept for about 2 days which can occupy about 750MB of disk space. But hey, disk space is cheap now of days. You should have no problem reserving a GIG of disk space for this info. It’s really worth it in the long run!

Some Further Reading


datemath: A Date/Time Manipulation Utility

I won’t go too deep on this subject since I’ve already blogged about it before here. In a nutshell, It can easily provide you a way of manipulating time on the fly to ease scripting. This tool is not rocket science by any means, but it simplifies shell scripting a great deal when preforming functionality that is time sensitive.

This is effectively an extension or the the missing features to the already existing date tool which a lot of developers and admins use regularly.

# Assuming you've connected to my repository,
# just type the following:
yum install -y --enablerepo=nuxref datemath

What You’ll Need To Know
There are 2 key applications; datemath and dateblock. Datemath can ease scripting by returning you time relative to when it was called; for example:

# Note: the current date/time is printed to the
#        screen to give you an idea how the math was
#        applied.
# date +'%Y-%m-%d %H:%M:%S' prints: 2014-11-14 21:49:42
# What will the time be in 20 hours from now?
datemath -o 20
# 2014-11-15 17:49:42

# Top of the hour 9000 minutes in the future:
datemath -n 9000 -f '%Y-%m-%d %H:00:00'
# 2014-11-21 03:00:00

If you use a negative sign, then it looks in the past. There are lots of reasons why you might want to do this; consider this little bit of code:

# Touch a file that is 30 seconds older than 'now'
touch -t $(datemath -s -30 -f '%Y%m%d%H%M.%S') reference_file

# Now we have a reference point when using the 'find'
# command. The below command will list all files that
# at least 30 seconds old. This might be useful if your
# hosting an FTP server and don't want to process files
# until they've arrived completely.
find -type f -newer reference_file
# You could move the fully delivered files to a new
# directory with a slight adjustment to the above line
find -type f -newer reference_file -exec mv -f {} /new/path ;

# Consider this for an archive clean-up for a system
# you maintain. Simply touch a file as being 31 days
# older then 'now'
touch -t $(datemath -d -31 -f '%Y%m%d%H%M.%S') reference_file

# now you can list all the files that are older then
# 31 days.  Add the -delete switch and you can clean
# up old content from a directory.
find /an/archive/directory -type f ! -newer reference_file

# You can easily script sql statements using this tool too
# consider that you need to just select yesterdays data
# for an archive:
START=$(datemath -d -1 -f '%Y-%m-%d 00:00:00')
FINISH=$(date +'%Y-%m-%d 00:00:00')
BACKUP_FILE=/absolute/path/to/put/backup/$START-backup.csv
# Now your SQL statement could be:
/usr/bin/psql -d postgres 
      -c "COPY (SELECT * FROM mysystem.data WHERE 
                mysystem.start_date >= '$START' AND 
                mysystem.finish_date < '$FINISH') 
          TO '$BACKUP_FILE';"
# Now that we've backed our data up, we can remove
# it from our database:
/usr/bin/psql -d postgres 
      -c "DELETE FROM mysystem.data  WHERE 
                mysystem.start_date >= '$START' AND 
                mysystem.finish_date < '$FINISH'"

Some Further Reading


dateblock: A Cron-Like Utility

dateblock allows us to mimic the functionality of sleep by blocking the process. The catch is dateblock blocks until a specific time instead of for a duration of time.

This very subtle difference can prove to be a very effective and powerful too, especially when you want to execute something at a specific time. Cron’s are fine for most scenarios, but they aren’t ideal in a clustered environment where this tool excels. In a clustered environment you may only want an action to occur on the node hosting the application, where a cron would require the action to fire on all nodes.

The python C++ library enables this tools usage even further since you can integrate it with your application.

Just like datemath is, dateblock is discussed in much more detail in my blog about it here.

# Assuming you've connected to my repository,
# just type the following:
yum install -y --enablerepo=nuxref dateblock

# Get the python library too if you want it
yum install -y --enablerepo=nuxref python-dateblock

What You’ll Need To Know
Consider the following:

# block for 30 seconds...
sleep 30

# however dateblock -s 30 would block until the next 30th second
# of the minute is reached. Consider the time:
# date +'%Y-%m-%d %H:%M:%S' prints: 2014-11-14 22:14:16
# dateblock would block until 22:14:30 (just 14 seconds later)
dateblock -s 30

Dateblock works just like a crontab does and supports the crontab format too. It was written by me to specifically allow accuracy for time sensitive applications running. If you write your actions and commands in a script under a dateblock command, you can always guarantee your actions will be called at a precise time.

Since it supports cron entries you can feed it things like this:

# The following will unblock if the minute becomes
# divisible by 10.  so this would unblock at the
# :00, :10, :20, :30: 40: and :50 intervals
dateblock -n /10

# the above command could also be written like this:
dateblock -n 0,10,20,30,40,50

# You can mix and match switches to tweak the tool to
# always work in your favour.

A really nice switch that can help with your debugging and development is the –test (-t) switch. This will cause dateblock to ‘not’ block at all. Instead it will just spit out the current time, followed by the time it ‘would’ have unblocked at had you not put it into test mode. This is good for trying to tweak complicated arguments to get the tool to work in your favour.

# using the test switch, we can make sure we're going
# to unblock at time intervals we expect it to. In this
# example, i use the test switch and a cron formatted
# argument.  In this example, I'm asking it to unblock
# ever 2 days at the start of each day (midnight with
# respect to the systems time)
dateblock -t -c "0 0 0 /2"
Current Time : 2014-11-14 22:24:06 (Fri)
Block Until  : 2014-11-16 00:00:00 (Sun)

# Note: there is an extra 0 above that may through you
#       off.. in the normal cron world, the first
#       argument is the 'minute' entry.  Well since
#       dateblock offers high resolution, the first
#       entry is actually 'seconds', then minute, etc.

There are man pages for both of these tools. You’ll get a lot more detail of the power they offer. Alternatively, if you even remotely interested, check out e my blog entry on them.

The other cool thing is dateblock also has a python library I created.

# Import the module
from dateblock import dateblock

# Here is a similar example as above and we set
# block to false so we know how long were blocking until
unblock_dt = dateblock('0 0 0 /2', block=False)
print("Blocking until %s" % unblock_dt
              .strftime('%Y-%m-%d %H:%M:%S))
# by default, blocking is enabled, so now we can just
# block for the specified time
datetime.datetime('0 0 0 /2')

Some Further Reading


nmap (Network Mapper)

nmap is port scanner & network mapping tool. You can use it to find out all of the open ports a system has on it and you can also use it to see who is on your network. This tool is awesome, but it’s intentions can be easily abused. It’s common sense to scan your own network to make sure that only the essential ports are open to the outside world, but it’s not kind to scan a system to which you are not the owner of. Hence, this tool should be used to identify your systems weaknesses so that you can fix them; it should not be used to identify an others weakness. For this reason, if you do install nmap on your system, consider limiting permission so that only the root user has access to it.

# Assuming you've connected to my repository,
# just type the following:
yum install -y --enablerepo=nuxref-shared nmap

# Restrict its access (optional, but ideal)
chmod o-rwx /usr/bin/nmap

What You’ll Need To Know
Now you can do things like:

  • Scan your system to identify all open ports:
    # Assuming the server you are scanning is 192.168.1.10
    nmap 192.168.1.10
    
  • Scan an entire network to reveal everyone on it (including MAC Address information):
    # Assuming a class C network (24 masked bits) with
    # a network address of 192.168.1.0
    nmap -n -v -sP 192.168.1.0/24
    

Note: Don’t panic if your system appears to have more ports open then you had thought. Well at least don’t panic until you determine where you are running your network map test from. You see, if you run nmap on the same system you are testing against, then there your test might not prove accurate since firewalls do not normally deny any local traffic. The best test is to use a second access point to test against the first. Also, if your server sits on more then one network, make sure to test it from a different server on each network it connects to!

Some Further Reading

  • A blog with a ton of different examples identifying things you can do with this tool.

lsof (LiSt Open Files)

This tool is absolutely fantastic when it comes to preforming diagnostics or handling emergency scenarios. This command is only available to the root user; this is intentional due to the power it offers the system administrators.

I’m not hosting this tool simply because it ships with most Linux flavors (Fedora, CentOS, Red Hat, etc). Therefore, the following should haul it into your system if it’s not already installed:

# just type the following:
yum install -y lsof

# If this doesn't work, the rpm will be found
# on your DVD/CD containing your Linux Operating
# System.

What You’ll Need To Know
This tool when executed on it’s own (without any options) simply lists every open file, device, socket you have on one great big list. This can be useful when paired with grep if your looking for something specific.

# list everything opened
lsof

But it’s true power comes from it’s ability to filter through this list and fetch specific things for you.

# list all of the processes utilizing port 80 on your system:
lsof -i :80 -t

# or be more specific and list all of the processes accessing
# a specific service you are hosting on an IP listening on
# port 80:
# Note: the below assumes we're interested in those accessing
#       the ip address 192.168.0.1
lsof -i @192.168.0.1:80 -t

# If you pair this with the kill command, you can easily kick
# everyone off your server this way:
kill -9 $(lsof -i @192.168.0.1:80 -t)

But the tool is also great for even local administration; let’s say there is a user account on your system with the login id of bob.

# find out what bob is up to and what he's accessing on
# your system this way (you may need to pair this with
# grep since the output can be quite detailed
lsof -u bob

# Perhaps you just want to see all of the network activity
# bob is involved in. The following spits out a really
# easy to read list of all of the network related processes
# bob is dealing with.
lsof -a -u bob -i

# You can kill all of the TCP/IP processes bob is using with
# the following (if bob was violating the system, this might
# be a good course of action to take):
kill -9 $(lsof -t -u bob)

Perhaps you have a file on your system you use regularly, you can find out who is also using it with the following command:

# List who is accessing a file
lsof /path/to/a/file

Some Further Reading

  • lsof Survival Guide: A Stack Overflow post with some awesome tricks you can do with this tool.
  • More great lsof examples. This is someones blog who specifically wrote about this tool specifically. They provide many more documented examples of this tools usage here.

Credit

This blog took me a very long time to put together and test! If you like what you see and wish to copy and paste this HOWTO, please reference back to this blog post at the very least. It’s really all I ask.

Configuring a Secure Mail Server on CentOS 6

Introduction

As you know, Mail Server tutorials are all over the internet. So if you stumbled upon mine, then you were like me trying to assemble one based on how others do it to save reading the manual. A mail server is NOT a walk through the park like some other servers are (such as Web, Database, etc). They can be very complicated to master; and frankly, when you’re short on time, it’s easier to just get one going right away. Don’t dismiss the need to learn how it works once you’ve set it up otherwise you may not know how to recover from a unexpected situation. But instead, learn about how it works (and how to further tweak it) after the deadlines have been met. Besides, your boss will be impressed when you show him your successful results sooner then later.

You’re reading this blog because your needs were/are similar to what mine were:

  • You’re looking for an automated solution to installing a mail server with little effort because you’re on a tight deadline.
  • You want a central configuration point; you want everything to be easy to maintain after it’s all set up.
  • You want everything to just work the first time and you want to leave the figuring it out part to the end.
  • Package management and version control is incredibly important to you.

Here is what my tutorial will be focused on:

  • A Database Backend (PostgreSQL) giving you central configuration. This tutorial focuses on version 8.4 because that is what ships with CentOS and Red Hat. But most (if not all) of this tutorial should still work fine if you choose to use version 9.x of the database instead.
  • Spam Control (SpamAssasin v3.3.x, Amavisd-new v2.8, and Clam AntiVirus v0.98) gives you some spam and anti-virus control. I’ve been looking into DSPAM but haven’t implemented it yet. I figure I’ll make a part 2 to this blog once i get around to working with it and mastering it’s setup.
  • Dovecot Pigeonhole v2.0.x provides you a way of routing mail in the users inbox based on it’s content. You can key off of certain content within a received message and mark it as spam, or flag it as important because it was sent by a certain individual, etc. It basically gives you the ability to custom some of the post processing of a received message that passed through all the other checks (spam, anti-virus, etc).
  • Security Considered
  • Mail Delivery Agent (MDA) (DoveCot v2.0.x):
    • Secure POP3 (POP3S) Access
    • Secure IMAP (IMAPS) Access
    • WebMail (RoundCube) Access
Message Delivery Agent Configuration
MDA Configuration
  • Mail Transport Agent (MTA) (PostFix v2.6.x): Secure SMTP (SMTPS)
Mail Transport Agent
MTA Configuration
  • Web Based Administration (PostFixAdmin v2.3.6). Life is made easier when you don’t have to do very much once everything has been set up. Run your server like an ISP would:
    • Virtual Domain Administration: Add/Remove as many as you want
    • Unlimited Virtual Aliases (or restrict if you want) per domain
    • Unlimited Mailboxes (or restrict if you want) per domain
    • Administrative Delegation: Grant enough privileges to another administrator who only has rights to his/her domain you granted them access to.
    • Away on Vacation Support (automatic replies)
Central Configuration
Central Configuration

Please note the application versions identified above as this tutorial focuses specifically on only them. One big issue I found while researching how to set up a mail server was that the other tutorials I found didn’t really mention the software versions they were using. Hence, when I stumbled upon these old article(s) with new(er) software, it made for quite a painful experience when things didn’t work.

Please also note that other tutorials will imply that you setup one feature at a time. Then test it to see if it worked correctly before moving on to the next step. This is no doubt the proper way to do things. However, I’m just going to give it all to you at once. If you stick with the versions and packages I provide… If you follow my instructions, it will just work for you the first time. Debugging on your end will be a matter of tracing back to see what step you missed.

I tried to make this tutorial as cookie cutter(ish) as I could. Therefore you can literally just copy and paste what I share right to your shell prompt and the entire setup will be automated for you.

Hurdles

Just to let you know, I had many hurdles in order to pull this feat off. They were as follows:

  • postfix as shipped with CentOS and in the EPEL repository is not compiled with PostgreSQL support. I had to recompile this package as well just to enable this extra database support.
  • postfixadmin in the EPEL repository has qwirks I wasn’t happy with. I needed to fix a php error that kept coming up. I needed to adjust the database schema and permissions as well as fixing the Vacation Notification feature. I also did not want the mandatory MySQL dependency; so I removed that too.
  • perl Yes… that’s right, I had to patch perl :(. I had to recompile it due to a bug that I don’t belive was correctly addressed. In a nutshell, prior to my custom rebuild, perl-devel would haul in absolutely every development package including kernel headers and the GCC compiler. In the past the perl-devel package could be installed by itself providing us some tools spamassassin and amavisd-new depended on. You’re welcome to use the official version of perl over the one I recompiled; but be prepared to have a ton of compilation tools and source code in your production environment. This is not something I wanted at all. Interestingly enough; after the developers at RedHat said they wouldn’t tackle the issue of rolling their changes back, they seem to be entertaining this new guy’s request who’s asking for a similar alternative. So who knows, maybe newer versions of perl will accommodate mail servers again! 🙂
  • This blog itself was a massive hurdle. There are just so many configuration files and important points to account for that I found it easier to package my own rpm (nuxref-templates-mxserver) containing a series of configuration templates. The templates themselves took a while to construct in such a way that they could all be used together.

Installation

The following seven steps below will get you up and running.

Step 1 of 7: Setup Your Environment

This is the key to my entire blog; it’s going to make all of the remaining steps just work the first time for you. All; I repeat All of the steps below (after this one) assume that you’ve set this environment up. You will need to reset up your environment at least once before running through any of the remaining steps below or they will not work.

Make sure you hook up with my repository and then install all of the necessary packages:

# You'll need to be hooked up to my repository for
# the installation to be this simple. Make sure to
# visit: http://nuxref.com/nuxref-repository/

# You should use yum priorities (if you're not already) to
# eliminate version problems later, but if you intend on 
# just pointing to my repository and not the others, then you'll
# be fine.
yum install -y yum-plugin-priorities

################################################################
# Install our required products
################################################################
yum install -y 
       --enablerepo=nuxref 
       --enablerepo=nuxref-shared 
           postgresql-server postgresql 
           php-pgsql php-imap php-mcrypt php-mbstring  
           dovecot dovecot-pgsql dovecot-pigeonhole 
           clamav amavisd-new spamassassin 
           postfix postfix-perl-scripts 
           roundcubemail postfixadmin 
           nuxref-templates-mxserver

# Choose between NginX or Apache
## NginX Option (a) - This one is my preferred choice:
yum install -y 
       --enablerepo=nuxref 
       --enablerepo=nuxref-shared 
           nginx php-fpm

## Apache Option (b):
yum install -y 
       --enablerepo=nuxref 
       --enablerepo=nuxref-shared 
            httpd php

# Make sure your build of Postfix supports PostgreSQL
# execute the following to see: 
postconf -c /etc/postfix -m | 
   egrep -q '^pgsql$' && echo "Supported!" || 
      echo "Not Supported!"

# If it's not supported then you ran into the first of many
# problems I had. It also means you didn't haul in my version
# from the previous command.  This can happen if you already had
# postfix installed on your machine and it's of a newer version
# then what I'm hosting. Your options at this point is to either
# uninstall your copy of postfix and install mine or recompile 
# your version of postfix (but this time with PostgreSQL support).

# Setup Default Timezone for PHP. For a list of supported
# timezones you can visit here: http://ca1.php.net/timezones
TIMEZONE="America/Montreal"
sed -i -e "s|^[ t]*;*(date.timezone[ t]*=).*|1 $TIMEZONE|g" /etc/php.ini

################################################################
# Setup PostgreSQL (v8.4)
################################################################
# The commands below should all work fine on a PostgreSQL v9.x
# database too; but your mileage may vary as I've not personally
# tested it yet.

# Only init the database if you haven't already. This command
# could otherwise reset things and you'll loose everything.
# If your database is already setup and running, then you can
# skip this line
service postgresql initdb

# Now that the database is initialized, configure it to trust
# connections from 'this' server (localhost)
sed -i -e 's/^[ t]*(local|host)([ t]+.*)/#12/g' 
    /var/lib/pgsql/data/pg_hba.conf
cat << _EOF >> /var/lib/pgsql/data/pg_hba.conf
# Configure all local database access with trust permissions
local   all         all                               trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust
_EOF

# Make sure PostgreSQL is configured to start up each time
# you start up your system
chkconfig --levels 345 postgresql on

# Start the database now too because we're going to need it
# very shortly in this tutorial
service postgresql start

To simplify your life, I’ve made the configuration of all the steps below reference a few global variables. The ones identified below are the only ones you’ll probably want to change. May I suggest you paste the below information in your favorite text editor (vi, emacs, etc) and adjust the variables to how you want them making it easier to paste them back to your terminal screen.

# Identify the domain name of your server here
DOMAIN=nuxref.com
# Setup what you want to be your Administrative email account
# Note: This does 'NOT' have to be of the same domain even though
#       thats how I set it up to be. Don't worry if the email
#       address doesn't exist, because when you're all done
#       following this blog, you'll be able to create it!
ADMIN=hostmaster@$DOMAIN

# The following is only used for our SSL Key Generation
COUNTRY_CODE="7K"
PROV_STATE="Westerlands"
CITY="Lannisport"
SITE_NAME="NuxRef"

Now for the rest of the global configuration; There really should be no reason to change any of these values (but feel free to). It’s important that you paste the above information (tailored to your liking’s) as well as the below information below to your command line interface (CLI) of the server you wish to set up.

# PostgreSQL Database
PGHOST=localhost
PGPORT=5432
PGNAME=system_mail
# This is the Read Only access user (or very limited access)
PGROUSER=mailreader
PGROPASS=mailreader
# This is for administration
PGRWUSER=mailwriter
PGRWPASS=mailwriter

# VHost Mail Directory
VHOST_HOME=/var/mail/vhosts
VHOST_UID=5000
# No real reason to make them differ
# but define tis variable anyway for
# below configuration to work
VHOST_GID=$VHOST_UID

# RoundCube Configuration
MXHOST=$PGHOST
PGRQNAME=system_roundcube
PGRQUSER=roundcube_user
PGRQPASS=roundcube_pass

# This is where our templates get installed to make your life
# incredibly easy and the setup to be painless. These files are
# installed from the nuxref-templates-mxserver RPM package you
# installed above. If you do not have this RPM package then you
# must install it or this blog simply won't work for you.
# > yum install --enablerepo=nuxref nuxref-templates-mxserver
NUXREF_TEMPLATES=/usr/share/nuxref

Once all of this has been set you can proceed to do any of the below steps! Keep in mind that if you decide to change any of the variables above, you may need to redo every single step identified below.

Step 2 of 7: System Preparation

First off, Make sure you’ve set up your environment correctly (defined in Step 1 above) or you will have problems with the outcome of this step!
General:

# Create vmail user; this will be a secure user no one else has
# permissions to that we can lock and keep our mail private
# from any prying eyes of people who have or gain access to our
# server.
useradd -u $VHOST_UID -d /var/mail/vhosts -M -s /sbin/nologin vmail
mkdir -p  /var/mail/vhosts/
chown vmail.vmail /var/mail/vhosts/
chmod 700 /var/mail/vhosts/

# Create a clam user we can preform our anti-virus scans as
usermod -G amavis clam

# Fix ClamD Sock Reference
sed -i -e 's|/var/spool/amavisd/clamd.sock|/var/run/clamav/clamd.sock|g' /etc/amavisd/amavisd.conf

# Fix Amavis Directory Permission
chmod 1770 /var/spool/amavisd/tmp/
# Amavis Domain Configuration
sed -i -e '/NuxRef BulletProofing/d' 
       -e "s/(# $myhostname.*)/1n$myhostname = 'mail.$DOMAIN'; # NuxRef BulletProofing/g" /etc/amavisd/amavisd.conf
sed -i -e "s/^($mydomain[ t]*=[ t]*).*$/1'$DOMAIN';/g" /etc/amavisd/amavisd.conf

# Generate SSL Keys (if you don't have any already) that we
# will secure all our inbound and outbound mail as.
openssl req -nodes -new -x509 -days 730 -sha256 -newkey rsa:2048 
   -keyout /etc/pki/tls/private/$DOMAIN.key 
   -out /etc/pki/tls/certs/$DOMAIN.crt 
   -subj "/C=$COUNTRY_CODE/ST=$PROV_STATE/L=$CITY/O=$SITE_NAME/OU=IT/CN=$DOMAIN"

# Permissions; protect our Private Key
chmod 400 /etc/pki/tls/private/$DOMAIN.key

# Permissions; protect our Public Key
chmod 444 /etc/pki/tls/certs/$DOMAIN.crt

Create PostgreSQL Mail Database:

# Optionally Eliminate Reset Database.
/bin/su -c "/usr/bin/dropdb -h $PGHOST -p $PGPORT $PGNAME 2>&1" postgres &>/dev/null
/bin/su -c "/usr/bin/dropuser -h $PGHOST -p $PGPORT $PGRWUSER 2>&1" postgres &>/dev/null
/bin/su -c "/usr/bin/dropuser -h $PGHOST -p $PGPORT $PGROUSER 2>&1" postgres &>/dev/null

# Create Read/Write User (our Administrator)
echo "Enter the role password of '$PGRWPASS' when prompted"
/bin/su -c "/usr/bin/createuser -h $PGHOST -p $PGPORT -S -D -R $PGRWUSER -P 2>&1" postgres

# Create Read-Only User
echo "Enter the role password of '$PGROPASS' when prompted"
/bin/su -c "/usr/bin/createuser -h $PGHOST -p $PGPORT -S -D -R $PGROUSER -P 2>&1" postgres

# Create our Database and assign it our Administrator as it's owner
/bin/su -c "/usr/bin/createdb -h $PGHOST -p $PGPORT -O $PGRWUSER $PGNAME 2>&1" postgres 2>&1

# Secure and protect a temporary file to work with
touch /tmp/pgsql.postfix.schema.sql
chmod 640 /tmp/pgsql.postfix.schema.sql
chown root.postgres /tmp/pgsql.postfix.schema.sql

# the below seems big; but will work fine if you just copy and
# it as is right to your terminal: This will prepare the SQL
# statement needed to build your mail server's database backend
sed -e '/^--?/d' 
    -e "s/%PGROUSER%/$PGROUSER/g" 
    -e "s/%PGRWUSER%/$PGRWUSER/g" 
    -e "s/%DOMAIN%/$DOMAIN/g" 
        $NUXREF_TEMPLATES/pgsql.postfix.template.schema.sql > 
          /tmp/pgsql.postfix.schema.sql

# load DB
/bin/su -c "/usr/bin/psql -h $PGHOST -p $PGPORT -f /tmp/pgsql.postfix.schema.sql $PGNAME 2>&1" postgres 2>&1
# cleanup
/bin/rm -f /tmp/pgsql.postfix.schema.sql

Step 3 of 7: Setup our Mail Transfer Agent (MTA): Postfix

First off, Make sure you’ve set up your environment correctly (defined in Step 1 above) or you will have problems with the outcome of this step!

################################################################
# Configure Postfix (MTA)
################################################################
# Create backup of configuration files
[ ! -f /etc/postfix/main.cf.orig ] && 
   cp /etc/postfix/main.cf /etc/postfix/main.cf.orig
[ ! -f /etc/postfix/master.cf.orig ] && 
   cp /etc/postfix/master.cf /etc/postfix/master.cf.orig

# Directory to store our configuration in
[ ! -d /etc/postfix/pgsql ] && 
    mkdir -p /etc/postfix/pgsql

# Secure this new directory since it will contain passwords
# information
chmod 750 /etc/postfix/pgsql
chown root.postfix /etc/postfix/pgsql

# Now using our templates, build our SQL files:
for FILE in relay_domains.cf 
            transport_maps.cf 
            virtual_alias_maps.cf 
            virtual_domains_maps.cf 
            virtual_mailbox_limit_maps.cf 
            virtual_mailbox_maps.cf
do
    sed -e "/^#?/d" 
        -e "s/%PGROUSER%/$PGROUSER/g" 
        -e "s/%PGROPASS%/$PGROPASS/g" 
        -e "s/%PGHOST%/$PGHOST/g" 
        -e "s/%PGNAME%/$PGNAME/g" 
            $NUXREF_TEMPLATES/pgsql.postfix.template.$FILE > 
                /etc/postfix/pgsql/$FILE
done

# main.cf
sed -e "/^#?/d" 
    -e "s/%DOMAIN%/$DOMAIN/g" 
    -e "s|%VHOST_HOME%|$VHOST_HOME|g" 
    -e "s/%VHOST_UID%/$VHOST_UID/g" 
    -e "s/%VHOST_GID%/$VHOST_GID/g" 
        $NUXREF_TEMPLATES/pgsql.postfix.template.main.cf > 
            /etc/postfix/main.cf

# master.cf
cat $NUXREF_TEMPLATES/pgsql.postfix.template.master.cf > 
   /etc/postfix/master.cf

# Run the newaliases command to generate /etc/aliases.db
newaliases

# Vacation Support
echo "autoreply.$DOMAIN vacation:" > /etc/postfix/transport
postmap /etc/postfix/transport

# Update to latest Spam Assassin Rules/Filters
sa-update
# Update to latest Antivirus
freshclam

# Setup Auto-Cron Entries (so future antivirus updates
# can just be automatic).
sed -i -e '//etc/cron.hourly/d' /etc/crontab
sed -i -e '//etc/cron.daily/d' /etc/crontab
sed -i -e '//etc/cron.weekly/d' /etc/crontab
sed -i -e '//etc/cron.monthly/d' /etc/crontab
cat << _EOF >> /etc/crontab
  01 * * * * root run-parts /etc/cron.hourly 
  02 4 * * * root run-parts /etc/cron.daily 
  22 4 * * 0 root run-parts /etc/cron.weekly 
  42 4 1 * * root run-parts /etc/cron.monthly
_EOF

# Enable our Services On Reboot
chkconfig --levels 345 spamassassin on
chkconfig --levels 345 clamd on
chkconfig --levels 345 clamd.amavisd on
chkconfig --levels 345 amavisd on
chkconfig --levels 345 postfix on

# Start all of our other services if they aren't already
service spamassassin start
service clamd start
service amavisd start
service clamd.amavisd start

# Restart our postfix service to on our new configuration
service postfix restart

Step 4 of 7: Setup our Mail Delivery Agent (MDA): Dovecot

First off, Make sure you’ve set up your environment correctly (defined in Step 1 above) or you will have problems with the outcome of this step!

################################################################
# Configure Dovecot (MDA)
################################################################
# Create backup of configuration files
[ ! -f /etc/dovecot/dovecot.conf.orig ] && 
   cp /etc/dovecot/dovecot.conf /etc/dovecot/dovecot.conf.orig

# dovcot.conf
sed -e "/^#?/d" 
    -e "s/%DOMAIN%/$DOMAIN/g" 
    -e "s|%VHOST_HOME%|$VHOST_HOME|g" 
    -e "s/%VHOST_UID%/$VHOST_UID/g" 
    -e "s/%VHOST_GID%/$VHOST_GID/g" 
        $NUXREF_TEMPLATES/pgsql.dovecot.template.dovecot.conf > 
            /etc/dovecot/dovecot.conf

# Create our Sieve Directories
[ ! -d /var/lib/sieve/users ] && 
   mkdir -p /var/lib/sieve/users
[ ! -d /var/lib/sieve/before.d ] && 
   mkdir -p /var/lib/sieve/before.d
[ ! -d /var/lib/sieve/after.d ] && 
   mkdir -p /var/lib/sieve/after.d
chown -R vmail.vmail /var/lib/sieve
chmod 750 /var/lib/sieve

# Dovecot PostgreSQL Configuration
for FILE in dovecot-sql.conf 
            dovecot-dict-user-quota.conf 
            dovecot-dict-domain-quota.conf
do
   sed -e "/^#?/d" 
       -e "s|%VHOST_HOME%|$VHOST_HOME|g" 
       -e "s/%VHOST_UID%/$VHOST_UID/g" 
       -e "s/%VHOST_GID%/$VHOST_GID/g" 
       -e "s/%PGROUSER%/$PGROUSER/g" 
       -e "s/%PGROPASS%/$PGROPASS/g" 
       -e "s/%PGHOST%/$PGHOST/g" 
       -e "s/%PGNAME%/$PGNAME/g" 
           $NUXREF_TEMPLATES/pgsql.dovecot.template.$FILE > 
               /etc/dovecot/$FILE
   # permissions
   chmod 640 /etc/dovecot/$FILE
   chown root.dovecot /etc/dovecot/$FILE
done

# Warning Message when mailbox is almost full
sed -e "/^#?/d" 
    -e "s/%DOMAIN%/$DOMAIN/g" 
        $NUXREF_TEMPLATES/pgsql.dovecot.template.mail-warning.sh > 
            /usr/libexec/dovecot/mail-warning.sh

# Make Script Executable
chmod 755 /usr/libexec/dovecot/mail-warning.sh

# Ensure Dovecot starts after each system reboot:
chkconfig --levels 345 dovecot on

# Start Dovecot (otherwise restart it if it's already running)
service dovecot status && service dovecot restart || service dovecot start

Step 5 of 7: Setup Postfix Admin

First off, Make sure you’ve set up your environment correctly (defined in Step 1 above) or you will have problems with the outcome of this step!

################################################################
# Configure PostfixAdmin
################################################################
sed -e "/^//?/d" 
    -e "s/%DOMAIN%/$DOMAIN/g" 
    -e "s/%ADMIN%/$ADMIN/g" 
    -e "s/%PGHOST%/$PGHOST/g" 
    -e "s/%PGNAME%/$PGNAME/g" 
    -e "s/%PGRWUSER%/$PGRWUSER/g" 
    -e "s/%PGRWPASS%/$PGRWPASS/g" 
        $NUXREF_TEMPLATES/pgsql.postfixadmin.template.config.local.php > 
            /etc/postfixadmin/config.local.php

# Protect file since it contains passwords
chmod 640 /etc/postfixadmin/config.local.php
chown root.apache /etc/postfixadmin/config.local.php

# Vacation Auto-respond Support
sed -e "/^#?/d" 
    -e "s/%DOMAIN%/$DOMAIN/g" 
    -e "s/%PGROUSER%/$PGROUSER/g" 
    -e "s/%PGROPASS%/$PGROPASS/g" 
    -e "s/%PGHOST%/$PGHOST/g" 
    -e "s/%PGNAME%/$PGNAME/g" 
        $NUXREF_TEMPLATES/pgsql.postfixadmin.template.vacation.conf > 
            /etc/postfixadmin/vacation.conf

# Protect file since it contains passwords
chmod 640 /etc/postfixadmin/vacation.conf
chown root.vacation /etc/postfixadmin/vacation.conf

# Log Rotation
cat << _EOF > /etc/logrotate.d/postfix-vacation
/var/log/vacation.log {
        missingok
        notifempty
        create 644 vacation root
}
_EOF

Now you can setup NginX to host your administration; in the below example, I set up https://postfixadmin.<your.domain>/ to go to the postfixadmin page.

# Create dummy favicon.ico for now (silences some log entries)
touch /usr/share/postfixadmin/favicon.ico

# PostfixAdmin NginX Configuration
sed -e "/^#?/d" 
    -e "s/%DOMAIN%/$DOMAIN/g" 
        $NUXREF_TEMPLATES/nginx.postfixadmin.template.conf > 
            /etc/nginx/conf.d/postfixadmin.conf

# You may have to bump php-fpm to be safe (if it's not already running)
service php-fpm status 2>/dev/null && service php-fpm restart || service php-fpm start
# make sure it starts on every reboot too:
chkconfig php-fpm --level 345 on
# Restart NginX if it's not already
service nginx status 2>/dev/null && service nginx restart || service nginx start
chkconfig nginx --level 345 on

Once you’re complete that, you’re now ready to access the administrator interface and set up a new account. Simply visit https://postfixadmin.<your.domain>/setup.php. The templates I provided will set the system password to admin. You’ll need to always enter this value prior to creating an account below.
PostfixAdmin Setup
Once you’re done creating an account, just change the setup.php script to read-only as a security precaution. You can preform every other action you’ll ever need using the account you already created.

################################################################
# Disable Future System Administrator Creation
################################################################
chmod 600 /usr/share/postfixadmin/setup.php

# later on, you can re-enable the setup.php file to create a new
# account in the distant future by just typing:
#
# chmod 644 /usr/share/postfixadmin/setup.php
#

You can simply access https://postfixadmin.<your.domain>/ now (without the setup.php) and login using the new administrator you created.

Step 6 of 7: Setup Roundcube

First off, Make sure you’ve set up your environment correctly (defined in Step 1 above) or you will have problems with the outcome of this step!

################################################################
# Configure RoundCube Mailer
################################################################
# RoundCube NginX Configuration
sed -e "/^#?/d" 
    -e "s/%DOMAIN%/$DOMAIN/g" 
        $NUXREF_TEMPLATES/nginx.roundcubemail.template.conf > 
            /etc/nginx/conf.d/roundcubemail.conf

# Optionally Eliminate Reset RoundCube Database
/bin/su -c "/usr/bin/dropdb -h  $PGHOST -p $PGPORT $PGRQNAME 2>&1" postgres &>/dev/null
/bin/su -c "/usr/bin/dropuser -h $PGHOST -p $PGPORT $PGRQUSER 2>&1" postgres &>/dev/null

# Create RoundCube Admistrator User
echo "Enter the role password of '$PGRQPASS' when prompted"
/bin/su -c "/usr/bin/createuser -h $PGHOST -p $PGPORT -S -D -R $PGRQUSER -P 2>&1" postgres 2>&1

# Create our Database and assign it our Administrator as it's owner
/bin/su -c "/usr/bin/createdb -h $PGHOST -p $PGPORT -O $PGRQUSER $PGRQNAME 2>&1" postgres 2>&1
/usr/bin/psql -h $PGHOST -p $PGPORT -U $PGRQUSER $PGRQNAME -f /usr/share/doc/roundcubemail-0.9.5/SQL/postgres.initial.sql

# Configure Roundmail
sed -i -e "s|(^[ t]*$rcmail_config[[']db_dsnw['"]][ t]*=).*$|1 'pgsql://$PGRQUSER:$PGRQPASS@$PGHOST/$PGRQNAME';|g" /etc/roundcubemail/db.inc.php
sed -i -e "s|(^[ t]*$rcmail_config[[']default_host['"]][ t]*=).*$|1 'ssl://$MXHOST:993';|g" /etc/roundcubemail/main.inc.php
sed -i -e "s|(^[ t]*$rcmail_config[[']smtp_server['"]][ t]*=).*$|1 'tls://$MXHOST';|g" /etc/roundcubemail/main.inc.php
sed -i -e "s|(^[ t]*$rcmail_config[[']smtp_user['"]][ t]*=).*$|1 '%u';|g" /etc/roundcubemail/main.inc.php
sed -i -e "s|(^[ t]*$rcmail_config[[']smtp_pass['"]][ t]*=).*$|1 '%p';|g" /etc/roundcubemail/main.inc.php

# Some extra Roundmail options I preferred:
sed -i -e "s|(^[ t]*$rcmail_config[[']identities_level['"]][ t]*=).*$|1 3;|g" /etc/roundcubemail/main.inc.php
sed -i -e "s|(^[ t]*$rcmail_config[[']quota_zero_as_unlimited['"]][ t]*=).*$|1 true;|g" /etc/roundcubemail/main.inc.php
sed -i -e "s|(^[ t]*$rcmail_config[[']htmleditor['"]][ t]*=).*$|1 1;|g" /etc/roundcubemail/main.inc.php
sed -i -e "s|(^[ t]*$rcmail_config[[']preview_pane['"]][ t]*=).*$|1 true;|g" /etc/roundcubemail/main.inc.php

# You may have to bump php-fpm to be safe (if it's not already running)
service php-fpm status 2>/dev/null && service php-fpm restart || service php-fpm start
# make sure it starts on every reboot too:
chkconfig php-fpm --level 345 on
# Restart NginX if it's not already
service nginx status 2>/dev/null && service nginx restart || service nginx start
chkconfig nginx --level 345 on

You can simply access https://roundcube.<your.domain>/ now and access any mailboxes you configured. Just remember to tell your users that they must specify their full email address as their username.

Each mailbox you create using PostfixAdmin you’ll be able to access with your Roundcube webpage.

Step 7 of 7: Security

If you aren’t familiar with Fail2Ban; now would be an excellent time to learn about it. I wrote a blog about securing your CentOS system a while back and encourage you to read it. At the very least, read the section on Fail2Ban. The below explains how you can protect yourself from brute force.

# Monitor for multiple failed SASL Logins into postfix
cat << _EOF > /etc/fail2ban/filter.d/postfix-sasl.conf
[Definition]
failregex = (?i): warning: [-._w]+[<HOST>]: SASL (?:LOGIN|PLAIN|(?:CRAM|DIGEST)-MD5) authentication failed(: [A-Za-z0-9+/ ]*)?$
ignoreregex =
_EOF
# Monitor for multiple failed postfixadmin logins
cat << _EOF > /etc/fail2ban/filter.d/postfixadmin-auth.conf
[Definition]
failregex = ^<HOST> -.*POST.*login.php HTTP[^ t]+ 500$
ignoreregex =
_EOF

# Now in /etc/fail2ban/jail.conf you will want the following:
cat << _EOF >> /etc/fail2ban/jail.conf
[dovecot-iptables]
enabled  = true
filter   = dovecot
backend  = polling
action   = iptables[name=dovecot, port=110,143,993,995, protocol=tcp]
           sendmail-whois[name=dovecot, dest=root, sender=fail2ban@$DOMAIN]
logpath  = /var/log/mail.log

[sasl-iptables]
enabled  = true
filter   = postfix-sasl
backend  = polling
action   = iptables[name=sasl, port=25,587, protocol=tcp]
           sendmail-whois[name=sasl, dest=root, sender=fail2ban@$DOMAIN]
logpath  = /var/log/mail.log

[roundcube-iptables]
enabled  = true
filter   = roundcube-auth
backend  = polling
action   = iptables[name=RoundCube, port="http,https"]
           sendmail-whois[name=RoundCube, dest=root, sender=fail2ban@$DOMAIN]
logpath  = /var/log/roundcubemail/errors

[pfixadmadmin-iptables]
enabled  = true
filter   = postfixadmin-auth
backend  = polling
action   = iptables[name=PostfixAdmin, port="http,https"]
           sendmail-whois[name=PostfixAdmin, dest=root, sender=fail2ban@$DOMAIN]
logpath  = /var/log/nginx/postfixadmin.access.log
_EOF

You will want to additionally add the following to your iptables /etc/sysconfig/iptables:

#---------------------------------------------------------------
# Web Traffic (for PostfixAdmin and RoundCube)
#---------------------------------------------------------------
# Allow non-encrypted port so we can redirect these users to the
# encrypted version.  It's just a nicer effect to support
# redirection
-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 443 -j ACCEPT

#---------------------------------------------------------------
# SMTP /Message Transfer Agent Communication
#---------------------------------------------------------------
-A INPUT -m state --state NEW -m tcp -p tcp --dport 465 -j ACCEPT

#---------------------------------------------------------------
# IMAP 
#---------------------------------------------------------------
-A INPUT -m state --state NEW -m tcp -p tcp --dport 993 -j ACCEPT

#---------------------------------------------------------------
# POP3
#---------------------------------------------------------------
-A INPUT -m state --state NEW -m tcp -p tcp --dport 995 -j ACCEPT

Useful Commands When Running Your Mail Server

  • doveconf -a: Display Dovecot configuration
  • postconf -n: Display Postfix configuration
  • postqueue -p: Display mail queue information

Sometimes when first playing with quotas, you may or may not want to recalculate them against a user. This can be done as follows:

#  Recalculate a specific users quota
doveadm quota recalc -u foobar@your.domain.com

# Or you can do this and recalculate ALL user quotas
doveadm quota recalc -A

# You will also want to run the following command if you decide
# to recalculate someone (or all) in your database:
# UPDATE domain_quota SET bytes=sq.sb, messages=sq.sm 
#   FROM (SELECT 'your.domain.com',
#           sum(bytes) as sb, sum(messages) as sm from quota2 WHERE
#            username like '%@your.domain.com') AS sq 
#   WHERE domain = 'your.domain.com';

Note: If you delete a mailbox for a specified domain, remember to manually remove: /var/mail/vhosts/domain/user

So… That’s it? Now I’m done?

Yes and No… My blog pretty much hands over a working mail server with little to no extra configuration needed on your part. But to properly accept mail from other people around the world, you will need:

  1. This mail server (ideally) must be accessible to the internet via a static IP address. This means that if you’re hosting this at home, the IP address your ISP provides you may not work (Dynamic IP vs Static IP). That said; a lot of ISPs can offer you a static IP if you don’t already have one for little (to no) extra cost.
  2. Your own domain name (if you don’t have an official one already) because you can’t have an email@your.domain.com if your.domain.com isn’t publicly recognized.
  3. A Mail Exchange (MX) Record that points to your new mail server by via it’s accessible IP on the internet. This is the only way the outside world will be able to send mail back to you; this step is not required if the only thing you’re doing is sending email out.

    Most Domain Registars allow you to set your own MX record (GoDaddy.com, NameCheap.com) which can simply be entered right from their webpage with little to no effort. If you’re paying someone else to host your websites for the domain you own, then most likely they have the MX record already pointing to them. You may need to open a support ticket (or call them) and tell them you want the MX record changed to point back to your own server instead or forward it.

Please keep in mind that there are risks involved with running your own mail server. You can get yourself temporarily (or permantenly) blacklisted if you’re not careful. Once you’re blacklisted, you’ll have a very hard time getting other mail servers on the internet to accept your emails for delivery. Blacklisting occurs when mail servers (which you will now be interacting with) detect abuse. In most cases, the mail server administrator (this is YOU) won’t even know you’re abusing other peoples servers. The abuse will start under your nose from emails that originated from your system by those you given mailboxes too. In fact, once your domain is blacklisted; it can be a pain in the @$$ to get de-listed later. A Blacklisted domain’s emails usually never reaches their intended recipient. Instead, they are immediately flagged as spam and sent to the deleted items (by the remote server). The configuration I provided considers most of the cases, but you still need to consider:

  • Don’t create mailboxes for people that you know intend to use it for derogatory purposes or for the intentions of spamming others. Hence; don’t allow users to send out hundreds of thousands of emails a day to a massive distribution on a regular bases even if it’s meaningful mail. Consider that the same stuff that you don’t like in your inbox is the same stuff that nobody else likes in theirs either. 🙂
  • Don’t allow your mail server to relay mail from untrusted sources. Hence; make sure you only allow users you create accounts for to send mail from your server.
  • Throttle all outbound mail delivery to each of their relay locations. With respect to the first point, even if you have to send massive amounts of mail from your system on a regular basis, do it in small batches. This way you won’t overwhelm the remote servers accepting your mail you want delivered.

If you followed my blog and are using the settings I put in place, then you’re already configured for the last 2 options above. The first option is governed by your own decisions.

No system is bulletproof; disaster can always strike when you’re least expecting it. To cover yourself, always consider backups of the following:

  • Your PostgreSQL Database: This is where all of your mail configuration is for both your MTA and MDA. You definitely do not want to lose this. May I suggest you reference my other blog entry here where I wrote a really simple backup/restore tool for a PostgreSQL database.
  • /etc/postfixadmin/*: Your Postfix Admin flat file configuration allowing you to centrally manage everything via a webpage.
  • /etc/postfix/*: Your Postfix flat file configuration which defines the core of your MTA. It’s configuration allows you to centrally manage everything else through the Postfix Administration website.
  • /etc/roundcube/*: Your Roundcube flat file configuration which allowing users to check their mail via a webpage you host.
  • /etc/dovecot/*: Your Dovecot flat file configuration which defines the core of your MDA. It’s configuration allows you to centrally manage everything through the Postfix Administration website.
  • /var/mail/vhosts/*: All of your user’s mailboxes are defined here. This is a vast storage of unread and read mail that resides on your server.

Oct 20th, 2014 Security Update: Handles The Poodle SSLv3 Exploit
Last week a security exploit was found specifically targeting web hosting making use of the SSLv3 (see here for details) protocol. Previously, the NginX templates (residing in nuxref-templates-mxserver version 1.0.1 or less) I provided did not protect you from this vulnerability. As a result; earlier readers of this blog entry may be susceptible to a man-in-the-middle attack. I just recently posted an update to the nuxref-templates-mxserver (v1.0.2) which will automatically cover any new bloggers building a mail server. For the previous readers, you just need to make 2 changes to correct yourself of this exploit:

  1. Open up /etc/nginx/conf.d/roundcubemail.conf and /etc/nginx/conf.d/postfixadmin.conf and change this:
       ssl_ciphers HIGH:!ADH:!MD5;
       ssl_prefer_server_ciphers on;
       ssl_protocols SSLv3;
       ssl_session_cache shared:SSL:1m;
       ssl_session_timeout 15m;
    

    to this:

       ssl_session_timeout  5m;
       ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
       ssl_ciphers 'ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:kEDH+AESGCM:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES256-SHA:DHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA256:DHE-RSA-AES256-SHA256:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128:AES256:AES:DES-CBC3-SHA:HIGH:!aNULL:!eNULL:!EXPORT:!DES:!RC4:!MD5:!PSK';
       ssl_prefer_server_ciphers on;
       ssl_session_cache  builtin:1000  shared:SSL:10m;
    

    This information was based on a great blog entry on securing your NginX configuration found here.

  2. Finally you will want to reload your NginX configuration so it takes on the new updates you applied:
    # Reload NginX
    service nginx reload
    

What about Apache?

Apache is a perfectly fine alternative solution as well! I simply chose NginX because it is much more lightweight approach. In fact, PostfixAdmin and RoundCube mail already come with Apache configuration out of the box located in /etc/httpd/conf.d/. Thus, if you simply start up your Apache instance (service httpd start), you will be hosting its services right away. Please keep in mind that the default (Apache) configuration does not come with all the SSL and added security I provided with the NginX templates. Perhaps later on, I will update the template rpm to include an Apache secure setup as well.

Credit

This blog took me a very (,very) long time to put together and test! The repository hosting alone now accommodates all my blog entries up to this date. If you like what you see and wish to copy and paste this HOWTO, please reference back to this blog post at the very least. It’s really all I ask.

Repository

This blog required me to set up my own repository of which I was thinking that some people might want me to continue to maintain. Such as fetching and applying the latest security updates after testing them first for the continued promise of stability. Personally, I believe I may be setting up a new can of worms for myself by offering this service because bandwidth costs money and updates cost time. But I’ll share it publicly and see how things go anyway.

If you’d like me to continue to monitor and apply updates as well as hosting the repository for long terms, please consider donating or offering a mirror server to help me out! This would would be greatly appreciated!

Sources

This blog could not have been made possible without the tons of resources that I used. Some of these resources including other peoples blogs too which I picked and chose approaches they took.

Software Resources

Backing Up and Restoring Your PostgreSQL Database

Introduction

As a Developer or even a System Administrator, experiencing database corruption is a scary thing. Usually when something you’re responsible fails… it fails hard. It’s important to always make frequent backups of your database(s) your in charge of. The internet is filled with solutions and one liners you can consider. But there is a lot of contradiction everywhere too as each site tells you how it should be done. Personally, I’m not going to tell you the way I do it is the correct way or not. But I will tell you it works, and it’s saved me in numerous occasions.

I’m lazy, and when I look for solutions online, I always hope someone is just going to spell it right out for me. It’s so much easier when someone else does all the work; but usually that’s wasn’t the case for me. Stack Overflow is fantastic for getting the quick 1 liners you need to build your solution, but I prefer automation. I don’t want to remember all of the different options one tool has from another. I want simplicity because when something fails, I don’t want to learn the complicated steps to do a database restoration.

As a strong supporter of PostgreSQL, I want to provide the solution I use to hopefully speed along someone else’s research in the future. I can’t stress enough also that if you aren’t taking regular backups of databases you are responsible for, then you really should reconsider and at use the scripts defined below.

Jun 28th, 2016 Update
I packaged pgnux_backup, pgnux_restore and pgnux_kickusers into an RPM to simplify things:
RPM: pgnux-1.0.3-1.el6.nuxref.noarch.rpm
Source: pgnux-1.0.3-1.el6.nuxref.src.rpm

Those who use CentOS/Red Hat 7 can get them here:
RPM: pgnux-1.0.3-1.el7.nuxref.noarch.rpm
Source: pgnux-1.0.3-1.el7.nuxref.src.rpm

These RPMs can also be fetch from my nuxref repository.

The rest of this blog contains a simplified version of the content found in the attached RPMs but should still suit the needs of most!

Database Backup

Assuming you you are the global system administrator responsible for a PostgreSQL database and have root privileges, here is a simple backup script you can use:

#!/bin/bash
# Author: Chris Caron <lead2gold at gmail.com>
# Name: pgnux_backup
# Description: Preform a backup of one ore more PostgreSQL Database(s)
# Returns: 0 on success, a non-zero value if a failure occurred
# Syntax: pgnux_backup [db1 [db2 [db3 [...]]]
#
# Note: If no database is specified to be backed up, then all of
#       databases hosted by the postgresql instance are used.

##################################
# Define some simple variables
##################################
PGENGINE=/usr/bin
PGPORT=5432
PGHOST=localhost
DUMPDIR=/var/pgbackup

##################################
# System Tweaks
##################################
# if COMPRESSION is enabled, then all database backups are
# gzipped to save space.  The trade off is more cpu power will
# be used to generate your backups and they will take longer
# to create.  But you can save yourself significant amounts of
# disk space.  Set this value to 0 if you do not wish to use
# compression. COMPRESSION can be set to a value between 0 and
# 9 where a setting of 0 is used to disable it.
# Please keep in mind also, that if you choose to use
# compression, the PostgreSQL restores will be a bit slower since
# the data needs to be first uncompressed before it can be
# reloaded.
COMPRESSION=6

# For SELinux we need to use 'runuser' not 'su'
if [ -x /sbin/runuser ]; then
    SU=/sbin/runuser
else
    SU=/bin/su
fi

# Safety Check
if [ $(whoami) != 'root' ]; then
   echo "Error: You must be 'root' to use this script."
   exit 1
fi

# List of databases you wish to back up by default.  If you
# leave this blank, then the system will automatically detect
# all of the databases hosted by the PostgreSQL instance.
#
# You can use Space, Tab or some form of white space delimiter
# between each database you specify if you need to specify more
# than one.
# If nothing is specified on the command line (when this script
# is called, then this is the default list that will be used in
# it's place.
DBLIST=""

# If anything was specified on the command line; we over-ride it here
[ ! -z "$@" ] && DBLIST="$@"

# Capture the current time to make it easy to locate our backups later
SNAPTIME=$(date +'%Y%m%d%H%M%S')

# Build DBLIST if it isn't already defined:
[ -z "$DBLIST" ] && DBLIST=$($SU -l postgres -c "$PGENGINE/psql 
                          -p $PGPORT -h $PGHOST -lt | 
                   awk 'NF >=5 { print $1}' | 
                    egrep -v '^[ t]*(template[0|1]||)')

# Create our backup directory if it doesn't already exist
[ ! -d $DUMPDIR ] && mkdir -p $DUMPDIR

# Ensure our directory is protected from prying eyes
chmod 770 $DUMPDIR
chown root.postgres $DUMPDIR

# Backup our Database Globals (exit on failure)
$SU -l postgres -c "$PGENGINE/pg_dumpall --globals-only 
   -p $PGPORT -h $PGHOST | 
   gzip -c > $DUMPDIR/${SNAPTIME}-globals.sql.gz"
if [ $? -ne 0 ]; then
   echo -n "$(date +'%Y-%m-%d %H:%M:%S') - $$ - ERROR - "
   echo "Failed to dump system globals; backup aborted."
   exit 2
fi

# Protect our backup and ensure the user postgres user can manage
# this file if need be.
chmod ug+rw "$DUMPDIR/${SNAPTIME}-globals.sql.gz" &>/dev/null
chown postgres.root $DUMPDIR/${SNAPTIME}-globals.sql.gz &>/dev/null

FAIL_COUNT=0
# Iterate over our database list and perform our backup:
for DBNAME in $DBLIST; do
   echo -n "$(date +'%Y-%m-%d %H:%M:%S') - $$ - INFO - "
   echo "Dumping database '$DBNAME' ..."
   PGBKUP=
   PGFLAGS="-p $PGPORT -h $PGHOST --create --oids --format=t --verbose"
   if [ $COMPRESSION -gt 0 ]; then
      # Use Compression
      PGBKUP=$SNAPTIME-$DBNAME.db.gz
      $SU -l postgres -c "$PGENGINE/pg_dump $PGFLAGS $DBNAME | 
                 gzip -cq$COMPRESSSION > $DUMPDIR/$PGBKUP"
      RETVAL=$?
   else
      # Do not compres (flag is not set to 1)
      PGBKUP=$SNAPTIME-$DBNAME.db
      $SU -l postgres -c "$PGENGINE/pg_dump $PGFLAGS 
                 --file=$DUMPDIR/$PGBKUP $DBNAME"
      RETVAL=$?
   fi
   if [ $RETVAL -eq 0 ]; then
      echo -n "$(date +'%Y-%m-%d %H:%M:%S') - $$ - INFO - "
      echo "Backup Complete: $PGBKUP"
      chmod ug+rw $DUMPDIR/$PGBKUP &>/dev/null
      chown postgres.root $DUMPDIR/$PGBKUP &>/dev/null
   else
      [ -f $DUMPDIR/$PGBKUP ] && 
          rm -f $DUMPDIR/$PGBKUP
      echo -n "$(date +'%Y-%m-%d %H:%M:%S') - $$ - ERROR - "
      echo "Backup Failed: $PGBKUP"
      let FAIL_COUNT+=1
   fi
done

if [ $FAIL_COUNT -ne 0 ]; then
   exit 1
fi
exit 0

Database Restore

Backups are one thing, but the ability to restore is another. This script pairs with the above and will allow you to restore different points in time.

#!/bin/bash
# Author: Chris Caron <lead2gold at gmail.com>
# Name: pgnux_restore
# Description: Preform a restore of one ore more PostgreSQL Database(s)
# Returns: 0 on success, a non-zero value if a failure occurred
# Syntax: pgnux_restore <database_name> [snapshot_time]
#
# Note: This script will not work if people are connected to the
#       databases in question you are trying to restore.
 
##################################
# Define some simple variables
##################################
PGENGINE=/usr/bin
PGPORT=5432
PGHOST=localhost
DUMPDIR=/var/pgbackup
 
# How many backups in the past to list when no restore
# has taken place
HISTORY=10
 
# Specify a keyword that when in place of the <database_name>
# it triggers all databases for specified snapshot time to be
# restored including database system globals. Set this to
# 'nothing' if you want the restore to do a global restore every
# time (might be dangerous)
# The below allows you to type 'pgnux_restore --' to restore
# everything.
RESTORE_ALL_KEYWORD="--"

# For SELinux we need to use 'runuser' not 'su'
if [ -x /sbin/runuser ]; then
    SU=/sbin/runuser
else
    SU=/bin/su
fi

# Safety Check
if [ $(whoami) != 'root' ]; then
   echo "Error: You must be 'root' to use this script."
   exit 1
fi

# Not worth continuing if there is no dump directory
if [ ! -d $DUMPDIR ]; then
   echo -n "$(date +'%Y-%m-%d %H:%M:%S') - $$ - ERROR - "
   echo "Directory '$DUMPDIR' does not exist."
   exit 2
fi
  
# Capture Database(s) and allow for delimiters to separate more
# then one. Keep in mind that the first database specified will
# be the one used to detect the SNAPTIME if one isn't specified
DBLIST=$(echo "$1" | sed -e 's/[,:+%!&]/ /g')
 
# Take the value specified on the command line (argument 2)
shift
_SNAPTIME=$(echo "$@" | sed -e 's/[^0-9]//g')
SNAPTIME=
if [ -z "$_SNAPTIME" ]; then
   # Fetch most recent backup time
   SNAPTIME=$(find -L $DUMPDIR -maxdepth 1 -mindepth 1 
        -type f -regex ".*/[0-9]+-.*.(db|db.gz)$" 
        -exec basename {} ; | 
        sed -e 's/^([0-9]+)-(.*).(db|db.gz)$/1/g' | 
        sort -r -n | uniq | head -n1)
   if [ -z "$SNAPTIME" ]; then
      echo -n "$(date +'%Y-%m-%d %H:%M:%S') - $$ - ERROR - "
      echo "No backups detected."
      exit 2
   fi
fi
 
# Initialize Globals Loaded Flag 
GLOBALS_LOADED=1
if [ "$DBLIST" == "$RESTORE_ALL_KEYWORD" ]; then
   # Now we build a list of the databases based on the
   # snapshot time specified
   DBLIST=$(find -L $DUMPDIR -maxdepth 1 -mindepth 1 
      -type f -regex ".*/$SNAPTIME-.*.(db|db.gz)$" 
      -exec basename {} ; | 
      sed -e 's/^[0-9]+-(.*).(db|db.gz)$/1/g')
   GLOBALS_LOADED=0
fi
 
FAILLIST=""
RESTORE_COUNT=0
for DBNAME in $DBLIST; do
   # Toggle 'Globals Loaded Flag' if 'Restore All Keyword' found
   # This neat little trick allows you to force the restores of
   # globals associated with a specified date of the backup
   # hence: pgnux_restore --,foobar_database 2014010100000
   # would cause the globals taken at this time to be loaded
   # with the database,
   [ "$DBLIST" == "$RESTORE_ALL_KEYWORD" ] && GLOBALS_LOADED=0

   # If no Snapshot time was specified, we need to
   # keep detecting it based on the database specified
   [ -z "$_SNAPTIME" ] &&
      SNAPTIME=$(find -L $DUMPDIR -maxdepth 1 -mindepth 1 
        -type f -regex ".*/[0-9]+-$DBNAME.(db|db.gz)$" 
        -exec basename {} ; | 
        sed -e 's/^([0-9]+)-(.*).(db|db.gz)$/1/g' | 
        sort -r -n | uniq | head -n1)
   PGBKUP=$DUMPDIR/$SNAPTIME-$DBNAME.db

   if [ $GLOBALS_LOADED -eq 0 ] && 
      [ -f $DUMPDIR/globals-$SNAPTIME.sql.gz ]; then
      echo -n "$(date +'%Y-%m-%d %H:%M:%S') - $$ - INFO - "
      echo -n $"Restoring Database Globals ..."
         $SU -l postgres -c "gunzip 
             -c $DUMPDIR/globals-$SNAPTIME.sql.gz | 
             $PGENGINE/psql -a -p $PGPORT -h $PGHOST"
      # Toggle Flag
      GLOBALS_LOADED=1
   fi
 
   # Detect Compression
   if [ -f $PGBKUP.gz ]; then
      echo -n "$(date +'%Y-%m-%d %H:%M:%S') - $$ - INFO - "
      echo $"Uncompressing Database $DBNAME ($SNAPTIME)..."
      gunzip -c $PGBKUP.gz > $PGBKUP
      if [ $? -ne 0 ]; then
         FAILLIST="$FAILLIST $DBNAME"
         [ -f $PGBKUP ] && rm -f $PGBKUP
      fi
   fi
 
   [ ! -f $PGBKUP ] && continue
 
   echo -n "$(date +'%Y-%m-%d %H:%M:%S') - $$ - INFO - "
   echo $"Restoring Database $DBNAME ($SNAPTIME)..."
   # This action can fail for 2 reasons:
   #  1. the database doesn't exist (this is okay because we
   #     are restoring it anyway.
   #  2. Someone is accessing the database we are trying to
   #     drop.  You need to make sure you've stopped all
   #     processes that are accessing the particular database
   #     being restored or you will have strange results.
   $SU -l postgres -c "$PGENGINE/dropdb 
          -p $PGPORT -h $PGHOST $DBNAME" &>/dev/null
   # use -C & -d 'template1' so we can bootstrap off of the
   # 'template1' database. Note, that this means we are bound
   # by the character encoding of 'template1' (default is UTF-8)
   $SU -l postgres -c "$PGENGINE/pg_restore -e -v -p $PGPORT 
                   -C -d template1 -h $PGHOST $PGBKUP"
   [ $? -ne 0 ] && FAILLIST="$FAILLIST $DBNAME" || 
        let RESTORE_COUNT+=1
   [ -f $PGBKUP.gz ] && [ -f $PGBKUP ] && rm -f $PGBKUP
done
  
# Spit a readable list of databases that were not recovered if
# required.
if [ ! -z "$FAILLIST" ]; then
   for DBNAME in $FAILLIST; do
      echo "Warning: $DBNAME ($SNAPTIME) was not correctly restored."
   done
   exit 1
elif [ $RESTORE_COUNT -eq 0 ]; then
   # Nothing was restored; now is a good time to display to the user
   # their options
   echo -n "$(date +'%Y-%m-%d %H:%M:%S') - $$ - WARNING - "
   echo $"There were no databases restored."
   echo
   # Display last X backups and the databases associated with each
   BACKUPS=$(find -L $DUMPDIR -maxdepth 1 -mindepth 1 
        -type f -regex ".*/[0-9]+-.*.(db|db.gz)$" 
        -exec basename {} ; | 
        sed -e "s/^([0-9]+)-(.*).(db|db.gz)$/1|2/g" | 
        sort -r -n | head -n$HISTORY)
   [ -z "$BACKUPS" ] && exit 1
 
   LAST_SNAPTIME=""
   printf "    %-16s %sn" "TIMESTAMP" "DATABASE(S)"
   for BACKUP in $BACKUPS; do
      SNAPTIME=$(echo $BACKUP | cut -d'|' -f1)
      DBNAME=$(echo $BACKUP | cut -d'|' -f2)
      if [ "$LAST_SNAPTIME" != "$SNAPTIME" ]; then
          printf "n%s: %s" $(echo "$SNAPTIME" | 
              sed -e 's/([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})/1-2-3_4:5:6/g') "$DBNAME"
          LAST_SNAPTIME=$SNAPTIME
      else
         printf ", %s" "$DBNAME"
      fi
   done
   printf "n"
   exit 1
fi
exit 0

Automation

Consider creating a few crontab entries to manage backups during off hours as well as some tool that can keep your backups from growing out of control on your hard drive. Feel free to adjust the numbers as you see fit. The below assumes you copied the 2 scripts above in /usr/bin

cat << EOF > /etc/cron.d/nuxref_postgresql_backup
# Create a backup of all databases every hour
0  * * * * root /usr/bin/pgnux_backup &>/dev/null
# Only keep the last 30 days of backups (check run every hour on the 40 min mark)
40 * * * * root find /var/pgbackup -maxdepth 1 -type f -mtime +30 -delete &>/dev/null
EOF

Usage

Backups are as simple as the following:

# Backup All databases
pgnux_backup

# Backup just the foobar_database:
pgnux_backup foobar_database

# Backup just the foobar_database and the barfoo_database:
pgnux_backup foobar_database barfoo_database

Restoring requires that no one is accessing the database (if it already exists). Otherwise it’s just as easy:

# Call the script with no parameters, to see a nice list of the last 10 backups.
# This list can be really helpful because it also identifies all of the
# snapshot times that they were taken.  You need these times to preform
# restores with.
pgnux_restore

# Restore ALL databases that resided in the last database snapshot
# Caution: If you just backed up a single database on it's own last
#          time, this might be a bit unsafe to run. If you backed
#          up other databases at a different time, then they won't
#          get included in this restore. The -- also forces the
#          system globals to get reloaded as well.
pgnux_restore --

# Restore the last snapshot taken of the foobar_database
pgnux_restore foobar_database

# Restore the last snapshot taken of the foobar_database and
# barfoo_database notice we can't use space as a delimiter (like
# pgnux_backup does) only because the second argument identifies
# the snapshot time (in case you want your restore to be specific).
pgnux_restore foobar_database,barfoo_database

# With respect to the last point, this will restore a database
# snapshot further back in the past (not the latest one). This
# also assumes you have a backup at this point.  You can find
# this out by running pgnux_restore all by itself as the first
# example given above. The script will take care of the formatting
# of the time so long as you provide YYYYMMDDHHmmss in that format.
# You can stick in as many delimiters to make the time readable as
# you want. The below restore a specific database snapshot:
pgnux_restore foobar_database 2014-03-13_15:47:06

# You can restore more then one database at a specific time as
# well using the a comma (,) to delimit your databases and
# specifying the snapshot time at the end. Here is how to restore
# the foobar_database and the barfoo_database at a specific
# instance of time:
pgnux_restore foobar_database,barfoo_database 2014-03-13_15:47:06

# the -- also plays another role, it can be used to force the loading
# of the postgresql globals again when it's paired with databases
# consider the above line but with this small tweak:
pgnux_restore --,foobar_database,barfoo_database 2014-03-13_15:47:06

Catastrophic Recoveries

If worst case scenario happens, and the restore process seems to be failing you, your not out of options. As long as you’ve been taking frequent backups, there is always a way back!

Remember that restores will fail if the database is being accessed by someone else. The below is a dirty little scripted trick that boots everyone accessing your system off. It’s somewhat dangerous to do, but if restoring is your final goal, then the danger is mitigated.

#!/bin/bash
# Author: Chris Caron <lead2gold at gmail.com>
# Name: pgnux_kickusers
# Description: Kicks all users off of a PostgreSQL instance being
#              accessed.
# Syntax: pgnux_kickusers

##################################
# Define some simple variables
##################################
PGENGINE=/usr/bin
PGPORT=5432
PGHOST=localhost

# For SELinux we need to use 'runuser' not 'su'
if [ -x /sbin/runuser ]; then
    SU=/sbin/runuser
else
    SU=/bin/su
fi

# Safety Check
if [ $(whoami) != 'root' ]; then
   echo "Error: You must be 'root' to use this script."
   exit 1
fi

# Get a list of all PIDS accessing our system while kicking
# the ones we can control via the database
# PostgreSQL 9.2 and above
PIDS=$($SU -l postgres \
      -c "$PGENGINE/psql -h $PGHOST -p $PGPORT -t -E --no-align \
      -c \"SELECT pg_stat_activity.pid, pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid();\"" 2>/dev/null | egrep -v 't' | cut -d'|' -f1)

# PostgreSQL 9.1 and below (un-comment if this is your version):
# PIDS=$($SU -l postgres \
#       -c "$PGENGINE/psql -h $PGHOST -p $PGPORT -t -E --no-align \
#      -c \"SELECT  pg_stat_activity.procpid,pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE procpid <> pg_backend_pid();\"" 2>/dev/null | egrep -v 't' | cut -d'|' -f1)

# Iterate over the left over pids that we couldn't otherwise
# handle and attempt to kick them by sending each of them a SIGTERM.
for PID in $PIDS; do
   RETRY=3
   while [ $RETRY -gt 0 ]; do
      kill $PID &>/dev/null
      ps -p $PID &>/dev/null
      if [ $? -ne 0 ]; then break; fi
      let RETRY-=1
   done
   # All PostgreSQL Documentation will tell you SIGKILL
   # is the absolute 'WORST' thing you can do.
   # The below code should only be uncommented in
   # catastrophic situations where a restore is inevitable
   # ps -p $PID &>/dev/null
   # [ $? -eq 0 ] && kill -9 $PID
done
exit 0

They can also fail if PostgreSQL data directory contents has been corrupted. Corruption can occur:

  • From a system that is experiencing harddrive failures.
  • From another processes accessing /var/lib/pgsql/data/* that shouldn’t be. Only the database itself should be modifying/accessing the contents of this directory unless you know what you’re doing.
  • From just a simple power outage. A busy system that isn’t backed up by a UPS device or redundant power sources can have it’s file system corrupted easily during a loss of power. What usually happens is PostgreSQL is writing content to the database when the power is pulled from the machine. At the end of the day, you’re left with a database that wasn’t correctly written to and in some cases can be seriously damaged from this as a result.

In some cases, the corruption can be so bad the database instance (PostgreSQL) won’t even start. Evidently, you can’t restore a working copy of a database if the service itself isn’t working for you.

Sometimes attempting to repair the write-ahead logging can help you recover. Obviously this completely depends on the type of failure you’re experiencing. /var/lib/pgsql/data/pg_xlog/* can help shed some light on this subject as well. The below script assumes your instance of PostgreSQL is stopped.

# Make sure your database is NOT running if you intend to run the below
# command:
service postgresql stop

Here is the the simple wrapper to the read-ahead write log repair:

#!/bin/bash
# Author: Chris Caron <lead2gold at gmail.com>
# Name: pgnux_repair
# Description: Simple wrapper to pg_resetxlog
# Syntax: pgnux_repair
 
##################################
# Define some simple variables
##################################
PGENGINE=/usr/bin
PGDATA=/var/lib/pgsql/data

# For SELinux we need to use 'runuser' not 'su'
if [ -x /sbin/runuser ]; then
    SU=/sbin/runuser
else
    SU=/bin/su
fi

# Safety Check
if [ $(whoami) != 'root' ]; then
   echo "Error: You must be 'root' to use this script."
   exit 1
fi

# Attempt to repair the WAL logs
$SU -l postgres -c "$PGENGINE/pg_resetxlog -f $PGDATA"

If it still doesn’t work, as a last resort consider these simple commands to bring your system back up again. Note that you should be the root user prior to preforming these steps:

# Stop the database (if it isn't already):
service postgresql stop

# Backup your configuration files
mkdir -p /tmp/postgresql_conf
cp -a /var/lib/pgsql/data/*.conf /tmp/postgresql_conf

# Destroy the database data directory. Unless you changed the
# default directory, the below command will do it:
# This is IRREVERSIBLE, be sure you have a recovery point to
# restore to before doing this otherwise you will make your
# situation worse!
find /var/lib/pgsql/ -mindepth 1 -maxdepth 1 rm -rf {} ;

# Initialize a new empty database; this will rebuild
# the /var/lib/pgsql/data directory for you
service postgresql initdb

# Restore all of your configuration you backed up above:
cp -af /tmp/postgresql_conf/*.conf /var/lib/pgsql/data/

# Just in case, for SELinux Users, make sure we're set okay
restorecon -R /var/lib/pgsql

# Start your database up
service postgresql start

# Now restore your databases using pgnux_restore and make sure you
# include the double dash '--' so that you load the system globals
# back as well:
#   pgnux_restore --,db1,db2,db3,db4,...
#
# If you've always been backing them all together, then you can
# restore them with a single command:
pgnux_restore --

# Your done! You should be back and running now and hopefully with
# minimal downtime.

Disclaimer

I can not be held responsible or liable for anything that goes wrong with your system. This blog is intended to be used as a resource only and will hopefully help you out in the time of need. These tools work for me and have been tested thoroughly using PostgreSQL v8.4 (which ships with CentOS 6.x). I have not tried any of these tools for PostgreSQL v9.x or higher and can not promise you anything, but I honestly can’t see a reason why they wouldn’t work.

Credit

Please note that this information took me several days to put together and test thoroughly. I may not blog often; but I want to re-assure the stability and testing I put into everything I intend share.

If you like what you see and wish to copy and paste this HOWTO, please reference back to this blog post at the very least. It’s really all I ask.