SQL based IP access control for Apache using PostgreSQL and mob_ruby

Everyone knows the .htaccess way to restrict access in Apache. You can use this to require a username/password, an IP address, or a combination. Apache is also able to use a database to look up username/password. However it is not trivial to store network addresses in a database and authenticate users by their IP address. If you also want to check whether an IP address is inside some CIDR network, it is even more difficult.
You could use PHP and hack together some IP authentication classes, for instance using the Pear Net_IPv4 and Net_IPv6 modules, but that still can only protect stuff that is under control of PHP. A directory that does not contain any PHP is left unprotected. What you really want is something from within Apache.

Enter PostgreSQL and mod_ruby!

Combing these two gives you a very intuitive and powerful way of authenticating users by their IP address.

Create a database table that has a field with type INET. You can store several kinds of network addresses, with or without netmask, IPv4 as well as IPv6:

networks=# select address from networks;
address
------------------------------
145.99.178.129/29
2001:610:148::/48
24.132.28.134
2001:610:0:800a:b192:87:5:98
127.0.0.0/8
::1
(6 rows)


Now create a Ruby script that will use DBI and PostgreSQL’s network functions to check whether an address appears in the database, or if it is contained in a network. I’ve saved it as /home/dick/access.rb:

#!/usr/bin/ruby
require 'dbi'
module CheckAccessDB
 
  DB_NAME = 'networks'
  DB_USER = 'postgres'
  DB_PW   = 'hackme'
  DB_HOST = '127.0.0.1'
 
  def self.check_access(r)
    DBI.connect("DBI:Pg:#{DB_NAME}:#{DB_HOST}",DB_USER,DB_PW) do |dbh|
      sql = 'SELECT address FROM networks WHERE address >>= ?'
      row = dbh.select_one(sql, r.remote_host(Apache::REMOTE_NOLOOKUP))
 
      if row.nil?
        return(Apache::FORBIDDEN)
      else
        return(Apache::OK)
      end
    end
  end
end

Now use this script and call the function in your .htaccess file:

RubyRequire '/home/dick/access.rb'
RubyAccessHandler CheckAccessDB

This is of course an example, I suggest you use PostgreSQL for your applications and use your imagination!
Later I will write an article on how to use mod_ruby to log to a database.

Requirements

On Ubuntu, I had to install these packages:

apt-get install libapache2-mod-ruby libdbd-pg-ruby1.8 libpgsql-ruby1.8 libdbi-ruby1.8