/* 

IPv4 DNS Resolution Functions for PostgreSQL
============================================

Author: Christian J. Dietrich <dietrich@internet-sicherheit.de>
Version: 1.1
License: BSD license

pg-dns-resolve contains PL/Python functions for DNS resolution at the SQL 
prompt. Ever wanted to issue SELECT hostbyname('www.google.com') in 
order to get its IP address(es) from the pgsql command line? Then 
pg-dns-resolve is the right thing for you. See below for more examples.


INSTALLATION
============
 * Make sure, you have ip4r installed. Get it from: http://pgfoundry.org/projects/ip4r/
 * Make sure, you have PL/Pythonu installed and are allowed to add new functions
 * PL/Pythonu must be built against Python >= 2.4
 * psql [YOUR OPTIONS] < plpython_dns-functions.sql


EXAMPLES
========

NOTE: If you run any of the functions below on a massive 
      data set, your DNS resolver might misinterpret this as a 
      DoS attack and get somewhat angry. Thus, it is a good idea 
      to run your own local resolver.


For all of the functions there is a variant ending in "_n" which means 
that on error, NULL is to be returned instead of an error string describing 
the cause of the error. Some functions have a _s version which means they 
return the result as a set, i.e. multiple rows.

Resolve the hostname for a given IP address:

db=# select dst, hostbyaddr(dst) from dns_per_ip limit 2;
      dst      |        hostbyaddr
---------------+-----------------------------
 192.168.1.1   | (1, 'Unbekannter Rechner')
 193.232.128.6 | ns5.msk-ix.net
(2 rows)


Forward resolve www.google.de to (one of) its IP address:

db=# select hostbyname('www.google.de');
 hostbyname
---------------
 74.125.43.105
(1 row)


Note that on error, NULL is returned by hostbyname_n, 
BUT hostbyname returns an error string instead. So if 
you want to know why the resolution failed, use 
hostbyname, otherwise use hostbyname_n.

db=# select hostbyname_n('deafd'), hostbyname('deafd');
  hostbyname_n |                  hostbyname
---------------+----------------------------------------------------
               | (-2, 'Der Name oder der Dienst ist nicht bekannt')
(1 row)


db=# select hostbyname_n('nonexistinghost') is NULL;
 ?column?
----------
 true
(1 row)


If you need all IP addresses of a hostname, use addrsbyname. 
DNS usually returns a different order of multiple IP addresses due 
to round-robin. Note that, the list of IP addresses of 
addrsbyname is sorted, thus two executions with the same 
argument return the same list. This is very useful for comparisons.

db=# select addrsbyname('www.google.de');
 addrsbyname
-------------------
 74.125.43.103
 74.125.43.104
 74.125.43.105
 74.125.43.106
 74.125.43.147
 74.125.43.99
(1 row)

If you want e.g. a comma-separated list instead of newline-separated list, 
use your own separator string as the second argument to addrsbyname:

db=# select addrsbyname('www.google.de', ', ');
                                    addrsbyname
-----------------------------------------------------------------------------------------
 74.125.43.103, 74.125.43.104, 74.125.43.105, 74.125.43.106, 74.125.43.147, 74.125.43.99
(1 row) 


hostsbyname works similar to addrsbyname. hostsbyname returns a list of 
all hostnames associated with a given hostname, including aliases. As with
addrsbyname there are 2 variants, one using the default newline delimiter
to separate elements and one where you can specify the delimiter yourself.
The list of resulting hostnames is sorted.

db=# select hostsbyname('www.google.de', ', ');
                  hostsbyname
-------------------------------------------------
 www.google.com, www.google.de, www.l.google.com
(1 row)


When working with sets, there are 4 interesting functions: addrsbyname_s and 
addrsbyname_ns as well as hostsbyname_s and hostsbyname_ns. 
Those return a set, i.e. multiple rows, instead of an aggregated 
string and they are useful when working with statements such as 

  SELECT ... 
  FROM ...
  WHERE xxx IN ( SELECT addrsbyname_ns('www.google.com') )


db=# SELECT addrsbyname_s('www.google.com');
 addrsbyname_s
---------------
 74.125.43.103
 74.125.43.104
 74.125.43.105
 74.125.43.106
 74.125.43.147
 74.125.43.99
(6 rows)

Note the subtle difference: 6 rows instead of 1 row when comparing the output 
of addrsbyname_s to that of addrsbyname.


db=# SELECT '74.125.43.103'::ip4 IN ( SELECT addrsbyname_s('www.google.com') );
 ?column?
----------
 t
(1 row)

db=# SELECT hostsbyname_ns('www.google.com');
  hostsbyname_ns
------------------
 www.google.com
 www.l.google.com
(2 rows)

Querying a non existing hostname will result in an empty set:

db=# SELECT hostsbyname_ns('nonexistinghost');
 hostsbyname_ns
----------------
(0 rows)

A special case is forward-confirmed reverse DNS resolution (http://en.wikipedia.org/wiki/Forward-confirmed_reverse_DNS):

db=# SELECT fcrdns('192.203.230.10');
 fcrdns
--------
 f
(1 row)

db=# SELECT fcrdns('74.125.43.104');
 fcrdns
--------
 t
(1 row)

*/


