Showing posts with label server. Show all posts
Showing posts with label server. Show all posts

Nov 29, 2012

Setting up a virtual guest on a headless CentOS 6 host

This guide assumes you have at least followed my guides for setting up the host (either my 6.2 or 6.3 version) and have set up the bridge networking interface. You optionally can see my other posts such as auditing your software installs, hardening your accounts, network hardening, services hardening and clearing out orphaned packages.

Note that if you followed my guide for services hardening you may want to turn the messagebus daemon back on. If the avahi daemon and zeroconf is disabled, you will need to edit /etc/libvirt/libvirtd.conf with the following:
mdns_adv=0
The rest of the guide should apply to virtually everyone else:

  1. Edit /etc/libvirt/qemu.conf to allow the VNC server to listen on all ports:
    vnc_listen='0.0.0.0'
  2. Restart the libvirt daemon:
    service libvirtd restart
  3. If you don't already have one create the LVM partition that we will be our VM's hard-disk:
    lvcreate -L20G -n lv_vm1 VolGroup
  4. Poke a hole in the firewall so we can connect via VNC to the server. You can choose any port you wish, but in this case we will be using port 7601. Make sure you change the network to match your own settings! Edit /etc/sysconfig/iptables
    -A INPUT -m --state NEW -s 192.168.0.0/24 -m tcp -p tcp --dport 7601 -j ACCEPT

  5. Restart the firewall:
    service iptables restart
  6. Run the installation command:
    virt-install -n vm1 -r 512 --vcpus=2 --disk path=/dev/VolGroup/lv_vm1 -c /path/to/disk.iso -v --accelerate -w bridge:br0 --vnc --vncport=7601 --noautoconsole --os-type linux --osvariant rhel6
  7. Now use a VNC client to connect to your server by connecting to the firewall hole we created earlier. Follow through with the rest of the installation process.
  8. To start and stop your VM just use the virsh command. The VM has been configured to use port 7601 for VNC, so you can always connect to it using that port unless you close it.
    virsh start vm1 

Further reading

Nov 23, 2012

Setting up a CentOS 6 server: Services Hardening

This is a follow on post from my guide to installing CentOS 6.2 (or you can read my updated 6.3 version). You can see my other posts such as auditing your software installs, hardening your accounts, network hardening and clearing out orphaned packages.

This guide outlines how to cut down on unnecessary services so that you have a lean and mean machine.


  1. List all the services running on your machine with the following command:
    chkconfig --list | grep :on
     
  2.  Go through the list and select packages to disable or remove. For instance:
    chkconfig mdmonitor off
    chkconfig smartd off
    chkconfig messagebus off
    chkconfig haldaemon off
    chkconfig cups off
    chkconfig atd off
    chkconfig kdump off
  3. If you do not know what a service is or does, just run:
    rpm -qf /etc/init.d/<service_name>

    Then run:
    rpm -qi <rpm>

References

Nov 22, 2012

Setting up a CentOS 6 server: Network Hardening

This is a follow on post from my guide to installing CentOS 6.2 (or you can read my updated 6.3 version). You can see my other posts such as auditing your software installs, hardening your accounts, and clearing out orphaned packages.

