PostgreSQL DNS 解析功能 - get ip, get host name

11 minute read

背景

在数据库中,根据主机名获得IP,根据IP解析主机名。

这个功能没有什么花哨,可以通过C函数或者PYTHON函数得到。

src/common/ip.c

/*  
 *      pg_getnameinfo_all - get name info for Unix, IPv4 and IPv6 sockets  
 *  
 * The API of this routine differs from the standard getnameinfo() definition  
 * in two ways: first, the addr parameter is declared as sockaddr_storage  
 * rather than struct sockaddr, and second, the node and service fields are  
 * guaranteed to be filled with something even on failure return.  
 */  
int  
pg_getnameinfo_all(const struct sockaddr_storage *addr, int salen,  
                                   char *node, int nodelen,  
                                   char *service, int servicelen,  
                                   int flags)  
{  
        int                     rc;  
  
#ifdef HAVE_UNIX_SOCKETS  
        if (addr && addr->ss_family == AF_UNIX)  
                rc = getnameinfo_unix((const struct sockaddr_un *) addr, salen,  
                                                          node, nodelen,  
                                                          service, servicelen,  
                                                          flags);  
        else  
#endif  
                rc = getnameinfo((const struct sockaddr *) addr, salen,  
                                                 node, nodelen,  
                                                 service, servicelen,  
                                                 flags);  
  
        if (rc != 0)  
        {  
                if (node)  
                        strlcpy(node, "???", nodelen);  
                if (service)  
                        strlcpy(service, "???", servicelen);  
        }  
  
        return rc;  
}  

pgdnsres

有个python的插件,可以直接使用。

https://www.postgresql.org/ftp/projects/pgFoundry/pgdnsres/pgdnsres/1.1/

/*   
  
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 */  

Flag Counter

digoal’s 大量PostgreSQL文章入口