CS & IT Solutions

FreeBSD-Postfix-MySQL-SpamAssassin-Maia-Virtual Setup

First, I would like to thank DrkShdw, Worth, and Fr0zen from ##FreeBSD on FreeNode for their time and ideas when it came down to me writing this tutorial/guide. So, thanks a ton, fellas! Now, on with it!

I wrote this tutorial because of the many, MANY times I’ve been asked about how to set up Postfix on a FreeBSD system. There are many great tutorials out there that can guide people through a Postfix setup. However, none of them are related to FreeBSD. So, I’ve decided to give it a go and here it is. Read the rest of this entry »

Compiling the Kernel
After installing FreeBSD, compiling the kernel.

Via sysinstall:

# Sysinstall
Configure
Distributions
[x] src
[x] sys

# Cd / sys/i386/conf /
Hostname # cp GENERIC
Hostname # ee

Include at the end of file:

# firewall options IPFIREWALL
options IPFIREWALL_VERBOSE # enable logging to syslogd (8)
options IPFIREWALL_VERBOSE_LIMIT = 100 # limit verbosity
options IPFIREWALL_DEFAULT_TO_ACCEPT # allow everything by default
options IPFIREWALL_FORWARD # packet destination changes
IPFILTER options
IPFILTER_LOG options
options IPDIVERT # divert sockets
options IPSTEALTH # support for stealth forwarding
DUMMYNET options
options HZ = 1000
ALTQ options
options ALTQ_CBQ # Class Bases Queuing
options ALTQ_RED # Random Early Drop
options ALTQ_RIO # RED In / Out
# options ALTQ_HFSC Hierarchical Packet Scheduler
options ALTQ_CDNR # Traffic conditioner
options ALTQ_PRIQ # Priority Queuing
netgraph options
NETGRAPH_PPPOE options
NETGRAPH_SOCKET options
NETGRAPH_IFACE options
options BRIDGE

# Config SEUKERNEL
# Cd .. / compile / SEUKERNEL
# Make depend
# Make
# Make install
# Reboot

Update the ports
Update the ports before you begin installing the packages:

# Portsnap fetch extract

Installing PPPoE
Edit the file:

# Ee / etc / ppp / ppp.conf

Delete everything and paste the configs below:

Ppp.conf ######## ########
BEGIN ######### ##########
default:
set log Chat Command Phase # Enables the client logged.
# enable pap pap Enables authentication (password authentication protocol)
enable chap # Activate account CHAP (Challenge Handshake Authentication Protocol)
# enable echo Sending LCP echos (Check if the link is active)
# 5 September echoperiod Shipping time for each echo (After 5 failures the tun is disconnected)
allow mode direct # Turn on ppp bridging
enable proxy # Enables ppp proxyarping
# How to disable IPV6CP not use IPV6, we do not want their mistakes
September mru 1492 # Set the MRU below 1500
September mtu 1492 # Set the MRU below 1500
September ifaddr 192.168.1.1 192.168.1.2-192.168.1.100 # gateway and range of IPs
set speed sync #
set timeout 0 #
enable lqr #
accept dns # DNS accepted
September radius / etc / radius.conf # Active Radius and specifies where the connection file
END ########### ##########
Ppp.conf ######## ########

Edit / etc / radius.conf (create this file).

RADIUS.CONF ###### ######
BEGIN ######### #########
# # # # type # # # # server password # ######
auth localhost senharadius
acct localhost senharadius Read the rest of this entry »

Backup

Dump ALL MySQL Databases

Dump Individual or Multiple MySQL Databases

Dump only certain tables from a MySQL Database

Restore

source here

MySQL is a widely spread SQL database management system mainly used on LAMP (Linux/Apache/MySQL/PHP) projects.

In order to be able to use a database, one needs to create: a new database, give access permission to the database server to a database user and finally grant all right to that specific database to this user.

This tutorial will explain how to create a new database and give a user the appropriate grant permissions.

For the purpose of this tutorial, I will explain how to create a database and user for the music player Amarok. In order to index its music collection, Amarok quand use a mysql backend.
The requirement for this set up is to have access to a database. We are going to create a database called amarok which will be accessible from localhost to user amarok idetified by the password amarok….

