3.9. Hosting

It's now possible to manage accounts from your services using LMS. This functionality is provided for advanced system administrators, because it requires in-depth knowledge of services running on server in order to configure them to use LMS database.

You can create five types of accounts now: shell (1), email (2), www (4), ftp (8) and sql (16). Decimal values for internal representation of those accounts are given in parenthesis. This approach enables to provide multi-type accounts, eg. if you define shell+email+ftp account it will be given number 11 in database. This means that for recognition of account type you should use binary sums in WHERE clauses of your SQL queries (few examples are written below).

You have also possibility to define domains and aliases.

3.9.1. Accounts

Account list contains basic information about your customer accounts. It's possible to sort it by any highlighted column title and to filter it for given criteria. You can edit any account clicking 'Edit' icon. Administrator (LMS user) is also able to change account passwords.

3.9.2. New Account

To create new account you have to provide its login, password, choose domain, at least one account type and assign customer to it.

You can define any home directory for account. Option homedir_prefix from section [phpui] consists default prefix "/home/".

3.9.3. Aliases

Every account (email type mainly) might have any number of aliases that is needed. Mail server administrator might redirect mail locally from all those aliases to physical existing account(s). Basic information about alias and accompanying account is provided on the list. It's possible to sort this list by any highlighted column name and to filter list for given criteria.

3.9.4. New Alias

Creating alias you have to define login, domain and destination account(s). Destination accounts must exists in LMS. If you need alias for external account, you should create an account with redirect address.

3.9.5. Domains

Currently LMS can directly manage PowerDNS server with mysql/pqsql backend. LMS now has full support for most of the features of PowerDNS server. It has full support for zone types: master, native and slave. You're able to see basic information about domains on the list such as: name, type, owner. It's possible to sort this list by any highlighted column name and to filter list for given criteria and by first letter of domain name. You can edit domain data by clicking 'Edit' icon. Add new domain at the bottom and at the top of the list.

3.9.6. New Domain

Domain data consists of name, description, type (master,slave,native), IP address of webserver, IP address of mailserver, IP address of master NS (when type slave is selected) and other parameter which have default values stored in "zones" section. Domain can be assigned to customer.

3.9.7. Search

You can search for accounts, aliases and domains here by any given criteria.

3.9.8. Examples

The following section contains fragments of proftpd daemon configuration (version 1.2.10) relevant to process of authentication with using data available in LMS database. This example contains Postgres configuration and optional MySQL configuration in comments, followed by pound (hash) sign.

Example 3-1. Accounts. Proftpd configuration.

  ServerName	"LMS FTP Server"
  
  #dbname@host:port dbuser dbpass
  SQLConnectInfo lms@localhost:5432 lms mypassword
  
  SQLAuthTypes Crypt Plaintext
  SQLUserInfo passwd login password uid NULL home NULL
  RequireValidShell off
  SQLAuthenticate users
  
  # create user home directory if it doesn't exists yet
  SQLHomedirOnDemand on
  
  # login message
  SQLShowInfo PASS "230" "Last login: %{getlastlogin}"
  SQLLog PASS setlastlogin
  
  # SQLNamedQuery getlastlogin SELECT "CASE lastlogin WHEN 0 THEN '' ELSE FROM_UNIXTIME(lastlogin) END FROM passwd WHERE login='%u'"
  # SQLNamedQuery setlastlogin UPDATE "lastlogin=UNIX_TIMESTAMP() WHERE login='%u'" passwd 
  SQLNamedQuery getlastlogin SELECT "CASE lastlogin WHEN 0 THEN '' ELSE lastlogin::abstime::timestamp::text END FROM passwd WHERE login='%u'"
  SQLNamedQuery setlastlogin UPDATE "lastlogin=EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)) WHERE login='%u'" passwd
  
  # We limit access to valid (not expired) accounts with ftp type
  # SQLUserWhereClause "type & 8 = 8 AND (expdate = 0 OR expdate > UNIX_TIMESTAMP())"
  SQLUserWhereClause "type & 8 = 8 AND (expdate = 0 OR expdate > EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)))"

Next example will show us how to configure Postfix 2.1.1 mail server with Cyrus-SASL 2.1.19 and Courier-IMAP/POP3 3.0.4, so that they use LMS database. LMS accounts will be virtual, which means that they will be all maintained by one uid on server with mail stored in Maildir format.

You'll need to patch your SASL for encrypted passwords, because LMS database contains them only in this form. In comments we have provided MySQL specific options. Only fragments relevant to database setup are shown below:

Example 3-2. Accounts. Mail server configuration (postfix+sasl+courier).

# smtpd.conf file (Cyrus-SASL):

pwcheck_method: auxprop
#sql_engine: mysql
sql_engine: pgsql
sql_user: lms
sql_passwd: dbpass
sql_hostnames: localhost
sql_database: lms
#sql_select: SELECT p.password FROM passwd p, domains d WHERE p.domainid = d.id
#   AND p.login='%u' AND d.name ='%r' AND p.type & 2 = 2 
#	AND (p.expdate = 0 OR p.expdate > UNIX_TIMESTAMP())
sql_select: SELECT p.password FROM passwd p, domains d WHERE p.domainid = d.id
	AND p.login='%u' AND d.name ='%r' AND p.type & 2 = 2 
	AND (p.expdate = 0 OR p.expdate > EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)))
password_format: crypt
mech_list: login plain

# authpgsqlrc (or authmysqlrc) (Courier):

