作者
digoal
日期
2018-10-27
标签
PostgreSQL , dns , python , gethostname , gethost
背景
在数据库中，根据主机名获得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 /
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
