Pgsql

From Virtual postfix admin

Jump to: navigation, search




Contents

How to create the DB

  • To create virtual_mail database just type createdb virtual_mail -h sql_server_name -U user_name
  • virtual_mail db use pltclu language because it is using pgmail function(this function send an email to the just new created email addresse.If it is not used, and the client is accesing the account an error ocure because the Maildir structure is not created)
  • To enable pltclu language in your virtual_mail db type createlang pltclu virtual_mail_db -h sql_server_name -U user_name
  • To enable plpgsql language in your virtual_mail db type 'createlang plpgsql virtual_mail_db -U virtula_mail_user -h sql_server'


Sql Tables 

CREATE TABLE addresses (
id integer DEFAULT nextval('addresses_id_seq'::regclass) NOT NULL,
domain_id integer NOT NULL,
username character varying(80) NOT NULL,
"password" character varying(80),
address character varying(160) NOT NULL);

CREATE TABLE users (
id bigint DEFAULT nextval('id_adm_inc'::regclass),
username text,
"password" text,
nume text,
prenume text);

CREATE TABLE aliases (
id integer DEFAULT nextval('aliases_id_seq'::regclass) NOT NULL,
address_id integer NOT NULL,
target_address character varying(160) NOT NULL,
domain_id text NOT NULL);

CREATE TABLE domains (
id integer DEFAULT nextval('domains_id_seq'::regclass) NOT NULL,
"domain" character varying(80) NOT NULL,
"password" character varying(40) NOT NULL,
 uid text,
 gid text,
 max_email text DEFAULT 10 NOT NULL);

CREATE TABLE logs (
id bigint DEFAULT nextval('id_inc_logs'::regclass),
ip text,
data text);

Sql sequences 

CREATE SEQUENCE addresses_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

CREATE SEQUENCE id_adm_inc
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

CREATE SEQUENCE aliases_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

CREATE SEQUENCE domains_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

Sql view 

CREATE VIEW addresses_number AS
SELECT count(*) AS count FROM addresses;

CREATE VIEW admins_number AS
SELECT count(*) AS count FROM users;

CREATE VIEW aliases_number AS
SELECT count(*) AS count FROM aliases;

CREATE VIEW domains_number AS
SELECT count(*) AS count FROM domains;

CREATE VIEW email_number AS
SELECT count(addresses.id) AS count FROM addresses;

CREATE VIEW postfix_accounts AS
SELECT addresses.address,((((domains."domain")::text || '/'::text) ||  (addresses.username)::text) || '/'::text) 
AS mailbox, domains.uid, domains.gid, addresses."password" 
FROM addresses, domains WHERE ((addresses.domain_id = domains.id) 
AND (addresses."password" IS NOT NULL));

CREATE VIEW postfix_aliases AS
SELECT addresses.domain_id, addresses.address, aliases.target_address 
FROM addresses, aliases 
WHERE (addresses.id = aliases.address_id);


Structure of sql table 

virtual_mail=# \d
             List of relations
Schema |       Name       |   Type   | Owner

public | addresses        | table    | user_name
public | addresses_id_seq | sequence | user_name
public | addresses_number | view     | user_name
public | admins_number    | view     | user_name
public | aliases          | table    | user_name
public | aliases_id_seq   | sequence | user_name
public | aliases_number   | view     | user_name
public | domains          | table    | user_name
public | domains_id_seq   | sequence | user_name
public | domains_number   | view     | user_name
public | email_number     | view     | user_name
public | id_adm_inc       | sequence | user_name
public | id_domain_inc    | sequence | user_name
public | id_inc_logs      | sequence | user_name
public | logs             | table    | user_name
public | postfix_accounts | view     | user_name
public | postfix_aliases  | view     | user_name
public | quotalimits      | table    | user_name
public | quotatallies     | table    | user_name
public | users            | table    | user_name
(20 rows)


Download virtual_demo_sql file

  • This sql contain also the tables for proftpd.V.A.P.E.S also can configure proftp user(this will be another module
  • To download the virtual_mail_demo.sql click here
  • To import this file to your new virtual_mail DB type psql -U user_name -d virtual_mail -h sql_host_name -f virtual_mail_demo.sql
  • To login use user:admin , password admin and user type Administrator.

instr function

  • instr funtion it is used for renaming domains.When a domain is renamed all the emails from that domain will be renamed.
  • To install instr function you must do:
  • Fist, download this and this file and insert them into your virtual_mail database following the instructions.
    • Second, is to download the virtual_mail_demo.sql from here and drop your old database, create a new one and insert virtual_mail_demo.sql in the new created DB.Be aware, doing this you will lost all your data, backup first ;).For more info read Pgsql
  • rename.sql function 
CREATE FUNCTION rename_emails() RETURNS "trigger"
AS $$begin
if NEW.domain <> OLD.domain  then
update addresses
set address=substr (address, 1,instr(address, '@')) || NEW.domain where domain_id  =  OLD.id;
end if;
return new;
end;$$
LANGUAGE plpgsql;

CREATE TRIGGER rename_emails
AFTER UPDATE ON domains
FOR EACH ROW
EXECUTE PROCEDURE rename_emails();
  • For instr sql porting you can read here more
Retrieved from "http://idea-soft.sci-office.com/~dan/virtual_wiki/index.php/Pgsql"
Personal tools
Requirements
Devel Page