Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

i trying use the function inet_client_addr() (PostgreSQL 9.2) in a trigger, but the return is '::1/128'. This is a problem with my script or with the server?

    CREATE OR REPLACE FUNCTION trg_aud_cms() RETURNS TRIGGER AS $aud_cms$
BEGIN
    --
    -- Cria uma linha na tabela para refletir o estado anterior ao atual
    IF (TG_OP = 'DELETE') THEN
            INSERT INTO aud_cms(operation, user, ip, date, codcms, titlecms, contentcms, hashtagcms) VALUES ('D', USER, inet_client_addr(), now(), OLD.codcms, OLD.titlecms, OLD.contentcms, OLD.hashtagcms);
            RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO aud_cms(operation, user, ip, date, codcms, titlecms, contentcms, hashtagcms) VALUES ('U', USER, inet_client_addr(), now(), OLD.codcms, OLD.titlecms, OLD.contentcms, OLD.hashtagcms);
            RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO aud_cms(operation, user, ip, date, codcms, titlecms, contentcms, hashtagcms) VALUES ('I', USER, inet_client_addr(), now(), NEW.codcms, NEW.titlecms, NEW.contentcms, NEW.hashtagcms);
            RETURN NEW;
    END IF;
    RETURN NULL; -- o resultado é ignorado uma vez que este é um gatilho AFTER
END;
$aud_cms$ LANGUAGE plpgsql;

CREATE TRIGGER aud_cms
AFTER INSERT OR UPDATE OR DELETE ON cms
FOR EACH ROW EXECUTE PROCEDURE trg_aud_cms();
share|improve this question
6  
I don't think it's doing what you think it is. That will be the (ipv4/ipv6) address of the Postgres client (so probably a PHP client or something running on the same serveras your Postgres installation), not the IP address of the computer that is running the software. Not upto speed with ipv6, but that's a ipv6 address/mask. I think ::1 is the equivalent to ipv4 127.0.0.1 –  Phil Jan 8 '13 at 16:34

2 Answers 2

Function inet_client_addr() just return the client IP,just look the example;

--1 Return null

[pg92@redhatB ~]$ psql skytf skytf;
psql (9.2.1)
Type "help" for help.

skytf=> select inet_client_addr() ;
 inet_client_addr 
------------------

(1 row)

--2 Return 127.0.0.1

[pg92@redhatB ~]$ psql -h 127.0.0.1 skytf skytf;
psql (9.2.1)
Type "help" for help.

skytf=> select inet_client_addr() ;
 inet_client_addr 
------------------
 127.0.0.1
(1 row)

--3 Return the client physical Ip

[pg92@redhatB ~]$ psql -h 192.168.1.36 skytf skytf;
Password for user skytf: 
psql (9.2.1)
Type "help" for help.

skytf=> select inet_client_addr() ;
 inet_client_addr 
------------------
 192.168.1.36
(1 row)
share|improve this answer
    
Is there any way to restore this IP, I'm doing the connection to the PostgreSQL using PHP. –  Tommy Jan 9 '13 at 20:58
    
The client process only can restore itself IP,and I guess it can not restore other processes's IP。 –  francs Jan 10 '13 at 1:52
    
Either you draw the wrong conclusion or your examples are really bad. –  dezso Jan 10 '13 at 14:16
    
why ? what's wrong ? –  francs Jan 14 '13 at 3:16

You could abuse the application_name setting to pass the HTTP client IP from PHP to Postgres.

In PHP, once connected:

<? pg_query("SET application_name TO '$_SERVER[REMOTE_ADDR]'"); ?>

Or set it right at connection time with pg_connect which would normally support it in the conninfo string (see Connection Strings in the manual)

In your trigger, use current_setting('application_name') to retrieve this information, instead of inet_client_addr() which doesn't do what you want, as already answered.

There should also be a check in the trigger that this value is what you expect before storing it, since the trigger may be fired in any session, not just the ones initiated by PHP.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.