<div>Hello<br></div><div><br></div><div>I would like to log all quarantined messages from amavisd-new into my PostgreSQL 9.4 database. For that purpose I have created a database called amavis_log and used the SQL create table queries concerning the logging database  which I found in the README.sql-pg file.<br></div><div><br></div><div>Note here also that I have also uncommented all FOREIGN KEYS in order to cascade properly the deletion of quarantined mails. See below for the exact SQL script I run against the database.<br></div><div><br></div><div>Unfortunately it can't create the 3rd "CREATE TABLE" statement concerning the msgrcpt table, the error PostgreSQL returns is the following:<br></div><div><br></div><div>ERROR:  there is no unique constraint matching given keys for referenced table "msgs"<br></div><div><br></div><div>Any ideas what could be wrong here? What am I missing?<br></div><div><br>Many thanks in advance for your help.</div><div><br></div><div>Best regards<br></div><div>Mabi<br></div><div><br></div><div><br></div><pre>-- R/W part of the dataset (optional)
--   May reside in the same or in a separate database as lookups database;
--   REQUIRES SUPPORT FOR TRANSACTIONS; specified in @storage_sql_dsn
--
--  Please create additional indexes on keys when needed, or drop suggested
--  ones as appropriate to optimize queries needed by a management application.
--  See your database documentation for further optimization hints.

-- provide unique id for each e-mail address, avoids storing copies
CREATE TABLE maddr (
  id         serial       PRIMARY KEY,
  partition_tag integer   DEFAULT 0,   -- see $partition_tag
  email      bytea        NOT NULL,    -- full e-mail address
  domain     varchar(255) NOT NULL,    -- only domain part of the email address
                                       -- with subdomain fields in reverse
  CONSTRAINT part_email UNIQUE (partition_tag,email)
);

-- information pertaining to each processed message as a whole;
-- NOTE: records with a NULL msgs.content should be ignored by utilities,
--   as such records correspond to messages just being processed, or were lost
CREATE TABLE msgs (
  partition_tag integer     DEFAULT 0,  -- see $partition_tag
  mail_id     bytea         NOT NULL,   -- long-term unique mail id, dflt 12 ch
  secret_id   bytea         DEFAULT '', -- authorizes release of mail_id, 12 ch
  am_id       varchar(20)   NOT NULL,   -- id used in the log
  time_num    integer NOT NULL CHECK (time_num >= 0),
                                        -- rx_time: seconds since Unix epoch
  time_iso timestamp WITH TIME ZONE NOT NULL,-- rx_time: ISO8601 UTC ascii time
  sid         integer NOT NULL CHECK (sid >= 0), -- sender: maddr.id
  policy      varchar(255)  DEFAULT '', -- policy bank path (like macro %p)
  client_addr varchar(255)  DEFAULT '', -- SMTP client IP address (IPv4 or v6)
  size        integer NOT NULL CHECK (size >= 0), -- message size in bytes
  originating char(1) DEFAULT ' ' NOT NULL,  -- sender from inside or auth'd
  content     char(1),                   -- content type: V/B/U/S/Y/M/H/O/T/C
    -- virus/banned/unchecked/spam(kill)/spammy(tag2)/
    -- /bad-mime/bad-header/oversized/mta-err/clean
    -- is NULL on partially processed mail
    -- (prior to 2.7.0 the CC_SPAMMY was logged as 's', now 'Y' is used;
    --- to avoid a need for case-insenstivity in queries)
  quar_type  char(1),                   -- quarantined as: ' '/F/Z/B/Q/M/L
                                        --  none/file/zipfile/bsmtp/sql/
                                        --  /mailbox(smtp)/mailbox(lmtp)
  quar_loc   varchar(255)  DEFAULT '',  -- quarantine location (e.g. file)
  dsn_sent   char(1),                   -- was DSN sent? Y/N/q (q=quenched)
  spam_level real,                      -- SA spam level (no boosts)
  message_id varchar(255)  DEFAULT '',  -- mail Message-ID header field
  from_addr  varchar(255)  DEFAULT '',  -- mail From header field,    UTF8
  subject    varchar(255)  DEFAULT '',  -- mail Subject header field, UTF8
  host       varchar(255)  NOT NULL,    -- hostname where amavisd is running
  CONSTRAINT msgs_partition_mail UNIQUE (partition_tag,mail_id),
  PRIMARY KEY (partition_tag,mail_id),
  FOREIGN KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT
);
CREATE INDEX msgs_idx_sid      ON msgs (sid);
CREATE INDEX msgs_idx_mess_id  ON msgs (message_id); -- useful with pen pals
CREATE INDEX msgs_idx_time_iso ON msgs (time_iso);
CREATE INDEX msgs_idx_time_num ON msgs (time_num);   -- optional

