Search:  
Gentoo Wiki

HOWTO_Email:_A_Complete_Virtual_System_-_PostgreSQL


Complete Virtual Mail Server


Getting Started


Basic Mail Setup


Enhanced Mail Services


Anti-Spam Configuration


Anti-Virus Configuration


Log Analyzer


Wrapping it Up

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

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)

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

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
  

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:

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)

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=#    
  

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

Last modified: Mon, 29 Sep 2008 07:27:00 +0000 Hits: 19,572