sandip's blog

Exclamations in the body of phplist emails resolved

PHPlist using phpmailer, by default sets to send email with Content-Transfer-Encoding set to 8-bit. However, mail transport standards forbid lines longer than 998 characters and has to handle such non-compliant lines by breaking/folding them, or the next hop MTA (Mail Transfer Agent) might reject the message or truncate the lines.

The fix for the problem is to use "base64" or "quoted-printable" encoding, which will fold the lines in a way that can be undone by the recipient MUA (Mail User Agent).

If using phplist with phpmailer, the file to edit would be "admin/phpmailer/class.phpmailer.php", as below:

var $Encoding = "base64";

PostgreSQL role based password authentication

To manage/setup role based password authentication for postgresql database.

Modify "/var/lib/pgsql/data/pg_hba.conf" and include:

host    samerole         all         127.0.0.1/32         md5

Save the below script and run:

./db_setup.sh <DBNAME> <DBMAINUSER> <DBMAINUSERPASS> create

#!/bin/bash
# db_setup.sh

USAGE="Usage: $0 <DBNAME> <DBMAINUSER> <DBMAINUSERPASS> <create|drop>"
DBNAME=${1?"$USAGE"}
DBMAINUSER=${2?"$USAGE"}
DBMAINUSERPASS=${3?"$USAGE"}

# Create new database + main user
#
create_db_and_mainuser() {
psql -U postgres template1 -f - <<EOT

CREATE ROLE ${DBNAME} NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOLOGIN;
CREATE ROLE ${DBMAINUSER} NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '${DBMAINUSERPASS}';
GRANT ${DBNAME} TO ${DBMAINUSER};
CREATE DATABASE ${DBNAME} WITH OWNER=${DBMAINUSER};

EOT
}

# Remove database + main user
#
drop_db_and_mainuser() {
psql -U postgres template1 -f - <<EOT

-- TERMINATE CONNECTIONS OF ALL USERS CONNECTED TO <DBNAME>
DROP DATABASE ${DBNAME};
DROP ROLE ${DBMAINUSER};
DROP ROLE ${DBNAME};

EOT
}

# Main
case "$4" in
  create)
    create_db_and_mainuser
    ;;
  drop)
    drop_db_and_mainuser
    ;;
  *)
    echo $USAGE
    exit 1
    ;;
esac

exit 0

You should now be able to connect using:

psql -U <DBMAINUSER> -d <DBNAME>  -h 127.0.0.1

Relay email via SMTP provider using sendmail