# user postfix (owner of mail directory)
#MYSQL_UID_FIELD '1004'
PGSQL_UID_FIELD '1004'
# group postfix (owner of mail directory)
#MYSQL_GID_FIELD '1004'
PGSQL_GID_FIELD '1004'
#MYSQL_PORT		3306
PGSQL_PORT		5432
#MYSQL_USERNAME		lms
PGSQL_USERNAME		lms
#MYSQL_PASSWORD		dbpass
PGSQL_PASSWORD		dbpass
#MYSQL_DATABASE		lms
PGSQL_DATABASE		lms
#MYSQL_SELECT_CLAUSE SELECT p.login, \
#       p.password, '', 104, 104, '/var/spool/mail/virtual', \
#       CONCAT(d.name,'/', p.login, '/'), '', p.login, '' \
#       FROM passwd p, domains d WHERE p.domainid = d.id \
#       AND p.login = '$(local_part)' AND d.name = '$(domain)' \
#       AND p.type & 2 = 2 AND (p.expdate = 0 OR p.expdate > UNIX_TIMESTAMP())
PGSQL_SELECT_CLAUSE SELECT p.login, \
        p.password, '', 104, 104, '/var/spool/mail/virtual', \
        d.name || '/' || p.login ||'/', '', p.login, '' \
        FROM passwd p, domains d WHERE p.domainid = d.id 
        AND p.login = '$(local_part)' AND d.name = '$(domain)' \
        AND p.type & 2 = 2 \
        AND (p.expdate = 0 OR p.expdate > EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)))

# main.cf (Postfix):

virtual_mailbox_base = /var/spool/mail/virtual
virtual_mailbox_domains = pgsql:/etc/postfix/virtual_domains_maps.cf
virtual_mailbox_maps = pgsql:/etc/postfix/virtual_mailbox_maps.cf
virtual_alias_maps = pgsql:/etc/postfix/virtual_alias_maps.cf
recipient_bcc_maps = pgsql:/etc/postfix/recipient_bcc_maps.cf

# virtual_domains_maps.cf (Postfix):

user = lms
password = dbpass
hosts = localhost
dbname = lms
query = SELECT name FROM domains WHERE name = '%s'

# virtual_mailbox_maps.cf (Postfix):

user = lms
password = dbpass
hosts = localhost
dbname = lms
# MySQL
#query = SELECT CONCAT(d.name, '/', p.login, '/') 
#	FROM passwd p, domains d WHERE p.domainid = d.id
#       AND p.login = '%u' AND d.name = '%d' 
#       AND p.type & 2 = 2 
#       AND (p.expdate = 0 OR p.expdate > UNIX_TIMESTAMP())
# PostgreSQL
query = SELECT d.name || '/' || p.login || '/' 
	FROM passwd p, domains d WHERE p.domainid = d.id
        AND p.login = '%u' AND d.name = '%d' 
        AND p.type & 2 = 2 
        AND (p.expdate = 0 OR p.expdate > EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)))

# virtual_alias_maps.cf (Postfix):

user = lms
password = dbpass
hosts = localhost
dbname = lms
# MySQL
# MySQL
#query = SELECT p.mail_forward
#	FROM passwd p
#	JOIN domains d ON (p.domainid = d.id)
#	WHERE p.login = '%u' AND d.name = '%d'
#		AND p.type & 2 = 2 AND p.mail_forward != ''
#		AND (p.expdate = 0 OR p.expdate > UNIX_TIMESTAMP())
#	UNION
#   SELECT CASE WHEN aa.mail_forward != '' THEN aa.mail_forward ELSE CONCAT(p.login, '@', pd.name) END
#	FROM aliases a
#	JOIN domains ad ON (a.domainid = ad.id)
#	JOIN aliasassignments aa ON (aa.aliasid = a.id)
#	LEFT JOIN passwd p ON (aa.accountid = p.id AND (p.expdate = 0 OR p.expdate > UNIX_TIMESTAMP()))
#	LEFT JOIN domains pd ON (p.domainid = pd.id)
#	WHERE a.login = '%u' AND ad.name = '%d'
#		AND (aa.mail_forward != '' OR p.id IS NOT NULL)
# PostgreSQL	
query = SELECT p.mail_forward
	FROM passwd p
	JOIN domains d ON (p.domainid = d.id)
	WHERE p.login = '%u' AND d.name = '%d'
		AND p.type & 2 = 2 AND p.mail_forward != ''
		AND (p.expdate = 0 OR p.expdate > EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)))
	UNION
	SELECT CASE WHEN aa.mail_forward != '' THEN aa.mail_forward ELSE p.login || '@' || pd.name END
	FROM aliases a
	JOIN domains ad ON (a.domainid = ad.id)
	JOIN aliasassignments aa ON (aa.aliasid = a.id)
	LEFT JOIN passwd p ON (aa.accountid = p.id
		AND (p.expdate = 0 OR p.expdate > EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))))
	LEFT JOIN domains pd ON (p.domainid = pd.id)
	WHERE a.login = '%u' AND ad.name = '%d'
		AND (aa.mail_forward != '' OR p.id IS NOT NULL)

# recipient_bcc_maps.cf (Postfix):

user = lms
password = dbpass
hosts = localhost
dbname = lms
# MySQL
#query = SELECT p.mail_bcc
#        FROM passwd p, domains d WHERE p.domainid = d.id
#		AND p.login = '%u' AND d.name = '%d'
#		AND p.type & 2 = 2
#		AND p.mail_bcc != ''
#		AND (p.expdate = 0 OR p.expdate > UNIX_TIMESTAMP())
# PostgreSQL
query = SELECT p.mail_bcc
        FROM passwd p, domains d WHERE p.domainid = d.id
		AND p.login = '%u' AND d.name = '%d'
		AND p.type & 2 = 2
		AND p.mail_bcc != ''
		AND (p.expdate = 0 OR p.expdate > EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)))