-- per-recipient information related to each processed message;
-- NOTE: records in msgrcpt without corresponding msgs.mail_id record are
--  orphaned and should be ignored and eventually deleted by external utilities
CREATE TABLE msgrcpt (
  partition_tag integer DEFAULT 0,  -- see $partition_tag
  mail_id    bytea    NOT NULL,     -- (must allow duplicates)
  rseqnum    integer  DEFAULT 0   NOT NULL, -- recip's enumeration within msg
  rid        integer  NOT NULL,     -- recipient: maddr.id (duplicates allowed)
  is_local   char(1)  DEFAULT ' ' NOT NULL, -- recip is: Y=local, N=foreign
  content    char(1)  DEFAULT ' ' NOT NULL, -- content type V/B/U/S/Y/M/H/O/T/C
  ds         char(1)  NOT NULL,     -- delivery status: P/R/B/D/T
                                    -- pass/reject/bounce/discard/tempfail
  rs         char(1)  NOT NULL,     -- release status: initialized to ' '
  bl         char(1)  DEFAULT ' ',  -- sender blacklisted by this recip
  wl         char(1)  DEFAULT ' ',  -- sender whitelisted by this recip
  bspam_level real,                 -- per-recipient (total) spam level
  smtp_resp  varchar(255) DEFAULT '', -- SMTP response given to MTA
  CONSTRAINT msgrcpt_partition_mail_rseq UNIQUE (partition_tag,mail_id,rseqnum),
  PRIMARY KEY (partition_tag,mail_id,rseqnum),
  FOREIGN KEY (rid)     REFERENCES maddr(id)     ON DELETE RESTRICT,
  FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
);
CREATE INDEX msgrcpt_idx_mail_id  ON msgrcpt (mail_id);
CREATE INDEX msgrcpt_idx_rid      ON msgrcpt (rid);

-- mail quarantine in SQL, enabled by $*_quarantine_method='sql:'
-- NOTE: records in quarantine without corresponding msgs.mail_id record are
--  orphaned and should be ignored and eventually deleted by external utilities
CREATE TABLE quarantine (
  partition_tag integer  DEFAULT 0,      -- see $partition_tag
  mail_id    bytea   NOT NULL,           -- long-term unique mail id
  chunk_ind  integer NOT NULL CHECK (chunk_ind >= 0), -- chunk number, 1..
  mail_text  bytea   NOT NULL,           -- store mail as chunks of octects
  PRIMARY KEY (partition_tag,mail_id,chunk_ind),
  FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
);

-- field msgrcpt.rs is primarily intended for use by quarantine management
-- software; the value assigned by amavisd is a space;
-- a short _preliminary_ list of possible values:
--   'V' => viewed (marked as read)
--   'R' => released (delivered) to this recipient
--   'p' => pending (a status given to messages when the admin received the
--                   request but not yet released; targeted to banned parts)
--   'D' => marked for deletion; a cleanup script may delete it

-- grant usage rights:
GRANT select,insert,update,delete ON maddr        TO amavis;
GRANT usage,update                ON maddr_id_seq TO amavis;
GRANT select,insert,update,delete ON msgs         TO amavis;
GRANT select,insert,update,delete ON msgrcpt      TO amavis;
GRANT select,insert,update,delete ON quarantine   TO amavis;<br></pre><div class="protonmail_signature_block-empty"><br></div><div class="protonmail_signature_block protonmail_signature_block-empty"><div class="protonmail_signature_block-user protonmail_signature_block-empty"><div><br></div></div><div class="protonmail_signature_block-proton protonmail_signature_block-empty"><br></div></div><div class="protonmail_signature_block-empty"><br></div>