This post will focus on hardening your networking infrastructure.
  1. Disable wireless networking in the kernel by running the following loop:
  2. for i in $(find /lib/modules/`uname -r`/kernel/drivers/net/wireless -name "*.ko" -type f ) ; do
    echo blacklist $i >> /etc/modprobe.d/blacklist-wireless ; done
  3. OPTIONAL: I also disabled the loading of bluetooth drivers by modifying the command loop. I replaced 'net/wireless' with 'bluetooth' and save it under a different filename.
  4. Edit /etc/sysctl.conf to secure the network within the kernel.
    # Disables packet forwarding
    net.ipv4.ip_forward = 0

    # Source route verification
    net.ipv4.conf.all.rp_file = 1
    net.ipv4.conf.default.rp_file = 1

    # Don't accept source routing
    net.ipv4.conf.all.accept_source_route = 0
    net.ipv4.conf.default.accept_source_route = 0

    # Not a router, so do not send redirects
    net.ipv4.conf.all.send_redirects = 0
    net.ipv4.conf.default.send_redirects = 0

    # Not a router, so do not accept redirects
    net.ipv4.conf.all.accept_redirects = 0
    net.ipv4.conf.default.accept_redirects = 0
    net.ipv4.conf.all.secure_redirects = 0
    net.ipv4.conf.default.secure_redirects = 0

    # Log all packets with impossible addresses to the kernel log
    net.ipv4.conf.all.log_martians = 1

    # Ignore all ICMP ECHO and TIMESTAMP requests sent via broadcast/multicast
    # And protect against ICMP attacks
    net.ipv4.icmp_echo_ignore_broadcasts = 1
    net.ipv4.icmp_ignore_bogus_error_messages = 1

    # Protect against SYN flood attacks, and controls the use of SYN cookies
    net.ipv4.tcp_syncookies = 1
    net.ipv4.tcp_synack_retries = 2

    # This is not  a router so don't accept IPv6 solicitations
    net.ipv6.conf.all.router_solicitations = 0
    net.ipv6.conf.default.router_solicitations = 0

    # Do not accept IPv6 preferences from the router
    net.ipv6.conf.all.accept_ra_rtr_pref = 0
    net.ipv6.conf.default.accept_ra_rtr_pref = 0

    # Do not accept IPv6 prefix information from the router
    net.ipv6.conf.all.accept_ra_pinfo = 0
    net.ipv6.conf.default.accept_ra_pinfo = 0

    # Do not accept Hop Limit settings from router
    net.ipv6.conf.all.accept_ra_defrtr = 0
    net.ipv6.conf.default.accept_ra_defrtr = 0

    # Do not accept configuration from router
    net.ipv6.conf.all.autoconf = 0
    net.ipv6.conf.default.autoconf = 0

    # Not a router so don't sent IPv6 solicitations
    net.ipv6.conf.all.dad_transmits = 0
    net.ipv6.conf.default.dad_transmits = 0

    #Assign only one address per interface
    net.ipv6.conf.all.max_addresses = 1
    net.ipv6.conf.default.max_addresses = 1
  5. OPTIONAL: While we are in /etc/sysctl.conf we may as well add a few hardening parameters for the kernel:
    # Controls System Request Debugging
    kernel.sysrq = 0

    # Append PID to core filename in a core dump (useful to determine what happened)
    kernel.core_users_pid = 1

    # Activate ExecShield
    kernel.exec-shield = 1
    kernel.randomize_va_space = 1
  6. OPTIONAL: If you are going to use bridge interfaces then disable packet filtering. This way we will use the Virtual Machine's firewall rules instead of defining complex rules on the host.
    net.bridge.bridge_nf_call_ip6tables = 0
    net.bridge.bridge_nf_call_iptables = 0
    net.bridge.bridge_nf_call_arptables = 0
  7. Disable automatic loading of IPv6 in the kernel by editing /etc/modprobe.d/dist.conf with:
    install ipv6 /bin/true
    While we are here, we will also disable the loading of uncommon networking protocols:
    install dccp /bin/true
    install sctp /bin/true
    install rds /bin/true
    install tipc /bin/true
  8. Disable IPv6 interfaces by modifying /etc/sysconfig/network:
    NETWORKING_IPV6=no
    IPV6INIT=no
    IPV6_AUTOCONF=no
    You can also turn off avahi and zeroconf by adding the line:
    NOZEROCONF=yes
    (NOTE: If you are not going to use zeroconf you may as well uninstall it with yum remove avahi avahi-autoipd. The avahi-libs package is required by other programs so you may still need it)
  9. Add the following line to every file that matches the pattern /etc/sysconfig/network-scripts/ifcfg-* with:
    IPV6INIT=no
  10. Deny all TCP Wrapper services by default. Edit /etc/hosts.deny and enter the following as the only entry:
    ALL: ALL
  11. OPTIONAL: If you wish, only allow TCP Wrapper services (like SSH) to run on the localhost loopback interface. Edit /etc/hosts.allow and enter the following:
    ALL: localhost
  12. Edit IP tables (the firewall) to automatically drop packets that do not match a given rule. Edit the files /etc/sysconfig/iptables & /etc/sysconfig/ip6tables
    *filter
    :INPUT DROP [0:0]
    :FORWARD DROP [0:0]
  13. Restrict ICMP messages by removing any lines in /etc/sysconfig/iptables containing the following:
    -p icmp
    and replace it with:
    -A INPUT -p icmp --icmp-type echo-reply -j ACCEPT
    -A INPUT -p icmp --icmp-type destination-unreachable -j ACCEPT
    -A INPUT -p icmp --icmp-type time-exceeded -j ACCEPT
  14. To log all dropped packets in the system replace the following line in /etc/sysconfig/iptables:
    -A INPUT -j REJECT --reject-with icmp-host-prohibited-A FORWARD -j REJECT --reject-with icmp-host-prohibited
    with:
    -A INPUT -j LOG
    -A INPUT -j DROP
    -A FORWARD-j LOG
    -A FORWARD -j DROP
    You will need to write the same in the equivalent IPv6 file (in /etc/sysconfig/ip6tables)
  15. You may have NFS installed; if you don't need it then uninstall it:

    yum remove portmap nfs-utils

    NOTE: If you are running virtual machines then it will need the libraries provided by portmap. Instead turn off the services:
    chkconfig portreserve off
    chkconfig rpcgssd off
    chkconfig rpcidmapd off
    chkconfig rpcbind off
    chkconfig rpcsvcgssd off
    chkconfig nfs off
    chkconfig nfslock off
  16. Finally, to check what is running on your server:

    • This will show all services:
      netstat -tulp
    • This will show only services with active connection
      netstat -ant
    • This will show you the routing table
      route
    • This will show you if any program is actively pulling raw packets, and is a sign that there is a network sniffer. Note that on a fresh system that a positive result may just be the DHCP client (if you use one).

      cat /proc/net/packet

References

Nov 18, 2012

Setting up a CentOS 6.2 web server: Accounts Hardening

This is a follow on post from my guide to installing CentOS 6.2 and auditing your software installs. We will go through some of the steps required to secure your server and get it ready for production use.

These steps will outline how to harden your user accounts to lessen the risk that they will be compromise (and limit the damage able to be done if they are compromised).
We will assumes you have already created a new user account; if you haven't, just run the following command:

adduser -m -U USERNAME
passwd USERNAME

Now let's lock down our accounts!
  1. Let's restrict the root access to the system console only. Edit /etc/securetty and remove everything except for the following:
    console
    tty1
    tty2
    ...
    tty10
    tty11
  2. Uncomment the following line in /etc/pam.d/su

    auth required pam_wheel.so use_uid

  3. Uncomment the following line in /etc/sudoers

    %wheel ALL=(ALL) ALL
  4. Add your new administrator user to the wheel group
    usermod -G wheel USERNAME
  5. Now we will lock non-root system accounts and block shell access. Figure out the list of accounts by running the following (it will print a list of accounts with the associated UID):
    awk -F: '{print $1 ":" $3 ":" $7}' /etc/passwd
  6. Run the following commands on any non-root account with a UID less than 500:
    usermod -L account
    usermod -s /sbin/nologin account
     
  7. For reference, this is a list of system accounts generally created on a fresh install:
    bin
    daemon
    adm
    lp
    sync
    shutdown
    halt
    mail
    uucp
    operator
    games
    gopher
    ftp
    nobody
    dbus
    rpc
    abrt
    vcsa
    haldaemon
    saslauth
    postfix
    rpcuser
    nfsnobody
    ntp
    qemu
    radvd
    sshd
    tcpdump
    oprofile
    avahi
    rtkit
    pulse
    avahi-autoipd
    mysql
  8. Ensure passwords expire by editing /etc/login.defs
    PASS_MAX_DAYS 360
    PASS_MIN_DAYS 14

    PASS_MIN_LENGTH 8
    PASS_WARN_AGE 32
    For any accounts that have already been created, run the following to enforce the new rules:

    chage -M 360 -m 14 -W 7 admin

References

Nov 15, 2012

Setting up a CentOS 6.3 Virtual Host

This guide outlines how I set-up my virtual host using CentOS 6.3 (a free release version of Red Hat Linux with all the branding removed). It's partially based on my previous installation guide. I also used my guide to create a bootable USB installation disk, but this guide should work equally well with the standard DVD install.

