Tag Archives: configuration

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!

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.

How To Disable WordPress Plugins Manually

If you get stuck being unable to login to WordPress because of a misbehaving plugin, you have a couple options.

You can login to your WordPress database using command line MySQL client or phpMyAdmin, and from there look for the wp_options table. If you are using a security plugin that changes table prefixes you’ll have to replace wp_ with your prefix. Once there, look for a value in the option_name column called active_plugins. You can find it like this:

SELECT * FROM wp_options WHERE option_name like '%plugin%';

This will give you a list of plugin options which you can examine. If you are using WPMU or WP3 Multisite and this is a globally enabled plugin, you’ll need to then use table name with the ID of the site in the prefix, like wp_2_options instead of wp_options. It used to be that option_name “active_plugins” would contain all plugins, but it appears from examining this that option_name “transient_plugin_slugs” contains plugins. If you choose to modify it, copy and paste the original value somewhere for easy editing and replacement once fixed.

The option I chose for this Multisite plugin that prevented login was to simply rename the folder of it in the plugins directory. That would cause WordPress not to load the bad files, because they were no longer in the expected location.

Once into the admin dashboard page, you can then disable the plugin completely and take whatever actions you need to repair, replace or trash it for good.

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.

Creating MySQL Users and Granting Permissions

When should you create a new user in MySQL? That’s a question that you have to ask whenever you add a new application or restructure your data. I’ve been in places where every application connects as root, and obviously that’s very bad.

Only you know your situation. When I build web applications sometimes I’ll have a shared database, such as geographic information or lists or musical artists, that I want multiple applications to have access to. But only read access, they can’t change it. So root is out. But they also have their own database (in MySQL) or schema (in PostGreSQL) in which they will need to INSERT, UPDATE, DELETE and SELECT from tables. They may even need to CREATE tables, such as temp tables, depending on the situation. In these cases, such applications would require a dedicated user, because their are no commonalities that would allow this user account with its privileges to be reused by another application.

In this guide I’ll only deal with command line actions from MySQL client, not CPanel actions, but you’ll be able to transfer the concept to the different interface.

To create users you’ll most likely have to be root:

mysql -u root -p

Create the new user for this application:

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

You’ll have to think about this

hostname

setting. If the only one accessing your database with this username is your application, and your MySQL process is on your local machine, then ‘localhost’ will do as a hostname. If you have a separate machine running MySQL as I do, then you’ll need to enter the hostname or IP address of your web server machine, as that is where the application will connect from.

CREATE USER 'username'@'webserver' IDENTIFIED BY 'password';

It is possible to use wildcards for the hostname, although you should think this through as generally it is a bad idea. If you are on an internal network and you have your web server cluster and SQL machine on the same subnet, you could do this:

CREATE USER 'username'@'10.20.30.%' IDENTIFIED BY 'password';

Which will allow 256 machines to connect to your MySQL server as that user. Think about the future use of your network before you do this.

GRANTing PRIVILEGES

Now you have a user who can’t do much. You’ll need to give them access to something. Since the user I’m creating will be the one populating the database with information and not just reading it, they will need greater privileges than the average web application that just reads and reports. The GRANT syntax can be found in the MySQL manual. My specific requirement allowed me to enter:

mysql> GRANT ALTER, CREATE TEMPORARY TABLES, DELETE, INSERT, LOCK TABLES, SELECT, UPDATE ON dbname.* TO userguy@10.20.30.40

  • This GRANT is followed by a list of actions the user can take as seen in the manual page quoted above, (SELECT, DELETE, etc).
  • ON a certain database and tables – dbname.* means these privileges apply to ALL tables on the database called dbname.
  • TO username @ IP address – meaning if this user connects from another address they won’t receive these privileges

DON’T enter:
mysql> GRANT ALL ON *.* TO userguy@%

Which is the equivalent of giving them root privileges on all tables of all databases when connecting from anywhere. Pointless to try and manage users this way. And never grant *.* to a user anyway unless you aware of what all the * privileges are. Most people would be mortified to find out they used *.* and granted their web user the ability to DROP all their databases. Of course they don’t find out until after it has happened if they don’t know the user had that ability with *.* to start with.

Now, just to be clear everything is in effect, issue the

FLUSH PRIVILEGES

command.

