Monthly Archives: February 2014

Pfsense behind a router

Ok so here’s my setup:
192.168.1.1 - current adsl / cable router
255.255.255.0 - router subnet

1. Install pfsense

2. Assign interfaces
1 for lan
1 for wan

Plug the WAN port into you ADSL / cable router
Plug the LAN port into eg. an un-managed switch.

3. Assign IPs
LAN IP - 192.168.2.1
Subnet - 255.255.255.0/24
Gateway - 192.168.2.1
Enable DHCP
IPRange - 192.168.2.50 to 192.168.2.100

WAN IP - 192.168.1.10 (static)
subnet 255.255.255.0/24
Gateway - 192.168.1.1

4. Configure web front end
Go to 192.168.2.1 (pfsense box) in your browser
Login using : admin pfsense
DNS1 : 8.8.8.8
DNS1 : 8.8.4.4
Allow router to override DNS
Allow 192/172/10 addresses (b/c pfsense is running on your lan)
(Click next, next, next)
Set the web gui password

5. Configure your router (cable/adsl modem)

Set a rule to route:
192.168.2.0 255.255.255.0 192.168.1.10

Go to the routers firewall rules and add this …
Ports : 1-65534
Protocol : ALL
Destination : 192.168.1.10

Or alternatively add the server to your DMZ

6. Load up your web browser and check you can get google.com

7. Enjoy … take a break and come back and install some ubercool packages.

Trouble shooting:
Can pfsense ping router – NO WAN config error
Can pfsense ping pfsense client – NO – LAN config error / Client firewall
Can pfsense client ping pfsense – NO – LAN config error / Client firewall
Can pfsense ping 8.8.8.8 – NO – ASDL/CABLE router config error
Can pfsense client ping router – NO – NAT error
Can pfsense client ping 8.8.8.8 – NO – NAT error / ADSL / CABLE config error
Can pfsense client ping 8.8.8.8 – YES – All good
Can pfsense client load a website – NO – DNS Error – Check everything above is OK
Can pfsense client load a website – YES – Everything is working

If you are having issue make sure you disable any firewalls you have running on the pfsense client boxes … once you have your network configured you can enable them later.

ENSURE YOUR FIREWALL RULES ARE CORRECT BEFORE CONTINUING TO SETUP THE REST OF THE NETWORK

SQL Tip : Maximum date via left join

Selecting records by their maximum date via a left join.

select * from maintable m

left join (

select id
, max(somedate) as mydate
from sometable
group by id
, mydate

) s on m.id = s.id

Whats going on?

1.) select * from maintable m
Select all the records from your base table.

2.) left join
Bring back all matching records

3.) select id, max(somedate) from sometable group by id, mydate
Select the id and date field you wish query the group by is needed to only bring back one record per id and because we are using an aggregate function the sql would error without it

4.) s on m.id = s.id
Here’s where the magic happens: select * from customersales left join (select id, max(lastsaledate) as dtmDate from customersales group by id, dtmDate) sales on sales.id = customersales.id voila you have all the most recent customer sales and a join away from all the customer information. You could also use an inner join to only bring back customers with sales and only their most recent sale.

eg.
select * from customersales
inner join (select id, max(lastsaledate) as dtmDate from customersales group by id, dtmDate) sales on sales.id = customersales.id
inner join customer c on c.id = customersales.customerid

Connecting to a serial device over usb in debian / linux

First of all you’ll need a USB to serial cable, mine came with a driver disk but I didn’t use it. I’m running debian jessie and it detected it without any issues. If you want to check to see the device is working and detected just run lsusb and look for your device in the list.

Look for something like this
Bus 003 Device 003: ID 063b:2837 Prolific Technology, Inc. PL2303 Serial Port

Next check the device is accessible run the following:
sudo ls /dev | grep USB

Look for something like this …
/dev/ttyUSB0

Next check you have screen installed by running
sudo apt-get install screen

Connect to the device over serial using :
sudo screen /dev/ttyUSB0

You should now be connected to your device.
This is what I used to configure a HP2626 Pro-Curve switch over serial.
When I first connected I had to hit enter a few times to establish a connection / shell.
Have fun hacking
Peace
Tea

New members to the blog

Good evening Hakology,

Its with great pleasure I can now introduce you to Jokke, AdvancedNewbie, Hexon and Wh1p are good friends of mine. Over the few years hakology has been running, these guys have helped me out and made a few really cool suggestions for the show. I asked them if they’d be interested posting for the blog, as we’re always discussing stuff on mumble or IRC. Ideas about coding concepts software / hardware and all the usual stuff. They all agreed and will hopefully be making a few submissions as and when they can.

AdvancedNewbie : Electrical boffin, inventor and always coding cool stuff for the web / automation / tools.
Jokke : Works full time developing opensource software and is a networking grandmaster 😀
Hexon : Full time linux admin, networking jedi & D20 qualified.
Wh1p : Cryptography enthusiast, network protocol / communication specialist.

Thanks you guys and welcome aboard.

Peace
MrTea

PS.
More surprises soon hehehe