Installation

  1. Boot up your installation media (you may need to edit your BIOS to do so)
  2. Select 'Install or upgrade an existing system' from the menu
  3. Select your language and keyboard layout.
  4. Click next at the splash screen.
  5. Choose the 'Basic Storage Device' option
  6. Select the 'Fresh Installation' option
  7. Enter in the host-name of your new server (for best results you should append your domain name to the end so it works seamlessly with SSL certificates) i.e. testserver.example.com

    If you want to configure a static IP address click on the 'Configure Network' button, select the your network card (probably eth0) and enter away.

    If you are going to use DHCP, or just don't know, just hit 'Next'
  8. Select the correct timezone for you (just click a location on the map and it should select the closest one to you).
  9. Enter in an appropriate root password. Make as long and complex as possible (long sentences with mixed character types are easier to remember than jibberish strings; for instance 'My office is situated in 1234 fake street, Fakeville!')
  10. In this example we are going to go for a custom partition layout, so select 'Create Custom Layout'. If you are fine with defaults, just skip to part .
  11. Delete all existing partitions and do the following:
    • A /boot partition of about 100MB. Use the ext4 format

    • Create a LVM Physical Volume that fills up the rest of the hard-drive

    • Create a LVM Volume Group with a Physical Extent of 4MB.

    • Create LVM Logical Volumes on the Volume group as follows (make sure you leave some free space for your Virtual machines!!):

      • Swap space that is at least equal to how much RAM is in your server
      • /tmp/ should be as big as the largest file you will be manipulating (for instance, if you are copying a DVD you will need at least 4GB)
      •  /var/log and /var/log/audit are separated so that if your log system goes haywire it does not kill the space for other applications. Dedicate a couple of gigabytes to each.
      • /home/ and /usr/ should be a few gigabytes each. /usr/ just holds your applications and should remain pretty static, while /home/ is where you will store your personal files.
      • /var/ and /var/www/ will contain the majority of space on your system. MySQL stores your database files in /var/lib/mysql/, while Apache runs from /var/www/. Dedicate adequate space to each folder.
      • Your root folder (/) will only need a few GB of space. It will mainly hold configuration files.
  12. The system will take some time to format your hard-drive. Once it is complete it will ask you to install the boot-loader. While the defaults are suitable, for extra security you should consider password protecting your boot-loader.
  13. We can now select our packages. You can customize the system to suit your needs, but for the basics just select 'Basic Server' from the menu and the 'Customize now' from the radio buttons. Hit 'Next'.
  14. Do the following edits:
    • Remove the 'Java Platform' and 'Directory Client' meta-packages
    • Add all of the Virtualization meta-packages (including client, platform and tools)
    • Because the virt-manager tool requires a GUI, you may need to install the 'X Windows System'  and the 'KDE Desktop'
    • From the base system, I removed packages such as hunspell and word (as well as hardware tools like RAID that I was not using)
  15. Reboot your system!

 House cleaning

I suggest you take this time to tighten up your machine; run updates, turn off services, install software and harden your machine. You should also consider setting up your SSH settings.

Have a look at some of my guide to Software package integrity checks (aide).


Creating our first guest

We are going to use LVM based guests, so if you haven't left any space on your LVM partition I suggest you use these guides to free up some space. If you have partitions you don't think you need anymore, just delete it.

You may also want to ensure that KVM is installed so that you get the benefit from it's kernel and hardware virtualisation.

yum install kvm qemu-kvm qemu-kvm-tools

Now you just need to create a logical partition in the volume to store your VM by running the following command (assuming your volume is call VolGroup):

lvcreate -L20G -n vm1 VolGroup

To install to this new partition, just run the following command:

virt-install --connect qemu:///system -n vm1 -r 512 --vcpus=2 --disk path=/dev/VolGroup/lv_vm1 -c /path/to/installation.iso --graphics vnc --noautoconsole --os-type linux --os-variant rhel6

Note the following parameters:
  • -r specifies the RAM size
  • --vcpus specifies the virtual CPU's to use
  • --os-type helps to optimise the VM by specifying an operating system
  • --os-variant is a optional parameter, but helps further optimisation of the emulator.

Further reading

Oct 18, 2012

PERL Script with command line options

It's been a long time since I have done a tutorial about PERL scripting, so I cracked my fingers and got down to hacking some code to create a basic command line script with options.

To give you an idea, I want to be able to run a command and pass some optional flags to it. For instance:

perl-script -h -username nassar

It turns out that this is a very simple thing to do because PERL has an inbuilt getopts function.

#!/usr/bin/perl -w
#
# This is a perl script that uses warnings (hence the -w flag)
#
# This perl scripts is a test of the getopts function

# Use strict perl
use strict;

# Use the Getopt library
use Getopt::Std;

# Creates a hash variable where we can store our command line options
my %options=();

# Grab the list of command line options (some with optional arguments,
#   denoted by a ':')
#   -u {username}   : Sets the username
#   -p {password}   : Sets the password
#   -H {hostname}   : Sets the hostname
#   -D {database}   : Sets the database
#   -h              : Displays the help
#   -d              : Debugging
getopts("hdu:p:H:D:", \%options);

# Sets some variables based on command line options, otherwise use defaults
my $username = (defined $options{u}) ? $options{u} : 'test';
my $password = (defined $options{p}) ? $options{p} : 'test';
my $hostname = (defined $options{H}) ? $options{H} : 'localhost';
my $database = (defined $options{D}) ? $options{D} : 'testdb';

# Output the display menu if asked for
if (defined $options{h}) {
    print "The '-h' flag was invoked\n\n";
}

# Output the display menu if asked for
if (defined $options{d}) {
    print "The '-d' flag was invoked\n\n";
}

print "USERNAME: " .$username . "\nPASSWORD: " . $password . "\nHOSTNAME: " . $hostname . "\nDATABASE: " . $database . "\n\n";

exit 0;

 Make sure the script is set to be executable and you will get output similar to the following:

nassar@computer:~$ ./test.pl -u test -d -h
The '-h' flag was invoked

The '-d' flag was invoked

USERNAME: test
PASSWORD: testslair
HOSTNAME: localhost
DATABASE: testslairdb

