Kibo world: October 2009

Thursday, October 29, 2009

Basic firewall on Linux server

As firewall I use netfilter. I show basic firewall rules for connecting server on Internet and sharing connecting wit other PCs in local network. In server usually run a lots of services but I want to public only some of them. PC in local network (LAN) use server for sharing Internet connection. Local PCs can send their packets through server (FORWARDING). Private IP addresses of local PCs are hide behind public IP address (MASQUERADE).

I want to write following rule:

For WAN (Internet) allow only Apache (http), Postfix (smtp), and ssh.
For LAN (local user) allow use server for sharing Internet connection.

Server has two interfaces.

eth0 with public IP connected to Internet
eth1 with private IP connected to local network (LAN)

For showing ip information I use command ifconfig.

# /sbin/ifconfig

eth0      Link encap:Ethernet  HWaddr 00:16:36:88:3E:CC
    inet addr:74.125.45.100  Bcast:74.125.45.255 Mask:255.255.255.0
    inet6 addr: fe80::216:36ff:fe88:3ecc/64 Scope:Link
    UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
    RX packets:47362 errors:0 dropped:0 overruns:0 frame:0
    TX packets:47094 errors:0 dropped:0 overruns:0 carrier:0
    collisions:0 txqueuelen:1000
    RX bytes:18821515 (17.9 MiB)  TX bytes:30314950 (28.9 MiB)
    Interrupt:74

eth1      Link encap:Ethernet  HWaddr 00:16:36:88:3E:CD
    inet addr:10.10.10.1  Bcast:10.10.10.255  Mask:255.255.255.0
    inet6 addr: fe80::216:36ff:fe88:3ecd/64 Scope:Link
    UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
    RX packets:23493 errors:0 dropped:0 overruns:0 frame:0
    TX packets:23511 errors:0 dropped:0 overruns:0 carrier:0
    collisions:0 txqueuelen:1000
    RX bytes:4045104 (3.8 MiB)  TX bytes:15666040 (14.9 MiB)
    Interrupt:82

lo        Link encap:Local Loopback
    inet addr:127.0.0.1  Mask:255.0.0.0
    inet6 addr: ::1/128 Scope:Host
    UP LOOPBACK RUNNING  MTU:16436  Metric:1
    RX packets:27071 errors:0 dropped:0 overruns:0 frame:0
    TX packets:27071 errors:0 dropped:0 overruns:0 carrier:0
    collisions:0 txqueuelen:0
    RX bytes:23502830 (22.4 MiB)  TX bytes:23502830 (22.4 MiB)

Now I can write following script.

#!/bin/sh
##firewall script
#chkconfig: 2345 01 99

#----------------------------------------------------------------------------#
#  Define variables
#----------------------------------------------------------------------------#

IPTABLES="/sbin/iptables"
LAN_IFACE="eth1"
WAN_IFACE="eth0"

#----------------------------------------------------------------------------#
#  Inicialization
#----------------------------------------------------------------------------#

#Flush all rules, delete all chains
$IPTABLES -F
$IPTABLES -X
$IPTABLES -t nat -F
$IPTABLES -t nat -X
$IPTABLES -t mangle -F
$IPTABLES -t mangle -X

#Zero out all counters
$IPTABLES -Z
$IPTABLES -t nat -Z
$IPTABLES -t mangle -Z

#----------------------------------------------------------------------------#
#  Set default policies
#----------------------------------------------------------------------------#

$IPTABLES -P INPUT DROP
$IPTABLES -P FORWARD DROP
$IPTABLES -P OUTPUT ACCEPT
$IPTABLES -t nat -P OUTPUT ACCEPT
$IPTABLES -t nat -P PREROUTING ACCEPT
$IPTABLES -t nat -P POSTROUTING ACCEPT
$IPTABLES -t mangle -P PREROUTING ACCEPT
$IPTABLES -t mangle -P POSTROUTING ACCEPT

#----------------------------------------------------------------------------#
#  INPUT
#----------------------------------------------------------------------------#

#these lines are necessary for the loopback interface and internal socket-based services to work correctly
$IPTABLES -A INPUT -i lo -j ACCEPT

#allow all from LAN
$IPTABLES -A INPUT -i $LAN_IFACE -j ACCEPT

#allow answer packets from Internet
$IPTABLES -A INPUT -i eth0 -m state --state ESTABLISHED,RELATED -j ACCEPT

#accept important ICMP packets
$IPTABLES -A INPUT -p icmp --icmp-type echo-request -j ACCEPT
$IPTABLES -A INPUT -p icmp --icmp-type time-exceeded -j ACCEPT
$IPTABLES -A INPUT -p icmp --icmp-type destination-unreachable -j ACCEPT

#allow services
$IPTABLES -A INPUT -p tcp --dport 22 --sport 1024:65535 -j ACCEPT # SSH
$IPTABLES -A INPUT -p tcp --dport 80 --sport 1024:65535 -j ACCEPT # HTTP
$IPTABLES -A INPUT -p tcp --dport 25 --sport 1024:65535 -j ACCEPT # SMTP

#----------------------------------------------------------------------------#
#  OUTPUT
#----------------------------------------------------------------------------#

# all allow

$IPTABLES -t nat -A POSTROUTING -o $WAN_IFACE -j MASQUERADE

#----------------------------------------------------------------------------#
#  FORWARD
#----------------------------------------------------------------------------#

$IPTABLES -A FORWARD -i $WAN_IFACE -o $LAN_IFACE -m state --state ESTABLISHED,RELATED -j ACCEPT
$IPTABLES -A FORWARD -i $LAN_IFACE -o $WAN_IFACE -m state --state NEW,ESTABLISHED,RELATED -j ACCEPT

#----------------------------------------------------------------------------#
#  End
#----------------------------------------------------------------------------#

