adaptation of http://flurdy.com/docs/postfix/#config-simple-database for postgresql

pgsql.sql

CREATE SEQUENCE seq_aliases_pkid;

CREATE TABLE IF NOT EXISTS aliases (
    pkid INT NOT NULL DEFAULT nextval('seq_aliases_pkid'),
    mail VARCHAR(120) NOT NULL default '',
    destination VARCHAR(120) NOT NULL default '',
    enabled BOOLEAN NOT NULL default true,
    PRIMARY KEY (pkid)
);

ALTER SEQUENCE seq_aliases_pkid OWNED BY aliases.pkid;
CREATE UNIQUE INDEX idx_aliases_mail ON aliases(mail);

CREATE SEQUENCE seq_domains_pkid;

CREATE TABLE IF NOT EXISTS domains (
    pkid INT NOT NULL DEFAULT nextval('seq_domains_pkid'),
    domain VARCHAR(120) NOT NULL default '',
    transport VARCHAR(120) NOT NULL default 'virtual:',
    enabled BOOLEAN NOT NULL default true,
    PRIMARY KEY (pkid)
);

ALTER SEQUENCE seq_domains_pkid OWNED BY domains.pkid;

CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(128) NOT NULL default '',
    name VARCHAR(128) NOT NULL default '',
    uid INT NOT NULL default '5000',
    gid INT NOT NULL default '5000',
    home VARCHAR(255) NOT NULL default '/var/spool/mail/virtual',
    maildir VARCHAR(255) NOT NULL default 'foobar/',
    enabled BOOLEAN NOT NULL default true,
    change_password BOOLEAN NOT NULL default true,
    clear VARCHAR(128) NOT NULL default 'this should not be a real password horse table vape 654321',
    crypt VARCHAR(128) NOT NULL default 'sdtrusfX0Jj66',
    quota VARCHAR(255) NOT NULL default '',
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX idx_users_id ON users(id);