nassar@
computer:~$

References:

Aug 30, 2012

CodeIgniter: Dynamically setting the website's language

I have been writing up my own View subsystem for CodeIgniter that seperates the  View more clearly from the controller. I have also designed it with Language support in mind, but for that I had to do some minor edits to the default Language class.

Even though you can load a specific language file in another language, the system will load error messages in the default language. This led to some cases where system errors would be in English, but the site language was something else.

With my new system I can have my default site language can be dynamically set by a user cookie… see the following code:

$lang_temp = $this->input->cookie('language');
if ( $lang_temp )
{
     // NOTE: getDefault() is one of my core Lang.php modifications
     if (!($lang_temp === $this->lang->getDefault()))
     {
        // NOTE: setDefault() is one of my core Lang.php modifications
        $this->lang->setDefault($lang_temp);
     }
} else {
     // Set a cookie with system default
     $this->input->set_cookie('language', $this->lang->getDefault(), '7200', '.' . $_SERVER['HTTP_HOST'], '/', NULL, FALSE);
}
 
But to make the above code work I had to make some changes to the core:



<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
* CodeIgniter
*
* An open source application development framework for PHP 5.1.6 or newer
*
* @package CodeIgniter
* @author ExpressionEngine Dev Team
* @copyright Copyright (c) 2008 - 2011, EllisLab, Inc.
* @license http://codeigniter.com/user_guide/license.html
* @link http://codeigniter.com
* @since Version 1.0
* @filesource
*/

// ------------------------------------------------------------------------

/**
* Language Class
*
* @package CodeIgniter
* @subpackage Libraries
* @category Language
* @author ExpressionEngine Dev Team
* @link http://codeigniter.com/user_guide/libraries/language.html
*/
class CI_Lang {

/**
* List of translations
*
* @var array
*/
var $language = array();
/**
* List of loaded language files
*
* @var array
*/
var $is_loaded = array();

/**
* Default language
*
* @var array
*/
var $default_lang;

/**
* Constructor
*
* @access public
*/
function __construct()
{
    $config =& get_config();
    $this->default_lang = ( ! isset($config['language'])) ? 'en' : $config['language'];

    log_message('debug', "Language Class Initialized");
}

// --------------------------------------------------------------------

/**
* Load a language file
*
* @access public
* @param mixed the name of the language file to be loaded. Can be an array
* @param string the language (english, etc.)
* @param bool return loaded array of translations
* @param bool add suffix to $langfile
* @param string alternative path to look for language file
* @return mixed
*/
function load($langfile = '', $idiom = '', $return = FALSE, $add_suffix = TRUE, $alt_path = '')
{
    $langfile = str_replace('.php', '', $langfile);

    if ($add_suffix == TRUE)
    {
       $langfile = str_replace('_lang.', '', $langfile).'_lang';
    }

    $langfile .= '.php';

    if (in_array($langfile, $this->is_loaded, TRUE))
    {
       return;
    }

    if ($idiom == '')
    {
       $idiom = ($this->default_lang == '') ? 'en' : $this->default_lang;
    }

    // Determine where the language file is and load it
    if ($alt_path != '' && file_exists($alt_path.'language/'.$idiom.'/'.$langfile))
    {
       include($alt_path.'language/'.$idiom.'/'.$langfile);
    }
    else
    {
       $found = FALSE;

       foreach (get_instance()->load->get_package_paths(TRUE) as $package_path)
       {
          if (file_exists($package_path.'language/'.$idiom.'/'.$langfile))
          {
              include($package_path.'language/'.$idiom.'/'.$langfile);
              $found = TRUE;
              break;
          }
      }

      if ($found !== TRUE)
      {
         show_error('Unable to load the requested language file: language/'.$idiom.'/'.$langfile);
      }
   }


    if ( ! isset($lang))
   {
      log_message('error', 'Language file contains no data: language/'.$idiom.'/'.$langfile);
      return;
   }

   if ($return == TRUE)
   {
      return $lang;
   }

   $this->is_loaded[] = $langfile;
   $this->language = array_merge($this->language, $lang);
   unset($lang);

   log_message('debug', 'Language file loaded: language/'.$idiom.'/'.$langfile);
   return TRUE;
}

// --------------------------------------------------------------------

/**
* Fetch a single line of text from the language array
*
* @access public
* @param string $line the language line
* @return string
*/
function line($line = '')
{
   $value = ($line == '' OR ! isset($this->language[$line])) ? FALSE : $this->language[$line];

   // Because killer robots like unicorns!
   if ($value === FALSE)
   {
      log_message('error', 'Could not find the language line "'.$line.'"');
   }

   return $value;
}

/**
* Fetch Default language
*
* @access public
* @param void
* @return string Default language code
*/
function getDefault()
{
   return $this->default_lang;
}

/**
* Set Default language
*
* @access public
* @param string The default language
* @return void
*/
function setDefault($language)
{
   $this->default_lang = $language;
}

}
// END Language Class

/* End of file Lang.php */
/* Location: ./system/core/Lang.php */

I am sure you can find other uses for this code.....

My other posts on CodeIgniter include:

Aug 29, 2012

PostgreSQL: Improving performance

This a continuation of my PostgreSQL series (an installation guide for Ubuntu, useful functions and operators, an overview of some basic concepts and some more advanced ones). Here I will briefly outline some functions available on the PostgreSQL database.

