PostgreSQL/Virtual_Mail_System
Virtual Mail System
This section will show you one use for your postgresql server namely a virtual mail system. If you don't want or need a virtual mail server just read through it might give you some ideas. Personally I added this section because it was on one of the postgresql howto's and I don't want to destroy somebody else his work (and it provides atleast a nice exercise). Nevertheless it might very handy for you so here we go : For a virtual mail system we first need a system user and a system group. These will provide the backbone of all virtual domain users which we will create later. Then we need to create several database tables which can hold the virual domain users information. Then use postfix so virtual domain users can send email to one another.
Also please check : postfixadmin which will provide a simular solution. But also closer to home there is an nice howto : HOWTO Email: A Complete Virtual System - PostgreSQL
The vmail User
The vmail user account will serve as the base linux account that all virtual domain users will share. This is not an actual account they can use to log into the system, it is simply an account that provides a valid group and user id for virtual users. I decided that I setup gid=uid in my system, so after creating the vmail user, I got the uid from webmin (which was 1000) and then created a new group, vmail with a gid of 1000. I then reassigned the vmail user to this group. Depending on your particular machine, you are likely to get a value other then 1000. No worries just use whatever you found with webmin. At this point, most of the HOWTO’s out there will tell you to get setup your /home/vmail directory which is where you will end up storing all your virtual mail. Because of the way I have partitioned data across my hard drives, I took a different approach and decided to stuff all my mail under /var/mail/vmail.
| Code: Adding the vmail User |
# useradd -d /var/mail/vmail -s /bin/false vmail # groupadd -g 1000 vmail # mkdir /var/mail/vmail # chown vmail:vmail /var/mail/vmail |
Creating the Database
It is now time to create the database and tables required. Code Listing 8: Creating the database
| Code: Adding the vmail User |
// Create the database # createdb -U postgres postfix CREATE DATABASE |
The listing below outlines the tables to be created. The schema is based on the postfixadmin schema, with a few extra elements that are included to support some additional (and optional) functionality. Postfixadmin will not be able to address all of these tables, however I have included them in my installation as I may make use of them by either directly inserting values into the table, or if I am feeling really ambitious, I may extend webmin or postfixadmin to address them (yah … right).
The simplest way is to copy the script below and paste it into a file. You can then use the psql utility to run the script. The following tables are used in postfix database:
- alias - local email alias and mailman alias information.
- relocated - relocated user email address maps
- transport – supports delivery to non-postfix mailbox stores for hosted domains.
- mailbox - all user account and mailbox information
- virtual - virtual domain email alias map
Do not forget to edit the data that is specific to your setup. Default settings for gid and uid in the mailbox table should be set (in my case, based on the gid and uid of the vmail user setup earlier). To make life easy, you should probably copy the text below and paste it into a file so you can use psql to create your tables.
| Code: Table Creation |
// Create the database
-- Postfix Admin Release 2.x --
----------------------------------------------------------
--
-- Copyright (c) 2002 - 2005 High5!
-- Created by: Mischa Peters <mischa at high5 dot net>
-- Updated by: Angus Muir
--
-- This is the complete database structure to support virtual
-- domains and Postfix Admin on a PostgreSQL database.
--
-- There are 2 entries for a database user in the file.
-- One you can use for Postfix and one for Postfix Admin.
--
--
-- Table structure for table admin
--
DROP TABLE admin;
CREATE TABLE admin (
username varchar(255) NOT NULL default '',
password varchar(255) NOT NULL default '',
created timestamp with time zone default now(),
modified timestamp with time zone default now(),
active boolean NOT NULL default true,
PRIMARY KEY (username)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON admin to postfixadmin, postfix;
--
-- Table structure for table alias
--
DROP TABLE alias;
CREATE TABLE alias (
address varchar(255) NOT NULL default '',
goto text NOT NULL,
domain varchar(255) NOT NULL default '',
created timestamp with time zone default now(),
modified timestamp with time zone default now(),
active boolean NOT NULL default true,
PRIMARY KEY (address)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON alias to postfixadmin, postfix;
--
-- Table structure for table domain
--
DROP TABLE domain;
CREATE TABLE domain (
domain varchar(255) NOT NULL default '',
description varchar(255) NOT NULL default '',
aliases integer NOT NULL default 0,
mailboxes integer NOT NULL default 0,
maxquota integer NOT NULL default 0,
transport varchar(255) default NULL,
backupmx boolean NOT NULL default false,
created timestamp with time zone default now(),
modified timestamp with time zone default now(),
active boolean NOT NULL default true,
PRIMARY KEY (domain)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON domain to postfixadmin, postfix;
--
-- Table structure for table mailbox
--
DROP TABLE mailbox;
CREATE TABLE mailbox (
username varchar(255) NOT NULL default '',
password varchar(255) NOT NULL default '',
name varchar(255) NOT NULL default '',
uid integer NOT NULL default '1000',
gid integer NOT NULL default '1000',
homedir text NOT NULL default '',
maildir text NOT NULL default '',
quota integer NOT NULL default '0',
domain varchar(255) NOT NULL default '',
created timestamp with time zone default now(),
modified timestamp with time zone default now(),
active boolean NOT NULL default true,
PRIMARY KEY (username)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON mailbox to postfixadmin, postfix;
--
-- Table structure for table relocated
--
DROP TABLE relocated;
CREATE TABLE relocated (
email varchar(255) NOT NULL default '',
destination varchar(255) NOT NULL default '',
created timestamp with time zone default now(),
modified timestamp with time zone default now(),
active boolean NOT NULL default true,
PRIMARY KEY (email)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON relocated to postfixadmin, postfix;
--
-- Table structure for table 'transport'
--
DROP TABLE transport;
CREATE TABLE transport (
domain varchar(255) NOT NULL default '',
destination varchar(255) NOT NULL default '',
created timestamp with time zone default now(),
modified timestamp with time zone default now(),
active boolean NOT NULL default true,
PRIMARY KEY (domain)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON transport to postfixadmin, postfix;
--
-- Table structure for table 'log'
--
DROP TABLE log;
CREATE TABLE log (
timestamp timestamp with time zone default now(),
username varchar(255) NOT NULL default '',
domain varchar(255) NOT NULL default '',
action varchar(255) NOT NULL default '',
data varchar(255) NOT NULL default ''
);
GRANT SELECT, INSERT, UPDATE, DELETE ON log to postfixadmin, postfix;
DROP TABLE domain_admins;
CREATE TABLE domain_admins (
username varchar(255) NOT NULL default '',
domain varchar(255) NOT NULL default '',
created timestamp with time zone default now(),
modified timestamp with time zone default now(),
active boolean NOT NULL default true,
PRIMARY KEY (username)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON domain_admins to postfixadmin, postfix;
--
-- Table structure for table vacation
--
DROP TABLE vacation;
CREATE TABLE vacation (
email varchar(255) NOT NULL default '',
subject varchar(255) NOT NULL default '',
body text NOT NULL,
cache text NOT NULL,
domain varchar(255) NOT NULL default '',
created timestamp with time zone default now(),
modified timestamp with time zone default now(),
active boolean NOT NULL default true,
Constraint "vacation_key" Primary Key ("email")
);
GRANT SELECT, INSERT, UPDATE, DELETE ON vacation to postfixadmin, postfix;
|
If you pasted the above into a file (say postfixdb.sql) then we can load it straight away (otherwise you will need to type it all in by hand).
| Code: Webmin Config Settings |
# psql –U postgres postfix < postfixdb.sql |
If this is the first time you have run this script, you will get a bunch of errors from the DROP TABLE command. Because I ran this script many times as I tweaked and changed things, it was easier to include the DROP and ensure I was getting a clean install.
Created by NickStallman.net, Luxury Homes Australia
Real estate agents should list their apartments, townhouses and units in Australia.