Keep in mind, that in newer versions, MYSQL will create a user when a GRANT is issued if the user doesn’t already exist. However, I prefer to explicitly CREATE a user, and keep track of it in a file.

Connecting remotely requires configuration settings changes. If you are not connecting locally, you’ll need to modify your MySQL’s my.cnf file to allow remote connections. This is part of your extended configuration and security, and I won’t discuss it in this article.

Now you can go about creating users and granting privileges with a total security plan in mind. Everyone can access only what they need, no one can access or change what they shouldn’t, and users can only connect from specific locations.

Forcing Redirect to a www URL with Apache Rewrite for SEO

Redirecting requests for the non www version of your domain to the www version only can help to boost your SEO, eliminate duplicate search engine entries and improve your ranking. And it is easy to do with Apache and mod_rewrite.

I registered a new domain about a week ago and then posted my new social networking style project to go live. I was still fiddling with server upgrades so I didn’t tell anybody about it. Sure enough Google comes crawling and takes all pages. Excellent.

I went to Google and performed a:


site:example.com

…and it returned all my pages. Excellent again, fully indexed in less than a week (but not showing in search results). Wait a minute. I forgot the www. I try it again with:


site:www.example.com

And Google returns one page – the doorway for my web host! Sometime after I registered the domain and pointed the DNS to my machine Google crawled the www. version of the URL. I don’t know how or where it got the link. Probably from a list of recently registered domains. Then after that week was up and I posted the full site, Google crawled the non-www version of the URL, and indexed both.

Anyone see a problem there? Well first, I’ve got a doorway page for my host showing up for the www version of my URL! Second, if Google crawls both versions on their own without any links on the net, sooner or later they are going to see duplicate content. Both versions will eventually be the same I hope. Third, they are crawling the two versions at different rates, giving out of date results for one of them. Last, they are splitting up the results for the domain, and I don’t see that being good.

A solution that has been proposed by others in the past, and one that I would recommend again, is to redirect all traffic from non-www requests to the the www host version of your domain, if that is the version you are using. If you are using Apache and have mod_rewrite you can do the following:


<IfModule mod_rewrite.c>

RewriteEngine on
Rewritecond %{HTTP_HOST} !^www\.example\.com
RewriteRule ^/(.*) http://www.example.com/$1 [R=301,L]
</IfModule>

I did this in my http-vhosts.conf file but I believe it should work equally well in .htaccess . See note at the bottom of the page for the differences.

The first Rewritecond line says for every entry where the HTTP_HOST DOES NOT EQUAL www.example.com …. then redirect them to www.example.com. This won’t work if you subdomains like subdomain.example.com because it will redirect those requests to www.example.com as well.

Very important, don’t forget the R=301 which is a seamless HTTP redirect using the status code 301. According to W3.org:

301 Moved Permanently

The requested resource has been assigned a new permanent URI and any future references to this resource SHOULD use one of the returned URIs.

So Google SHOULD remove old references to the non-www domain and all your future results should look the way you like.

Proponents of SEO will tell you this automatic redirect to the www version of your domain using Apache and mod_rewrite is good for ranking, elimination of duplicate entries and boosting your visibility. Give it a try if you haven’t yet and see how it works for you.

UPDATE: If you put this in your root Apache configs instead of .htaccess make sure you write ^/(.*) instead of (.*) or you may get a double slash between your domain name and pages which Google will also consider duplicate content.

PHP’s APC cache and how it relates to Apache Bloat

You just finished your install of Apache, PHP and APC cache on a VPS and you’re beat. You spent about a day going over every configuration and compile option trying to eek performance out of your VPS machine and web server. Then you take a look at the output of top or ps and notice, hey, my Apache process is 150 Megs! Well isn’t that grand. Compiling the Big Apache on a limited resource VPS can be a challenge for those of us who like to tinker. On the last VPS I got I scrapped the packaged stuff and started on compiling an Apache for the machine. The problem is trying to meet your needs with the limited memory you usually get on these machines. Although I like lighttpd, I’m not ready to give it a go on a production server that is being monitored by the media all the time. I need something as guaranteed as I can get. Although lighttpd may be such a beast, I’m not going to test it out on this site at this time!

I sat down and started to go over all configuration option for Apache, PHP and APC to get a nice small package to do the job. I was surprised how much junk PHP has compiled in by default. Compile it once and execute a phpinfo() and you’ll see. Just start with the “–disable” switches for anything you don’t want.