You can also check out my notes on designing relational databases.

  • Although PostgreSQL does a good job of maintenance of tuning. However, it is useful to analyses the systems real-time query use of indexes. There are plenty of tools available to profile your server, but the usefulness depends on the requirements of your particular system.

    NOTE: Always performing tuning on real data. Test data will just tell you what will be optimal for the test data only.

  • EXPLAIN is a tool that shows the execution plan of a statement and the associated costs (measured in disk page fetches). You can add the options ANALYZE (actually executes the statement; no longer uses estimates), and VERBOSE (display additional information).

    A good explanation on how to use EXPLAIN is provided by the documentation.
    EXPLAIN [ANALYZE] [VERBOSE] statement

  • ANALYZE collects statistics about the contents of tables and stores the results in a system catalog. These statistics are then used by the planner in selecting an appropriate query plan.

    It is useless to conduct performance tuning without first running the ANALYZE command. Otherwise, any results gathered will be generating using default values that are sure to be wrong.
    ANALYZE [VERBOSE] [table]

  • There are a number of useful views to display statistical information about the server:
    ViewDescription
    pg_stat_activityOne row per server process
    pg_stat_databaseOne row per database
    pg_stat_database_conflictsShow standby server database conflicts due to dropped tablespaces, lock timeouts, old snapshots, pinned buffers and deadlocks.
    pg_stat_all_tablesShow all tables in the current database
    pg_stat_all_indexesShow all indexes in the current database
    pg_statio_all_tablesShow all the tables' disk io statistics
    pg_statio_all_indexesShow all the indexes' disk io statistics

  • The default configuration of PostgreSQL is designed to work on a wide variety of hardware and software installations and is most definitely not optimal for your system. You should edit the postgresql.conf file to increase the values of shared_buffers, effective_cache_size, sort_mem, max_fsm_pages and max_fsm_relations.

  • The physical memory and disks will most probably be the slowest thing in your system. You should always tweak your hardware to generate the optimal performance in your system.

References:

Aug 27, 2012

PostgreSQL: Functions & Operators

This a continuation of my PostgreSQL series (an installation guide for Ubuntu, an overview of some basic concepts and some more advanced ones). Here I will briefly outline some functions available on the PostgreSQL database.

You can also check out my notes on designing relational databases

  • The usual logical operators of AND, OR, and NOT are available. However, SQL uses a three-valued logic system of TRUE, FALSE and NULL (which represents "unknown"). You should therefore design your system to handle a NULL result.

  • The usual comparison operators of >, <, >=, <=, = and <> (or !=, but the parser resolves this to <> anyway) are available.

  • The BETWEEN construct allows you to test a value between a range (NOT BETWEEN tests if a value is not within a range)
    a BETWEEN x AND y
    is equivalent to
    a >= x AND a <= y

  • The following extra comparison operators will always resolve to a TRUE or FALSE value:
    a IS NULL
    a IS NOT NULL
    a IS TRUE
    a IS NOT TRUE
    a IS FALSE
    a IS NOT FALSE
    a IS UNKNOWN
    a IS NOT UNKNOWN

  • Pattern matching can be achieved through LIKE, SIMILAR TO, or POSIX style regular expressions.

    LIKE is the simplest pattern matching technique. The _ represents any single character, while a % represents a sequence of characters.

    SIMILAR TO is a hybrid approach between LIKE and regular expressions. It still uses the _ and %, but additionally uses symbols borrowed from regular expressions like *, ?, {} and [] (see the documentation for an in-depth explanation).

    Regular expression support is also provided, but is an extensive topic that would be too lengthy to include here. A great reference can be found at regular-expressions.info

  • PostgreSQL provides a wide array of date & time functions. These include current_date, current_time, and current_timestamp.

    Another useful function is age(timestamp), which will subtract the timestamp from the current_date and output a result in years, months and days.

  • A CASE expression exists for conditional statements. expression is the SQL expression, value is the matching result of the expression, and result is the value to return
    CASE expression
    WHEN value THEN result
    [WHEN....]
    [ELSE result]
    END

  • The general purpose window functions will only work if invoked via the window function syntax (that is, the OVER clause is required). In addition to in-built aggregate functions, the following general purpose functions will work:
    • row_number(): number of the row within the partition
    • first_value(field): returns the value of the field from the first row of the window frame
    • last_value(field): returns the value of the field from the last row of the window frame
    • nth_value(field): returns the value of the field from the nth row of the window frame

  • There are many functions to get the current system state, but here are a few of the useful ones:
    • version(): Version info
    • current_user: Username of the current execution context
    • current_database(): Name of the database in use
    • current_schema[()]: Name of the current schema
    • inet_client_addr(): The address of the remote connection
    • inet_client_port(): The port of the remote connection
    • pg_conf_load_time(): Configuration loading time

  • There also exists many administration functions that allow to configure PostgreSQL at runtime. You can also get other running information from the system such as the physical size of databases, initiate back-ups and recoveries, and signal actions to the server.

References:


Aug 21, 2012

PostgreSQL: Basic Concepts

This a continuation of my PostgreSQL series (an installation guide for Ubuntu can be found here). Here I will briefly outline some basic concepts about the database.

You can also check out my notes on designing relational databases.
  • Views are a concept that is common to almost all relational database (RDBMS) systems. The best way to think of a view is akin to an object-orientated interface; an abstraction that takes away all the underlying structural details and provides a generic way to access data and functions.

  • Foreign keys help maintain the referential integrity of your data. It links a record to another record stored in a different relation.You can additionally define the constraints and associations for the link.

  • Transactions are atomic operations where all the steps happen or none of the steps happen. A transaction also guarantees the the result of the operation has been logged to physical storage so that no data is lost in the case of a system crash.

  • In PostgreSQL, a transaction block starts at the BEGIN command and completes when it reaches the COMMIT command. You can place as many SQL commands as you wish between these two block commands. If you want to cancel the update and reset the database, you just issue a ROLLBACK command.

    An example of a transaction block is as follows:
    BEGIN;
    UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
    UPDATE branches SET balance = balance - 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
    UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';
    UPDATE branches SET balance = balance + 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
    COMMIT;
  • Window functions perform a calculation across a relational table. It is similar to aggregate functions like count() and min(), however where these functions return a single output row a window function retains the identities of all rows in the relation. PostgreSQL defines some in-built aggregate functions that can be used within a window function.

  • The OVER clause in a window function specifies how the rows are split up and processed. For instance, you can specify a PARTITION BY list that groups records by a field value. The aggregate function then processes these records by the group rather than all records in the relation. This is a relatively complex topic, so it is strongly suggested that you read the PostgreSQL documentation on this topic.

  • Inheritance is a concept that has carried over from object-orientated databases. However, it is not feature-complete and has some serious caveats that severely limits the functionality of this feature. You should read the documentation to see if the inheritance feature is suitable for your needs.

  • You can alternatively use views, foreign key constraints and other database concepts to mimic inheritance in your database.

  • PostgreSQL supports the idea of constraints for table fields. This allows us to fine-tune the allowable data that can be stored beyond just defining data types. The following constraints have been defined:

    TypeDescription
    CHECKThe value for a column must satisfy a Boolean condition. You can also specify a name by using the optional CONSTRAINT clause.

    CREATE TABLE products(
    price numeric CONSTRAINT positive_price CHECK (price >0)
    );
    NOT NULLSimply states that a value cannot be NULL
    UNIQUESimply states that the value is unique across all record rows
    PRIMARY KEYSimilar to a combination of UNIQUE and NOT NULL constraints, however it specifies that the field can be used as a unique identifier for the row. There can only be one PRIMARY KEY entry for each table.
    REFERENCES (Foreign Key)The REFERENCES clause is used to specify a foreign key relationship between two tables, which will maintain referential integrity of the data.

    When this clause is used, it will become impossible to create a row when the foreign key does not exist in the referenced table.

    You can also specify what happens to the row when the referenced field is updated or deleted by using the ON UPDATE and ON DELETE clauses, respectively. The actions you can define are RESTRICT, CASCADE, NO ACTION, SET NULL and SET DEFAULT. See the documentation for more information on how this works.


  • Each table is created with several system columns. These are reserved and cannot be used as names for user-defined columns. System columns store information such as the unique id of the table, the unique id of the record in the table, and the physical location of the row.
  • The ALTER TABLE command allows you to modify an existing table. This is useful if the table already contains data or is being referenced by other tables.
  • PostgreSQL Schemas can be thought of as table namespaces. You can logically group tables in a database by schema, and you can have identically named tables existing in two different schemas.
    By Default, a new database will automatically have a 'public' schema that is accessible by all users with privileges on the database.
  • Partitioning a table is done via table inheritance (so read up on how PostgreSQL does inheritance before using this feature!). By physically partitioning a table you can increase query times (since the server only needs to search the partition and not the whole table), and you can move less-used data to cheaper disks.

