Postgres Pro
Downloads
Home   >   mailing lists

Thread: function that resolves IP addresses

function that resolves IP addresses

From
"Marcel Gsteiger"
Date:
Hi all

Does anybody know how I could create a database function that accepts
an INET parameter and reverse-lookups the hostname via DNS PTR lookup?
Something like the dnsname command line utility in the djbdns package. I
need this function for analyzing firewall logs stored in the database
with ulogd.

Any idea would much be appreciated.

Regards
--Marcel

Re: function that resolves IP addresses

From
"Jim Buttafuoco"
Date:
give this a try if you don't mind using plperlu.  This was tested with Postgresql 8.0.3


create or replace function gethostbyaddr(inet) returns text
as
$$
use strict;
use Socket;
my $inet = $_[0];
my $iaddr=inet_aton($inet);
my $name = gethostbyaddr($iaddr,AF_INET);
return $name;
$$
language plperlu;

select gethostbyaddr('199.181.132.250'::inet);




---------- Original Message -----------
From: "Marcel Gsteiger" <Marcel.Gsteiger@milprog.ch>
To: <pgsql-general@postgresql.org>
Sent: Wed, 19 Oct 2005 14:36:46 +0200
Subject: [GENERAL] function that resolves IP addresses

> Hi all
>
> Does anybody know how I could create a database function that accepts
> an INET parameter and reverse-lookups the hostname via DNS PTR lookup?
> Something like the dnsname command line utility in the djbdns package. I
> need this function for analyzing firewall logs stored in the database
> with ulogd.
>
> Any idea would much be appreciated.
>
> Regards
> --Marcel
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
------- End of Original Message -------

Re: function that resolves IP addresses

From
Richard Huxton
Date:
Marcel Gsteiger wrote:
> Hi all
>
> Does anybody know how I could create a database function that accepts
> an INET parameter and reverse-lookups the hostname via DNS PTR lookup?
> Something like the dnsname command line utility in the djbdns package. I
> need this function for analyzing firewall logs stored in the database
> with ulogd.

You can write "unsafe" functions in any of the pl...u languages and also
pl/sh and "C". Should be simple enough in whatever language you're most
familiar with.

--
   Richard Huxton
   Archonet Ltd

Re: function that resolves IP addresses

From
"A. Kretschmer"
Date:
am  19.10.2005, um 14:36:46 +0200 mailte Marcel Gsteiger folgendes:
> Hi all
>
> Does anybody know how I could create a database function that accepts
> an INET parameter and reverse-lookups the hostname via DNS PTR lookup?
> Something like the dnsname command line utility in the djbdns package. I
> need this function for analyzing firewall logs stored in the database
> with ulogd.
>
> Any idea would much be appreciated.

You can write such a function with a language like plperl or plsh.


Regards, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

Re: function that resolves IP addresses

From
Bruno Wolff III
Date:
On Wed, Oct 19, 2005 at 14:36:46 +0200,
  Marcel Gsteiger <Marcel.Gsteiger@milprog.ch> wrote:
> Hi all
>
> Does anybody know how I could create a database function that accepts
> an INET parameter and reverse-lookups the hostname via DNS PTR lookup?
> Something like the dnsname command line utility in the djbdns package. I
> need this function for analyzing firewall logs stored in the database
> with ulogd.
>
> Any idea would much be appreciated.

Note that dns lookups can be slow and that might cause some issues for you
if it leaves resources needed for other queries locked while the function
waits for a response.

Re: function that resolves IP addresses

From
"Joshua D. Drake"
Date:
Bruno Wolff III wrote:

>On Wed, Oct 19, 2005 at 14:36:46 +0200,
>  Marcel Gsteiger <Marcel.Gsteiger@milprog.ch> wrote:
>
>
>>Hi all
>>
>>Does anybody know how I could create a database function that accepts
>>an INET parameter and reverse-lookups the hostname via DNS PTR lookup?
>>Something like the dnsname command line utility in the djbdns package. I
>>need this function for analyzing firewall logs stored in the database
>>with ulogd.
>>
>>Any idea would much be appreciated.
>>
>>
You could do this easily with plPerlU or plPythonU. However you probably
don't want to do this
as a per entry basis due to the reasons listed below. Run it as a batch
process.

