Postgresql – inet_client_addr in PostgreSQL don’t return IP

postgresqlpostgresql-9.2

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();

Best Answer

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.

Related Question