References:

Aug 20, 2012

PostgreSQL 9.1 and Ubuntu 12.04

This is just a quick guide on how to get started using PostgreSQL on Ubuntu.

Installing

  1. Install the PostgreSQL server using apt:
    sudo apt-get install postgresql
  2. Install the contrib add-on package. This provides additional tools and features, such as improved logging and administration functions.
    sudo apt-get install postgresql-contrib
  3. Install the GUI admin interface pgadmin3:
    sudo apt-get install pgadmin3
  4.  Edit the file /etc/postgresql/9.1/main/postgresql.conf to allow TCP/IP connections to the server. Just uncomment the following line in the file:
    listen_addresses = 'localhost'
  5. Start the database
    sudo service postgresql start
  6.  To automatically start the server run the following command:
    sudo update-rc.d postgresql defaults
  7. Put a password on the default postgresql user by opening up a connection to the database:
    sudo -u postgres psql template1
    And then running the following SQL command:
    ALTER USER postgres WITH PASSWORD 'password';
  8. Set up the same password for the system postgres user:
    # Delete the existing postgres user password
    sudo passwd -d postgres


    # Set the user password
    sudo su postgres -c passwd
     

Interfacing with PostgreSQL

  • You can create a database directly from the command line (this assumes that your linux user has a corresponding account to use PostgreSQL):
    createdb newdb
  • To create a new database with a user that has full rights on that database:
    # Create the user
    sudo -u postgres createuser -D -A -P newuser# Create the database
    sudo -u postgres createdb -O newuser newdb
  • The corresponding command to delete a database is:
    dropdb olddb
  • Accessing the database through the commandline:
    psql newdb
    #OR you could use
    sudo -u postgres psql newdb#OR you could use
    psql -h localhost -U postgres -W newdb

    NOTE:
    If the command prompt shows "=#", then you are using an admin account which bypasses normal access controls. This is potentially dangerous. Your average account should be displaying "=>" as part of the prompt.
  • In-built psql commands are prefaced with a '\':
    • \h displays the help for SQL commands
    • \? displays the help for psql-specific commands
    • \q exits the terminal interface for psql
  • Some useful in-built SQL statements:
    • SELECT version(); will display the current PostgreSQL version
    • SELECT current_date; will display the current date of the system
    • SELECT now(); will display the date and time
    • SELECT now()::date; will only display the date portion
    • SELECT now()::time; will only display the time portion
    • count(), sum(), avg(), max(), and min() are special aggregate functions you can perform on fields in your SQL statement.

References:

Jun 26, 2012

Handling HTTP GET requests with webapp2 and Google App Engine: Python

This is a continuation of my Python 2.7 and Google App Engine series. This particular blog post builds upon the code given in my previous posts URL Routing and  Cron and Datastore in Google App Engine: Python, which in turn builds upon my earlier work. If you don't understand parts of the code I highly suggest you browse my earlier blog posts so you can understand some of the design decisions I have made.

A brief overview...

For those who are diving straight in, let me explain the old code and how I will update it:

I have a script feed.py that I have mapped using app.yaml. A cron script (configured by cron.yaml) simply connects to my Twitter account and converts my status updates into an RSS feed. It then stores the RSS feed into a Google Datastore object.

The feed script takes the Datastore object and displays it. We use another script (entity.py) to define the Datastore object.

We will now configure the system so that it can convert multiple twitter accounts into an RSS feed. To display a particular RSS feed we will use a HTTP GET request.

The main application

We will create a file called feed.py. This script will be our controller; it simply gets the HTTP requests and maps them to certain classes. These classes will then call other functions to perform the required tasks.

# The webapp2 framework
import webapp2

# Our datastore interface
from google.appengine.ext import db

# Our entity library
import entity

# Our XML2RSS library
import XML2RSS

# Output the XML in a HTML friendly manner
class Cron(webapp2.RequestHandler):
    # Respond to a HTTP GET request
    def get(self):
        # A try-catch statement
        try:
            XML2RSS.getTweets("almightyolive")
            XML2RSS.getTweets("founding")
            XML2RSS.getTweets("ABCNews24")
            XML2RSS.getTweets("SBSNews")
       
        # Our exception code
        except (TypeError, ValueError):
            self.response.out.write("<html><body><p>Invalid inputs</p></body></html>")

