About creating database

Discussion in 'Server Operation' started by satimis, Jun 14, 2007.

  1. satimis

    satimis New Member

    Hi folks,


    OpenBSD 4.0 x86_64
    Postfix-2.4.3
    Mysql
    ($ mysql --version
    mysql Ver 14.12 Distrib 5.0.24a, for unknown-openbsd4.0 (x86_64) using readline 4.3)


    According to;

    http://www.postfixvirtual.net/postfixconf.html#postfix

    I issued following SQL commands to create a database named "mail" and a user "vmailuser" with password "password123"

    $ mysql -u root -p
    Enter password:
    Code:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 7 to server version: 5.0.24a-log
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql> CREATE DATABASE mail;
    Query OK, 1 row affected (0.03 sec)
    
    mysql> GRANT all privileges on mail.* TO [email protected] IDENTIFIED BY 'pass
    word123' ;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> GRANT all privileges on mail.* TO [email protected] IDENTIFIED BY 'pass
    word123' ;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> use mail;
    Database changed
    mysql> show tables;
    Empty set (0.03 sec)
    
    mysql> \q
    Bye
    
    No complaint indicated.


    Coming to "Create SQL FILE", on which directory shall I create the file "postfix_virtual.sql" so that it can be evoked on running;

    # mysql -u root -p < postfix_virtual.sql

    What shall be its owner and permission ???

    I'll copy the script there on the file. UID and GIU are 2007. Shall I add "use mail" as the first line of the scrip

    Please advise. TIA

    What other attention I have to pay ???


    B.R.
    satimis
     
  2. falko

    falko Super Moderator ISPConfig Developer

    The file postfix_virtual.sql must already exist, and you must run the above command in the directory where postfix_virtual.sql is.

    Doesn't matter, but the file must be readable.

    Depends on what is in the postfix_virtual.sql file. If it begins with
    Code:
    use mail;
    , then you don't have to type it in; otherwise you must type it in.
     
  3. satimis

    satimis New Member

    Hi falko,


    Your advice noted with tks.


    Performed following steps, copying the complete script on mysql shell with uid/gid=2007;

    $ mysql -u root -p
    Enter Password
    Code:
    mysql> use mail;
    mysql> CREATE TABLE postfix_alias (
        ->   id int(11) unsigned NOT NULL auto_increment,
        ->   alias varchar(128) NOT NULL default '',
        ->   destination varchar(128) NOT NULL default '',
        ->   PRIMARY KEY (id)
        -> ) TYPE=MyISAM;
    Query OK, 0 rows affected, 1 warning (0.05 sec)
    
    mysql> CREATE TABLE postfix_relocated (
        ->   id int(11) unsigned NOT NULL auto_increment,
        ->   email varchar(128) NOT NULL default '',
        ->   destination varchar(128) NOT NULL default '',
        ->   PRIMARY KEY (id)
        -> ) TYPE=MyISAM;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> CREATE TABLE postfix_transport (
        ->   id int(11) unsigned NOT NULL auto_increment,
        ->   domain varchar(128) NOT NULL default '',
        ->   destination varchar(128) NOT NULL default '',
        ->   PRIMARY KEY (id),
        ->   UNIQUE KEY domain (domain)
        -> ) TYPE=MyISAM;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> CREATE TABLE postfix_virtual_domains (
        ->   id int(11) unsigned NOT NULL auto_increment,
        ->   domain varchar(128) NOT NULL default '',
        ->   destination varchar(128) NOT NULL default '',
        ->   PRIMARY KEY (id),
        ->   UNIQUE KEY domain (domain)
        -> ) TYPE=MyISAM;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> CREATE TABLE postfix_users (
        ->   id int(11) unsigned NOT NULL auto_increment,
        ->   email varchar(128) NOT NULL default '',
        ->   clear varchar(128) NOT NULL default '',
        ->   crypt varchar(128) NOT NULL default '',
        ->   name tinytext NOT NULL,
        ->   uid int(11) unsigned NOT NULL default '2007',
        ->   gid int(11) unsigned NOT NULL default '2007',
        ->   homedir tinytext NOT NULL,
        ->   maildir tinytext NOT NULL,
        ->   quota tinytext NOT NULL,
        ->   access enum('Y','N') NOT NULL default 'Y',
        ->   postfix enum('Y','N') NOT NULL default 'Y',
        -> disablepop3 char(1) NOT NULL default '0',
        -> disableimap char(1) NOT NULL default '0',
        -> disablewebmail char(1) NOT NULL default '0',
        -> sharedgroup varchar(128) NOT NULL default '0',
        ->   smtpaccess enum('Y','N') NOT NULL default 'Y',
        ->
        ->   PRIMARY KEY (id),
        ->   UNIQUE KEY email (email)
        -> ) TYPE=MyISAM;
    Query OK, 0 rows affected, 1 warning (0.03 sec)
    
    mysql> CREATE TABLE postfix_virtual (
        ->   id int(11) unsigned NOT NULL auto_increment,
        ->   email varchar(128) NOT NULL default '',
        ->   destination varchar(128) NOT NULL default '',
        ->   PRIMARY KEY (id)
        -> ) TYPE=MyISAM;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql>  CREATE TABLE postfix_access (
        ->   id int(10) unsigned NOT NULL auto_increment,
        ->   source varchar(128) NOT NULL default '',
        ->   access varchar(128) NOT NULL default '',
        ->   type enum('recipient','sender','client') NOT NULL default
    'recipient',
        ->   PRIMARY KEY (id)
        -> ) TYPE=MyISAM ;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> \q
    Bye
    
    Tables created


    $ mysql -u root -p
    Enter password:
    Code:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3 to server version: 5.0.24a-log
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql> use mail;
    Database changed
    mysql> show tables;
    +-------------------------+
    | Tables_in_mail          |
    +-------------------------+
    | postfix_access          |
    | postfix_alias           |
    | postfix_relocated       |
    | postfix_transport       |
    | postfix_users           |
    | postfix_virtual         |
    | postfix_virtual_domains |
    +-------------------------+
    7 rows in set (0.00 sec)
    
    mysql> \q
    Bye
    

    Tks


    How to test the tables are functioning properly ???


    B.R.
    satimis
     
  4. falko

    falko Super Moderator ISPConfig Developer

    Create some email accounts and try to send from/to these email accounts.
     
  5. satimis

    satimis New Member

    Hi falko


    I have following account created

    /var/mail/vmail/satimis.com/albert

    Made following tests without result;

    1) send mail from [email protected]
    $ telnet localhost 25
    ehlo satimis.com
    mail from: [email protected]
    etc.
    ..

    The mail was queueing only not sent out. Whether I should run;
    $ telnet [email protected] 25
    etc.
    ...
    ???


    2) Send mail to [email protected] from ISP website. The mail was rejected
    Code:
    ----- The following addresses had permanent fatal errors -----
    <[email protected]>
    (reason: 550 5.1.1 <[email protected]>: Recipient address rejected: User
    unknown in local recipient table)
    
    ----- Transcript of session follows -----
    ... while talking to smtp.satimis.com.:
    >>> DATA
    <<< 550 5.1.1 <[email protected]>: Recipient address rejected: User unknown in
    local recipient table
    550 5.1.1 <[email protected]>... User unknown
    <<< 554 5.5.1 Error: no valid recipients
    
    Pls shed me some light. Tks.


    B.R.
    satimis
     

Share This Page