Complete Virtual Mail Server/PostgreSQL
From Gentoo Linux Wiki
|
|
|
Getting Started Basic Mail Setup
Enhanced Mail Services
Anti-Spam Configuration
Anti-Virus Configuration Log Analyzer Wrapping it Up |
| edit |
[edit] PostgreSQL
Now lets install, configure and initialize PostgresSQL 8.x.x so we have the database we will need to start authorizing users against. Note: When I emerged Postgres.
| Shell: Installing PostgreSQL |
# emerge postgresql # emerge --config postgresql |
With these items setup, modify /etc/conf.d/postgresql to your liking. The default should be fine.. Code Listing 2: Installing and configuring PostgreSql
| Shell: Configure PostgreSQL |
# nano –w /etc/conf.d/postgresql |
You are now ready to run postgres.
| Shell: Starting PostgreSQL |
# /etc/init.d/postgresql start * Starting PostgreSQL... [ ok ] // Check to ensure that postgres has started # ps –fu postgres postgres 26667 1 0 19:33 pts/1 00:00:00 /usr/bin/postmaster –D /var/lib/postgresql/data postgres 26669 26667 0 19:33 pts/1 00:00:00 postgres: writer process postgres 26672 26667 0 19:33 pts/1 00:00:00 postgres: stats buffer process postgres 26673 26672 0 19:33 pts/1 00:00:00 postgres: stats collector process # /etc/init.d/postgresql stop * Stopping PostgreSQL... [ ok ] |
You can also check the /var/lib/postgresql/data/postgresql.log file to verify that the database started alright, or to find the source of errors if it did not.
Once we're satisfied we add postgres to the default runlevel and start it.
| Shell: Finalizing PostgreSQL Setup |
# /etc/init.d/postgresql start # rc-update add postgresql default |
[edit] Postgres and Webmin
In my first version of this howto, I included phpadmin for assisting in administering postgres. While it offers a little more functionality, webmin has everything I really needed so I took that part out. Instead, this is as good a time as any ensure webmin is setup properly.
When I first tried to access Postgres under webmin’s Server menu, it responded that Postgres was not installed and could not find the pb_hba.conf file. In my case the default paths did not match what I had above, so link to the configure module page and ensure the two incorrect settings are changed to:
| Code: Webmin Config Settings |
Path to postmaster PID file /var/lib/postgresql/data/postmaster.pid Path to host access config file /var/lib/postgresql/data/pg_hba.conf |
With these two set you should be good to go. Of course if the database isn’t running, webmin will let you know and give you a big start PostgreSQL server button.
This may not be needed as the paths were picked up here in my install (yes i did add -postgress for the webmin useflag) - G2g591 - April 4 2008
- I can verify this, as I had no issues and had -postgres for the webmin build. - Linuxbeak 21:41, 7 April 2008 (UTC)
[edit] Database Users
Now that the database works, it is time to start creating the database, users and tables you will need. If you stopped the database above when testing above, then restart it using “/etc/init.d/postgresql start” or webmin. For some common errors that can occur at this point, reference the postgreSQL user docs on their website.
We will start by creating a couple of new database user, postfix and postfixadmin. These will be used by postfix and postfixadmin respectively to access the database. For the purposes of this guide, I will assume that you have called your database user postfix with the password specified by $password.
| Shell: Creating the Database User |
// If you stopped postgres then restart # /etc/init.d/postgresql start * Starting PostgreSQL... [ ok ] // Change to uid postgres and then create the database user # createuser -U postgres -P Enter name of role to add: postfix Enter password for new role: $password Enter it again: $password Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n CREATE ROLE # createuser -U postgres -P Enter name of role to add: postfixadmin Enter password for new role: $password Enter it again: $password Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n CREATE ROLE // If you made a mistake or want a different user, you can delete // the one you just added using the dropuser utility # dropuser -U postgres Enter name of role to drop: postfix DROP ROLE |
[edit] The vmail User
I’m going to jump the gun on this one a little and setup the vmail user as some of these settings will be important when creating your database. The vmail user account will serve as the base linux account that all virtual domain users will share. This is not an 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.
| Shell: Adding the vmail User |
# useradd -d /home/vmail -s /bin/false vmail # groupadd -g 1000 vmail # mkdir /home/vmail # chown vmail:vmail /home/vmail /* this isn't needed at all! */ or just: #useradd -u $UID -d /home/vmail -m -s /sbin/nologin vmail |
[edit] Creating the Database
It is now time to create the database and tables required. Code Listing 8: Creating the database
| Shell: Creating the database |
// 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.
/* Currently the table transport doesn't appear to be used. The howto this document seems to be relying on somewhat, used the transport table to determin whether a messages were to be deliverd virtually or local (relay=virtal relay=local). However this doesn't appear to be happening that way. If it would be needed, the domain table has the transport field but doesn't use it. Postfix documentation is hard to read and compilcated as it is. Anyhow, I think that mydestination field should be commented out in main.cf (as mentioned later) and rely on the transport field of domain. How that is to be accomplished, I'm not sure though.
Also mailbox table should have a field called email (or similar). with 'only' having a username, and that username beeing an email address, we are forcing users to log in by e-mail. As I see it, postfix doesn't care about usernames, just email addresses where it accepts messages for, hence the email field. However clients (pop, imap, webmail, USERS) do care about usernames. This way users can have any username they want, and it doesn't need to be their email address. */
| Code: Table Creation |
-- 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,
auxoptions text NOT NULL default '',
PRIMARY KEY (domain)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON domain to postfixadmin, postfix;
--
-- Table structure for table mailbox
--
DROP TABLE mailbox;
CREATE TABLE mailbox (
email varchar(255) NOT NULL default '',
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).
| Shell: 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.
Note: I found that when editing some of these tables with webmin, it would claim that there was no primary key for certain tables, because the field being used as the primary key was a reserved word, case in point being the domain table, domain is a reserved word, therefore if one, for example, renamed the domain column to domainx and made that the primary key as well, there was no problem. This is only a problem in webmin, normal sql commands still just work fine. - Nino Dubin (05/12/06)
[edit] Postgres Access
With the database created, that last thing you will need to do is set the Postgres parameters to allow remote tcp/ip connections to the database. If you do everything on one host, just use localhost as your hostname throughout the rest of the configuration files. Note the port number as you will require this later when configuring Postgres authentication.
| File: /etc/conf.d/postgresql |
PGOPTS="-i" |
You will need to restart Postgres for the above changes to take effect.
-i option makes Postgres listening on all interfaces which is not what you usually want. If your Postfix is on the same host as Postgres, it is safer and faster to use the UNIX socket. Just use unix:/path/to/socket as a hostname. Default path is /tmp/.S.PGSQL.5432.
Elprans 18:50, 10 September 2006 (UTC)
(Peaceful 6 November 2006) I configured my pg_hba.conf and pg_ident.conf to require passwords for access to postgres, except when the root system user is accessing the postgres database user.
| File: /var/lib/postgresql/data/pg_hba.conf |
# TYPE DATABASE USER CIDR-ADDRESS METHOD local all postgres ident rootmap local all all md5 host all all 127.0.0.1/32 md5 # You could use the following for access from some other location #host all all 1.2.3.4/32 md5 |
| File: /var/lib/postgresql/data/pg_ident.conf |
# MAPNAME IDENT-USERNAME PG-USERNAME rootmap root postgres |
(End of Peaceful's suggested stuff)
| Shell: Restarting PostgreSQL |
# /etc/init.d/postgresql restart |
At this point I jumped off to webmin to be sure the tables where there and everything looked good. One limitation of webmin is that it does not report any of the permissions on the database so it is worth login into psql as the postfix or postfixadmin user and be sure that you can query the tables. I got caught on this one before.
| Shell: Testing Database Access |
# psql -U postfix postfix
Welcome to psql 8.1.0, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postfix=# SELECT * FROM admin;
username | password | created | modified | active
----------+----------+---------+----------+--------
(0 rows)
postfix=#
|
