Racker Hacker

When you need to find information about anything in Plesk, here’s some SQL statements that you can use:

Start out with:

# mysql -u admin -p`cat /etc/psa/.psa.shadow`
mysql> use psa;

Find all e-mail passwords:

select concat_ws('@',mail.mail_name,domains.name),accounts.password from domains,mail,accounts where domains.id=mail.dom_id and accounts.id=mail.account_id order by domains.name ASC,mail.mail_name ASC;

Find e-mail passwords made out of only letters:

select concat_ws('@',mail.mail_name,domains.name),accounts.password from domains,mail,accounts where domains.id=mail.dom_id and accounts.id=mail.account_id and accounts.password rlike binary '^[a-z]+$’;

Find e-mail passwords made out of only numbers:

select concat_ws('@',mail.mail_name,domains.name),accounts.password from domains,mail,accounts where domains.id=mail.dom_id and accounts.id=mail.account_id and accounts.password rlike '^[0-9]+$’;

Find which domains aren’t bouncing/rejecting e-mails to unknown recipients:

select d.name as domain, p.value as catchall_address from Parameters p, DomainServices ds, domains d where d.id = ds.dom_id and ds.parameters_id = p.id and p.parameter = 'catch_addr' order by d.name

To add a chrooted FTP user outside of Plesk properly, you need to:

• Create the user with the home directory as the root of what they can access
• Give the user a password
• Make their primary group psacln
• Add them to the psaserv group as well

# useradd username -d /var/www/html/website/slideshow/
# echo "password" | passwd username --stdin
Changing password for user username.
passwd: all authentication tokens updated successfully.
# usermod -g psacln username
# usermod -G psaserv username
# lftp username:password@localhost
lftp username@localhost:/> cd ..
lftp username@localhost:/>

To install PayFlowPro, you will need a few things:

• The PHP source code for version of PHP installed (go here)
• The SDK from Verisign/PayPal (this comes from the portal, login required)
• The gcc and automake packages

Take the Verisign SDK and copy the following:

• Copy pfpro.h to /usr/include
• Copy the .so file to /usr/lib

Untar the PHP source code and cd into php-[version]/ext/pfpro. Run phpize and make sure it finishes successfully. Now run:

./configure --prefix=/usr --enable-shared

Then run make and make install. Now, go to the php.ini and add:

extension=pfpro.so

Run php -i | grep pfpro to make sure the module was successfully built. Restart Apache and you’re all set!

The pfpro module is now available via pecl in PHP 5.1+. Thanks to Chris R. for pointing that out.

If you find yourself in the situation where you need to bulk add SPF records to every domain in Plesk, you can use this huge one-liner:

mysql -u admin -p`cat /etc/psa/.psa.shadow` psa -e "select dns_zone_id,displayHost from dns_recs GROUP BY dns_zone_id ORDER BY dns_zone_id ASC;" | awk '{print "INSERT INTO dns_recs (type,host,val,time_stamp,dns_zone_id,displayHost,displayVal) VALUES ('\''TXT'\'','\''"$2"'\'','\''v=spf1 a mx ~all'\'',NOW(),"$1",'\''"$2"'\'','\''v=spf1 a mx ~all'\'');"}' | mysql -u admin -p`cat /etc/psa/.psa.shadow` psa

Then you’ll need to make Plesk write these changes to the zone files:

# 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

You can check your work by viewing the new entries you made:

mysql -u admin -p`cat /etc/psa/.psa.shadow` psa -e "SELECT * FROM dns_recs WHERE type='TXT';"

If you get this error, you’ve most likely done a file-based MySQL backup restore, and the InnoDB files are hosed. The horde_sessionhandler table isn’t a MyISAM table at all - it’s actually an InnoDB table. The easiest way to fix the issue is to stop MySQL and trash the .frm:

# /etc/init.d/mysqld stop
# rm /var/lib/mysql/horde/horde_sessionhandler.frm

Now start MySQL and re-create the table:

# /etc/init.d/mysqld start
# mysql -u admin -p`cat /etc/psa/.psa.shadow`

Here’s the SQL statements to run:

CREATE TABLE horde_sessionhandler (session_id VARCHAR(32) NOT NULL, session_lastmodified INT NOT NULL, session_data LONGBLOB, PRIMARY KEY (session_id)) ENGINE = InnoDB;
GRANT SELECT, INSERT, UPDATE, DELETE ON horde_sessionhandler TO horde@localhost;

You’re good to go!

If Plesk throws an error that it can’t upgrade your license key because of languages, you need to remove the extra locales:

# rpm -qa | grep psa-locale | grep -v base
psa-locale-el-GR-8.1-build81061127.19
psa-locale-fr-FR-8.1-build81061127.19
psa-locale-lt-LT-8.1-build81061127.19
psa-locale-pt-BR-8.1-build81061127.19
psa-locale-sv-SE-8.1-build81061127.19
psa-locale-ca-ES-8.1-build81061127.19
psa-locale-de-DE-8.1-build81061127.19
psa-locale-es-ES-8.1-build81061127.19
psa-locale-fi-FI-8.1-build81061127.19
psa-locale-hu-HU-8.1-build81061127.19
psa-locale-ja-JP-8.1-build81061127.19
psa-locale-nl-BE-8.1-build81061127.19
psa-locale-pl-PL-8.1-build81061127.19
psa-locale-pt-PT-8.1-build81061127.19
psa-locale-ru-RU-8.1-build81061127.19
psa-locale-tr-TR-8.1-build81061127.19
psa-locale-zh-TW-8.1-build81061127.19
psa-locale-cs-CZ-8.1-build81061127.19
psa-locale-es-MX-8.1-build81061127.19
psa-locale-it-IT-8.1-build81061127.19
psa-locale-nl-NL-8.1-build81061127.19
psa-locale-ro-RO-8.1-build81061127.19
psa-locale-zh-CN-8.1-build81061127.19
# rpm -ev `rpm -qa | grep psa-locale | grep -v base`

If you ever need to communicate with a POP3 server via telnet to test it, here’s some commands you can use:

USER userid
PASS password
STAT
LIST
RETR msg#
TOP msg# #lines
DELE msg#
RSET
QUIT

If you have weird SSL errors and this one appears, you are trying to speak SSL to a daemon that doesn’t understand it:

$ openssl s_client -connect 222.222.222.222:443
CONNECTED(00000003)
5057:error:140770FC:SSL routines:SSL23_GET_SERVER_HELLO:unknown protocol:s23_clnt.c:567:

If you get this with Apache, be sure that you have SSLEngine On in the applicable VirtualHost and be sure that mod_ssl is being loaded.

To pretty much completely disable SSH timeouts, simply adjust the following directives in /etc/ssh/sshd_config:

TCPKeepAlive yes
ClientAliveInterval 30
ClientAliveCountMax 99999

Before you upgrade Plesk, it’s always a good idea to make a backup and also make your ip and shell maps:

/usr/local/psa/bin/psadump -f /path/to/psa.dump --nostop --nostop-domain
/usr/local/psa/bin/psarestore -t -f /path/to/psa.dump -m ip_map -s shell_map

If you need to restore data, just drop the -t on the psarestore command.