After about a day of thinning out the size of my stack I slipped into TOP and used the SHIFT-A toggle to see the alternate views. I noticed something odd…..

3  PID %MEM  VIRT SWAP  RES CODE DATA  SHR nFLT nDRT S  PR  NI %CPU COMMAND
 24140  0.1 78592  69m 7228  504 2124 2952    6    0 S  16   0    0 httpd
 11329  0.1 78408  69m 6908  504 1964 2736    2    0 S  16   0    0 httpd
 24141  0.1 78564  70m 6752  504 2120 2492    0    0 S  16   0    0 httpd
 24142  0.1 78564  70m 6732  504 2120 2476    0    0 S  16   0    0 httpd
 27813  0.1 78560  70m 6684  504 2116 2432    0    0 S  16   0    0 httpd

Doesn’t that seem like a lot of VIRT and SWAP for Apache2? Yes, it is. If you used the suggested 128 for apc.shm_size it will be a lot bigger too! I used 64M and you can see that at 78592 minus the 64M shared mem for the APC cache, the actual size is around 13M for apache with a 6.7M RES. That makes me feel better! I was so worried I had gotten something horribly wrong.

If you happen to run across this kind of thing in testing, simply set your apc.enabled in php.ini to ‘0’ to disable it, restart Apache and check ‘top’ again. Likely nothing to worry about.

The machine I am running is only hosting 4 sites so 64M is a good starting point, but what you should do is copy the file ‘apc.php’ from your APC source directory into a folder on your webserver and visit it frequently to see how it behaves. Depending on your other settings like the cache lifetime and garbage collection, if you have a large portion of FREE and a small USED then you can probably set the apc.shm_size lower. If it makes you feel better :)

Apache, Postgres and Drupal on a VPS

I really would prefer to have my own server but sticking a box in colo is expensive. Where I live, getting access to the physical colo space would be nearly impossible too. As a result I run on a VPS. Unfortunately VPS has some horrible limitations depending on who is on the box with you.

Recently I decided to move my personal blog off of b2evolution and stick it on Drupal. Too bad drupal is such a resource hog. Most CMS and blog software is though and it is really hard to find a minimalized, optimized blog software that uses any form of caching. Today, it hit the skids and my patience hit the wall. Argh!

I was converting my personal blog by hand because I only have about 30 entries so it didn’t pay to write a conversion script. Everytime I hit the ‘post’ button in Drupal I wound up with a blank screen, could not connect or worse, the results of any command in SSH terminal window showed a “could not allocate memory”. As a result, I had to do some fast tuning of something because I had to reboot the server after every blog post!

I chose to tackle Apache first because they have an Apache Performance Tuning Guide that helps a bunch. Postgres, well I’m running an old version that I really need to upgrade before I tackle configuration and optimization of it. That’s not a 30 minute job.

VPS, Apache and Low Memory

VPS has a low memory for sure. Even though you can sometimes burst more, in general it is pretty low. The very first thing in the Apache performance tuning guide is tackling memory.


You can, and should, control the MaxClients setting so that your server does not spawn so many children it starts swapping. This procedure for doing this is simple: determine the size of your average Apache process, by looking at your process list via a tool such as top, and divide this into your total available memory, leaving some room for other processes.

Using top and sorting by thread using ‘h’ I am able to see that the average Apache processes is using WAY TOO MUCH memory at 25M a piece – and 10 processes running. I don’t have time to tune the size now so I’ll tune the number of servers using very simple configuration parameters. Since we are using MPM Prefork, the directives can be found in extra/httpd-mpm.conf file under mpm_prefork_module.

Since I am supposed to be guaranteed 256M memory burstable to 1G I’ll optimize to the lower number. 256M / 25M is 10. Not including room for other processes. The current setting is 150!

From:


StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 150
MaxRequestsPerChild 0

To:


StartServers 2
MinSpareServers 2
MaxSpareServers 5
MaxClients 10
MaxRequestsPerChild 0

I also changed it to only start 2 servers, only keep 2 spare servers instead of 5, and only allow 10 clients instead of 150. This will essentially create a queue if anyone is waiting but it shouldn’t dip into swap space and it will save a bunch of memory usage. This will of course be monitored. Once time permits and I am able to minimize the size of each Apache2 process (the other factor) then I will revisit this and likely increase the MaxClients accordingly.