Obviously, we need to to have a mysql server installed as well as amarok:

$ sudo apt-get install mysql-server amarok

On a default settings, mysql root user do not need a password to authenticate from localhost. In this case, ou can login as root on your mysql server using:

$ mysql -u root

If a password is required, use the extra switch -p:

$ mysql -u root -p
Enter password:

Now that you are logged in, we create a database:

mysql> create database amarokdb;
Query OK, 1 row affected (0.00 sec)

We allow user amarokuser to connect to the server from localhost using the password amarokpasswd:

mysql> grant usage on *.* to amarokuser@localhost identified by ‘amarokpasswd’;
Query OK, 0 rows affected (0.00 sec)

And finally we grant all privileges on the amarok database to this user:

mysql> grant all privileges on amarokdb.* to amarokuser@localhost ;
Query OK, 0 rows affected (0.00 sec)

And that’s it. You can now check that you can connect to the MySQL server using this command:

$ mysql -u amarokuser -p’amarokpasswd’ amarokdb
Your MySQL connection id is 12
Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

 Thanks to Author, orignal link http://www.debuntu.org/how-to-create-a-mysql-database-and-set-privileges-to-a-user

Using ProFTPD and its SQL module for MySQL, it is possible to give FTP access to non system users.
By doing so, one can give access to a Virtual User which is only allowed to use the FTP service.

This Tutorial will show how to create and populate the user and group tables as well as configure proftpd in order to use the MySQL backend.

In order to achieve this, we will:

1. have to create a local user and group that people login in through will be run as.
2. create a database for proftpd as well as grant permissions to the proftp database user.
3. populate the database.
4. and finally, configure proftpd.

1. Requirements:

as we will be running proftpd server and we will authenticate using a mysql database, we need to install both components plus the mysql module for proftpd:

# apt-get install proftpd proftpd-mysql mysql-server

On Ubuntu and Debian, the default root password for MySQL is empty. You might want to change it by typing:

# mysqladmin -u root password ‘hereismypassword’

2. Local user and group:

In our example, the ftp user uid and group gid will be 3001 (this value will be used later on). The local user we are going to create will have /dev/null as home and a shell of /bin/false.

To create the group and user type:

# groupadd -G 3001 ftpgroup
# useradd -s /bin/false -d /bin/null -c “proftpd user” –uid 3001 -g ftpgroup ftpuser

3. MySQL Database:

Now, it is time to create the database, grant a user permissions and finally create the tables.

The database will be called proftpddb and user proftpuser will be able to connect from localhost using password proftpdpassword.

3.1 Create the database:

Log into mysql as root:

# mysql -u root -p

And create the database proftpddb:

mysql> CREATE DATABASE proftpddb;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT USAGE ON *.* TO proftpduser@localhost IDENTIFIED BY ‘proftpdpassword’;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON proftpddb.* TO ‘proftpduser’@'localhost’;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

After we create the database and gave access to a specific user we have to create the tables that are going to host our groups and users.

3.2. Creating database tables:

There is different ways of creating the tables. Either interactively through mysql command line or by using a sql dump file.
In this tutorial we will create a dump file and inject it directly from the shell command line.

Copy the following text in a file called proftpd-mysql.sql:

CREATE TABLE ftpgroup (
 groupname varchar(16) NOT NULL default '',
 gid smallint(6) NOT NULL default '3000',
 members varchar(16) NOT NULL default '',
 KEY groupname (groupname)
 ) TYPE=MyISAM COMMENT='ProFTPd group table';

 CREATE TABLE ftpuser (
 id int(10) unsigned NOT NULL auto_increment,
 userid varchar(32) NOT NULL default '',
 passwd varchar(80) NOT NULL default '',
 uid smallint(6) NOT NULL default '3000',
 gid smallint(6) NOT NULL default '3000',
 homedir varchar(255) NOT NULL default '',
 shell varchar(16) NOT NULL default '/sbin/nologin',
 count int(11) NOT NULL default '0',
 accessed datetime NOT NULL default '0000-00-00 00:00:00',
 modified datetime NOT NULL default '0000-00-00 00:00:00',
 PRIMARY KEY (id),
 UNIQUE KEY userid (userid)
 ) TYPE=MyISAM COMMENT='ProFTPd user table';