# Fetches an XML document and parses it
class MainPage(webapp2.RequestHandler):
    # Respond to a HTTP GET request
    def get(self):
        # A try-catch statement
        try:
            account = self.request.get('account')
           
            feed = entity.Rss()
            feed_k = db.Key.from_path('Rss', account)
            feed = db.get(feed_k)
           
            # Outputs the RSS
            self.response.out.write(feed.content)

        # Our exception code
        except (TypeError,ValueError):
            self.response.out.write("<html><body><p>Invalid inputs (Type Error)</p></body></html>")
        except:
            self.response.out.write("<html><body><p>Unspecified Error</p></body></html>")

# Create our application instance that maps the root to our
# MainPage handler
app = webapp2.WSGIApplication([('/', MainPage),('/cron', Cron)], debug=True)

The XML2RSS script

As you may have noticed,the feed.py script made reference to an XML2RSS object. This is a separate script that outsources the conversion of XML to RSS into easy-to-call functions. Create a new file called XML2RSS.py and add the following:

# The minidom library for XML parsing
from xml.dom.minidom import parseString

# The URL Fetch library
from google.appengine.api import urlfetch

# Our entity library
import entity

# Detects if it is a URL link and adds the HTML tags
def linkify(text):
    # If http is present in, add the link tag
    if "http" in text:
        text = "&lt;a href='" + text + "'&gt;" + text + "&lt;/a&gt;"
    elif "@" in text:
        text = "&lt;a href='http://twitter.com/#!/" + text.split("@")[1] + "'&gt;" + text + "&lt;/a&gt;"
    elif "#" in text:
        text = "&lt;a href='https://twitter.com/#!/search/%23" + text.split("#")[1] + "'&gt;" + text + "&lt;/a&gt;"
       
    return text

# Output the XML in a HTML friendly manner
def outputRSS(xml, account):
    # The get the states list
    statuses = xml.getElementsByTagName("status")
   
    # Our return string
    outputString = "<?xml version='1.0'?>\n<rss version='2.0'>\n\t<channel>\n\t\t<title>Twitter: " + account + "</title>\n\t\t"
    outputString+= "<link>https://twitter.com/#!/almightyolive</link>\n\t\t<description>The twitter feed for " + account + "</description>"
   
    # Cycled through the states
    for status in statuses:
        #Gets the statuses
        text = status.getElementsByTagName("text")[0].firstChild.data
        date = status.getElementsByTagName("created_at")[0].firstChild.data
        tweet = status.getElementsByTagName("id")[0].firstChild.data
       
        # Insert links into the text
        words = text.split()
       
        for i in range (len(words)):
            words[i] = linkify(words[i])
       
        # Recompile words
        text = " ".join(words)
       
        # Creates our output
        string = "\n\t\t<item>\n\t\t\t<title>" + str(date) + "</title>\n\t\t\t<link>https://twitter.com/AlmightyOlive/status/" + tweet + "</link>\n\t\t\t<description>" + str(text) + "</description>\n\t\t</item>"
        outputString+=string
       
    # Output string
    outputString += "\n\t</channel>\n</rss>"
    return outputString   

# Our RSS storage function
def getTweets(account):
    # Grabs the XML
    url = urlfetch.fetch('https://api.twitter.com/1/statuses/user_timeline.xml?screen_name=' + account + '&count=10&trim_user=true')
           
    # Parses the document
    xml = parseString(url.content)

    # Converts the XML into RSS
    content = outputRSS(xml, account)
   
    # Our RSS storage entity
    rssStore = entity.Rss(key_name='' + account)

    # Elements of our RSS
    rssStore.feed = '' + account
    rssStore.content = content

    # Stores our RSS Feed into the datastore
    rssStore.put()

The pieces to make it all work

If you have been following on from my previous work, then you should already have most of this code. I won't bother explaining it here because it is mostly self-explanatory.

app.yaml:
application: almightynassar
version: 1
runtime: python27
api_version: 1
threadsafe: yes

handlers:
- url: /cron
  script: feed.app
  login: admin
 
- url: /.*
  script: feed.app

cron.yaml:


cron:
- description: daily summary job
  url: /cron
  schedule: every 1 hours

entity.py:

# Our datastore interface
from google.appengine.ext import db

# Our RSS entity object
class Rss(db.Model):
    feed = db.StringProperty()
    content = db.TextProperty()

And that's it! You now have a fully functional application that just uses the webapp2 framework!

If you navigate to http://localhost:8080/?account=almightyolive you should now see the RSS feed. You can test if your mapping works by navigating to http://localhost:8080/?account=founding; you should see the Founding Institute twitter account instead!


References:

May 28, 2012

Setting up a CentOS 6.2 web server: Securing the file system

This is a follow on post from my guide to installing CentOS 6.2. We will go through some of the steps required to secure your server and get it ready for production use.

This section will outline how to lock down your partitions and file system. We will assume that you went for a file system structure similar to the one outlined in the above blog post.

Since the filesystem flags will be the most foriegn concept in this guide, I will give a quick outline about file system flags. However, I strongly suggest you follow the links provided in the references for more in-depth detail on any topic provided in this guide.
  • nosuid disallows the filesystem from granting a user the temporary elevated privileges of a file's owner or group.
  • noexec disallows the filesystem from running an executable.
  • nodev disallows the filesystem from running files as block devices (i.e. treat the file as an I/O source or sink).
