Postfix + PostgreSQL HOW-TO

原文档

PostmailAdmin

本地下载

系统环境:

[root@linux root]# uname -a
Linux linuxas3.9812.net 2.4.21-4.ELsmp #1 SMP Fri Oct 3 17:52:56 EDT 2003 i686 i686 i386 GNU/Linux
[root@linux root]# psql --version
psql (PostgreSQL) 7.3.4
contains support for command-line editing
	

PostgreSQL

关于PostgreSQL更多信息请查看作者的文档:

《PostgreSQL 实用实例参考》

《PostgreSQL 7.4 文档》

确认PostgreSQL是否已经安装

[root@linux root]# rpm -qa |grep postgres
postgresql-devel-7.3.4-1PGDG
postgresql-7.3.4-1PGDG
postgresql-python-7.3.4-1PGDG
postgresql-contrib-7.3.4-1PGDG
postgresql-jdbc-7.3.4-1PGDG
postgresql-server-7.3.4-1PGDG
postgresql-debuginfo-7.3.4-1PGDG
postgresql-libs-7.3.4-1PGDG
postgresql-tcl-7.3.4-1PGDG
postgresql-test-7.3.4-1PGDG
postgresql-pl-7.3.4-1PGDG
postgresql-docs-7.3.4-1PGDG
			

启动PostgreSQL 数据库(Redhat Linux)

[ root@server/~ ]# service postgresql start
[ root@server/~ ]# su - postgres
		

其它系统(bsd ext)

[ root@server/~ ]# su - postgres
[ postgres@server/~ ]$ postmaster &
		

创建用户,数据库

[ postgres@server/~ ]$ $ createuser postfix
[ postgres@server/~ ]$ $ createdb postfix
		

启用tcp/ip连接数据库,端口5432

[root@linux root]# vi /var/lib/pgsql/data/postgresql.conf

tcpip_socket = true
port = 5432
		

访问规则

[root@linux root]# cat /var/lib/pgsql/data/pg_hba.conf

local  all      all             ident   sameuser
host   all      all         127.0.0.1         255.255.255.0   md5
host   all      all         192.168.0.1       255.255.255.0   md5
		

登录测试

[ postgres@server/~ ] $ psql postfix -U postfix
		

postfix 数据库脚本

[ postgres@server/~ ] $ psql postfix -U postfix

CREATE TABLE "admin" (
"username" character varying(255) NOT NULL,
"password" character varying(255) NOT NULL,
"created" timestamp(13) with time zone DEFAULT '1999-04-23 01:05:06',
"modified" timestamp(13) with time zone DEFAULT '1999-04-23 01:05:06',
"active" boolean default true,
Constraint "admin_key" Primary Key ("username")
);

GRANT ALL ON admin TO postfix;

CREATE TABLE "alias" (
"address" character varying(255) NOT NULL,
"goto" text NOT NULL,
"domain" character varying(255) NOT NULL,
"created" timestamp(13) with time zone DEFAULT '1999-04-23 01:05:06',
"modified" timestamp(13) with time zone DEFAULT '1999-04-23 01:05:06',
"active" boolean default true,
Constraint "alias_key" Primary Key ("address")
);

GRANT ALL ON alias TO postfix; 
CREATE TABLE "domain" (
"domain" character varying(255) NOT NULL,
"description" character varying(255) NOT NULL,
"aliases" numeric(10,0) DEFAULT '0' NOT NULL,
"mailboxes" numeric(10,0) DEFAULT '0' NOT NULL,
"maxquota" numeric(10,0) DEFAULT '0' NOT NULL,
"created" timestamp(13) with time zone DEFAULT '1999-04-23 01:05:06',
"modified" timestamp(13) with time zone DEFAULT '1999-04-23 01:05:06',
"active" boolean default true,
Constraint "domain_key" Primary Key ("domain")
);

GRANT ALL ON domain TO postfix;

CREATE TABLE "domain_admins" (
"username" character varying(255) NOT NULL,
"domains" character varying(255) NOT NULL,
"created" timestamp(13) with time zone DEFAULT '1999-04-23 01:05:06',
"active" boolean default true,
Constraint "domain_admins_key" Primary Key ("username")
);

GRANT ALL ON domain_admins TO postfix; 

CREATE TABLE "mailbox" (
"username" character varying(255) NOT NULL,
"password" character varying(255) NOT NULL,
"name" character varying(255) NOT NULL,
"maildir" character varying(255) NOT NULL,
"quota" numeric(10,0) DEFAULT '0' NOT NULL,
"domain" character varying(255) NOT NULL,
"created" timestamp(13) with time zone DEFAULT '1999-04-23 01:05:06',
"modified" timestamp(13) with time zone DEFAULT '1999-04-23 01:05:06',
"active" boolean DEFAULT 't'::bool,
"home" character varying(255) DEFAULT '/var/spool/virtual/',
"uid" numeric(3,0) DEFAULT 200,
"gid" numeric(3,0) DEFAULT 200,
Constraint "mailbox_key" Primary Key ("username")
);


GRANT ALL ON mailbox TO postfix;

CREATE TABLE "vacation" (
"email" character varying(255) NOT NULL,
"subject" character varying(255) NOT NULL,
"body" text,
"cache" text NOT NULL,
"domain" character varying(255) NOT NULL,
"created" timestamp(13) with time zone DEFAULT '1999-04-23 01:05:06',
"active" boolean default true,
Constraint "vacation_key" Primary Key ("email")
);

GRANT ALL ON vacation TO postfix; 

INSERT INTO domain (domain,description,aliases,mailbox,maxquota) values ('dominio.com.br','dominio virtual',1,1,1);
INSERT INTO mailbox (username,password,name,maildir) values ('teste@dominio.com.br','$1$Fi8IP53B$3yeGqD1Cnax.f.yAkLiAd1','name','teste@dominio.com.br/');

De "\q" para sair do psql