Save it and finally type:

# mysql -u root -p proftpddb < proftpd-mysql.sql

Note that this must be done as root since proftpduser does not have credentials for creating tables in database proftpddb.

Finally, we are going to create our first virtual ftp user and group.

3.3. First user and group:

In order to create our first user and password, we are going to connect using the database user access we have just created:

# mysql -u proftpduser -p proftpddb

and create your first group:

mysql> INSERT INTO `ftpgroup` (`groupname`, `gid`, `members`) VALUES (‘ftpgroup’, 3001, ‘ftpuser’);

and your first user which is going to belong to this group:

mysql> INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`, `shell`, `count`, `accessed`, `modified`) VALUES (1, ‘firstuser’, PASSWORD(‘userpassword’), 3001, 3001, ‘/var/proftp/firstuser’, ‘/sbin/nologin’, 0, ”, ”);

In order to have your user able to copy and delete files to their home dir, you have to create /var/proftp and give proftpuser/proftpgroup write access to it:

# mkdir /var/proftp
# chown ftpuser:ftpgroup /var/proftp
# chmod 775 /var/proftp

Well, now, we still need to configure proFTPd to use our newly create mysql database as a backend.

4. Configure ProFTPd:

ProFTPd main configuration file is /etc/proftpd/proftpd.conf. On Debian, proFTPd over the capability of including files from the main configuration file. Thus, we are going to create a file call /etc/proftpd/mysql.conf which we will include from the main file.
This has the advantage that if the new config is not working, we can easily comment our include and fall back to the previous config.

So, get ready with your favorite text editor, edit /etc/proftpd/mysql.conf and put the following inside:

# Force the use of mysql backend
SQLBackend                      mysql

# The passwords in MySQL are using its own PASSWORD function
SQLAuthTypes                    Backend
SQLAuthenticate                 users* groups*

# details to connect to mysql db
# dbname@host dbuser dbpass
SQLConnectInfo     proftpddb@localhost proftpduser proftpdpassword

# Let proFTPd know the name of the columns in the user table
# Mind that this need to match the name in you table
SQLUserInfo        ftpuser userid passwd uid gid homedir shell

# Let proFTPd know the name of the columns in the group table
# we want it to interact with.

#Again the names match with those in the db
SQLGroupInfo                    ftpgroup groupname gid members

# proftpd will dynamicaly create if the homedir does not yet exist
SQLHomedirOnDemand              on

# update counter when a user logs in
SQLLog                          PASS updatecount
SQLNamedQuery     updatecount UPDATE "count=count+1, accessed=now()

			     WHERE userid='%u'" ftpuser

# change modified time anytime a user delete a file or upload one
SQLLog                          STOR,DELE modified
SQLNamedQuery                   modified UPDATE "modified=now()

			     WHERE userid='%u'" ftpuser

No, let’s modify proftpd.conf and add the following lines just after Include /etc/proftpd/modules.conf:

Include /etc/proftpd/mysql.conf
RootLogin off
RequireValidShell off

The include will include our customized file, RootLogin is to off to forbid root to use the ftp service and finally RequireValidShell is to off to allow our virtual users to log in. Remember that the shell of our ftp user is /bin/false.

Finally restart proftpd:

# /etc/init.d/proftpd restart

That’s it, you can now connect to your ftp server using user firstuser.

5. Tips

If you get the following error:

May 22 21:37:10 mydomain.com proftpd[9308] mydomain.com (WW.XX.YY.ZZ[WW.XX.YY.ZZ]): USER firstuser (Login failed): Invalid shell: ‘/sbin/nologin’

This is most probably because you forget to turn RequireValidShell to off.

Thanks to Author, orignal link http://www.debuntu.org/how-to-ftp-virtual-host-with-proftpd-mysql