Copy SMTP Rules - Database Query

This is an advanced Database action. There is no simple query that will perform the required operation, so we have to create a custom PostgreSQL function to copy the data.

At a command prompt in the VPOP3 directory run

psql

(The password is 'vpop3pass')

Then, type (or copy/paste) the text below, to create the required function

CREATE OR REPLACE FUNCTION copysmtprules(fromid integer,toid integer) RETURNS integer AS $$
DECLARE
  rl RECORD;
  newruleid INTEGER;
  cnt INTEGER := 0;
BEGIN
  FOR rl IN SELECT * FROM rules.smtprules WHERE service=fromid ORDER BY ruleid LOOP
    INSERT INTO rules.smtprules (service,rulephase,ruleorder,name,orrule,action,data) VALUES(toid,rl.rulephase,rl.ruleorder,rl.name,rl.orrule,rl.action,rl.data) returning ruleid into newruleid;
    INSERT INTO rules.smtpruleconditions (ruleid,invert,field,comparison,data) SELECT newruleid, invert,field,comparison,data FROM rules.smtpruleconditions WHERE ruleid=rl.ruleid;
    cnt := cnt + 1;
  END LOOP;
  RETURN cnt;
END;
$$ LANGUAGE plpgsql;

Now, type:

SELECT * FROM settings.settings WHERE name ILIKE '\\service-%\\~Name';

This will give you a list of all the Services created in VPOP3, and their IDs (the number after the 'service-' text). Take a note of the service ID you want to copy the rules from, and the service ID you want to copy the rules to.

Now, type:

SELECT copysmtprules(<fromid>, <toid>);

For instance:

SELECT copysmtprules(1, 20);

This will perform the copy operation and display the number of rules copied.

You do not need to stop VPOP3 while you do this, or restart VPOP3 afterwards.