Wednesday, April 14, 2010

VPN client server configuration

In our company we need to allow certain members access to our computer network from the outside. Therefore, we decided to get a VPN.

We choose OpenVPN, because it is Open Source and it is available for Linux, Windows and another OS.

You can let running VPN server on Linux and let blunt Windows client to connect to server.

For server-client configuration you need to generate keys and certificates, but configuration is very simple.


Server configuration

/etc/openvpn/server.conf


port 1194
proto tcp
dev tun
keepalive 10 60
comp-lzo

server 10.10.1.0 255.255.255.0
ifconfig-pool-persist /etc/openvpn/ipp.txt
max-clients 3

push "route 172.16.128.0 255.255.255.0"
push "dhcp-option DNS 172.16.128.1"

ca /etc/openvpn/keys/ca.crt
cert /etc/openvpn/keys/akela.crt
key /etc/openvpn/keys/akela.key
dh /etc/openvpn/keys/dh1024.pem
tls-auth /etc/openvpn/keys/static.key

log-append /var/log/openvpn.log
status /var/log/openvpn-status.log
mute 10
verb 5

All important you find out in man pages or on project documentation.

push "dhcp-option DNS 172.16.128.1" get only Windows client. For Linux client you need script. See HOWTO

Generate certificates

You can use OpenSSL, but developers of OpenVPN prepare scripts for simply generate keys and certificates for server and clients.

You find out it: /user/share/doc/openvpn/examples/easy-rsa/2.0/

Scripts config file is vars. There you can set same variables.

1. We load the config file to system, and create self-signed Certificate Authority


. ./vars
./clean-all
./build-ca

In folder keys (depend on your vars files) you find out file:

  • ca.pem - public certificate
  • ca.key - private key of Certificate Authority

2. we create key and certificate for server.


./build-key-server akela

It creates file:

  • akela.pem - certificate of your server signed of your CA
  • akela.ctr - request of sign (you don´t need that file)
  • akela.key - private key of your server

3. we create Deffie-Hellmann parameters for dynamic key encryption


./build-df

It creates file:

  • dh1024.pem

4. we create static TLS-AUTH key


openvpn --genkey --secret static.key

It creates file:

  • static.key

Client configuration

We need to create certificate for your client.

In your server use:


./build-key kibo
  • kibo.pem - certificate of your client(notebook) signed of your CA
  • kibo.ctr - request of sign (you don´t need that file)
  • kibo.key - private key of your client(notebook)

In your client(notebook) save this file:

  • kibo.pem
  • kibo.key
  • ca.pem
  • static.key

Clinet configuration on /etc/openvpn/client.conf in Linux client.

Clinet configuration on ..\ProgramFiles\OpenVPN\Config\client.ovpn in Windows client.


client
pull
dev tun
proto tcp
remote ourServer.tld

ca /etc/openvpn/keys/ca.crt
cert /etc/openvpn/keys/kibo.crt
key /etc/openvpn/keys/kibo.key
tls-auth /etc/openvpn/keys/static.key

comp-lzo
verb 4
mute 10
ns-cert-type server

Start server and then try to connect from client.

  • openvpn /etc/openvpn/server.conf - on Linux server (or as service)
  • openvpn /etc/openvpn/client.conf - on Linux client(notebook)

OpenVPN and IPTables


$IPTABLES -A INPUT -p tcp --dport 1194 -j ACCEPT # VPN new
$IPTABLES -A INPUT -i tun+ -j ACCEPT # VPN tun
$IPTABLES -A FORWARD -i tun+ -o $LAN_IFACE -j ACCEPT # VPN forward >
$IPTABLES -A FORWARD -i $LAN_IFACE -o tun+ -j ACCEPT # VPN forward <

Good Luck.

Tuesday, April 6, 2010

www.harvart.cz with Grails

For us last web project www.harvart.cz we used Grails framework.
Earlier we used Spring MVC with Spring Roo for all us projects.
We are really excited to work with Grails framework.
Let mi introduce same of features in Grails:

Rapid development

We don´t often restart Tomcat. After changes in controller classes, gsp pages or config classes is project automatically reloaded. When you add or change same bean in Spring context, it automatically reload too. It is very comfortable behaviour.

URL mapping

For URL mapping Grails use simple mapping file:
"/clanky"(controller:'article', action:'all')
"/clanky/$url"(controller:'article', action:'show')
"/klub"(controller:'klub', action:'about')
"/fotogalerie"(controller:'gallery', action:'all')
"/fotogalerie/$url"(controller:'gallery', action:'show')  
You only determine which controller and action is conjunction with url. When you use Grails link tag then the url is automatically compile in all your HTML links. It is grait!

GORM

Every domain class has automatically all GRUD methods. You don´t need to write boring DAO classes.

Plugins

There are a lot of useful plugins. You only install same plugin and you can use it immediately. In www.harvart.cz we used search plugin. We only marked domain class, which can be search. It is unbelievably simply!

Conclusion

Grails is great! As developer you can concentrate to write your own code only. Groovy give you all you need as web developer. You can use all for Spring, Hibernate and SiteMesh.

Saturday, April 3, 2010

MYSQL - JOIN conjunction

Where you have two and more tables, you can made conjunction between table.

You can use this kind of conjunction:

  • INNER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL JOIN
  • CROSS JOIN

Source table


mysql> select* from EMPLOYEE;
+----+--------+---------------+
| ID | NAME | ID_DEPARTMENT |
+----+--------+---------------+
| 1 | Tom | 1 |
| 2 | Petr | 1 |
| 3 | Jan | 2 |
| 4 | Michal | 2 |
| 5 | Pepa | 3 |
| 6 | Zdenek | 0 |
+----+--------+---------------+

