Category Archives: Linux

Postfix, Dovecot and PostGreSQL Setup

In trying to setup a simple mail server on Ubuntu for a couple of small virtual domains with limited users I came across a couple excellent articles, but they were written for use with MySQL.  I have a machine with limited resources running custom software using PostGreSQL.  Although flat files would have been acceptable, I didn’t have the same luck in breadth of documentation.  So, I decided to work with the excellent articles I found and adapt them to Postgres for my server.

The best guide I found was from Linode, and is geared to help customers setting up Email with Postfix, Dovecot, and MySQL on their excellent platform.  The second guide I found left a lot to be desired, but since it was Postgres specific I was able to take those pieces from it and adapt it to the Linode guide.  That guide was called How to setup a postfix email server with Dovecot and published as a community tutorial from DigitalOcean.  While DigitalOcean and Linode are tight competitors, Linode wins out for having the better mail server how-to.

I’m going to mainly show what changes need to be done to the Linode guide in order to make it work with Postgres.  Otherwise use that guide for complete instructions. I’ll assume you already have a Postgres server up and running on that machine.

Postgres – Creating the Database

Your first change will be under the “Creating the Database” heading.  Let’s start by using the DigitalOcean guide to create the user.  Let’s try and keep the Linode naming convention as much as possible though.

First follow the adjustments to the  /etc/postgresql/vers.ion/main/pg_ident.conf  file.

mailmap         dovecot                 mailuser
mailmap         postfix                 mailuser
mailmap         root                    mailuser

Reload postgres and setup the user.

# sudo -u postgres psql
postgres=# CREATE USER mailuser;
postgres=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
postgres=# REVOKE USAGE ON SCHEMA public FROM PUBLIC;
postgres=# GRANT CREATE ON SCHEMA public TO postgres;
postgres=# GRANT USAGE ON SCHEMA public TO postgres;
postgres=# CREATE DATABASE mailserver WITH OWNER mailuser;
postgres=# \q 
# sudo psql -U mailuser -d mailserver
postgres=# \c mailserver
CREATE SEQUENCE seq_mail_domain_id START 1;
CREATE SEQUENCE seq_mail_user_id START 1;
CREATE SEQUENCE seq_mail_alias_id START 1;

CREATE TABLE virtual_domains (
  domain_id INT2 NOT NULL DEFAULT nextval('seq_mail_domain_id'),
  domain_name varchar(50) NOT NULL,
  PRIMARY KEY (domain_id)
);


CREATE TABLE virtual_users (
  user_id INT2 NOT NULL DEFAULT nextval('seq_mail_user_id'),
  domain_id INT2 NOT NULL,
  password varchar(106) NOT NULL,
  email varchar(100) NOT NULL,
  PRIMARY KEY (user_id),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(domain_id) ON DELETE CASCADE
);

CREATE TABLE virtual_aliases (
  alias_id INT2 NOT NULL DEFAULT nextval('seq_mail_alias_id'),
  domain_id INT2 NOT NULL,
  source varchar(100) NOT NULL,
  destination varchar(100) NOT NULL,
  PRIMARY KEY (alias_id),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(domain_id) ON DELETE CASCADE
);

To add your domains to the database:

INSERT INTO virtual_domains (domain_name)
VALUES
  ('example.com'),
  ('hostname.example.com'),
  ('hostname'),
  ('localhost.example.com');

Do a simple select from the virtual_domains table and note the id of the domain you wish to add addresses for:

SELECT * FROM virtual_domains;

According to the postgres document we referenced earlier, you have to get the password from command line like so:

# doveadm pw -s sha512 -r 100
Enter new password: ...
Retype new password: ...
{SHA512}.............................................................==

Now you need to add some email addresses like so:

INSERT INTO virtual_users
  ( domain_id, password , email)
VALUES
  ( '1', '{SHA512}.............................................................==', 'postmaster@example.com'),
  ( '1', '{SHA512}.............................................................==', 'supah_user@example.com');

Now, if you created your user according to the digital ocean document there is no password.  You’ll have to add one for your user like this:

ALTER ROLE mailuser WITH PASSWORD 'supersecretPaSSvvord';

That should be it for your direct Postgres editing. You can follow all the testing they show for MySQL since it looks like standard SQL.

Postfix

You can follow the Linode guide for postfix, only needing to make changes in step 8.  Just change in main.cf where they name their files by prepending mysql: to pgsql: and changing the filenames. Everything else there is the same.

  #Virtual domains, users, and aliases
  virtual_mailbox_domains = pgsql:/etc/postfix/pgsql-virtual-mailbox-domains.cf
  virtual_mailbox_maps = pgsql:/etc/postfix/pgsql-virtual-mailbox-maps.cf
  virtual_alias_maps = pgsql:/etc/postfix/pgsql-virtual-alias-maps.cf

Watch for these filenames further down as you’ll have to remember the change.  The actualy content of the files including the queries themselves from steps 11 on are the same as for MySQL.

The Linode document had a great line to test your db connection in step 15 and on.  Issue this from the shell:

postmap -q cmydomain.com pgsql:/etc/postfix/pgsql-virtual-mailbox-domains.cf

Dovecot

Keep in mind when working with dovecot configuration that if you don’t intend to use pop3, don’t enable it.

In step 25 specify pgsql instead of mysql in the driver line.

In step 27, Note in the dovecot-sql.conf.ext file your default_pass_scheme using the method above from the DigitalOcean document is SHA512,  not SHA512-CRYPT as in the Linode document.

Done!

That’s it! Everything else should be the same.  Follow the Linode guide to test your connections, make sure your firewall is open, and if you have any problems they have an excellent troubleshooting guide linked from the original article.  Enjoy!

Find Conficker Infected Machines with SGUIL

This command line query for mysql will grab for you a list of conficker infected machines for a given date range, their IP address, the count of events in the logs for each machine, and sort them by the biggest offenders. Distribution is SecurityOnion Linux.

# -A turns off "reading table information for completion of table and column names" for faster DB selection
 
mysql -uroot -A
 
use securityonion_db;

# change date range as needed
SELECT 
  INET_NTOA(event.src_ip),
  count(INET_NTOA(event.src_ip)) AS total
FROM event IGNORE INDEX (event_p_key, sid_time) 
WHERE event.timestamp > '2013-04-15' AND event.timestamp < '2013-04-16' 
AND  event.signature like '%Conficker%' 
GROUP BY INET_NTOA(event.src_ip)
ORDER BY INET_NTOA(event.src_ip) ASC

I was unable to get a list of host names of machines, which would have been nice when you have a large list of DHCP clients and aren’t looking at this query until many days after.

Another important query for security purposes is to obtain a list of IP addresses which the Conficker infected machines on your network are trying to contact. In this case I’m going to leave out the date range condition since I’m looking for the IP’s that have had the most activity of all time so I can ban them.  Network wide. For fun. Just ’cause I can.

SELECT 
  INET_NTOA(event.dst_ip),
  count(INET_NTOA(event.dst_ip)) AS total
FROM event IGNORE INDEX (event_p_key, sid_time) 
WHERE event.signature like '%Conficker%' 
GROUP BY INET_NTOA(event.dst_ip)
ORDER BY INET_NTOA(event.dst_ip) ASC

MySQL too many connections – solution

When greeted with a MySQL error this morning stating that a database connection could not be made from our PHP web application I had to do some testing.  First, try connecting to the database.  I did this from a remote host and was thrown back an “Error: too many connections.”

From there you should login as root from the localhost and issue:

SHOW FULL PROCESSLIST;

This will give a list of the connections. Take a good look at the big offenders because you’ll need to troubleshoot that application later and find out why it has so many connections.  Now that you know the problem, restart your MySQL and connections should resume as normal (with the excessive stale connections released) and you can go about the repair.

Also, in your my.cnf you can reduce the wait_timeout which will reduce the amount of time a connection can be held open by an application. The default is 28800 seconds, or 8 hours. I reduced mine to 30 minutes. You could go less but we have some apps that do their own connection pooling and don’t want to mess that up. In my.cnf:

wait_timeout = 1800

If you are absolutely sure you need more connections, you can increase this from the default 151 in your my.cnf as well:

# default max_connections is 151 (1 spare for super user)
max_connections = 201

Now you need to keep an eye on the situation by issuing the SHOW FULL PROCESSLIST once in a while to see if they are filling up fast. And don’t forget to visit any applications that may be showing up in there frequently.  I found two applications tha were using persistent connections, being held open for two hours, and they just didn’t need to.

Replacing Sendmail with Postfix on Ubuntu causes error postdrop: warning: unable to look up public/pickup: No such file or directory

On one of our machines the original Sysadmin had setup Sendmail, but on all our other machines the default version installed by Ubuntu is Postfix.  Rather than having to maintain and troubleshoot several type of systems, I wanted the sendmail one to be changed to match our Postfix systems.

The advice at this forum post which said to simply apt-get install postfix and it would automatically remove Sendmail.  Which it did.  Goes into the postfix screen and I set it up as per usual.  However, when testing it out from the command line by trying to send a logwatch report, I get this error:

postdrop: warning: unable to look up public/pickup: No such file or directory

Thanks to this article at databasically.com I found out that Ubuntu wasn’t removing sendmail completely, in fact it wasn’t stopping the sendmail process!  Here’s the solution that was posted:

mkfifo /var/spool/postfix/public/pickup
ps aux | grep mail
kill [insert process number]
sudo /etc/init.d/postfix restart

PHP APC config syntax causes [apc-error] apc_mmap: mmap failed: Invalid argument

After upgrading Ubuntu server from 9.10 to 10.04LTS PHP’s APC cache wasn’t functioning.  Apache wouldn’t start, it hung in the process list and printed this error to /var/log/apache2/error.log

[apc-error] apc_mmap: mmap failed: Invalid argument

The apache process would show up in the process like this:

apc@hostaname# ps aux | grep apache
www-data 6958 104 0.0 139044 3624 ? R 12:47 0:19 /usr/sbin/apache2 -k start