>
>Note that dns lookups can be slow and that might cause some issues for you
>if it leaves resources needed for other queries locked while the function
>waits for a response.
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>
>


--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Re: function that resolves IP addresses

From
Dennis Jenkins
Date:

--- "Joshua D. Drake" <jd@commandprompt.com> wrote:
It is not fully debugged, but this is what I wrote a
few months ago for sh*ts and grins.

/* djenkins, 2005-7-22

    Implements poor-man's reverse DNS lookup tool for use
in
    Postgresql SQL functions.

CREATE FUNCTION reverse_dns_lookup(text) RETURNS text
     AS 'dns_tools.so', 'reverse_dns_lookup'
     LANGUAGE C STRICT;

CREATE FUNCTION forward_dns_lookup(text) RETURNS text
     AS 'dns_tools.so', 'forward_dns_lookup'
     LANGUAGE C STRICT;
*/

#include "postgres.h"
#include <string.h>
#include "fmgr.h"
#include <stdlib.h>
#include <netdb.h>
#include <sys/types.h>
#include <sys/socket.h>
#include <netinet/in.h>
#include <arpa/inet.h>
#include <syslog.h>

PG_FUNCTION_INFO_V1(forward_dns_lookup);

Datum     forward_dns_lookup(PG_FUNCTION_ARGS)
{
    text     *t = PG_GETARG_TEXT_P(0);
    struct hostent *he = NULL;
    int ret_len = 0;
    text    *ret_text = NULL;
    char *in_str = VARDATA(t);
    int in_len = VARSIZE(t) - VARHDRSZ;
    char temp[256];

    if (!in_str || (in_len > sizeof(temp)-1))
    {
        PG_RETURN_NULL();
    }

    strncpy(temp, in_str, in_len);
    temp[in_len] = 0;
    he = gethostbyname(temp);
    if (!he)
    {
        PG_RETURN_NULL();
    }

    strncpy(temp, inet_ntoa(*((struct in_addr
*)he->h_addr)), sizeof(temp));
    ret_len = strlen(temp);

//    syslog(LOG_DEBUG, "'%s'[%d] = '%s'[%d]\n", in_str,
strlen(in_str), temp, ret_len);

    ret_text = (text*)palloc(ret_len + VARHDRSZ);
    VARATT_SIZEP(ret_text) = ret_len + VARHDRSZ;
    memcpy(VARDATA(ret_text), temp, ret_len);

    PG_RETURN_TEXT_P(ret_text);
}

PG_FUNCTION_INFO_V1(reverse_dns_lookup);

Datum     reverse_dns_lookup(PG_FUNCTION_ARGS)
{
    text     *t = PG_GETARG_TEXT_P(0);
    struct in_addr in;
    struct hostent *he = NULL;
    unsigned long *l = (unsigned long*)((void*)&in);
    int ret_len = 0;
    text    *ret_text = NULL;
    char *in_str = VARDATA(t);
    int in_len = VARSIZE(t) - VARHDRSZ;
    char temp[16];

    if (!in_str || (in_len > sizeof(temp)-1))
    {
        PG_RETURN_NULL();
    }

    memcpy(temp, in_str, in_len);
    temp[in_len] = 0;

    // First, convert the string to IPV4 'long'
    memset(&in, 0, sizeof(in));
    if (!inet_aton(temp, &in))
    {
//        syslog(LOG_DEBUG, "inet_aton('%s'[%d]) failed: %d
{%08lx}", in_str, strlen(in_str), errno, *l);
        PG_RETURN_NULL();
    }

    he = gethostbyaddr((char*)l, 4, AF_INET);
    if (!he)
    {
//        syslog(LOG_DEBUG, "gethostbyaddr('%s') failed:
%d", in_str, errno);
        PG_RETURN_NULL();
    }

    // return string is in 'he->h_name'
    ret_len = strlen(he->h_name);
    ret_text = palloc(ret_len + VARHDRSZ);
    VARATT_SIZEP(ret_text) = ret_len + VARHDRSZ;
    memcpy(VARDATA(ret_text), he->h_name, ret_len);

    PG_RETURN_TEXT_P(ret_text);
}



Dennis Jenkins