Daily Archives: 23rd 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