Script to retreive customer email passwords

[spacelink]

I thought I'd share this script as it has helped me out over the years.

Regards
Stu



#!/bin/sh
#
# get-email-details: a script to obtain credentials from the Plesk database.
#
# Run without arguments to view the usage information.
#
###########################################################################
# VARIABLES
#
# You must change the variables below to suit your environment.
#
SQLUSR="admin"
SQLPWD="XXXXXX"
SQLHST="localhost"
SQLPDB="psa"
#
#
###########################################################################

ARGUSR="$(echo "$1" | cut -f1 -d'@')"
ARGDOM="$(echo "$1" | cut -f2 -d'@')"

usage()
{

cat <<eof
get-email-details: a script to obtain credentials from the Plesk database.


USAGE: $0 user@domain

Where successful, the password for the account you have requested is
printed to stdout.

If the attempt does not succeed, error message(s) are sent to stdout;
and where no arguments are given, the usage information (which you are
reading now) is also printed to stdout. In these situations, no stdout
data is given, making this script an easy one to integrate with others.

There are several variables within the script itself which may require
setting if you have not used this script before. This information relates
to the hostname of the mysql server, and the credentials it should use.

eof
}

if [ "$ARGUSR" = "" ]; then usage; exit; fi
if [ "$ARGDOM" = "" ]; then usage; exit; fi

# Ensure that the required variables have been defined.
#
for v in "SQLUSR:${SQLUSR}" "SQLPWD:${SQLPWD}" "SQLHST:${SQLHST}" \
"SQLPDB:${SQLPDB}"; do
if [ "$(echo "$v" | cut -f2 -d':')" = "" ]; then
echo "Variable $(echo "$v" | cut -f1 -d':') not set. Abort." 2>/dev/stderr;exit;fi;done


# Check SQL server connectivity and sanity by performing basic math,
# and checking against the anticipated answer (368+456 should = 824)
#
if [ "$(echo "select 368 + 456;" | mysql -u "$SQLUSR" --password="$SQLPWD" -h "$SQLHST" | sed 1d)" != "824" ]; then
echo "SQL connectiivty and math sanity check failed. Abort." 2>/dev/stderr; exit; fi

# Another sanity check, which confirms that there is access to the Plesk
# database (as defined in SQLPDB) and that the required tables (domains,
# mail, accounts) are present and usable.
#

unset unacc
for tbl in domains mail accounts; do
if [ "$(echo "use ${SQLPDB}; select * from $tbl limit 1;" | mysql -u "$SQLUSR" --password="$SQLPWD" -h "$SQLHST" | sed 1d)" = "" ]; then
if [ "$unacc" = "" ]; then unacc="${SQLPDB}:${tbl}"; else unacc="${unacc} ${SQLPDB}:${tbl}"; fi; fi
done

# Display an error message and abort if the tables cannot be accessed.
if [ "$unacc" != "" ]; then echo "Tables $(echo "$unacc" | sed s/\ /,\ /g) cannot be accessed. Abort." 2>/dev/stderr; exit; fi

domid="$(echo "use ${SQLPDB}; select id from domains where name = '${ARGDOM}' limit 1;" |
mysql -u "$SQLUSR" --password="$SQLPWD" -h "$SQLHST" | sed 1d)"

if [ "$domid" = "" ]; then echo "Domain not found. Abort." 2>/dev/stderr; exit; fi

mailid="$(echo "use ${SQLPDB}; select account_id from mail where mail_name = '${ARGUSR}' and dom_id = '${domid}' limit 1;" |
mysql -u "$SQLUSR" --password="$SQLPWD" -h "$SQLHST" | sed 1d)"

if [ "$mailid" = "" ]; then echo "Mail account not found. Abort." >/dev/stderr; exit; fi

mailpwd="$(echo "use ${SQLPDB}; select password from accounts where id = '${mailid}' and type = 'plain' limit 1;" |
mysql -u "$SQLUSR" --password="$SQLPWD" -h "$SQLHST" | sed 1d)"

if [ "$mailpwd" = "" ]; then echo "Password not found. Abort." >/dev/stderr; exit; fi

# Echo the password to standard out.
#
echo "$mailpwd" >/dev/stdout