mysql> select* from DEPARTMENT;
+----+----------+
| ID | TITLE |
+----+----------+
| 1 | economy |
| 2 | it |
| 3 | research |
| 4 | sales |
+----+----------+

INNER JOIN

Return only corresponding records. You use inner join or join or empty clause.

This selects are the same:


select * from EMPLOYEE inner join DEPARTMENT on EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID

select * from EMPLOYEE join DEPARTMENT on EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID

select * from EMPLOYEE, DEPARTMENT where EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID

+----+--------+---------------+----+----------+
| ID | NAME | ID_DEPARTMENT | ID | TITLE |
+----+--------+---------------+----+----------+
| 1 | Tom | 1 | 1 | economy |
| 2 | Petr | 1 | 1 | economy |
| 3 | Jan | 2 | 2 | it |
| 4 | Michal | 2 | 2 | it |
| 5 | Pepa | 3 | 3 | research |
+----+--------+---------------+----+----------+

LEFT OUTER JOIN

Return all records from left table. You can use left outer join or
left join clause only.


mysql> SELECT *
-> FROM EMPLOYEE
-> LEFT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID;
+----+--------+---------------+------+----------+
| ID | NAME | ID_DEPARTMENT | ID | TITLE |
+----+--------+---------------+------+----------+
| 1 | Tom | 1 | 1 | economy |
| 2 | Petr | 1 | 1 | economy |
| 3 | Jan | 2 | 2 | it |
| 4 | Michal | 2 | 2 | it |
| 5 | Pepa | 3 | 3 | research |
| 6 | Zdenek | 0 | NULL | NULL |
+----+--------+---------------+------+----------+

RIGHT OUTER JOIN

Return all records from right table. You can use right outer join or
right join clause only.


mysql> SELECT *
-> FROM EMPLOYEE
-> RIGHT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID;
+------+--------+---------------+----+----------+
| ID | NAME | ID_DEPARTMENT | ID | TITLE |
+------+--------+---------------+----+----------+
| 1 | Tom | 1 | 1 | economy |
| 2 | Petr | 1 | 1 | economy |
| 3 | Jan | 2 | 2 | it |
| 4 | Michal | 2 | 2 | it |
| 5 | Pepa | 3 | 3 | research |
| NULL | NULL | NULL | 4 | sales |
+------+--------+---------------+----+----------+

CROSS JOIN

Create cartesian product, You can use cross join or empty.

This selects are the same:


SELECT * FROM EMPLOYEE CROSS JOIN DEPARTMENT

SELECT * FROM EMPLOYEE, DEPARTMENT

mysql> SELECT *
-> FROM EMPLOYEE
-> CROSS JOIN DEPARTMENT;
+----+--------+---------------+----+----------+
| ID | NAME | ID_DEPARTMENT | ID | TITLE |
+----+--------+---------------+----+----------+
| 1 | Tom | 1 | 1 | economy |
| 1 | Tom | 1 | 2 | it |
| 1 | Tom | 1 | 3 | research |
| 1 | Tom | 1 | 4 | sales |
| 2 | Petr | 1 | 1 | economy |
| 2 | Petr | 1 | 2 | it |
| 2 | Petr | 1 | 3 | research |
| 2 | Petr | 1 | 4 | sales |
| 3 | Jan | 2 | 1 | economy |
| 3 | Jan | 2 | 2 | it |
| 3 | Jan | 2 | 3 | research |
| 3 | Jan | 2 | 4 | sales |
| 4 | Michal | 2 | 1 | economy |
| 4 | Michal | 2 | 2 | it |
| 4 | Michal | 2 | 3 | research |
| 4 | Michal | 2 | 4 | sales |
| 5 | Pepa | 3 | 1 | economy |
| 5 | Pepa | 3 | 2 | it |
| 5 | Pepa | 3 | 3 | research |
| 5 | Pepa | 3 | 4 | sales |
| 6 | Zdenek | 0 | 1 | economy |
| 6 | Zdenek | 0 | 2 | it |
| 6 | Zdenek | 0 | 3 | research |
| 6 | Zdenek | 0 | 4 | sales |
+----+--------+---------------+----+----------+

FULL JOIN

You need to use two JOINs and UNION. More example in article How to simulate FULL OUTER JOIN in MySQL


mysql> SELECT *
-> FROM EMPLOYEE
-> LEFT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID
-> UNION
-> SELECT *
-> FROM EMPLOYEE
-> RIGHT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID;
+------+--------+---------------+------+----------+
| ID | NAME | ID_DEPARTMENT | ID | TITLE |
+------+--------+---------------+------+----------+
| 1 | Tom | 1 | 1 | economy |
| 2 | Petr | 1 | 1 | economy |
| 3 | Jan | 2 | 2 | it |
| 4 | Michal | 2 | 2 | it |
| 5 | Pepa | 3 | 3 | research |
| 6 | Zdenek | 0 | NULL | NULL |
| NULL | NULL | NULL | 4 | sales |
+------+--------+---------------+------+----------+

COALESCE


In most of DB engine you can find COALESCE function. It is helpful where you need to work with NULL values.

source table


mysql> select * from INFO;
+----+-------------+--------------+
| ID | GALLERY | PHOTOGRAPHER |
+----+-------------+--------------+
| 1 | Cars | Tom |
| 2 | Countryside | Petr |
| 3 | City | Jan |
| 4 | Sport | NULL |
+----+-------------+--------------+

When you need to return not NULL report, you can use COALESCE

COALESCE function


mysql> select GALLERY, coalesce(PHOTOGRAPHER,"unknown") as PHOTOGRAPHER from coalesce;
+-------------+--------------+
| GALLERY | PHOTOGRAPHER |
+-------------+--------------+
| Cars | Tom |
| Countryside | Petr |
| City | Jan |
| Sport | unknown |
+-------------+--------------+