/* **********   reverse resolution    ********** */

-- returns the hostname for a given IP address
-- returns an error string for non-resolvable hostnames or resolution errors
CREATE OR REPLACE FUNCTION hostbyaddr (addr ip4)
    RETURNS text
AS $$
    import socket
	
    if addr is None: return None
    try:
        hostname = socket.gethostbyaddr(addr)[0]
    except Exception, e: 
        hostname = str(e)
    return hostname
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION hostbyaddr (ip4) IS 'Returns the hostname for a given IP address. Returns an error string on resolution errors. Returns NULL on NULL input.';

-- returns the hostname for a given IP address
-- returns NULL for non-resolvable hostnames or resolution errors
CREATE OR REPLACE FUNCTION hostbyaddr_n (addr ip4)
    RETURNS text
AS $$
    import socket
	
    if addr is None: return None
    try:
        hostname = socket.gethostbyaddr(addr)[0]
    except Exception, e: 
        hostname = None
    return hostname
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION hostbyaddr_n (ip4) IS 'Returns the hostname for a given IP address, NULL on error. Returns NULL on NULL input.';

-- returns true, if the given IP address passes forward-confirmed reverse DNS, false otherwise (also on error)
-- see http://en.wikipedia.org/wiki/Forward-confirmed_reverse_DNS
CREATE OR REPLACE FUNCTION fcrdns (addr ip4)
    RETURNS boolean
AS $$
    import socket
	
    if addr is None: return None
    try:
        # reverse resolution of the given IP address returns all rhostnames
        (hostname, aliaslist, ipaddrlist) = socket.gethostbyaddr(addr)
        for rhostname in [hostname]+aliaslist:
            (hostname, aliaslist, ipaddrlist) = socket.gethostbyname_ex(rhostname)
            # if the given ip addr is at least once in ipaddrlist, we pass the test
            if addr in ipaddrlist:
                return True
        # if the ip addr has not been in any of the ipaddrlists, we fail
        return False
    except Exception, e: 
        pass
    return False
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION fcrdns (ip4) IS 'Returns true, if the given IP address passes forward-confirmed reverse DNS, false otherwise. see http://en.wikipedia.org/wiki/Forward-confirmed_reverse_DNS';




/* **********   forward resolution    ********** */

-- returns an IP address for the given hostname
-- returns an error string for non-resolvable hostnames or resolution errors
CREATE OR REPLACE FUNCTION hostbyname (name text)
    RETURNS text
AS $$
    import socket
	
    if name is None: return None
    try:
        addr = socket.gethostbyname(name)
    except Exception, e: 
        addr = str(e)
    return addr
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION hostbyname (text) IS 'Returns an IP address for the given hostname. Returns an error string on resolution errors. Returns NULL on NULL input.';

-- returns an IP address for the given hostname
-- returns NULL for non-resolvable hostnames
CREATE OR REPLACE FUNCTION hostbyname_n (name text)
    RETURNS ip4