This process would then have to be killed, APC commented out, and then the web server restarted just to continue on without APC until a solution was found.

The PHP manual states this regarding MMAP support in APC:

http://php.net/manual/en/apc.configuration.php

When APC is compiled with mmap support (Memory Mapping), it will use only one memory segment, unlike when APC is built with SHM (SysV Shared Memory) support that uses multiple memory segments. MMAP does not have a maximum limit like SHM does in /proc/sys/kernel/shmmax. In general MMAP support is recommeded because it will reclaim the memory faster when the webserver is restarted and all in all reduces memory allocation impact at startup.

APC was made to run by commenting out all lines from the PHP config file except for:

extension=apc.so
apc.enabled = 1

This config can exist in a number of places. In 9.10 APC had been compiled by PECL so it was in our /etc/php5/apache2/php.ini file. However, in 10.04 APC is a package so we removed the PECL version, installed the version using apt-get install php-apc and moved the configuration to /etc/php5/conf.d/apc.ini for better consistency.

pear uninstall apc
apt-get install php-apc

As I began to uncomment lines one by one, it turned out the culprit was in the apc.shm_size directive. The default size is 30M, but as soon as the directive was uncommented it crashed Apache. I was unable to specify any value at all, even the same or lesser value. I even tried with quotes and removing quotes. That’s when I started thinking syntax may be a problem because it works when using the default value (shm_size commented out) but fails with an “invalid argument” error. That makes me think APC is sending an invalid argument to MMAP. In which case I find this post that confirms my suspicion.

http://stackoverflow.com/questions/6716929/apc-configuration-on-ubuntu-10-4-problem-with-apc-shm-size-apc-shm-segments-an

It turns out that the “M” for Megabytes cannot be specified in the shm_size directive for APC in Ubuntu server 10.04 because it is using APC version 3.1.3p1. However, on 9.10 APC wasn’t included as a package so it was installed with PEAR PECL which installed a more recent version of APC (3.1.9) which did allow specifying the “M” in the shm_size directive.

If you wish this to work in your config file, it should read like this in older versions of APC:

apc.shm_size = 100

This would specify 100M shared memory segments, and would be equivalent to this in newer versions:

apc.shm_size = 100M

And you can also put quotees around the “100M” if you like.

After these changes I had Apache up and running again, the APC cache helping PHP along, and some of the quickest loading pages I’ve seen in a while.

Mounting NFS causes error “mount: wrong fs type, bad option, bad superblock on …”

Some days the obvious just passes you by.

On a fresh install of Ubuntu server I found that trying to mount an NFS export from our NAS wouldn’t complete. I had the correct options in /etc/fstab on this machine and in /etc/exports on the NFS server as evidence by the success of other machines connected to it. Mounting it on this server gave the following error:

root@iknow:/var/log# mount /mnt/backup
mount: wrong fs type, bad option, bad superblock on 10.20.30.40:/mnt/my_backup,
missing codepage or helper program, or other error
(for several filesystems (e.g. nfs, cifs) you might
need a /sbin/mount. helper program)
In some cases useful info is found in syslog - try
dmesg | tail or so

After double checking everything several times the light bulb finally came on. nfs-common wasn’t installed!

sudo apt-get install nfs-common

It installed, I retried the mount, and it succeeded. Just remember that as a client, you have to have nfs-common installed if you want to mount an NFS export from the server.

Echo Layla Working in Ubuntu Linux

The Echo pro audio products are great. I’ve been using an Echo Layla 24/96 since about 2002. I primarily do my recording with 64Studio, but I like to listen to music while I work on programming in Ubuntu.

I haven’t had problems getting it working in most versions of Linux for the last few years. Easiest install was recognized right off the bat by Mandriva. My memory doesn’t serve me too well, but 64Studio works flawlessly with record low latencies.

But it didn’t’ work right out of the box with Ubuntu, and that’s no big deal. Finding out how to get it to work was tougher. Lot’s of people complaining that Linux doesn’t “just work.” Well, when I installed Layla on Windows it didn’t just work either, I had to install a driver. Fortunately in Linux the driver is already included. What’s missing is the mixer and firmware.

It’s too bad but there are many solutions out there including compiling ALSA from scratch. But the solution I found over at the Echo forums was much, much simpler. Here’s the excerpt from I want to use Layla 3g in Ubuntu

1) Add the Medibuntu (https://help.ubuntu.com/community/Medibuntu) repository to your software sources.
2) Install the alsa-firmware package to get the drivers.
3) Install alsa-tools-gui package to get echomixer (a mixer for Echo Audio cards).
4) If you’re using PulseAudio you need to manually add it to your /etc/pulse/default.pa file *.
5) Reboot & enjoy high quality sound.

And that worked for me. Make sure after you reboot to have your volume down low on your speakers, then after it starts up load the echomixer program from your Sound menu, and adjust it properly.

No problem. You should have your Echo Layla, Layla24, and possibly 3g, Mia, Gina and other Echo pro audio cards working in Linux.