Gentoo Wiki ArchivesGentoo Wiki

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:

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.

Retrieved from "http://www.gentoo-wiki.info/PostgreSQL/Virtual_Mail_System"

Last modified: Fri, 29 Aug 2008 14:55:00 +1000 Hits: 3,103

Created by NickStallman.net, Luxury Homes Australia
Real estate agents should list their apartments, townhouses and units in Australia.