The below is specific to CentOS-5.4 and may work similarly with other distros.

  • Additional packages required if not installed already:

    sendmail-cf
    m4
    make
    cyrus-sasl-plain

  • Edit ”/etc/mail/sendmail.mc”:
    define(`SMART_HOST', `{smtprelay.domain.tld}')dnl
    FEATURE(`authinfo',`hash -o /etc/mail/authinfo.db')dnl
  • Create file ”/etc/mail/authinfo” with below contents and chmod 640:
    AuthInfo:smtprelay.domain.tld "U:{username}" "P:{password}" "M:PLAIN"
  • Update the sendmail conf and db hashes:
    cd /etc/mail
    make
  • Restart sendmail for the new configs to pick up.
  • Now mails sent to localhost is relayed via your SMTP provider.

drweb antivirus update cron output

A cron is run every 30 minutes to check on virus database updates via /etc/cron.d/drweb-update, which cause a slew of emails addressed to drweb and filling up mail queue, since drweb does not have a maildir.

To resolve, direct the output to /dev/null or to a log file instead... if you need to keep a watch on the updates.

Edit /etc/cron.d/drweb-update redirecting the output to a log file:

*/30 * * * * drweb /opt/drweb/update.pl >>/tmp/drweb_update.log

If you still want to deliver the output, this can be done by directing drweb mail to root via "/var/drweb/.qmail" or "/var/qmail/alias/.qmail-drweb" with "&root".

Optimize PostgreSQL Database Size

Recently, I noticed that the postgresql database partition had been filled up. The general advice is to export data, drop and recreate database and import the data back in, since this would save a lot of time and also reduce the database size comparatively.

Reference: optimize postgresql database size.

Not too sure, if I wanted to go the dump and restore route, so decided to vacuum and reindex instead.

To my surprise, I did regain about 70% of space running the full vacuum and reindexing the database.

  • Full vacuum reduced the database size from 8GB to 4GB.
    vacuumdb -afzv | tee /tmp/vacuumdb.log
  • Reindex then reduced further to 2.5GB.
    reindexdb -a | tee /tmp/reindexdb.log
  • Total of 5.5GB space reduced with a full vacuum and reindex of the database.
  • Check the vacuumdb.log for database optimization notices.
  • It is also advised to reindex prior to vacuuming to further save on vacuum time.

#2002 - The server is not responding

PhpMyAdmin Error:

#2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)

There are various reasons for this error. Several things to check are:

  1. Edit config.inc.php file, and specify '127.0.0.1' (instead of any other value like 'localhost) for the $cfg['Servers'][$i]['host'] setting as follows:

    $cfg['Servers'][$i]['host'] = '127.0.0.1';

  2. Check my.cnf file for the following entries:

    [mysqld]
    socket=/tmp/mysql.sock
    old-passwords

    [client]
    socket=/tmp/mysql.sock

    Only add the 'old-passwords' mentioned above if you are using an older version of PHP (PHP4).

  3. Make sure that you do not have any older versions of MySQL installed that are conflicting.
  4. Edit config.inc.php file, and specify the MySQL socket location (as seen in the above [mysqld] socket setting):

    For example, if your MySQL socket is /tmp/mysql.sock, find the line in config.inc.php that has the $cfg['Servers'][$i]['socket'] setting and change it to:

    $cfg['Servers'][$i]['socket'] = '/tmp/mysql.sock';

  5. Set the connection mode to socket by changing $cfg['Servers'][$i]['connect_type'] to:
    $cfg['Servers'][$i]['connect_type'] = 'socket';

    If there is no such setting, simply add in the above line and re-try.

  6. Add this entry to php.ini file (again changing the '/tmp/mysql.sock' to your actual socket location) and restart Apache:

    mysql.default_socket = "/tmp/mysql.sock"

  7. Edit the config.inc.php using the phpmyadmin /setup configuration page:

    In the setup screen, select the end-of-line character to be '\r' if you have a Macintosh, or '\n' for a Linux machine.

  8. Disable SELinux if you are using it, and re-try. If it works, SELinux is the issue.
  9. Run the following at your mysql prompt (using the standard mysql client):
    mysql> SELECT USER,HOST FROM mysql.user;

    Next, identify the user your are using for phpMyAdmin, and then run:

    mysql> SHOW GRANTS FOR 'user'@'host';

    Ensure that this userid has appropriate access rights.

List partitions by UUID

UUID mounts in fstab is very useful if you have external usb hdd that is often connected and disconnected.

To find out the UUID of a disk drive, you can use the simple `ls` command as below:

ls -l /dev/disk/by-uuid

Additionally below commands can also be used:

# blkid
# vol_id /dev/sda1
# tune2fs -l /dev/sda1

cannot create temporary file - (13) Permission denied

This is dues to bug in the default installation of qmail in handling local mails... such as mails to root@localhost where the qmail-local binary is not set with the right owner and permissions, as such mails are not able to be written to the spool located at "/usr/local/psa/handlers/spool".

The error in "/usr/local/psa/var/log/maillog" looks like below:

qmail-local-handlers[......]: cannot create temporary file - (13) Permission denied

To resolve, change the owner and permission of /var/qmail/bin/qmail-local to reflect the same as qmail-remote.

cd /var/qmail/bin
chown mhandlers-user:popuser qmail-local
chmod g+s,g-r,o-r qmail-local

Restart qmail:

/etc/init.d/qmail restart

Bulk update DNS TTL for all Plesk domains

This is useful when migrating servers and you want to reduce the DNS time to live for all domains.

Change the TTL on all domains to 5 mins (300 seconds) in the psa database.

# mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa
mysql> UPDATE `dns_zone` SET `ttl` = '300', `ttl_unit` = '60' WHERE `id` >1;
mysql> quit

Then update the zone files via:

# mysql -Ns -uadmin -p`cat /etc/psa/.psa.shadow` -D psa -e 'select name from domains' | awk '{print "/usr/local/psa/admin/sbin/dnsmng update " $1 }' | sh

Verify with:

$ dig @nameserver domain.tld soa

Upgrade CentOS 5.3 to 5.4

Below is a clean method of updating, instead of doing a straight `yum update` which I have often done in the past and broken OS.

yum clean all
yum update glibc\*
yum update yum\* rpm\* python\*
yum clean all
yum update
shutdown -r now

Syndicate content
Comment