The process for securing your file system is as follows:
  1. Secure your partitions by editing /etc/fstab as follows:
    • Add nosuid, noexec, and nodev to partitions like  /dev/shm, /var/log, /tmp, and /var/log/audit. Basically any partition where you only expect to read and write files.
    • Add nodev to all non-root file systems like /home and /var/www. You can add the noexec flag if you want, but note that cgi scripts stored in /var/www will break (as well as any scripts stored in the user's home directory).
    • Add nosuid only to file systems like /var.
    • DO NOT ADD ANY OF THESE FLAGS TO /!!!!!

     
  2.  Add the following line to /etc/fstab to hardlink /var/tmp to /tmp

    /tmp /var/tmp none rw,noexec,nosuid,nodev,bind 0 0
     
  3. Disable the autofs service if you do not need NFS (unless you have already un-installed the service):

    chkconfig autofs off 

References

May 25, 2012

Setting up a CentOS 6.2 web server: Software and package integrity and installation

This is a follow on post from my guide to installing CentOS 6.2. We will go through some of the steps required to secure your server and get it ready for production use.

These steps will outline how to check what is installed on your system and whether your system has been compromised.
  1.  Log in as root and grab a current software list (check out my previous blog post on this topic).

    #Using RPM
    rpm -qa
    #Using yum
    yum list installed

     
  2. Check to ensure that yum is forced to check the gpg signature when installing packages. This is default behaviour in CentOS 6.2, but for the sake of completeness I have included this step. Check /etc/yum.conf and all the files in /etc/yum.repos.d/ for the following line:

    gpgcheck=1

     
  3. AIDE is an intrusion dectection environment that checks the integrity of installed packages and files. It can report on the changes to your system. Install it using yum:

    yum install aide

     
  4. It should be your priority to read and understand /etc/aide.conf and tailor it to your system. While the defaults should be adequate for most installations, you should nevertheless know what AIDE does.
     
  5. Generate the initial AIDE database (by default it will be stored as /var/lib/aide/aide.db.new.gz):

    /usr/sbin/aide --init
     
  6. Back up the database (in this example we are copying it to root's home directory):

    cp /var/lib/aide/aide.db.new.gz ~/
     
  7. Install the AIDE database:

    mv /var/lib/aide/aide.db.new.gz /var/lib/aide/aide.db.gz
     
  8. Run a manual check:

    /usr/sbin/aide --check
     
  9. Stop the yum update daemon (we are going to write our own). If you followed the above guide the service won't even be installed, but again I am including it here for completeness.

    chkconfig yum-updatesd off
     
  10. Edit /etc/crontab by adding the following line (this will do a daily check of the system with AIDE):

    50 4 * * * root /usr/sbin/aide --check
     
  11. Add a file called update.cron in /etc/cron.weekly/ and add in the following (NOTE!: cron.weekly is run by the anacron service. Keep this in mind when you are disabling services later on in this guide).

    #!/bin/sh
    #
    # Update yum, then the rest of the system
    /usr/bin/yum -R 120 -e 0 -d 0 -y update yum
    /usr/bin/yum -R 10 -e -0 -d 0 -y update
    #
    # Save a list of software currently installed on the system
    /bin/rpm -qa > /root/`/bin/hostname -s`_software_`/bin/date +%Y%m%d`.txt
    #
    # OPTIONAL: Fix all the prelinks (otherwise you may get alot of prelink messages)
    /usr/sbin/prelink --all
    #
    # Update the AIDE database
    /usr/sbin/aide --update
    #
    # Make a back-up of the new database
    /bin/cp /var/lib/aide/aide.db.gz /root/aide.db.`/bin/date +%Y%m%d`.gz

     
  12. Make /etc/cron.weekly/update.cron executable:

    chmod 755 /etc/cron.weekly/update.cron
     

References

May 21, 2012

Setting up a CentOS 6.2 web server: Installation

CentOS is the free release version of Red Hat Linux with all the branding removed. It also does not have the support options and some of the fancy trimmings the Enterprise version offers, but it is still a solid server OS. This guide is a brief step-by-step guide in how to install CentOS 6.2 and configure it as a web-server.

Install from DVD:

  1. Boot up from your DVD (you will need to enter into the Boot menu of your computer OR edit your BIOS to do so)

  2. Select Install or upgrade an existing system from the menu

  3. If you are worried about your DVD you can choose to to test it, but this is not a necessary step so you can skip it.

  4. On the Welcome screen select 'Next'

  5. Select your language (in our case we are going for the default of 'English (English)')

  6. Select your keyboard type (in Australia we use 'U.S. English')

  7. If you are just going for a standard local hard-drive set-up then just choose the 'Basic Storage Devices' option. If you are going for something fancy (such as network storage or special drives), or you just want to disable some devices for that extra level of paranoid security then choose 'Specialized Storage Devices'.

    If you have no idea which one you should choose then just select the Basic option.

  8. Enter in the host-name of your new server (for best results you should append your domain name to the end so it works seamlessly with SSL certificates) i.e. testserver.example.com

    If you want to configure a static IP address click on the 'Configure Network' button, select the your network card (probably eth0) and enter away.

    If you are going to use DHCP, or just don't know, just hit 'Next'

  9. Select the correct timezone for you (just click a location on the map and it should select the closest one to you).

  10. Enter in an appropriate root password. Make as long and complex as possible (long sentences with mixed character types are easier to remember than jibberish strings; for instance 'My office is situated in 1234 fake street, Fakeville!')

  11. In this example we are going to go for a custom partition layout, so select 'Create Custom Layout'. If you are fine with defaults, just skip to part .

  12. Delete all existing partitions and do the following:
    • A /boot partition of about 100MB. Use the ext4 format

    • Create a LVM Physical Volume that fills up the rest of the hard-drive

    • Create a LVM Volume Group with a Physical Extent of 4MB.

    • Create LVM Logical Volumes on the Volume group as follows:

      • Swap space that is at least equal to how much RAM is in your server
      • /tmp/ should be as big as the largest file you will be manipulating (for instance, if you are copying a DVD you will need at least 4GB)
      •  /var/log and /var/log/audit are separated so that if your log system goes haywire it does not kill the space for other applications. Dedicate a couple of gigabytes to each.
      • /home/ and /usr/ should be a few gigabytes each. /usr/ just holds your applications and should remain pretty static, while /home/ is where you will store your personal files.
      • /var/ and /var/www/ will contain the majority of space on your system. MySQL stores your database files in /var/lib/mysql/, while Apache runs from /var/www/. Dedicate adequate space to each folder.
      • Your root folder (/) will only need a few GB of space. It will mainly hold configuration files.

  13. The system will take some time to format your hard-drive. Once it is complete it will ask you to install the boot-loader. While the defaults are suitable, for extra security you should consider password protecting your boot-loader.

  14. We can now select our packages. You can customize the system to suit your needs, but for the basics just select 'Basic Server' from the menu and the 'Customize now' from the radio buttons. Hit 'Next'.

  15. Do the following edits:
    • Base system - Remove 'Java Platform' and 'Directory Client'
    • Web Server - Add 'Web Server' and 'PHP support'

  16. Reboot your system!

References