# Save settings
/sbin/service iptables save

# List rules
$IPTABLES -L -v -n

For forwarding is neccessary set variable net.ipv4.ip_forward=1. The best way is find file /etc/sysctl.conf and rewrite the value net.ipv4.ip_forward=0 to net.ipv4.ip_forward=1. Then I must save the changes in file. I use command:

/sbin/sysctl -p

After run script in server and test firewall from PC in local network, You can test firewall from other PC from Internet:

# /usr/bin/nmap 74.125.45.100

When is all right You can adapt this script or add your specific rules.

Sunday, October 25, 2009

Mysql - join multiple rows

This week I need fill one table from data from another tables. It was necessary join multiple columns to one column and join multiple rows to one row too.

I use function CONCAT and GROUP_CONCAT in Mysql

I create table RESULT_TABLE for result data. The table contains any data. The table look like:


mysql> select * from RESULT_TABLE;
+---------+------------+-------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR | BOOK_SCOPE |
+---------+------------+-------------+------------+
|                  meanwhile EMPTY                |
+---------+------------+-------------+------------+

In DB I have following table:


mysql> select * from BOOK;
+---------+------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_SCOPE |
+---------+------------+------------+
|       1 | title 1    | law        |
|       2 | title 2    | law        |
|       3 | title 3    | math       |
+---------+------------+------------+


mysql> select * from AUTHOR;
+-----------+------------------+-----------------+
| AUTHOR_ID | AUTHOR_FIRSTNAME | AUTHOR_LASTNAME |
+-----------+------------------+-----------------+
|         1 | first1           | last1           |
|         2 | first2           | last2           |
|         3 | first3           | last3           |
|         4 | first4           | last4           |
|         5 | first5           | last5           |
+-----------+------------------+-----------------+


mysql> select * from BOOK_X_AUTHOR;
+---------+-----------+
| BOOK_ID | AUTHOR_ID |
+---------+-----------+
|       1 |         1 |
|       1 |         2 |
|       2 |         3 |
|       2 |         4 |
|       3 |         5 |
+---------+-----------+

In first step I join tables


mysql> select BOOK.BOOK_ID, BOOK.BOOK_TITLE, AUTHOR.AUTHOR_FIRSTNAME, AUTHOR.AUTHOR_LASTNAME, BOOK.BOOK_SCOPE from BOOK, AUTHOR, BOOK_X_AUTHOR
    -> where
    -> BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID and
    -> BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID;
+---------+------------+------------------+-----------------+------------+
| BOOK_ID | BOOK_TITLE | AUTHOR_FIRSTNAME | AUTHOR_LASTNAME | BOOK_SCOPE |
+---------+------------+------------------+-----------------+------------+
|       1 | title 1    | first1           | last1           | law        | 
|       1 | title 1    | first2           | last2           | law        | 
|       2 | title 2    | first3           | last3           | law        | 
|       2 | title 2    | first4           | last4           | law        | 
|       3 | title 3    | first5           | last5           | math       | 
+---------+------------+------------------+-----------------+------------+

In second step I use function concat and alias. Function concat return more columns as one column. With alias we can return another column name (BOOK_AUTHOR).


mysql> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME ) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
    -> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
    -> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
    -> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID;
+---------+------------+--------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR  | BOOK_SCOPE |
+---------+------------+--------------+------------+
|       1 | title 1    | first1 last1 | law        | 
|       1 | title 1    | first2 last2 | law        | 
|       2 | title 2    | first3 last3 | law        | 
|       2 | title 2    | first4 last4 | law        | 
|       3 | title 3    | first5 last5 | math       | 
+---------+------------+--------------+------------+

In third step I use function GROUP_CONCAT. You notice the clause GROUP BY at the end of select. I join multiple rows from table AUTHOR to one colum.


mysql> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, group_concat(concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME )) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
    -> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
    -> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
    -> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID
    -> group by BOOK_ID;
+---------+------------+---------------------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR               | BOOK_SCOPE |
+---------+------------+---------------------------+------------+
|       1 | title 1    | first2 last2,first1 last1 | law        | 
|       2 | title 2    | first4 last4,first3 last3 | law        | 
|       3 | title 3    | first5 last5              | math       | 
+---------+------------+---------------------------+------------+

I add SEPARATOR and clause ORDER BY to function GROUP_CONCAT


mysql> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, group_concat( concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME )
    -> ORDER BY AUTHOR.AUTHOR_ID
    -> SEPARATOR ", " ) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
    -> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
    -> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
    -> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID
    -> GROUP BY BOOK_ID;
+---------+------------+----------------------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR                | BOOK_SCOPE |
+---------+------------+----------------------------+------------+
|       1 | title 1    | first1 last1, first2 last2 | law        | 
|       2 | title 2    | first3 last3, first4 last4 | law        | 
|       3 | title 3    | first5 last5               | math       | 
+---------+------------+----------------------------+------------+

At the end I inserting result to RESULT_TABLE


mysql> insert into RESULT_TABLE (BOOK_ID, BOOK_TITLE, BOOK_AUTHOR, BOOK_SCOPE)
    -> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, group_concat( concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME )
    -> ORDER BY AUTHOR.AUTHOR_ID
    -> SEPARATOR ", " ) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
    -> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
    -> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
    -> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID
    -> GROUP BY BOOK_ID;


mysql> select *  from RESULT_TABLE;
+---------+------------+----------------------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR                | BOOK_SCOPE |
+---------+------------+----------------------------+------------+
|       1 | title 1    | first1 last1, first2 last2 | law        | 
|       2 | title 2    | first3 last3, first4 last4 | law        | 
|       3 | title 3    | first5 last5               | math       | 
+---------+------------+----------------------------+------------+