AS $$
    import socket
	
    if name is None: return None
    try:
        addr = socket.gethostbyname(name)
    except Exception, e: 
        addr = None
    return addr
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION hostbyname_n (text) IS 'Returns an IP address for the given hostname, NULL on error. Returns NULL on NULL input.';


-- returns all IP addresses for the given hostname
-- returns an error string for non-resolvable hostnames or resolution errors
CREATE OR REPLACE FUNCTION addrsbyname (name text)
    RETURNS text
AS $$
    import socket, string
	
    if name is None: return None
    try:
        (hostname, aliaslist, ipaddrlist) = socket.gethostbyname_ex(name)
        addr = string.join(sorted(ipaddrlist), '\n')
    except Exception, e: 
        addr = str(e)
    return addr
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION addrsbyname (text) IS 'Returns all IP addresses for the given hostname. Returns an error string on resolution errors. Returns NULL on NULL input.';

-- returns all IP addresses for the given hostname as a set (multiple rows)
-- returns an error string for non-resolvable hostnames or resolution errors
CREATE OR REPLACE FUNCTION addrsbyname_s (name text)
    RETURNS SETOF ip4
AS $$
    import socket, string
	
    if name is None: return []
    try:
        (hostname, aliaslist, ipaddrlist) = socket.gethostbyname_ex(name)
        addr = sorted(ipaddrlist)
    except Exception, e: 
        addr = [str(e)]
    return addr
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION addrsbyname_s (text) IS 'Returns all IP addresses for the given hostname as multiple rows. Returns an error string on resolution errors. Returns empty set on NULL input.';


-- returns all IP addresses for the given hostname as a string where the elements are separated by sep
-- returns an error string for non-resolvable hostnames or resolution errors
CREATE OR REPLACE FUNCTION addrsbyname (name text, sep text)
    RETURNS text
AS $$
    import socket, string
	
    if name is None or sep is None: return None
    try:
        (hostname, aliaslist, ipaddrlist) = socket.gethostbyname_ex(name)
        addr = string.join(sorted(ipaddrlist), sep)
    except Exception, e: 
        addr = str(e)
    return addr
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION addrsbyname (text, text) IS 'Returns all IP addresses for the given hostname with custom delimiter. Returns an error string on resolution errors. Returns NULL on NULL input.';

-- returns all IP addresses for the given hostname
-- returns NULL for non-resolvable hostnames or resolution errors
CREATE OR REPLACE FUNCTION addrsbyname_n (name text)
    RETURNS text
AS $$
    import socket, string
	
    if name is None: return None
    try:
        (hostname, aliaslist, ipaddrlist) = socket.gethostbyname_ex(name)
        addr = string.join(sorted(ipaddrlist), '\n')
    except Exception, e: 
        addr = None
    return addr
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION addrsbyname_n (text) IS 'Returns all IP addresses for the given hostname, NULL on error. Returns NULL on NULL input.';

-- returns all IP addresses for the given hostname as a set (multiple rows)
-- returns an empty set for non-resolvable hostnames or resolution errors
CREATE OR REPLACE FUNCTION addrsbyname_ns (name text)
    RETURNS SETOF ip4
AS $$
    import socket, string
	
    if name is None: return []
    try:
        (hostname, aliaslist, ipaddrlist) = socket.gethostbyname_ex(name)
        addr = sorted(ipaddrlist)
    except Exception, e: 
        addr = ()
    return addr
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION addrsbyname_ns (text) IS 'Returns all IP addresses for the given hostname as a set (multiple rows), empty set on error or NULL input.';

-- returns all IP addresses for the given hostname as a string where the elements are separated by sep
-- returns NULL for non-resolvable hostnames or resolution errors
CREATE OR REPLACE FUNCTION addrsbyname_n (name text, sep text)
    RETURNS text
AS $$
    import socket, string
	
    if name is None or sep is None: return None
    try:
        (hostname, aliaslist, ipaddrlist) = socket.gethostbyname_ex(name)
        addr = string.join(sorted(ipaddrlist), sep)
    except Exception, e: 
        addr = None
    return addr
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION addrsbyname_n (text, text) IS 'Returns all IP addresses for the given hostname with custom delimiter, NULL on error or NULL input.';




