在 PostgreSQL 9.0 上審核登錄

我已經設置了一個腳本，該腳本是從網路上的各種連結創建的，以使用觸發器跟踪表中的活動。

/* Create the MinUser Table */
CREATE TABLE MinUser (
   User_Id             int NOT NULL PRIMARY KEY,
   User_Name           varchar (50) NOT NULL,
   User_Password           varchar (50) NOT NULL,
   User_Email          varchar (50),
   User_Role           varchar (50),
   UNIQUE (User_Name)
) WITH (OIDS=FALSE);


/* Create the Audit Table */
CREATE TABLE MinUser_Audit (
   AuditUser_Id            SERIAL,
       operation               char(1)   NOT NULL,
       stamp                   timestamp NOT NULL,
       userid                  text      NOT NULL,
       ipaddress                   text      NOT NULL,
   User_Id             int NOT NULL,
   User_Name           varchar (50) NOT NULL,
   User_Password           varchar (50) NOT NULL,
   User_Email          varchar (50),
   User_Role           varchar (50)
) WITH (OIDS=FALSE);

/* Function that will add a row to the MinUser_audit table when an insertion/deletion/modification is made to the MinUser table */
CREATE OR REPLACE FUNCTION MinUser_audit() RETURNS TRIGGER AS $usr_audit$
   BEGIN
       --
       -- Create a row in MinUser_Audit to reflect the operation performed on MinUser,
       -- make use of the special variable TG_OP to work out the operation.
       --
       IF (TG_OP = 'DELETE') THEN
           INSERT INTO MinUser_audit (operation, stamp, userid, ipaddress, User_ID, User_Name, User_Password, User_Email, User_Role) VALUES ('D', now(), user, inet_client_addr(), OLD.User_Id,OLD.User_Name, OLD.User_Email, OLD.User_Password, OLD.User_Role);
           RETURN OLD;
       ELSIF (TG_OP = 'UPDATE') THEN
           INSERT INTO MinUser_audit (operation, stamp, userid, ipaddress, User_ID, User_Name, User_Password, User_Email, User_Role) VALUES ('U', now(), user, inet_client_addr(), NEW.User_Id,NEW.User_Name, NEW.User_Email, NEW.User_Password, NEW.User_Role);
           RETURN NEW;
       ELSIF (TG_OP = 'INSERT') THEN
           INSERT INTO MinUser_audit (operation, stamp, userid, ipaddress, User_ID, User_Name, User_Password, User_Email, User_Role) VALUES ('I', now(), user, inet_client_addr(), NEW.User_Id,NEW.User_Name, NEW.User_Email, NEW.User_Password, NEW.User_Role);
           RETURN NEW;
       END IF;
       RETURN NULL; -- result is ignored since this is an AFTER trigger
   END;
$usr_audit$ LANGUAGE plpgsql;


/* Create the trigger that will use the MinUser_audit() function */
CREATE TRIGGER MinUser_auditt AFTER INSERT OR UPDATE OR DELETE ON MinUser 
   FOR EACH ROW EXECUTE PROCEDURE MinUser_audit();

我了解如何獲取使用使用者插入/修改/刪除記錄的人的使用者名。有沒有我可以用來獲取IP地址的功能？此外，PostgreSQL 的網站上是否有顯示您可以在查詢中使用的所有不同伺服器變數的參考？

對於IP地址，您可以使用該功能inet_client_addr()

所有系統資訊功能都記錄在手冊的“系統資訊功能”一章中

http://www.postgresql.org/docs/current/static/functions-info.html

引用自：https://dba.stackexchange.com/questions/9270