/* **********   hostname alias resolution    ********** */

-- returns all hostnames for the given hostname
-- returns an error string for non-resolvable hostnames or resolution errors
CREATE OR REPLACE FUNCTION hostsbyname (name text)
    RETURNS text
AS $$
    import socket, string
	
    if name is None: return None
    try:
        (hostname, aliaslist, ipaddrlist) = socket.gethostbyname_ex(name)
        addr = string.join(sorted([hostname] + aliaslist), '\n')
    except Exception, e: 
        addr = str(e)
    return addr
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION hostsbyname (text) IS 'Returns all hostnames for the given hostname. Returns an error string on resolution errors. Returns NULL on NULL input.';

-- returns all hostnames for the given hostname as a string where the elements are separated by sep
-- returns an error string for non-resolvable hostnames or resolution errors
CREATE OR REPLACE FUNCTION hostsbyname (name text, sep text)
    RETURNS text
AS $$
    import socket, string
	
    if name is None or sep is None: return None
    try:
        (hostname, aliaslist, ipaddrlist) = socket.gethostbyname_ex(name)
        addr = string.join(sorted([hostname] + aliaslist), sep)
    except Exception, e: 
        addr = str(e)
    return addr
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION hostsbyname (text, text) IS 'Returns all hostnames for the given hostname with custom delimiter. Returns an error string on resolution errors. Returns NULL on NULL input.';

-- returns all hostnames for the given hostname as a set
-- returns an error string for non-resolvable hostnames or resolution errors
CREATE OR REPLACE FUNCTION hostsbyname_s (name text)
    RETURNS SETOF text
AS $$
    import socket, string
	
    if name is None: return []
    try:
        (hostname, aliaslist, ipaddrlist) = socket.gethostbyname_ex(name)
        addr = sorted([hostname] + aliaslist)
    except Exception, e: 
        addr = [str(e)]
    return addr
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION hostsbyname_s (text) IS 'Returns all hostnames for the given hostname as a set. Returns an error string on resolution errors. Returns empty set on NULL input.';



-- returns all hostnames for the given hostname
-- returns NULL for non-resolvable hostnames or resolution errors
CREATE OR REPLACE FUNCTION hostsbyname_n (name text)
    RETURNS text
AS $$
    import socket, string
	
    if name is None: return None
    try:
        (hostname, aliaslist, ipaddrlist) = socket.gethostbyname_ex(name)
        addr = string.join(sorted([hostname] + aliaslist), '\n')
    except Exception, e: 
        addr = None
    return addr
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION hostsbyname_n (text) IS 'Returns all hostnames for the given hostname, NULL on error. Returns NULL on NULL input.';

-- returns all hostnames for the given hostname as a string where the elements are separated by sep
-- returns NULL for non-resolvable hostnames or resolution errors
CREATE OR REPLACE FUNCTION hostsbyname_n (name text, sep text)
    RETURNS text
AS $$
    import socket, string
	
    if name is None or sep is None: return None
    try:
        (hostname, aliaslist, ipaddrlist) = socket.gethostbyname_ex(name)
        addr = string.join(sorted([hostname] + aliaslist), sep)
    except Exception, e: 
        addr = None
    return addr
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION hostsbyname_n (text, text) IS 'Returns all hostnames for the given hostname with custom delimiter, NULL on error. Returns NULL on NULL input.';

-- returns all hostnames for the given hostname as a set
-- returns an error string for non-resolvable hostnames or resolution errors
CREATE OR REPLACE FUNCTION hostsbyname_ns (name text)
    RETURNS SETOF text
AS $$
    import socket, string
	
    if name is None: return []
    try:
        (hostname, aliaslist, ipaddrlist) = socket.gethostbyname_ex(name)
        addr = sorted([hostname] + aliaslist)
    except Exception, e: 
        addr = ()
    return addr
$$ LANGUAGE plpythonu;
COMMENT ON FUNCTION hostsbyname_ns (text) IS 'Returns all hostnames for the given hostname as a set, on error or on NULL input returns empty set.';

/* end of file */