Hi Sawada, On 3/25/15 9:24 AM, David Steele wrote: > On 3/25/15 7:46 AM, Sawada Masahiko wrote: >> 2. >> I got ERROR when executing function uses cursor. >> >> 1) create empty table (hoge table) >> 2) create test function as follows. >> >> create function test() returns int as $$ >> declare >> cur1 cursor for select * from hoge; >> tmp int; >> begin >> open cur1; >> fetch cur1 into tmp; >> return tmp; >> end$$ >> language plpgsql ; >> >> 3) execute test function (got ERROR) >> =# select test(); >> LOG: AUDIT: SESSION,6,1,READ,SELECT,,,selecT test(); >> LOG: AUDIT: SESSION,6,2,FUNCTION,EXECUTE,FUNCTION,public.test,selecT test(); >> LOG: AUDIT: SESSION,6,3,READ,SELECT,,,select * from hoge >> CONTEXT: PL/pgSQL function test() line 6 at OPEN >> ERROR: pg_audit stack is already empty >> STATEMENT: selecT test(); >> >> It seems like that the item in stack is already freed by deleting >> pg_audit memory context (in MemoryContextDelete()), >> before calling stack_pop in dropping of top-level Portal.
This has been fixed and I have attached a new patch. I've seen this with cursors before where the parent MemoryContext is freed before control is returned to ProcessUtility. I think that's strange behavior but there's not a lot I can do about it. The code I put in to deal with this situation was not quite robust enough so I had to harden it a bit more. Let me know if you see any other issues. Thanks, -- - David Steele da...@pgmasters.net
diff --git a/contrib/Makefile b/contrib/Makefile
index 195d447..d8e75f4 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -29,6 +29,7 @@ SUBDIRS = \
pageinspect \
passwordcheck \
pg_archivecleanup \
+ pg_audit \
pg_buffercache \
pg_freespacemap \
pg_prewarm \
diff --git a/contrib/pg_audit/Makefile b/contrib/pg_audit/Makefile
new file mode 100644
index 0000000..32bc6d9
--- /dev/null
+++ b/contrib/pg_audit/Makefile
@@ -0,0 +1,20 @@
+# pg_audit/Makefile
+
+MODULE = pg_audit
+MODULE_big = pg_audit
+OBJS = pg_audit.o
+
+EXTENSION = pg_audit
+
+DATA = pg_audit--1.0.0.sql
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/pg_audit
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/pg_audit/pg_audit--1.0.0.sql
b/contrib/pg_audit/pg_audit--1.0.0.sql
new file mode 100644
index 0000000..9d9ee83
--- /dev/null
+++ b/contrib/pg_audit/pg_audit--1.0.0.sql
@@ -0,0 +1,22 @@
+/* pg_audit/pg_audit--1.0.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION pg_audit" to load this file.\quit
+
+CREATE FUNCTION pg_audit_ddl_command_end()
+ RETURNS event_trigger
+ LANGUAGE C
+ AS 'MODULE_PATHNAME', 'pg_audit_ddl_command_end';
+
+CREATE EVENT TRIGGER pg_audit_ddl_command_end
+ ON ddl_command_end
+ EXECUTE PROCEDURE pg_audit_ddl_command_end();
+
+CREATE FUNCTION pg_audit_sql_drop()
+ RETURNS event_trigger
+ LANGUAGE C
+ AS 'MODULE_PATHNAME', 'pg_audit_sql_drop';
+
+CREATE EVENT TRIGGER pg_audit_sql_drop
+ ON sql_drop
+ EXECUTE PROCEDURE pg_audit_sql_drop();
diff --git a/contrib/pg_audit/pg_audit.c b/contrib/pg_audit/pg_audit.c
new file mode 100644
index 0000000..b34df5a
--- /dev/null
+++ b/contrib/pg_audit/pg_audit.c
@@ -0,0 +1,1688 @@
+/*------------------------------------------------------------------------------
+ * pg_audit.c
+ *
+ * An auditing extension for PostgreSQL. Improves on standard statement logging
+ * by adding more logging classes, object level logging, and providing
+ * fully-qualified object names for all DML and many DDL statements (See
+ * pg_audit.sgml for details).
+ *
+ * Copyright (c) 2014-2015, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/pg_audit/pg_audit.c
+
*------------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/htup_details.h"
+#include "access/sysattr.h"
+#include "access/xact.h"
+#include "catalog/catalog.h"
+#include "catalog/objectaccess.h"
+#include "catalog/pg_class.h"
+#include "catalog/namespace.h"
+#include "commands/dbcommands.h"
+#include "catalog/pg_proc.h"
+#include "commands/event_trigger.h"
+#include "executor/executor.h"
+#include "executor/spi.h"
+#include "miscadmin.h"
+#include "libpq/auth.h"
+#include "nodes/nodes.h"
+#include "tcop/utility.h"
+#include "utils/acl.h"
+#include "utils/builtins.h"
+#include "utils/guc.h"
+#include "utils/lsyscache.h"
+#include "utils/memutils.h"
+#include "utils/rel.h"
+#include "utils/syscache.h"
+#include "utils/timestamp.h"
+
+PG_MODULE_MAGIC;
+
+void _PG_init(void);
+
+/*
+ * Event trigger prototypes
+ */
+Datum pg_audit_ddl_command_end(PG_FUNCTION_ARGS);
+Datum pg_audit_sql_drop(PG_FUNCTION_ARGS);
+
+PG_FUNCTION_INFO_V1(pg_audit_ddl_command_end);
+PG_FUNCTION_INFO_V1(pg_audit_sql_drop);
+
+/*
+ * auditRole is the string value of the pg_audit.role GUC, which contains the
+ * role for grant-based auditing.
+ */
+char *auditRole = NULL;
+
+/*
+ * auditLog is the string value of the pg_audit.log GUC, e.g. "read, write,
ddl"
+ * (it's not used by the module but is required by DefineCustomStringVariable).
+ * Each token corresponds to a flag in enum LogClass below. We convert the list
+ * of tokens into a bitmap in auditLogBitmap for internal use.
+ */
+char *auditLog = NULL;
+static uint64 auditLogBitmap = 0;
+
+/*
+ * String constants for audit types - used when logging to distinguish session
+ * vs. object auditing.
+ */
+#define AUDIT_TYPE_OBJECT "OBJECT"
+#define AUDIT_TYPE_SESSION "SESSION"
+
+/*
+ * String constants for log classes - used when processing tokens in the
+ * pg_audit.log GUC.
+ */
+#define CLASS_DDL "DDL"
+#define CLASS_FUNCTION "FUNCTION"
+#define CLASS_MISC "MISC"
+#define CLASS_PARAMETER "PARAMETER"
+#define CLASS_READ "READ"
+#define CLASS_WRITE "WRITE"
+
+#define CLASS_ALL "ALL"
+#define CLASS_NONE "NONE"
+
+/* Log class enum used to represent bits in auditLogBitmap */
+enum LogClass
+{
+ LOG_NONE = 0,
+
+ /* DDL: CREATE/DROP/ALTER */
+ LOG_DDL = (1 << 1),
+
+ /* Function execution */
+ LOG_FUNCTION = (1 << 2),
+
+ /* Statements not covered by another class */
+ LOG_MISC = (1 << 3),
+
+ /* Function execution */
+ LOG_PARAMETER = (1 << 4),
+
+ /* SELECT */
+ LOG_READ = (1 << 5),
+
+ /* INSERT, UPDATE, DELETE, TRUNCATE */
+ LOG_WRITE = (1 << 6),
+
+ /* Absolutely everything */
+ LOG_ALL = ~(uint64)0
+};
+
+/* String constants for logging commands */
+#define COMMAND_DELETE "DELETE"
+#define COMMAND_EXECUTE "EXECUTE"
+#define COMMAND_INSERT "INSERT"
+#define COMMAND_UPDATE "UPDATE"
+#define COMMAND_SELECT "SELECT"
+
+#define COMMAND_UNKNOWN "UNKNOWN"
+
+/* String constants for logging object types */
+#define OBJECT_TYPE_COMPOSITE_TYPE "COMPOSITE TYPE"
+#define OBJECT_TYPE_FOREIGN_TABLE "FOREIGN TABLE"
+#define OBJECT_TYPE_FUNCTION "FUNCTION"
+#define OBJECT_TYPE_INDEX "INDEX"
+#define OBJECT_TYPE_TABLE "TABLE"
+#define OBJECT_TYPE_TOASTVALUE "TOASTVALUE"
+#define OBJECT_TYPE_MATVIEW "MATERIALIZED VIEW"
+#define OBJECT_TYPE_SEQUENCE "SEQUENCE"
+#define OBJECT_TYPE_VIEW "VIEW"
+
+#define OBJECT_TYPE_UNKNOWN "UNKNOWN"
+
+/*
+ * An AuditEvent represents an operation that potentially affects a single
+ * object. If a statement affects multiple objects multiple AuditEvents must be
+ * created to represent it.
+ */
+typedef struct
+{
+ int64 statementId;
+ int64 substatementId;
+
+ LogStmtLevel logStmtLevel;
+ NodeTag commandTag;
+ const char *command;
+ const char *objectType;
+ char *objectName;
+ const char *commandText;
+ ParamListInfo paramList;
+
+ bool granted;
+ bool logged;
+} AuditEvent;
+
+/*
+ * A simple FIFO queue to keep track of the current stack of audit events.
+ */
+typedef struct AuditEventStackItem
+{
+ struct AuditEventStackItem *next;
+
+ AuditEvent auditEvent;
+
+ int64 stackId;
+
+ MemoryContext contextAudit;
+ MemoryContextCallback contextCallback;
+} AuditEventStackItem;
+
+AuditEventStackItem *auditEventStack = NULL;
+
+/*
+ * Track when an internal statement is running so it is not logged
+ */
+static bool internalStatement = false;
+
+/*
+ * Track running total for statements and substatements and whether or not
+ * anything has been logged since this statement began.
+ */
+static int64 statementTotal = 0;
+static int64 substatementTotal = 0;
+static int64 stackTotal = 0;
+
+static bool statementLogged = false;
+
+/*
+ * Stack functions
+ *
+ * Audit events can go down to multiple levels so a stack is maintained to keep
+ * track of them.
+ */
+
+/*
+ * Respond to callbacks registered with MemoryContextRegisterResetCallback().
+ * Removes the event(s) off the stack that have become obsolete once the
+ * MemoryContext has been freed. The callback should always be freeing the top
+ * of the stack, but the code is tolerant of out-of-order callbacks.
+ */
+static void
+stack_free(void *stackFree)
+{
+ AuditEventStackItem *nextItem = auditEventStack;
+
+ /* Only process if the stack contains items */
+ while (nextItem != NULL)
+ {
+ /* Check if this item matches the item to be freed */
+ if (nextItem == (AuditEventStackItem *)stackFree)
+ {
+ /* Move top of stack to the item after the freed item */
+ auditEventStack = nextItem->next;
+
+ /* If the stack is not empty */
+ if (auditEventStack == NULL)
+ {
+ /* Reset internal statement in case of error */
+ internalStatement = false;
+
+ /* Reset sub statement total */
+ substatementTotal = 0;
+
+ /* Reset statement logged flag total */
+ statementLogged = false;
+ }
+
+ return;
+ }
+
+ /* Still looking, test the next item */
+ nextItem = nextItem->next;
+ }
+}
+
+/*
+ * Push a new audit event onto the stack and create a new memory context to
+ * store it.
+ */
+static AuditEventStackItem *
+stack_push()
+{
+ MemoryContext contextAudit;
+ MemoryContext contextOld;
+ AuditEventStackItem *stackItem;
+
+ /* Create a new memory context */
+ contextAudit = AllocSetContextCreate(CurrentMemoryContext,
+
"pg_audit stack context",
+
ALLOCSET_DEFAULT_MINSIZE,
+
ALLOCSET_DEFAULT_INITSIZE,
+
ALLOCSET_DEFAULT_MAXSIZE);
+ contextOld = MemoryContextSwitchTo(contextAudit);
+
+ /* Allocate the stack item */
+ stackItem = palloc0(sizeof(AuditEventStackItem));
+
+ /* Store memory contexts */
+ stackItem->contextAudit = contextAudit;
+
+ /* If item already on stack then push it down */
+ if (auditEventStack != NULL)
+ stackItem->next = auditEventStack;
+ else
+ stackItem->next = NULL;
+
+ /*
+ * Create the unique stackId - used to keep the stack sane when memory
+ * contexts are freed unexpectedly.
+ */
+ stackItem->stackId = ++stackTotal;
+
+ /*
+ * Setup a callback in case an error happens. stack_free() will
truncate
+ * the stack at this item.
+ */
+ stackItem->contextCallback.func = stack_free;
+ stackItem->contextCallback.arg = (void *)stackItem;
+ MemoryContextRegisterResetCallback(contextAudit,
+
&stackItem->contextCallback);
+
+ /* Push item on the stack */
+ auditEventStack = stackItem;
+
+ /* Return to the old memory context */
+ MemoryContextSwitchTo(contextOld);
+
+ /* Return the stack item */
+ return stackItem;
+}
+
+/*
+ * Pop an audit event from the stack by deleting the memory context that
+ * contains it. The callback to stack_free() does the actual pop.
+ */
+static void
+stack_pop(int64 stackId)
+{
+ /* Make sure what we want to delete is at the top of the stack */
+ if (auditEventStack != NULL && auditEventStack->stackId == stackId)
+ {
+ MemoryContextDelete(auditEventStack->contextAudit);
+ }
+}
+
+/*
+ * Appends a properly quoted CSV field to StringInfo.
+ */
+static void
+append_valid_csv(StringInfoData *buffer, const char *appendStr)
+{
+ const char *pChar;
+
+ /*
+ * If the append string is null then return. NULL fields are not quoted
+ * in CSV
+ */
+ if (appendStr == NULL)
+ return;
+
+ /* Only format for CSV if appendStr contains: ", comma, \n, \r */
+ if (strstr(appendStr, ",") || strstr(appendStr, "\"") ||
+ strstr(appendStr, "\n") || strstr(appendStr, "\r"))
+ {
+ appendStringInfoCharMacro(buffer, '"');
+
+ for (pChar = appendStr; *pChar; pChar++)
+ {
+ if (*pChar == '"') /* double single quotes */
+ appendStringInfoCharMacro(buffer, *pChar);
+
+ appendStringInfoCharMacro(buffer, *pChar);
+ }
+
+ appendStringInfoCharMacro(buffer, '"');
+ }
+ /* Else just append */
+ else
+ {
+ appendStringInfoString(buffer, appendStr);
+ }
+}
+
+/*
+ * Takes an AuditEvent, classifies it, then logs it if permissions were granted
+ * via roles or if the statement belongs in a class that is being logged.
+ */
+static void
+log_audit_event(AuditEventStackItem *stackItem)
+{
+ MemoryContext contextOld;
+ StringInfoData auditStr;
+
+ /* By default put everything in the MISC class. */
+ enum LogClass class = LOG_MISC;
+ const char *className = CLASS_MISC;
+
+ /* Classify the statement using log stmt level and the command tag */
+ switch (stackItem->auditEvent.logStmtLevel)
+ {
+ case LOGSTMT_MOD:
+ className = CLASS_WRITE;
+ class = LOG_WRITE;
+ break;
+
+ case LOGSTMT_DDL:
+ className = CLASS_DDL;
+ class = LOG_DDL;
+
+ case LOGSTMT_ALL:
+ switch (stackItem->auditEvent.commandTag)
+ {
+ case T_CopyStmt:
+ case T_SelectStmt:
+ case T_PrepareStmt:
+ case T_PlannedStmt:
+ case T_ExecuteStmt:
+ className = CLASS_READ;
+ class = LOG_READ;
+ break;
+
+ case T_VacuumStmt:
+ case T_ReindexStmt:
+ className = CLASS_DDL;
+ class = LOG_DDL;
+ break;
+
+ case T_DoStmt:
+ className = CLASS_FUNCTION;
+ class = LOG_FUNCTION;
+ break;
+
+ default:
+ break;
+ }
+ break;
+
+ case LOGSTMT_NONE:
+ break;
+ }
+
+ /*
+ * Only log the statement if:
+ *
+ * 1. If permissions were granted via roles
+ * 2. The statement belongs to a class that is being logged
+ */
+ if (!stackItem->auditEvent.granted && !(auditLogBitmap & class))
+ return;
+
+ /* Use audit memory context in case something is not freed */
+ contextOld = MemoryContextSwitchTo(stackItem->contextAudit);
+
+ /* Set statement and substatement Ids */
+ if (stackItem->auditEvent.statementId == 0)
+ {
+ /* If nothing has been logged yet then create a new statement
Id */
+ if (!statementLogged)
+ {
+ statementTotal++;
+ statementLogged = true;
+ }
+
+ stackItem->auditEvent.statementId = statementTotal;
+ stackItem->auditEvent.substatementId = ++substatementTotal;
+ }
+
+ /* Create the audit string */
+ initStringInfo(&auditStr);
+
+ append_valid_csv(&auditStr, stackItem->auditEvent.command);
+ appendStringInfoCharMacro(&auditStr, ',');
+
+ append_valid_csv(&auditStr, stackItem->auditEvent.objectType);
+ appendStringInfoCharMacro(&auditStr, ',');
+
+ append_valid_csv(&auditStr, stackItem->auditEvent.objectName);
+ appendStringInfoCharMacro(&auditStr, ',');
+
+ append_valid_csv(&auditStr, stackItem->auditEvent.commandText);
+
+ /* If parameter logging is turned on and there are parameters to log */
+ if (auditLogBitmap & LOG_PARAMETER &&
+ stackItem->auditEvent.paramList != NULL &&
+ stackItem->auditEvent.paramList->numParams > 0 &&
+ !IsAbortedTransactionBlockState())
+ {
+ ParamListInfo paramList = stackItem->auditEvent.paramList;
+ int paramIdx;
+
+ /* Iterate through all params */
+ for (paramIdx = 0; paramIdx < paramList->numParams; paramIdx++)
+ {
+ ParamExternData *prm = ¶mList->params[paramIdx];
+ Oid typeOutput;
+ bool typeIsVarLena;
+ char *paramStr;
+
+ /* Add a comma for each param */
+ appendStringInfoCharMacro(&auditStr, ',');
+
+ /* Skip this param if null or if oid is invalid */
+ if (prm->isnull || !OidIsValid(prm->ptype))
+ {
+ continue;
+ }
+
+ /* Output the string */
+ getTypeOutputInfo(prm->ptype, &typeOutput,
&typeIsVarLena);
+ paramStr = OidOutputFunctionCall(typeOutput,
prm->value);
+
+ append_valid_csv(&auditStr, paramStr);
+ pfree(paramStr);
+ }
+ }
+
+ /* Log the audit string */
+ ereport(LOG,
+ (errmsg("AUDIT: %s,%ld,%ld,%s,%s",
+ stackItem->auditEvent.granted ?
+ AUDIT_TYPE_OBJECT : AUDIT_TYPE_SESSION,
+ stackItem->auditEvent.statementId,
+ stackItem->auditEvent.substatementId,
+ className, auditStr.data),
+ errhidestmt(true)));
+
+ /* Mark the audit event as logged */
+ stackItem->auditEvent.logged = true;
+
+ /* Switch back to the old memory context */
+ MemoryContextSwitchTo(contextOld);
+}
+
+/*
+ * Check if the role or any inherited role has any permission in the mask. The
+ * public role is excluded from this check and superuser permissions are not
+ * considered.
+ */
+static bool
+audit_on_acl(Datum aclDatum,
+ Oid auditOid,
+ AclMode mask)
+{
+ bool result = false;
+ Acl *acl;
+ AclItem *aclItemData;
+ int aclIndex;
+ int aclTotal;
+
+ /* Detoast column's ACL if necessary */
+ acl = DatumGetAclP(aclDatum);
+
+ /* Get the acl list and total */
+ aclTotal = ACL_NUM(acl);
+ aclItemData = ACL_DAT(acl);
+
+ /* Check privileges granted directly to auditOid */
+ for (aclIndex = 0; aclIndex < aclTotal; aclIndex++)
+ {
+ AclItem *aclItem = &aclItemData[aclIndex];
+
+ if (aclItem->ai_grantee == auditOid &&
+ aclItem->ai_privs & mask)
+ {
+ result = true;
+ break;
+ }
+ }
+
+ /*
+ * Check privileges granted indirectly via role memberships. We do this
in
+ * a separate pass to minimize expensive indirect membership tests. In
+ * particular, it's worth testing whether a given ACL entry grants any
+ * privileges still of interest before we perform the has_privs_of_role
+ * test.
+ */
+ if (!result)
+ {
+ for (aclIndex = 0; aclIndex < aclTotal; aclIndex++)
+ {
+ AclItem *aclItem = &aclItemData[aclIndex];
+
+ /* Don't test public or auditOid (it has been tested
already) */
+ if (aclItem->ai_grantee == ACL_ID_PUBLIC ||
+ aclItem->ai_grantee == auditOid)
+ continue;
+
+ /*
+ * Check that the role has the required privileges and
that it is
+ * inherited by auditOid.
+ */
+ if (aclItem->ai_privs & mask &&
+ has_privs_of_role(auditOid,
aclItem->ai_grantee))
+ {
+ result = true;
+ break;
+ }
+ }
+ }
+
+ /* if we have a detoasted copy, free it */
+ if (acl && (Pointer) acl != DatumGetPointer(aclDatum))
+ pfree(acl);
+
+ return result;
+}
+
+/*
+ * Check if a role has any of the permissions in the mask on a relation.
+ */
+static bool
+audit_on_relation(Oid relOid,
+ Oid auditOid,
+ AclMode mask)
+{
+ bool result = false;
+ HeapTuple tuple;
+ Datum aclDatum;
+ bool isNull;
+
+ /* Get relation tuple from pg_class */
+ tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relOid));
+
+ /* Return false if tuple is not valid */
+ if (!HeapTupleIsValid(tuple))
+ return false;
+
+ /* Get the relation's ACL */
+ aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
+ &isNull);
+
+ /* If not null then test */
+ if (!isNull)
+ result = audit_on_acl(aclDatum, auditOid, mask);
+
+ /* Free the relation tuple */
+ ReleaseSysCache(tuple);
+
+ return result;
+}
+
+/*
+ * Check if a role has any of the permissions in the mask on an attribute.
+ */
+static bool
+audit_on_attribute(Oid relOid,
+ AttrNumber attNum,
+ Oid auditOid,
+ AclMode mask)
+{
+ bool result = false;
+ HeapTuple attTuple;
+ Datum aclDatum;
+ bool isNull;
+
+ /* Get the attribute's ACL */
+ attTuple = SearchSysCache2(ATTNUM,
+
ObjectIdGetDatum(relOid),
+
Int16GetDatum(attNum));
+
+ /* Return false if attribute is invalid */
+ if (!HeapTupleIsValid(attTuple))
+ return false;
+
+ /* Only process attribute that have not been dropped */
+ if (!((Form_pg_attribute) GETSTRUCT(attTuple))->attisdropped)
+ {
+ aclDatum = SysCacheGetAttr(ATTNUM, attTuple,
Anum_pg_attribute_attacl,
+ &isNull);
+
+ if (!isNull)
+ result = audit_on_acl(aclDatum, auditOid, mask);
+ }
+
+ /* Free attribute */
+ ReleaseSysCache(attTuple);
+
+ return result;
+}
+
+/*
+ * Check if a role has any of the permissions in the mask on an attribute in
+ * the provided set. If the set is empty, then all valid attributes in the
+ * relation will be tested.
+ */
+static bool
+audit_on_any_attribute(Oid relOid,
+ Oid auditOid,
+ Bitmapset *attributeSet,
+ AclMode mode)
+{
+ bool result = false;
+ AttrNumber col;
+ Bitmapset *tmpSet;
+
+ /* If bms is empty then check for any column match */
+ if (bms_is_empty(attributeSet))
+ {
+ HeapTuple classTuple;
+ AttrNumber nattrs;
+ AttrNumber curr_att;
+
+ /* Get relation to determine total attribute */
+ classTuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relOid));
+
+ if (!HeapTupleIsValid(classTuple))
+ return false;
+
+ nattrs = ((Form_pg_class) GETSTRUCT(classTuple))->relnatts;
+ ReleaseSysCache(classTuple);
+
+ /* Check each column */
+ for (curr_att = 1; curr_att <= nattrs; curr_att++)
+ {
+ if (audit_on_attribute(relOid, curr_att, auditOid,
mode))
+ return true;
+ }
+ }
+
+ /* bms_first_member is destructive, so make a copy before using it. */
+ tmpSet = bms_copy(attributeSet);
+
+ /* Check each column */
+ while ((col = bms_first_member(tmpSet)) >= 0)
+ {
+ col += FirstLowInvalidHeapAttributeNumber;
+
+ if (col != InvalidAttrNumber &&
+ audit_on_attribute(relOid, col, auditOid, mode))
+ {
+ result = true;
+ break;
+ }
+ }
+
+ /* Free the column set */
+ bms_free(tmpSet);
+
+ return result;
+}
+
+/*
+ * Create AuditEvents for SELECT/DML operations via executor permissions
checks.
+ */
+static void
+log_select_dml(Oid auditOid, List *rangeTabls)
+{
+ ListCell *lr;
+ bool first = true;
+ bool found = false;
+
+ /* Do not log if this is an internal statement */
+ if (internalStatement)
+ return;
+
+ foreach(lr, rangeTabls)
+ {
+ Oid relOid;
+ Relation rel;
+ RangeTblEntry *rte = lfirst(lr);
+
+ /* We only care about tables, and can ignore subqueries etc. */
+ if (rte->rtekind != RTE_RELATION)
+ continue;
+
+ found = true;
+
+ /*
+ * Filter out any system relations
+ */
+ relOid = rte->relid;
+ rel = relation_open(relOid, NoLock);
+
+ if (IsSystemNamespace(RelationGetNamespace(rel)))
+ {
+ relation_close(rel, NoLock);
+ continue;
+ }
+
+ /*
+ * We don't have access to the parsetree here, so we have to
generate
+ * the node type, object type, and command tag by decoding
+ * rte->requiredPerms and rte->relkind.
+ */
+ if (rte->requiredPerms & ACL_INSERT)
+ {
+ auditEventStack->auditEvent.logStmtLevel = LOGSTMT_MOD;
+ auditEventStack->auditEvent.commandTag = T_InsertStmt;
+ auditEventStack->auditEvent.command = COMMAND_INSERT;
+ }
+ else if (rte->requiredPerms & ACL_UPDATE)
+ {
+ auditEventStack->auditEvent.logStmtLevel = LOGSTMT_MOD;
+ auditEventStack->auditEvent.commandTag = T_UpdateStmt;
+ auditEventStack->auditEvent.command = COMMAND_UPDATE;
+ }
+ else if (rte->requiredPerms & ACL_DELETE)
+ {
+ auditEventStack->auditEvent.logStmtLevel = LOGSTMT_MOD;
+ auditEventStack->auditEvent.commandTag = T_DeleteStmt;
+ auditEventStack->auditEvent.command = COMMAND_DELETE;
+ }
+ else if (rte->requiredPerms & ACL_SELECT)
+ {
+ auditEventStack->auditEvent.logStmtLevel = LOGSTMT_ALL;
+ auditEventStack->auditEvent.commandTag = T_SelectStmt;
+ auditEventStack->auditEvent.command = COMMAND_SELECT;
+ }
+ else
+ {
+ auditEventStack->auditEvent.logStmtLevel = LOGSTMT_ALL;
+ auditEventStack->auditEvent.commandTag = T_Invalid;
+ auditEventStack->auditEvent.command = COMMAND_UNKNOWN;
+ }
+
+ /*
+ * Fill values in the event struct that are required for session
+ * logging.
+ */
+ auditEventStack->auditEvent.granted = false;
+
+ /* If this is the first rte then session log */
+ if (first)
+ {
+ auditEventStack->auditEvent.objectName = "";
+ auditEventStack->auditEvent.objectType = "";
+
+ log_audit_event(auditEventStack);
+
+ first = false;
+ }
+
+ /* Get the relation type */
+ switch (rte->relkind)
+ {
+ case RELKIND_RELATION:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_TABLE;
+ break;
+
+ case RELKIND_INDEX:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_INDEX;
+ break;
+
+ case RELKIND_SEQUENCE:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_SEQUENCE;
+ break;
+
+ case RELKIND_TOASTVALUE:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_TOASTVALUE;
+ break;
+
+ case RELKIND_VIEW:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_VIEW;
+ break;
+
+ case RELKIND_COMPOSITE_TYPE:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_COMPOSITE_TYPE;
+ break;
+
+ case RELKIND_FOREIGN_TABLE:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_FOREIGN_TABLE;
+ break;
+
+ case RELKIND_MATVIEW:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_MATVIEW;
+ break;
+
+ default:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_UNKNOWN;
+ break;
+ }
+
+ /* Get the relation name */
+ auditEventStack->auditEvent.objectName =
+ quote_qualified_identifier(get_namespace_name(
+
RelationGetNamespace(rel)),
+
RelationGetRelationName(rel));
+ relation_close(rel, NoLock);
+
+ /* Perform object auditing only if the audit role is valid */
+ if (auditOid != InvalidOid)
+ {
+ AclMode auditPerms = (ACL_SELECT | ACL_UPDATE |
ACL_INSERT) &
+
rte->requiredPerms;
+
+ /*
+ * If any of the required permissions for the relation
are granted
+ * to the audit role then audit the relation
+ */
+ if (audit_on_relation(relOid, auditOid, auditPerms))
+ {
+ auditEventStack->auditEvent.granted = true;
+ }
+
+ /*
+ * Else check if the audit role has column-level
permissions for
+ * select, insert, or update.
+ */
+ else if (auditPerms != 0)
+ {
+ /*
+ * Check the select columns to see if the audit
role has
+ * priveleges on any of them.
+ */
+ if (auditPerms & ACL_SELECT)
+ {
+ auditEventStack->auditEvent.granted =
+ audit_on_any_attribute(relOid,
auditOid,
+
rte->selectedCols,
+
ACL_SELECT);
+ }
+
+ /*
+ * Check the modified columns to see if the
audit role has
+ * privileges on any of them.
+ */
+ if (!auditEventStack->auditEvent.granted)
+ {
+ auditPerms &= (ACL_INSERT | ACL_UPDATE);
+
+ if (auditPerms)
+ {
+
auditEventStack->auditEvent.granted =
+
audit_on_any_attribute(relOid, auditOid,
+
rte->modifiedCols,
+
auditPerms);
+ }
+ }
+ }
+ }
+
+ /* Only do relation level logging if a grant was found. */
+ if (auditEventStack->auditEvent.granted)
+ {
+ auditEventStack->auditEvent.logged = false;
+ log_audit_event(auditEventStack);
+ }
+
+ pfree(auditEventStack->auditEvent.objectName);
+ }
+
+ /*
+ * If no tables were found that means that RangeTbls was empty or all
+ * relations were in the system schema. In that case still log a
+ * session record.
+ */
+ if (!found)
+ {
+ auditEventStack->auditEvent.granted = false;
+ auditEventStack->auditEvent.logged = false;
+
+ log_audit_event(auditEventStack);
+ }
+}
+
+/*
+ * Create AuditEvents for certain kinds of CREATE, ALTER, and DELETE statements
+ * where the object can be logged.
+ */
+static void
+log_create_alter_drop(Oid classId,
+ Oid objectId)
+{
+ /* Only perform when class is relation */
+ if (classId == RelationRelationId)
+ {
+ Relation rel;
+ Form_pg_class class;
+
+ /* Open the relation */
+ rel = relation_open(objectId, NoLock);
+
+ /* Filter out any system relations */
+ if (IsToastNamespace(RelationGetNamespace(rel)))
+ {
+ relation_close(rel, NoLock);
+ return;
+ }
+
+ /* Get rel information and close it */
+ class = RelationGetForm(rel);
+ auditEventStack->auditEvent.objectName =
+ quote_qualified_identifier(get_namespace_name(
+
RelationGetNamespace(rel)),
+
RelationGetRelationName(rel));
+ relation_close(rel, NoLock);
+
+ /* Set object type based on relkind */
+ switch (class->relkind)
+ {
+ case RELKIND_RELATION:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_TABLE;
+ break;
+
+ case RELKIND_INDEX:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_INDEX;
+ break;
+
+ case RELKIND_SEQUENCE:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_SEQUENCE;
+ break;
+
+ case RELKIND_VIEW:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_VIEW;
+ break;
+
+ case RELKIND_COMPOSITE_TYPE:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_COMPOSITE_TYPE;
+ break;
+
+ case RELKIND_FOREIGN_TABLE:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_FOREIGN_TABLE;
+ break;
+
+ case RELKIND_MATVIEW:
+ auditEventStack->auditEvent.objectType =
+ OBJECT_TYPE_MATVIEW;
+ break;
+
+ /*
+ * Any other cases will be handled by
log_utility_command().
+ */
+ default:
+ return;
+ break;
+ }
+ }
+}
+
+/*
+ * Create AuditEvents for non-catalog function execution, as detected by
+ * log_object_access() below.
+ */
+static void
+log_function_execute(Oid objectId)
+{
+ HeapTuple proctup;
+ Form_pg_proc proc;
+ AuditEventStackItem *stackItem;
+
+ /* Get info about the function. */
+ proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(objectId));
+
+ if (!proctup)
+ elog(ERROR, "cache lookup failed for function %u", objectId);
+ proc = (Form_pg_proc) GETSTRUCT(proctup);
+
+ /*
+ * Logging execution of all pg_catalog functions would make the log
+ * unusably noisy.
+ */
+ if (IsSystemNamespace(proc->pronamespace))
+ {
+ ReleaseSysCache(proctup);
+ return;
+ }
+
+ /* Push audit event onto the stack */
+ stackItem = stack_push();
+
+ /* Generate the fully-qualified function name. */
+ stackItem->auditEvent.objectName =
+
quote_qualified_identifier(get_namespace_name(proc->pronamespace),
+
NameStr(proc->proname));
+ ReleaseSysCache(proctup);
+
+ /* Log the function call */
+ stackItem->auditEvent.logStmtLevel = LOGSTMT_ALL;
+ stackItem->auditEvent.commandTag = T_DoStmt;
+ stackItem->auditEvent.command = COMMAND_EXECUTE;
+ stackItem->auditEvent.objectType = OBJECT_TYPE_FUNCTION;
+ stackItem->auditEvent.commandText =
stackItem->next->auditEvent.commandText;
+
+ log_audit_event(stackItem);
+
+ /* Pop audit event from the stack */
+ stack_pop(stackItem->stackId);
+}
+
+/*
+ * Log object accesses (which is more about DDL than DML, even though it
+ * sounds like the latter).
+ */
+static void
+log_object_access(ObjectAccessType access,
+ Oid classId,
+ Oid objectId,
+ int subId,
+ void *arg)
+{
+ switch (access)
+ {
+ /* Log execute */
+ case OAT_FUNCTION_EXECUTE:
+ if (auditLogBitmap & LOG_FUNCTION)
+ log_function_execute(objectId);
+ break;
+
+ /* Log create */
+ case OAT_POST_CREATE:
+ if (auditLogBitmap & LOG_DDL)
+ {
+ ObjectAccessPostCreate *pc = arg;
+
+ if (pc->is_internal)
+ return;
+
+ log_create_alter_drop(classId, objectId);
+ }
+ break;
+
+ /* Log alter */
+ case OAT_POST_ALTER:
+ if (auditLogBitmap & LOG_DDL)
+ {
+ ObjectAccessPostAlter *pa = arg;
+
+ if (pa->is_internal)
+ return;
+
+ log_create_alter_drop(classId, objectId);
+ }
+ break;
+
+ /* Log drop */
+ case OAT_DROP:
+ if (auditLogBitmap & LOG_DDL)
+ {
+ ObjectAccessDrop *drop = arg;
+
+ if (drop->dropflags & PERFORM_DELETION_INTERNAL)
+ return;
+
+ log_create_alter_drop(classId, objectId);
+ }
+ break;
+
+ /* All others processed by log_utility_command() */
+ default:
+ break;
+ }
+}
+
+/*
+ * Hook functions
+ */
+static ExecutorCheckPerms_hook_type next_ExecutorCheckPerms_hook = NULL;
+static ProcessUtility_hook_type next_ProcessUtility_hook = NULL;
+static object_access_hook_type next_object_access_hook = NULL;
+static ExecutorStart_hook_type next_ExecutorStart_hook = NULL;
+static ExecutorEnd_hook_type next_ExecutorEnd_hook = NULL;
+
+/*
+ * Hook ExecutorStart to get the query text and basic command type for queries
+ * that do not contain a table so can't be idenitified accurately in
+ * ExecutorCheckPerms.
+ */
+static void
+pg_audit_ExecutorStart_hook(QueryDesc *queryDesc, int eflags)
+{
+ AuditEventStackItem *stackItem = NULL;
+
+ if (!internalStatement)
+ {
+ /* Allocate the audit event */
+ stackItem = stack_push();
+
+ /* Initialize command */
+ switch (queryDesc->operation)
+ {
+ case CMD_SELECT:
+ stackItem->auditEvent.logStmtLevel =
LOGSTMT_ALL;
+ stackItem->auditEvent.commandTag = T_SelectStmt;
+ stackItem->auditEvent.command = COMMAND_SELECT;
+ break;
+
+ case CMD_INSERT:
+ stackItem->auditEvent.logStmtLevel =
LOGSTMT_MOD;
+ stackItem->auditEvent.commandTag = T_InsertStmt;
+ stackItem->auditEvent.command = COMMAND_INSERT;
+ break;
+
+ case CMD_UPDATE:
+ stackItem->auditEvent.logStmtLevel =
LOGSTMT_MOD;
+ stackItem->auditEvent.commandTag = T_UpdateStmt;
+ stackItem->auditEvent.command = COMMAND_UPDATE;
+ break;
+
+ case CMD_DELETE:
+ stackItem->auditEvent.logStmtLevel =
LOGSTMT_MOD;
+ stackItem->auditEvent.commandTag = T_DeleteStmt;
+ stackItem->auditEvent.command = COMMAND_DELETE;
+ break;
+
+ default:
+ stackItem->auditEvent.logStmtLevel =
LOGSTMT_ALL;
+ stackItem->auditEvent.commandTag = T_Invalid;
+ stackItem->auditEvent.command = COMMAND_UNKNOWN;
+ break;
+ }
+
+ /* Initialize the audit event */
+ stackItem->auditEvent.objectName = "";
+ stackItem->auditEvent.objectType = "";
+ stackItem->auditEvent.commandText = queryDesc->sourceText;
+ stackItem->auditEvent.paramList = queryDesc->params;
+ }
+
+ /* Call the previous hook or standard function */
+ if (next_ExecutorStart_hook)
+ next_ExecutorStart_hook(queryDesc, eflags);
+ else
+ standard_ExecutorStart(queryDesc, eflags);
+}
+
+/*
+ * Hook ExecutorCheckPerms to do session and object auditing for DML.
+ */
+static bool
+pg_audit_ExecutorCheckPerms_hook(List *rangeTabls, bool abort)
+{
+ Oid auditOid;
+
+ /* Get the audit oid if the role exists. */
+ auditOid = get_role_oid(auditRole, true);
+
+ /* Log DML if the audit role is valid or session logging is enabled. */
+ if ((auditOid != InvalidOid || auditLogBitmap != 0) &&
+ !IsAbortedTransactionBlockState())
+ log_select_dml(auditOid, rangeTabls);
+
+ /* Call the next hook function. */
+ if (next_ExecutorCheckPerms_hook &&
+ !(*next_ExecutorCheckPerms_hook) (rangeTabls, abort))
+ return false;
+
+ return true;
+}
+
+/*
+ * Hook ExecutorEnd to pop statement audit event off the stack.
+ */
+static void
+pg_audit_ExecutorEnd_hook(QueryDesc *queryDesc)
+{
+ /* Call the next hook or standard function */
+ if (next_ExecutorEnd_hook)
+ next_ExecutorEnd_hook(queryDesc);
+ else
+ standard_ExecutorEnd(queryDesc);
+
+ /* Pop the audit event off the stack */
+ if (!internalStatement)
+ {
+ stack_pop(auditEventStack->stackId);
+ }
+}
+
+/*
+ * Hook ProcessUtility to do session auditing for DDL and utility commands.
+ */
+static void
+pg_audit_ProcessUtility_hook(Node *parsetree,
+ const char
*queryString,
+ ProcessUtilityContext
context,
+ ParamListInfo params,
+ DestReceiver *dest,
+ char *completionTag)
+{
+ AuditEventStackItem *stackItem = NULL;
+ int64 stackId;
+
+ /* Allocate the audit event */
+ if (!IsAbortedTransactionBlockState())
+ {
+ /* Process top level utility statement */
+ if (context == PROCESS_UTILITY_TOPLEVEL)
+ {
+ if (auditEventStack != NULL)
+ elog(ERROR, "pg_audit stack is not empty");
+
+ /* Set params */
+ stackItem = stack_push();
+ stackItem->auditEvent.paramList = params;
+ }
+ else
+ stackItem = stack_push();
+
+ stackId = stackItem->stackId;
+ stackItem->auditEvent.logStmtLevel =
GetCommandLogLevel(parsetree);
+ stackItem->auditEvent.commandTag = nodeTag(parsetree);
+ stackItem->auditEvent.command = CreateCommandTag(parsetree);
+ stackItem->auditEvent.objectName = "";
+ stackItem->auditEvent.objectType = "";
+ stackItem->auditEvent.commandText = queryString;
+
+ /*
+ * If this is a DO block log it before calling the next
ProcessUtility
+ * hook.
+ */
+ if (auditLogBitmap != 0 &&
+ stackItem->auditEvent.commandTag == T_DoStmt &&
+ !IsAbortedTransactionBlockState())
+ {
+ log_audit_event(stackItem);
+ }
+ }
+
+ /* Call the standard process utility chain. */
+ if (next_ProcessUtility_hook)
+ (*next_ProcessUtility_hook) (parsetree, queryString, context,
+
params, dest, completionTag);
+ else
+ standard_ProcessUtility(parsetree, queryString, context,
+ params, dest,
completionTag);
+
+ /* Process the audit event if there is one. */
+ if (stackItem != NULL)
+ {
+ /* Log the utility command if logging is on, the command has
not already
+ * been logged by another hook, and the transaction is not
aborted. */
+ if (auditLogBitmap != 0 && !stackItem->auditEvent.logged &&
+ !IsAbortedTransactionBlockState())
+ log_audit_event(stackItem);
+
+ stack_pop(stackId);
+ }
+}
+
+/*
+ * Hook object_access_hook to provide fully-qualified object names for execute,
+ * create, drop, and alter commands. Most of the audit information is filled
in
+ * by log_utility_command().
+ */
+static void
+pg_audit_object_access_hook(ObjectAccessType access,
+ Oid classId,
+ Oid objectId,
+ int subId,
+ void *arg)
+{
+ if (auditLogBitmap != 0 && !IsAbortedTransactionBlockState() &&
+ auditLogBitmap & (LOG_DDL | LOG_FUNCTION))
+ log_object_access(access, classId, objectId, subId, arg);
+
+ if (next_object_access_hook)
+ (*next_object_access_hook) (access, classId, objectId, subId,
arg);
+}
+
+/*
+ * Event trigger functions
+ */
+
+/*
+ * Supply additional data for (non drop) statements that have event trigger
+ * support and can be deparsed.
+ */
+Datum
+pg_audit_ddl_command_end(PG_FUNCTION_ARGS)
+{
+ /* Continue only if session logging is enabled */
+ if (auditLogBitmap != LOG_DDL)
+ {
+ EventTriggerData *eventData;
+ int result, row;
+ TupleDesc spiTupDesc;
+ const char *query;
+ MemoryContext contextQuery;
+ MemoryContext contextOld;
+
+ /* This is an internal statement - do not log it */
+ internalStatement = true;
+
+ /* Make sure the fuction was fired as a trigger */
+ if (!CALLED_AS_EVENT_TRIGGER(fcinfo))
+ elog(ERROR, "not fired by event trigger manager");
+
+ /* Switch memory context */
+ contextQuery = AllocSetContextCreate(
+ CurrentMemoryContext,
+ "pg_audit_func_ddl_command_end
temporary context",
+ ALLOCSET_DEFAULT_MINSIZE,
+ ALLOCSET_DEFAULT_INITSIZE,
+ ALLOCSET_DEFAULT_MAXSIZE);
+ contextOld = MemoryContextSwitchTo(contextQuery);
+
+ /* Get information about triggered events */
+ eventData = (EventTriggerData *) fcinfo->context;
+
+ /* Return objects affected by the (non drop) DDL statement */
+ query = "SELECT classid, objid, objsubid, UPPER(object_type),
schema,\n"
+ " identity, command\n"
+ " FROM
pg_event_trigger_get_creation_commands()";
+
+ /* Attempt to connect */
+ result = SPI_connect();
+
+ if (result < 0)
+ elog(ERROR, "pg_audit_ddl_command_end: SPI_connect
returned %d",
+ result);
+
+ /* Execute the query */
+ result = SPI_execute(query, true, 0);
+
+ if (result != SPI_OK_SELECT)
+ elog(ERROR, "pg_audit_ddl_command_end: SPI_execute
returned %d",
+ result);
+
+ /* Iterate returned rows */
+ spiTupDesc = SPI_tuptable->tupdesc;
+
+ for (row = 0; row < SPI_processed; row++)
+ {
+ HeapTuple spiTuple;
+ bool isNull;
+
+ spiTuple = SPI_tuptable->vals[row];
+
+ /* Supply addition data to current audit event */
+ auditEventStack->auditEvent.logStmtLevel =
+ GetCommandLogLevel(eventData->parsetree);
+ auditEventStack->auditEvent.commandTag =
+ nodeTag(eventData->parsetree);
+ auditEventStack->auditEvent.command =
+ CreateCommandTag(eventData->parsetree);
+ auditEventStack->auditEvent.objectName =
+ SPI_getvalue(spiTuple, spiTupDesc, 6);
+ auditEventStack->auditEvent.objectType =
+ SPI_getvalue(spiTuple, spiTupDesc, 4);
+ auditEventStack->auditEvent.commandText =
+ TextDatumGetCString(
+
DirectFunctionCall1(pg_event_trigger_expand_command,
+
SPI_getbinval(spiTuple, spiTupDesc,
+
7, &isNull)));
+
+ /* Log the audit event */
+ log_audit_event(auditEventStack);
+ }
+
+ /* Complete the query */
+ SPI_finish();
+
+ /* Switch to the old memory context */
+ MemoryContextSwitchTo(contextOld);
+ MemoryContextDelete(contextQuery);
+
+ /* No longer in an internal statement */
+ internalStatement = false;
+ }
+
+ PG_RETURN_NULL();
+}
+
+/*
+ * Supply additional data for drop statements that have event trigger support.
+ */
+Datum
+pg_audit_sql_drop(PG_FUNCTION_ARGS)
+{
+ if (auditLogBitmap & LOG_DDL)
+ {
+ int result, row;
+ TupleDesc spiTupDesc;
+ const char *query;
+ MemoryContext contextQuery;
+ MemoryContext contextOld;
+
+ /* This is an internal statement - do not log it */
+ internalStatement = true;
+
+ /* Make sure the fuction was fired as a trigger */
+ if (!CALLED_AS_EVENT_TRIGGER(fcinfo))
+ elog(ERROR, "not fired by event trigger manager");
+
+ /* Switch memory context */
+ contextQuery = AllocSetContextCreate(
+ CurrentMemoryContext,
+ "pg_audit_func_ddl_command_end
temporary context",
+ ALLOCSET_DEFAULT_MINSIZE,
+ ALLOCSET_DEFAULT_INITSIZE,
+ ALLOCSET_DEFAULT_MAXSIZE);
+ contextOld = MemoryContextSwitchTo(contextQuery);
+
+ /* Return objects affected by the drop statement */
+ query = "SELECT classid, objid, objsubid, UPPER(object_type),\n"
+ " schema_name, object_name,
object_identity\n"
+ " FROM pg_event_trigger_dropped_objects()";
+
+ /* Attempt to connect */
+ result = SPI_connect();
+
+ if (result < 0)
+ elog(ERROR, "pg_audit_ddl_drop: SPI_connect returned
%d",
+ result);
+
+ /* Execute the query */
+ result = SPI_execute(query, true, 0);
+
+ if (result != SPI_OK_SELECT)
+ elog(ERROR, "pg_audit_ddl_drop: SPI_execute returned
%d",
+ result);
+
+ /* Iterate returned rows */
+ spiTupDesc = SPI_tuptable->tupdesc;
+
+ for (row = 0; row < SPI_processed; row++)
+ {
+ HeapTuple spiTuple;
+ char *schemaName;
+
+ spiTuple = SPI_tuptable->vals[row];
+
+ auditEventStack->auditEvent.objectType =
+ SPI_getvalue(spiTuple, spiTupDesc, 4);
+ schemaName = SPI_getvalue(spiTuple, spiTupDesc, 5);
+
+ if
(!(pg_strcasecmp(auditEventStack->auditEvent.objectType,
+ "TYPE") == 0 ||
+ pg_strcasecmp(schemaName, "pg_toast") == 0))
+ {
+ auditEventStack->auditEvent.objectName =
+ SPI_getvalue(spiTuple,
spiTupDesc, 7);
+
+ log_audit_event(auditEventStack);
+ }
+ }
+
+ /* Complete the query */
+ SPI_finish();
+
+ /* Switch to the old memory context */
+ MemoryContextSwitchTo(contextOld);
+ MemoryContextDelete(contextQuery);
+
+ /* No longer in an internal statement */
+ internalStatement = false;
+ }
+
+ PG_RETURN_NULL();
+}
+
+/*
+ * GUC check and assign functions
+ */
+
+/*
+ * Take a pg_audit.log value such as "read, write, dml", verify that each of
the
+ * comma-separated tokens corresponds to a LogClass value, and convert them
into
+ * a bitmap that log_audit_event can check.
+ */
+static bool
+check_pg_audit_log(char **newval, void **extra, GucSource source)
+{
+ List *flags;
+ char *rawval;
+ ListCell *lt;
+ uint64 *f;
+
+ /* Make sure newval is a comma-separated list of tokens. */
+ rawval = pstrdup(*newval);
+ if (!SplitIdentifierString(rawval, ',', &flags))
+ {
+ GUC_check_errdetail("List syntax is invalid");
+ list_free(flags);
+ pfree(rawval);
+ return false;
+ }
+
+ /*
+ * Check that we recognise each token, and add it to the bitmap we're
+ * building up in a newly-allocated uint64 *f.
+ */
+ f = (uint64 *) malloc(sizeof(uint64));
+ if (!f)
+ return false;
+ *f = 0;
+
+ foreach(lt, flags)
+ {
+ bool subtract = false;
+ uint64 class;
+
+ /* Retrieve a token */
+ char *token = (char *)lfirst(lt);
+
+ /* If token is preceded by -, then then token is subtractive. */
+ if (strstr(token, "-") == token)
+ {
+ token = token + 1;
+ subtract = true;
+ }
+
+ /* Test each token. */
+ if (pg_strcasecmp(token, CLASS_NONE) == 0)
+ class = LOG_NONE;
+ else if (pg_strcasecmp(token, CLASS_ALL) == 0)
+ class = LOG_ALL;
+ else if (pg_strcasecmp(token, CLASS_DDL) == 0)
+ class = LOG_DDL;
+ else if (pg_strcasecmp(token, CLASS_FUNCTION) == 0)
+ class = LOG_FUNCTION;
+ else if (pg_strcasecmp(token, CLASS_MISC) == 0)
+ class = LOG_MISC;
+ else if (pg_strcasecmp(token, CLASS_PARAMETER) == 0)
+ class = LOG_PARAMETER;
+ else if (pg_strcasecmp(token, CLASS_READ) == 0)
+ class = LOG_READ;
+ else if (pg_strcasecmp(token, CLASS_WRITE) == 0)
+ class = LOG_WRITE;
+ else
+ {
+ free(f);
+ pfree(rawval);
+ list_free(flags);
+ return false;
+ }
+
+ /* Add or subtract class bits from the log bitmap. */
+ if (subtract)
+ *f &= ~class;
+ else
+ *f |= class;
+ }
+
+ pfree(rawval);
+ list_free(flags);
+
+ /*
+ * Store the bitmap for assign_pg_audit_log.
+ */
+ *extra = f;
+
+ return true;
+}
+
+/*
+ * Set pg_audit_log from extra (ignoring newval, which has already been
+ * converted to a bitmap above). Note that extra may not be set if the
+ * assignment is to be suppressed.
+ */
+static void
+assign_pg_audit_log(const char *newval, void *extra)
+{
+ if (extra)
+ auditLogBitmap = *(uint64 *)extra;
+}
+
+/*
+ * Define GUC variables and install hooks upon module load.
+ */
+void
+_PG_init(void)
+{
+ if (IsUnderPostmaster)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("pg_audit must be loaded via
shared_preload_libraries")));
+
+ /*
+ * pg_audit.role = "audit"
+ *
+ * This variable defines a role to be used for auditing.
+ */
+ DefineCustomStringVariable("pg_audit.role",
+ "Enable auditing for
role",
+ NULL,
+ &auditRole,
+ "",
+ PGC_SUSET,
+ GUC_NOT_IN_SAMPLE,
+ NULL, NULL, NULL);
+
+ /*
+ * pg_audit.log = "read, write, ddl"
+ *
+ * This variables controls what classes of commands are logged.
+ */
+ DefineCustomStringVariable("pg_audit.log",
+ "Enable auditing for
classes of commands",
+ NULL,
+ &auditLog,
+ "none",
+ PGC_SUSET,
+ GUC_LIST_INPUT |
GUC_NOT_IN_SAMPLE,
+ check_pg_audit_log,
+ assign_pg_audit_log,
+ NULL);
+
+ /*
+ * Install our hook functions after saving the existing pointers to
preserve
+ * the chain.
+ */
+ next_ExecutorStart_hook = ExecutorStart_hook;
+ ExecutorStart_hook = pg_audit_ExecutorStart_hook;
+
+ next_ExecutorCheckPerms_hook = ExecutorCheckPerms_hook;
+ ExecutorCheckPerms_hook = pg_audit_ExecutorCheckPerms_hook;
+
+ next_ExecutorEnd_hook = ExecutorEnd_hook;
+ ExecutorEnd_hook = pg_audit_ExecutorEnd_hook;
+
+ next_ProcessUtility_hook = ProcessUtility_hook;
+ ProcessUtility_hook = pg_audit_ProcessUtility_hook;
+
+ next_object_access_hook = object_access_hook;
+ object_access_hook = pg_audit_object_access_hook;
+}
diff --git a/contrib/pg_audit/pg_audit.control
b/contrib/pg_audit/pg_audit.control
new file mode 100644
index 0000000..6730c68
--- /dev/null
+++ b/contrib/pg_audit/pg_audit.control
@@ -0,0 +1,5 @@
+# pg_audit extension
+comment = 'provides auditing functionality'
+default_version = '1.0.0'
+module_pathname = '$libdir/pg_audit'
+relocatable = true
diff --git a/contrib/pg_audit/test/test.pl b/contrib/pg_audit/test/test.pl
new file mode 100755
index 0000000..b3f2443
--- /dev/null
+++ b/contrib/pg_audit/test/test.pl
@@ -0,0 +1,1433 @@
+#!/usr/bin/perl
+################################################################################
+# test.pl - pg_audit Unit Tests
+################################################################################
+
+################################################################################
+# Perl includes
+################################################################################
+use strict;
+use warnings;
+use Carp;
+
+use Getopt::Long;
+use Pod::Usage;
+use DBI;
+use Cwd qw(abs_path);
+use IPC::System::Simple qw(capture);
+
+################################################################################
+# Constants
+################################################################################
+use constant
+{
+ true => 1,
+ false => 0
+};
+
+use constant
+{
+ CONTEXT_GLOBAL => 'GLOBAL',
+ CONTEXT_DATABASE => 'DATABASE',
+ CONTEXT_ROLE => 'ROLE'
+};
+
+use constant
+{
+ CLASS => 'CLASS',
+
+ CLASS_DDL => 'DDL',
+ CLASS_FUNCTION => 'FUNCTION',
+ CLASS_MISC => 'MISC',
+ CLASS_PARAMETER => 'PARAMETER',
+ CLASS_READ => 'READ',
+ CLASS_WRITE => 'WRITE',
+
+ CLASS_ALL => 'ALL',
+ CLASS_NONE => 'NONE'
+};
+
+use constant
+{
+ COMMAND => 'COMMAND',
+ COMMAND_LOG => 'COMMAND_LOG',
+
+ COMMAND_ANALYZE => 'ANALYZE',
+ COMMAND_ALTER_AGGREGATE => 'ALTER AGGREGATE',
+ COMMAND_ALTER_COLLATION => 'ALTER COLLATION',
+ COMMAND_ALTER_CONVERSION => 'ALTER CONVERSION',
+ COMMAND_ALTER_DATABASE => 'ALTER DATABASE',
+ COMMAND_ALTER_ROLE => 'ALTER ROLE',
+ COMMAND_ALTER_ROLE_SET => 'ALTER ROLE SET',
+ COMMAND_ALTER_TABLE => 'ALTER TABLE',
+ COMMAND_ALTER_TABLE_COLUMN => 'ALTER TABLE COLUMN',
+ COMMAND_ALTER_TABLE_INDEX => 'ALTER TABLE INDEX',
+ COMMAND_BEGIN => 'BEGIN',
+ COMMAND_CLOSE => 'CLOSE CURSOR',
+ COMMAND_COMMIT => 'COMMIT',
+ COMMAND_COPY => 'COPY',
+ COMMAND_COPY_TO => 'COPY TO',
+ COMMAND_COPY_FROM => 'COPY FROM',
+ COMMAND_CREATE_AGGREGATE => 'CREATE AGGREGATE',
+ COMMAND_CREATE_COLLATION => 'CREATE COLLATION',
+ COMMAND_CREATE_CONVERSION => 'CREATE CONVERSION',
+ COMMAND_CREATE_DATABASE => 'CREATE DATABASE',
+ COMMAND_CREATE_INDEX => 'CREATE INDEX',
+ COMMAND_DEALLOCATE => 'DEALLOCATE',
+ COMMAND_DECLARE_CURSOR => 'DECLARE CURSOR',
+ COMMAND_DO => 'DO',
+ COMMAND_DISCARD_ALL => 'DISCARD ALL',
+ COMMAND_CREATE_FUNCTION => 'CREATE FUNCTION',
+ COMMAND_CREATE_ROLE => 'CREATE ROLE',
+ COMMAND_CREATE_SCHEMA => 'CREATE SCHEMA',
+ COMMAND_CREATE_TABLE => 'CREATE TABLE',
+ COMMAND_CREATE_TABLE_AS => 'CREATE TABLE AS',
+ COMMAND_DROP_DATABASE => 'DROP DATABASE',
+ COMMAND_DROP_SCHEMA => 'DROP SCHEMA',
+ COMMAND_DROP_TABLE => 'DROP TABLE',
+ COMMAND_DROP_TABLE_CONSTRAINT => 'DROP TABLE CONSTRAINT',
+ COMMAND_DROP_TABLE_INDEX => 'DROP TABLE INDEX',
+ COMMAND_DROP_TABLE_TOAST => 'DROP TABLE TOAST',
+ COMMAND_DROP_TABLE_TYPE => 'DROP TABLE TYPE',
+ COMMAND_EXECUTE => 'EXECUTE',
+ COMMAND_EXECUTE_READ => 'EXECUTE READ',
+ COMMAND_EXECUTE_WRITE => 'EXECUTE WRITE',
+ COMMAND_EXECUTE_FUNCTION => 'EXECUTE FUNCTION',
+ COMMAND_EXPLAIN => 'EXPLAIN',
+ COMMAND_FETCH => 'FETCH',
+ COMMAND_GRANT => 'GRANT',
+ COMMAND_INSERT => 'INSERT',
+ # COMMAND_PARAMETER => 'PARAMETER',
+ # COMMAND_PARAMETER_READ => 'PARAMETER_READ',
+ # COMMAND_PARAMETER_WRITE => 'PARAMETER_WRITE',
+ COMMAND_PREPARE => 'PREPARE',
+ COMMAND_PREPARE_READ => 'PREPARE READ',
+ COMMAND_PREPARE_WRITE => 'PREPARE WRITE',
+ COMMAND_REVOKE => 'REVOKE',
+ COMMAND_SELECT => 'SELECT',
+ COMMAND_SET => 'SET',
+ COMMAND_UPDATE => 'UPDATE'
+};
+
+use constant
+{
+ TYPE => 'TYPE',
+ TYPE_NONE => '',
+
+ TYPE_AGGREGATE => 'AGGREGATE',
+ TYPE_COLLATION => 'COLLATION',
+ TYPE_CONVERSION => 'CONVERSION',
+ TYPE_SCHEMA => 'SCHEMA',
+ TYPE_FUNCTION => 'FUNCTION',
+ TYPE_INDEX => 'INDEX',
+ TYPE_TABLE => 'TABLE',
+ TYPE_TABLE_COLUMN => 'TABLE COLUMN',
+ TYPE_TABLE_CONSTRAINT => 'TABLE CONSTRAINT',
+ TYPE_TABLE_TOAST => 'TABLE TOAST',
+ TYPE_TYPE => 'TYPE'
+};
+
+use constant
+{
+ NAME => 'NAME'
+};
+
+################################################################################
+# Command line parameters
+################################################################################
+my $strPgSqlBin = '../../../../bin/bin'; # Path of PG binaries to use for
+
# this test
+my $strTestPath = '../../../../data'; # Path where testing will occur
+my $iDefaultPort = 6000; # Default port
to run Postgres on
+my $bHelp = false; #
Display help
+my $bQuiet = false; #
Supress output except for errors
+my $bNoCleanup = false; #
Cleanup database on exit
+
+GetOptions ('q|quiet' => \$bQuiet,
+ 'no-cleanup' => \$bNoCleanup,
+ 'help' => \$bHelp,
+ 'pgsql-bin=s' => \$strPgSqlBin,
+ 'test-path=s' => \$strTestPath)
+ or pod2usage(2);
+
+# Display version and exit if requested
+if ($bHelp)
+{
+ print 'pg_audit unit test\n\n';
+ pod2usage();
+
+ exit 0;
+}
+
+################################################################################
+# Global variables
+################################################################################
+my $hDb; # Connection to Postgres
+my $strLogExpected = ''; # The expected log compared with grepping AUDIT
+ # entries from the
postgres log.
+
+my $strDatabase = 'postgres'; # Connected database (modified by PgSetDatabase)
+my $strUser = 'postgres'; # Connected user (modified by PgSetUser)
+my $strAuditRole = 'audit'; # Role to use for auditing
+
+my %oAuditLogHash; # Hash to store pg_audit.log
GUCS
+my %oAuditGrantHash; # Hash to store pg_audit grants
+
+my $strCurrentAuditLog; # pg_audit.log setting was Postgres was
started with
+my $strTemporaryAuditLog; # pg_audit.log setting that was set hot
+
+################################################################################
+# Stores the mapping between commands, classes, and types
+################################################################################
+my %oCommandHash =
+(&COMMAND_ANALYZE => {
+ &CLASS => &CLASS_DDL, &TYPE => &TYPE_NONE},
+ &COMMAND_ALTER_AGGREGATE => {&CLASS => &CLASS_DDL,
+ &TYPE => &TYPE_AGGREGATE},
+ &COMMAND_ALTER_DATABASE => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_NONE},
+ &COMMAND_ALTER_COLLATION => {&CLASS => &CLASS_DDL,
+ &TYPE => &TYPE_COLLATION},
+ &COMMAND_ALTER_CONVERSION => {&CLASS => &CLASS_DDL,
+ &TYPE => &TYPE_CONVERSION},
+ &COMMAND_ALTER_ROLE => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_NONE},
+ &COMMAND_ALTER_ROLE_SET => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_NONE,
+ &COMMAND => &COMMAND_ALTER_ROLE},
+ &COMMAND_ALTER_TABLE => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_TABLE},
+ &COMMAND_ALTER_TABLE_COLUMN => {&CLASS => &CLASS_DDL,
+ &TYPE => &TYPE_TABLE_COLUMN, &COMMAND => &COMMAND_ALTER_TABLE},
+ &COMMAND_ALTER_TABLE_INDEX => {&CLASS => &CLASS_DDL, &TYPE =>
&TYPE_INDEX,
+ &COMMAND => &COMMAND_ALTER_TABLE},
+ &COMMAND_BEGIN => {&CLASS => &CLASS_MISC, &TYPE => &TYPE_NONE},
+ &COMMAND_CLOSE => {&CLASS => &CLASS_MISC, &TYPE => &TYPE_NONE},
+ &COMMAND_COMMIT => {&CLASS => &CLASS_MISC, &TYPE => &TYPE_NONE},
+ &COMMAND_COPY_FROM => {&CLASS => &CLASS_WRITE, &TYPE => &TYPE_NONE,
+ &COMMAND => &COMMAND_COPY},
+ &COMMAND_COPY_TO => {&CLASS => &CLASS_READ, &TYPE => &TYPE_NONE,
+ &COMMAND => &COMMAND_COPY},
+ &COMMAND_CREATE_AGGREGATE => {&CLASS => &CLASS_DDL,
+ &TYPE => &TYPE_AGGREGATE},
+ &COMMAND_CREATE_CONVERSION => {&CLASS => &CLASS_DDL,
+ &TYPE => &TYPE_CONVERSION},
+ &COMMAND_CREATE_COLLATION => {&CLASS => &CLASS_DDL,
+ &TYPE => &TYPE_COLLATION},
+ &COMMAND_CREATE_DATABASE => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_NONE},
+ &COMMAND_CREATE_INDEX => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_INDEX},
+ &COMMAND_DEALLOCATE => {&CLASS => &CLASS_MISC, &TYPE => &TYPE_NONE},
+ &COMMAND_DECLARE_CURSOR => {&CLASS => &CLASS_READ, &TYPE => &TYPE_NONE},
+ &COMMAND_DO => {&CLASS => &CLASS_FUNCTION, &TYPE => &TYPE_NONE},
+ &COMMAND_DISCARD_ALL => {&CLASS => &CLASS_MISC, &TYPE => &TYPE_NONE},
+ &COMMAND_CREATE_FUNCTION => {&CLASS => &CLASS_DDL, &TYPE =>
&TYPE_FUNCTION},
+ &COMMAND_CREATE_ROLE => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_NONE},
+ &COMMAND_CREATE_SCHEMA => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_SCHEMA},
+ &COMMAND_CREATE_TABLE => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_TABLE},
+ &COMMAND_CREATE_TABLE_AS => {&CLASS => &CLASS_DDL, &TYPE =>
&TYPE_TABLE},
+ &COMMAND_DROP_DATABASE => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_NONE},
+ &COMMAND_DROP_SCHEMA => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_NONE},
+ &COMMAND_DROP_TABLE => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_TABLE},
+ &COMMAND_DROP_TABLE_CONSTRAINT => {&CLASS => &CLASS_DDL,
+ &TYPE => &TYPE_TABLE_CONSTRAINT, &COMMAND =>
&COMMAND_DROP_TABLE},
+ &COMMAND_DROP_TABLE_INDEX => {&CLASS => &CLASS_DDL, &TYPE =>
&TYPE_INDEX,
+ &COMMAND => &COMMAND_DROP_TABLE},
+ &COMMAND_DROP_TABLE_TOAST => {&CLASS => &CLASS_DDL,
+ &TYPE => &TYPE_TABLE_TOAST, &COMMAND => &COMMAND_DROP_TABLE},
+ &COMMAND_DROP_TABLE_TYPE => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_TYPE,
+ &COMMAND => &COMMAND_DROP_TABLE},
+ &COMMAND_EXECUTE_READ => {&CLASS => &CLASS_READ, &TYPE => &TYPE_NONE,
+ &COMMAND => &COMMAND_EXECUTE},
+ &COMMAND_EXECUTE_WRITE => {&CLASS => &CLASS_WRITE, &TYPE => &TYPE_NONE,
+ &COMMAND => &COMMAND_EXECUTE},
+ &COMMAND_EXECUTE_FUNCTION => {&CLASS => &CLASS_FUNCTION,
+ &TYPE => &TYPE_FUNCTION, &COMMAND => &COMMAND_EXECUTE},
+ &COMMAND_EXPLAIN => {&CLASS => &CLASS_MISC, &TYPE => &TYPE_NONE},
+ &COMMAND_FETCH => {&CLASS => &CLASS_MISC, &TYPE => &TYPE_NONE},
+ &COMMAND_GRANT => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_TABLE},
+ &COMMAND_PREPARE_READ => {&CLASS => &CLASS_READ, &TYPE => &TYPE_NONE,
+ &COMMAND => &COMMAND_PREPARE},
+ &COMMAND_PREPARE_WRITE => {&CLASS => &CLASS_WRITE, &TYPE => &TYPE_NONE,
+ &COMMAND => &COMMAND_PREPARE},
+ &COMMAND_INSERT => {&CLASS => &CLASS_WRITE, &TYPE => &TYPE_NONE},
+ &COMMAND_REVOKE => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_TABLE},
+ &COMMAND_SELECT => {&CLASS => &CLASS_READ, &TYPE => &TYPE_NONE},
+ &COMMAND_SET => {&CLASS => &CLASS_MISC, &TYPE => &TYPE_NONE},
+ &COMMAND_UPDATE => {&CLASS => &CLASS_WRITE, &TYPE => &TYPE_NONE}
+);
+
+################################################################################
+# CommandExecute
+################################################################################
+sub CommandExecute
+{
+ my $strCommand = shift;
+ my $bSuppressError = shift;
+
+ # Set default
+ $bSuppressError = defined($bSuppressError) ? $bSuppressError : false;
+
+ # Run the command
+ my $iResult = system($strCommand);
+
+ if ($iResult != 0 && !$bSuppressError)
+ {
+ confess "command '${strCommand}' failed with error ${iResult}";
+ }
+}
+
+################################################################################
+# log
+################################################################################
+sub log
+{
+ my $strMessage = shift;
+ my $bError = shift;
+
+ # Set default
+ $bError = defined($bError) ? $bError : false;
+
+ if (!$bQuiet)
+ {
+ print "${strMessage}\n";
+ }
+
+ if ($bError)
+ {
+ exit 1;
+ }
+}
+
+################################################################################
+# ArrayToString
+################################################################################
+sub ArrayToString
+{
+ my @stryArray = @_;
+
+ my $strResult = '';
+
+ for (my $iIndex = 0; $iIndex < @stryArray; $iIndex++)
+ {
+ if ($iIndex != 0)
+ {
+ $strResult .= ', ';
+ }
+
+ $strResult .= $stryArray[$iIndex];
+ }
+
+ return $strResult;
+}
+
+################################################################################
+# BuildModule
+################################################################################
+sub BuildModule
+{
+ capture('cd ..;make');
+ CommandExecute("cp ../pg_audit.so" .
+ " ${strPgSqlBin}/../lib/postgresql");
+ CommandExecute("cp ../pg_audit.control" .
+ "
${strPgSqlBin}/../share/postgresql/extension");
+ CommandExecute("cp ../pg_audit--1.0.0.sql" .
+ "
${strPgSqlBin}/../share/postgresql/extension");
+}
+
+################################################################################
+# PgConnect
+################################################################################
+sub PgConnect
+{
+ my $iPort = shift;
+
+ # Set default
+ $iPort = defined($iPort) ? $iPort : $iDefaultPort;
+
+ # Log Connection
+ &log(" DB: connect user ${strUser}, database ${strDatabase}");
+
+ # Disconnect user session
+ PgDisconnect();
+
+ # Connect to the db
+ $hDb =
DBI->connect("dbi:Pg:dbname=${strDatabase};port=${iPort};host=/tmp",
+ $strUser, undef,
+ {AutoCommit => 1, RaiseError =>
1});
+}
+
+################################################################################
+# PgDisconnect
+################################################################################
+sub PgDisconnect
+{
+ # Connect to the db (whether it is local or remote)
+ if (defined($hDb))
+ {
+ $hDb->disconnect;
+ undef($hDb);
+ }
+}
+
+################################################################################
+# PgExecute
+################################################################################
+sub PgExecute
+{
+ my $strSql = shift;
+
+ # Log the statement
+ &log(" SQL: ${strSql}");
+
+ # Execute the statement
+ my $hStatement = $hDb->prepare($strSql);
+
+ $hStatement->execute();
+ $hStatement->finish();
+}
+
+################################################################################
+# PgExecuteOnly
+################################################################################
+sub PgExecuteOnly
+{
+ my $strSql = shift;
+
+ # Log the statement
+ &log(" SQL: ${strSql}");
+
+ # Execute the statement
+ $hDb->do($strSql);
+}
+
+################################################################################
+# PgSetDatabase
+################################################################################
+sub PgSetDatabase
+{
+ my $strDatabaseParam = shift;
+
+ # Stop and start the database to reset pgconf entries
+ PgStop();
+ PgStart();
+
+ # Execute the statement
+ $strDatabase = $strDatabaseParam;
+ PgConnect();
+}
+
+################################################################################
+# PgSetUser
+################################################################################
+sub PgSetUser
+{
+ my $strUserParam = shift;
+
+ $strUser = $strUserParam;
+
+ # Stop and start the database to reset pgconf entries
+ if ((defined($strTemporaryAuditLog) && !defined($strCurrentAuditLog)) ||
+ (defined($strCurrentAuditLog) &&
!defined($strTemporaryAuditLog)) ||
+ $strCurrentAuditLog ne $strTemporaryAuditLog)
+ {
+ $strCurrentAuditLog = $strTemporaryAuditLog;
+
+ PgStop();
+ PgStart();
+ }
+ else
+ {
+ # Execute the statement
+ PgConnect();
+ }
+}
+
+################################################################################
+# SaveString
+################################################################################
+sub SaveString
+{
+ my $strFile = shift;
+ my $strString = shift;
+
+ # Open the file for writing
+ my $hFile;
+
+ open($hFile, '>', $strFile)
+ or confess "unable to open ${strFile}";
+
+ if ($strString ne '')
+ {
+ syswrite($hFile, $strString)
+ or confess "unable to write to ${strFile}: $!";
+ }
+
+ close($hFile);
+}
+
+################################################################################
+# PgLogExecute
+################################################################################
+sub PgLogExecute
+{
+ my $strCommand = shift;
+ my $strSql = shift;
+ my $oData = shift;
+ my $bExecute = shift;
+ my $bWait = shift;
+ my $bLogSql = shift;
+ my $strParameter = shift;
+ my $bExpectError = shift;
+
+ # Set defaults
+ $bExecute = defined($bExecute) ? $bExecute : true;
+ $bWait = defined($bWait) ? $bWait : true;
+ $bLogSql = defined($bLogSql) ? $bLogSql : true;
+
+ if ($bExecute)
+ {
+ eval
+ {
+ PgExecuteOnly($strSql);
+ };
+
+ if ($@ && !$bExpectError)
+ {
+ confess $@;
+ }
+ }
+
+ PgLogExpect($strCommand, $bLogSql ? $strSql : '', $strParameter,
$oData);
+
+ if ($bWait)
+ {
+ PgLogWait();
+ }
+}
+
+################################################################################
+# QuoteCSV
+################################################################################
+sub QuoteCSV
+{
+ my $strCSV = shift;
+
+ if (defined($strCSV) &&
+ (index($strCSV, ',') >= 0 || index($strCSV, '"') > 0 ||
+ index($strCSV, "\n") > 0 || index($strCSV, "\r") >= 0))
+ {
+ $strCSV =~ s/"/""/g;
+ $strCSV = "\"${strCSV}\"";
+ }
+
+ return $strCSV;
+}
+
+################################################################################
+# PgLogExpect
+################################################################################
+sub PgLogExpect
+{
+ my $strCommand = shift;
+ my $strSql = shift;
+ my $strParameter = shift;
+ my $oData = shift;
+
+ # If oData is false then no logging
+ if (defined($oData) && ref($oData) eq '' && !$oData)
+ {
+ return;
+ }
+
+ # Quote SQL if needs to be quoted
+ $strSql = QuoteCSV($strSql);
+
+ if (defined($strParameter))
+ {
+ $strSql .= ",${strParameter}";
+ }
+
+ # Log based on session
+ if (PgShouldLog($strCommand))
+ {
+ # Make sure class is defined
+ my $strClass = $oCommandHash{$strCommand}{&CLASS};
+
+ if (!defined($strClass))
+ {
+ confess "class is not defined for command
${strCommand}";
+ }
+
+ # Make sure object type is defined
+ my $strObjectType = $oCommandHash{$strCommand}{&TYPE};
+
+ if (!defined($strObjectType))
+ {
+ confess "object type is not defined for command
${strCommand}";
+ }
+
+ # Check for command override
+ my $strCommandLog = $strCommand;
+
+ if ($oCommandHash{$strCommand}{&COMMAND})
+ {
+ $strCommandLog = $oCommandHash{$strCommand}{&COMMAND};
+ }
+
+ my $strObjectName = '';
+
+ if (defined($oData) && ref($oData) ne 'ARRAY')
+ {
+ $strObjectName = QuoteCSV($oData);
+ }
+
+ my $strLog .= "SESSION,${strClass},${strCommandLog}," .
+
"${strObjectType},${strObjectName},${strSql}";
+ &log("AUDIT: ${strLog}");
+
+ $strLogExpected .= "${strLog}\n";
+ }
+
+ # Log based on grants
+ if (ref($oData) eq 'ARRAY' && ($strCommand eq COMMAND_SELECT ||
+ $oCommandHash{$strCommand}{&CLASS} eq CLASS_WRITE))
+ {
+ foreach my $oTableHash (@{$oData})
+ {
+ my $strObjectName = QuoteCSV(${$oTableHash}{&NAME});
+ my $strCommandLog = ${$oTableHash}{&COMMAND};
+
+ if (defined($oAuditGrantHash{$strAuditRole}
+
{$strObjectName}{$strCommandLog}))
+ {
+ my $strCommandLog =
defined(${$oTableHash}{&COMMAND_LOG}) ?
+ ${$oTableHash}{&COMMAND_LOG} :
$strCommandLog;
+ my $strClass =
$oCommandHash{$strCommandLog}{&CLASS};
+ my $strObjectType = ${$oTableHash}{&TYPE};
+
+ my $strLog .=
"OBJECT,${strClass},${strCommandLog}," .
+
"${strObjectType},${strObjectName},${strSql}";
+ &log("AUDIT: ${strLog}");
+
+ $strLogExpected .= "${strLog}\n";
+ }
+ }
+
+ $oData = undef;
+ }
+}
+
+################################################################################
+# PgShouldLog
+################################################################################
+sub PgShouldLog
+{
+ my $strCommand = shift;
+
+ # Make sure class is defined
+ my $strClass = $oCommandHash{$strCommand}{&CLASS};
+
+ if (!defined($strClass))
+ {
+ confess "class is not defined for command ${strCommand}";
+ }
+
+ # Check logging for the role
+ my $bLog = undef;
+
+ if (defined($oAuditLogHash{&CONTEXT_ROLE}{$strUser}))
+ {
+ $bLog = $oAuditLogHash{&CONTEXT_ROLE}{$strUser}{$strClass};
+ }
+
+ # Else check logging for the db
+ elsif (defined($oAuditLogHash{&CONTEXT_DATABASE}{$strDatabase}))
+ {
+ $bLog =
$oAuditLogHash{&CONTEXT_DATABASE}{$strDatabase}{$strClass};
+ }
+
+ # Else check logging for global
+ elsif (defined($oAuditLogHash{&CONTEXT_GLOBAL}{&CONTEXT_GLOBAL}))
+ {
+ $bLog =
$oAuditLogHash{&CONTEXT_GLOBAL}{&CONTEXT_GLOBAL}{$strClass};
+ }
+
+ return defined($bLog) ? true : false;
+}
+
+################################################################################
+# PgLogWait
+################################################################################
+sub PgLogWait
+{
+ my $strLogActual;
+
+ # Run in an eval block since grep returns 1 when nothing was found
+ eval
+ {
+ $strLogActual = capture("grep 'LOG: AUDIT: '" .
+ "
${strTestPath}/postgresql.log");
+ };
+
+ # If an error was returned, continue if it was 1, otherwise confess
+ if ($@)
+ {
+ my $iExitStatus = $? >> 8;
+
+ if ($iExitStatus != 1)
+ {
+ confess "grep returned ${iExitStatus}";
+ }
+
+ $strLogActual = '';
+ }
+
+ # Strip the AUDIT and timestamp from the actual log
+ $strLogActual =~ s/prefix LOG: AUDIT\: //g;
+ $strLogActual =~ s/SESSION,[0-9]+,[0-9]+,/SESSION,/g;
+ $strLogActual =~ s/OBJECT,[0-9]+,[0-9]+,/OBJECT,/g;
+
+ # Save the logs
+ SaveString("${strTestPath}/audit.actual", $strLogActual);
+ SaveString("${strTestPath}/audit.expected", $strLogExpected);
+
+ CommandExecute("diff ${strTestPath}/audit.expected" .
+ " ${strTestPath}/audit.actual");
+}
+
+################################################################################
+# PgDrop
+################################################################################
+sub PgDrop
+{
+ my $strPath = shift;
+
+ # Set default
+ $strPath = defined($strPath) ? $strPath : $strTestPath;
+
+ # Stop the cluster
+ PgStop(true, $strPath);
+
+ # Remove the directory
+ CommandExecute("rm -rf ${strTestPath}");
+}
+
+################################################################################
+# PgCreate
+################################################################################
+sub PgCreate
+{
+ my $strPath = shift;
+
+ # Set default
+ $strPath = defined($strPath) ? $strPath : $strTestPath;
+
+ CommandExecute("${strPgSqlBin}/initdb -D ${strPath} -U ${strUser}" .
+ ' -A trust > /dev/null');
+}
+
+################################################################################
+# PgStop
+################################################################################
+sub PgStop
+{
+ my $bImmediate = shift;
+ my $strPath = shift;
+
+ # Set default
+ $strPath = defined($strPath) ? $strPath : $strTestPath;
+ $bImmediate = defined($bImmediate) ? $bImmediate : false;
+
+ # Disconnect user session
+ PgDisconnect();
+
+ # If postmaster process is running then stop the cluster
+ if (-e $strPath . '/postmaster.pid')
+ {
+ CommandExecute("${strPgSqlBin}/pg_ctl stop -D ${strPath} -w -s
-m " .
+ ($bImmediate ? 'immediate' : 'fast'));
+ }
+}
+
+################################################################################
+# PgStart
+################################################################################
+sub PgStart
+{
+ my $iPort = shift;
+ my $strPath = shift;
+
+ # Set default
+ $iPort = defined($iPort) ? $iPort : $iDefaultPort;
+ $strPath = defined($strPath) ? $strPath : $strTestPath;
+
+ # Make sure postgres is not running
+ if (-e $strPath . '/postmaster.pid')
+ {
+ confess "${strPath}/postmaster.pid exists, cannot start";
+ }
+
+ # Start the cluster
+ CommandExecute("${strPgSqlBin}/pg_ctl start -o \"" .
+ "-c port=${iPort}" .
+ " -c unix_socket_directories='/tmp'" .
+ " -c shared_preload_libraries='pg_audit'" .
+ " -c log_min_messages=debug1" .
+ " -c log_line_prefix='prefix '" .
+ " -c log_statement=all" .
+ (defined($strCurrentAuditLog) ?
+ " -c
pg_audit.log='${strCurrentAuditLog}'" : '') .
+ " -c pg_audit.role='${strAuditRole}'" .
+ " -c log_connections=on" .
+ "\" -D ${strPath} -l
${strPath}/postgresql.log -w -s");
+
+ # Connect user session
+ PgConnect();
+}
+
+################################################################################
+# PgAuditLogSet
+################################################################################
+sub PgAuditLogSet
+{
+ my $strContext = shift;
+ my $strName = shift;
+ my @stryClass = @_;
+
+ # Create SQL to set the GUC
+ my $strCommand;
+ my $strSql;
+
+ if ($strContext eq CONTEXT_GLOBAL)
+ {
+ $strCommand = COMMAND_SET;
+ $strSql = "set pg_audit.log = '" .
+ ArrayToString(@stryClass) . "'";
+ $strTemporaryAuditLog = ArrayToString(@stryClass);
+ }
+ elsif ($strContext eq CONTEXT_ROLE)
+ {
+ $strCommand = COMMAND_ALTER_ROLE_SET;
+ $strSql = "alter role ${strName} set pg_audit.log = '" .
+ ArrayToString(@stryClass) . "'";
+ }
+ else
+ {
+ confess "unable to set pg_audit.log for context ${strContext}";
+ }
+
+ # Reset the audit log
+ if ($strContext eq CONTEXT_GLOBAL)
+ {
+ delete($oAuditLogHash{$strContext});
+ $strName = CONTEXT_GLOBAL;
+ }
+ else
+ {
+ delete($oAuditLogHash{$strContext}{$strName});
+ }
+
+ # Store all the classes in the hash and build the GUC
+ foreach my $strClass (@stryClass)
+ {
+ if ($strClass eq CLASS_ALL)
+ {
+ $oAuditLogHash{$strContext}{$strName}{&CLASS_DDL} =
true;
+ $oAuditLogHash{$strContext}{$strName}{&CLASS_FUNCTION}
= true;
+ $oAuditLogHash{$strContext}{$strName}{&CLASS_MISC} =
true;
+ $oAuditLogHash{$strContext}{$strName}{&CLASS_READ} =
true;
+ $oAuditLogHash{$strContext}{$strName}{&CLASS_WRITE} =
true;
+ }
+
+ if (index($strClass, '-') == 0)
+ {
+ $strClass = substr($strClass, 1);
+
+
delete($oAuditLogHash{$strContext}{$strName}{$strClass});
+ }
+ else
+ {
+ $oAuditLogHash{$strContext}{$strName}{$strClass} = true;
+ }
+ }
+
+ PgLogExecute($strCommand, $strSql);
+}
+
+################################################################################
+# PgAuditGrantSet
+################################################################################
+sub PgAuditGrantSet
+{
+ my $strRole = shift;
+ my $strPrivilege = shift;
+ my $strObject = shift;
+ my $strColumn = shift;
+
+ # Create SQL to set the grant
+ PgLogExecute(COMMAND_GRANT, "GRANT " .
+
(defined($strColumn) ?
+
lc(${strPrivilege}) ." (${strColumn})" :
+
uc(${strPrivilege})) .
+ " ON TABLE
${strObject} TO ${strRole} ");
+
+ $oAuditGrantHash{$strRole}{$strObject}{$strPrivilege} = true;
+}
+
+################################################################################
+# PgAuditGrantReset
+################################################################################
+sub PgAuditGrantReset
+{
+ my $strRole = shift;
+ my $strPrivilege = shift;
+ my $strObject = shift;
+ my $strColumn = shift;
+
+ # Create SQL to set the grant
+ PgLogExecute(COMMAND_REVOKE, "REVOKE " . uc(${strPrivilege}) .
+ (defined($strColumn) ? " (${strColumn})" : '')
.
+ " ON TABLE ${strObject} FROM ${strRole} ");
+
+ delete($oAuditGrantHash{$strRole}{$strObject}{$strPrivilege});
+}
+
+################################################################################
+# Main
+################################################################################
+my @oyTable; # Store table info for select, insert, update, delete
+my $strSql; # Hold Sql commands
+
+# Drop the old cluster, build the code, and create a new cluster
+PgDrop();
+BuildModule();
+PgCreate();
+PgStart();
+
+PgExecute("create extension pg_audit");
+
+# Create test users and the audit role
+PgExecute("create user user1");
+PgExecute("create user user2");
+PgExecute("create role ${strAuditRole}");
+
+PgAuditLogSet(CONTEXT_GLOBAL, undef, (CLASS_DDL));
+
+PgAuditLogSet(CONTEXT_ROLE, 'user2', (CLASS_READ, CLASS_WRITE));
+
+# User1 follows the global log settings
+PgSetUser('user1');
+
+$strSql = 'CREATE TABLE public.test (id pg_catalog.int4 )' .
+ ' WITH (oids=OFF) ';
+PgLogExecute(COMMAND_CREATE_TABLE, $strSql, 'public.test');
+PgLogExecute(COMMAND_SELECT, 'select * from test');
+
+$strSql = 'drop table test';
+PgLogExecute(COMMAND_DROP_TABLE, $strSql, 'public.test');
+
+PgSetUser('user2');
+PgLogExecute(COMMAND_CREATE_TABLE,
+ 'create table test2 (id int)', 'public.test2');
+PgAuditGrantSet($strAuditRole, &COMMAND_SELECT, 'public.test2');
+PgLogExecute(COMMAND_CREATE_TABLE,
+ 'create table test3 (id int)', 'public.test2');
+
+# Catalog select should not log
+PgLogExecute(COMMAND_SELECT, 'select * from pg_class limit 1',
+ false);
+
+# Multi-table select
+@oyTable = ({&NAME => 'public.test3', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_SELECT},
+ {&NAME => 'public.test2', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_SELECT});
+PgLogExecute(COMMAND_SELECT, 'select * from test3, test2',
+ \@oyTable);
+
+# Various CTE combinations
+PgAuditGrantSet($strAuditRole, &COMMAND_INSERT, 'public.test3');
+
+@oyTable = ({&NAME => 'public.test3', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_INSERT},
+ {&NAME => 'public.test2', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_SELECT});
+PgLogExecute(COMMAND_INSERT,
+ 'with cte as (select id from test2)' .
+ ' insert into test3 select id from cte',
+ \@oyTable);
+
+@oyTable = ({&NAME => 'public.test2', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_INSERT},
+ {&NAME => 'public.test3', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_INSERT});
+PgLogExecute(COMMAND_INSERT,
+ 'with cte as (insert into test3 values (1) returning
id)' .
+ ' insert into test2 select id from cte',
+ \@oyTable);
+
+PgAuditGrantSet($strAuditRole, &COMMAND_UPDATE, 'public.test2');
+
+@oyTable = ({&NAME => 'public.test3', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_INSERT},
+ {&NAME => 'public.test2', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_UPDATE});
+PgLogExecute(COMMAND_INSERT,
+ 'with cte as (update test2 set id = 1 returning id)' .
+ ' insert into test3 select id from cte',
+ \@oyTable);
+
+@oyTable = ({&NAME => 'public.test3', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_UPDATE},
+ {&NAME => 'public.test2', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_INSERT},
+ {&NAME => 'public.test2', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_SELECT, &COMMAND_LOG =>
&COMMAND_INSERT});
+PgLogExecute(COMMAND_UPDATE,
+ 'with cte as (insert into test2 values (1) returning
id)' .
+ ' update test3 set id = cte.id' .
+ ' from cte where test3.id <> cte.id',
+ \@oyTable);
+
+PgSetUser('postgres');
+PgAuditLogSet(CONTEXT_ROLE, 'user2', (CLASS_NONE));
+PgSetUser('user2');
+
+# Column-based audits
+PgLogExecute(COMMAND_CREATE_TABLE,
+ 'create table test4 (id int, name text)',
'public.test4');
+PgAuditGrantSet($strAuditRole, COMMAND_SELECT, 'public.test4', 'name');
+PgAuditGrantSet($strAuditRole, COMMAND_UPDATE, 'public.test4', 'id');
+PgAuditGrantSet($strAuditRole, COMMAND_INSERT, 'public.test4', 'name');
+
+# Select
+@oyTable = ();
+PgLogExecute(COMMAND_SELECT, 'select id from public.test4',
+ \@oyTable);
+
+@oyTable = ({&NAME => 'public.test4', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_SELECT});
+PgLogExecute(COMMAND_SELECT, 'select name from public.test4',
+ \@oyTable);
+
+# Insert
+@oyTable = ();
+PgLogExecute(COMMAND_INSERT, 'insert into public.test4 (id) values (1)',
+ \@oyTable);
+
+@oyTable = ({&NAME => 'public.test4', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_INSERT});
+PgLogExecute(COMMAND_INSERT, "insert into public.test4 (name) values ('test')",
+ \@oyTable);
+
+# Update
+@oyTable = ();
+PgLogExecute(COMMAND_UPDATE, "update public.test4 set name = 'foo'",
+ \@oyTable);
+
+@oyTable = ({&NAME => 'public.test4', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_UPDATE});
+PgLogExecute(COMMAND_UPDATE, "update public.test4 set id = 1",
+ \@oyTable);
+
+@oyTable = ({&NAME => 'public.test4', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_SELECT, &COMMAND_LOG =>
&COMMAND_UPDATE});
+PgLogExecute(COMMAND_UPDATE,
+ "update public.test4 set name = 'foo' where name =
'bar'",
+ \@oyTable);
+
+# Drop test tables
+PgLogExecute(COMMAND_DROP_TABLE, "drop table test2", 'public.test2');
+PgLogExecute(COMMAND_DROP_TABLE, "drop table test3", 'public.test3');
+PgLogExecute(COMMAND_DROP_TABLE, "drop table test4", 'public.test4');
+
+
+# Make sure there are no more audit events pending in the postgres log
+PgLogWait();
+
+# Create some email friendly tests. These first tests are session logging
only.
+PgSetUser('postgres');
+
+&log("\nExamples:");
+
+&log("\nSession Audit:\n");
+
+PgAuditLogSet(CONTEXT_GLOBAL, undef, (CLASS_DDL, CLASS_READ));
+
+PgSetUser('user1');
+
+$strSql = 'CREATE TABLE public.account (id pg_catalog.int4 ,' .
+ ' name pg_catalog.text COLLATE pg_catalog."default", ' .
+ 'password pg_catalog.text COLLATE pg_catalog."default", '.
+ 'description pg_catalog.text COLLATE pg_catalog."default")
'.
+ 'WITH (oids=OFF) ';
+PgLogExecute(COMMAND_CREATE_TABLE, $strSql, 'public.account');
+PgLogExecute(COMMAND_SELECT,
+ 'select * from account');
+PgLogExecute(COMMAND_INSERT,
+ "insert into account (id, name, password,
description)" .
+ " values (1, 'user1', 'HASH1', 'blah, blah')");
+&log("AUDIT: <nothing logged>");
+
+# Now tests for object logging
+&log("\nObject Audit:\n");
+
+PgSetUser('postgres');
+PgAuditLogSet(CONTEXT_GLOBAL, undef, (CLASS_NONE));
+PgExecute("set pg_audit.role = 'audit'");
+PgSetUser('user1');
+
+PgAuditGrantSet($strAuditRole, &COMMAND_SELECT, 'public.account', 'password');
+
+@oyTable = ();
+PgLogExecute(COMMAND_SELECT, 'select id, name from account',
+ \@oyTable);
+&log("AUDIT: <nothing logged>");
+
+@oyTable = ({&NAME => 'public.account', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_SELECT});
+PgLogExecute(COMMAND_SELECT, 'select password from account',
+ \@oyTable);
+
+PgAuditGrantSet($strAuditRole, &COMMAND_UPDATE,
+ 'public.account', 'name, password');
+
+@oyTable = ();
+PgLogExecute(COMMAND_UPDATE, "update account set description = 'yada, yada'",
+ \@oyTable);
+&log("AUDIT: <nothing logged>");
+
+@oyTable = ({&NAME => 'public.account', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_UPDATE});
+PgLogExecute(COMMAND_UPDATE, "update account set password = 'HASH2'",
+ \@oyTable);
+
+# Now tests for session/object logging
+&log("\nSession/Object Audit:\n");
+
+PgSetUser('postgres');
+PgAuditLogSet(CONTEXT_ROLE, 'user1', (CLASS_READ, CLASS_WRITE));
+PgSetUser('user1');
+
+PgLogExecute(COMMAND_CREATE_TABLE,
+ 'create table account_role_map (account_id int,
role_id int)',
+ 'public.account_role_map');
+PgAuditGrantSet($strAuditRole, &COMMAND_SELECT, 'public.account_role_map');
+
+@oyTable = ({&NAME => 'public.account', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_SELECT},
+ {&NAME => 'public.account_role_map', &TYPE =>
&TYPE_TABLE,
+ &COMMAND => &COMMAND_SELECT});
+PgLogExecute(COMMAND_SELECT,
+ 'select account.password, account_role_map.role_id
from account' .
+ ' inner join account_role_map' .
+ ' on account.id = account_role_map.account_id',
+ \@oyTable);
+
+@oyTable = ({&NAME => 'public.account', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_SELECT});
+PgLogExecute(COMMAND_SELECT, 'select password from account',
+ \@oyTable);
+
+@oyTable = ();
+PgLogExecute(COMMAND_UPDATE, "update account set description = 'yada, yada'",
+ \@oyTable);
+&log("AUDIT: <nothing logged>");
+
+@oyTable = ({&NAME => 'public.account', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_SELECT, &COMMAND_LOG =>
&COMMAND_UPDATE});
+PgLogExecute(COMMAND_UPDATE,
+ "update account set description = 'yada, yada'" .
+ " where password = 'HASH2'",
+ \@oyTable);
+
+@oyTable = ({&NAME => 'public.account', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_UPDATE});
+PgLogExecute(COMMAND_UPDATE, "update account set password = 'HASH2'",
+ \@oyTable);
+
+# Test all sql commands
+&log("\nExhaustive Command Tests:\n");
+
+PgSetUser('postgres');
+
+PgAuditLogSet(CONTEXT_GLOBAL, undef, (CLASS_ALL));
+PgLogExecute(COMMAND_SET, "set pg_audit.role = 'audit'");
+
+PgLogExecute(COMMAND_DO, "do \$\$\ begin raise notice 'test'; end; \$\$;");
+
+$strSql = 'CREATE SCHEMA test ';
+PgLogExecute(COMMAND_CREATE_SCHEMA, $strSql, 'test');
+
+# Test COPY
+PgLogExecute(COMMAND_COPY_TO,
+ "COPY pg_class to '" . abs_path($strTestPath) .
"/class.out'");
+
+$strSql = 'CREATE TABLE test.pg_class WITH (oids=OFF) AS SELECT relname,'
.
+ ' relnamespace, reltype, reloftype, relowner, relam,
relfilenode, ' .
+ 'reltablespace, relpages, reltuples, relallvisible,
reltoastrelid, ' .
+ 'relhasindex, relisshared, relpersistence, relkind, relnatts,
' .
+ 'relchecks, relhasoids, relhaspkey, relhasrules,
relhastriggers, ' .
+ 'relhassubclass, relrowsecurity, relispopulated,
relreplident, ' .
+ 'relfrozenxid, relminmxid, relacl, reloptions ' .
+ 'FROM pg_catalog.pg_class ';
+PgLogExecute(COMMAND_INSERT, $strSql, undef, true, false);
+PgLogExecute(COMMAND_CREATE_TABLE_AS, $strSql, 'test.pg_class', false, true);
+
+$strSql = "COPY test.pg_class from '" . abs_path($strTestPath) . "/class.out'";
+PgLogExecute(COMMAND_INSERT, $strSql);
+#PgLogExecute(COMMAND_COPY_FROM, $strSql, undef, false, true);
+
+# Test prepared SELECT
+PgLogExecute(COMMAND_PREPARE_READ,
+ 'PREPARE pgclassstmt (oid) as select *' .
+ ' from pg_class where oid = $1');
+PgLogExecute(COMMAND_EXECUTE_READ,
+ 'EXECUTE pgclassstmt (1)');
+PgLogExecute(COMMAND_DEALLOCATE,
+ 'DEALLOCATE pgclassstmt');
+
+# Test cursor
+PgLogExecute(COMMAND_BEGIN,
+ 'BEGIN');
+PgLogExecute(COMMAND_DECLARE_CURSOR,
+ 'DECLARE ctest SCROLL CURSOR FOR SELECT * FROM
pg_class');
+PgLogExecute(COMMAND_FETCH,
+ 'FETCH NEXT FROM ctest');
+PgLogExecute(COMMAND_CLOSE,
+ 'CLOSE ctest');
+PgLogExecute(COMMAND_COMMIT,
+ 'COMMIT');
+
+# Test prepared INSERT
+$strSql = 'CREATE TABLE test.test_insert (id pg_catalog.int4 ) ' .
+ 'WITH (oids=OFF) ';
+PgLogExecute(COMMAND_CREATE_TABLE, $strSql, 'test.test_insert');
+
+$strSql = 'PREPARE pgclassstmt (oid) as insert into test.test_insert (id) ' .
+ 'values ($1)';
+PgLogExecute(COMMAND_PREPARE_WRITE, $strSql);
+PgLogExecute(COMMAND_INSERT, $strSql, undef, false, false, undef, "1");
+
+$strSql = 'EXECUTE pgclassstmt (1)';
+PgLogExecute(COMMAND_EXECUTE_WRITE, $strSql, undef, true, true);
+
+# Create a table with a primary key
+$strSql = 'CREATE TABLE public.test (id pg_catalog.int4 , ' .
+ 'name pg_catalog.text COLLATE pg_catalog."default",
description ' .
+ 'pg_catalog.text COLLATE pg_catalog."default", CONSTRAINT '
.
+ 'test_pkey PRIMARY KEY (id)) WITH (oids=OFF) ';
+PgLogExecute(COMMAND_CREATE_INDEX, $strSql, 'public.test_pkey', true, false);
+PgLogExecute(COMMAND_CREATE_TABLE, $strSql, 'public.test', false, true);
+
+PgLogExecute(COMMAND_ANALYZE, 'analyze test');
+
+# Grant select to public - this should have no affect on auditing
+$strSql = 'GRANT SELECT ON TABLE public.test TO PUBLIC ';
+PgLogExecute(COMMAND_GRANT, $strSql);
+
+PgLogExecute(COMMAND_SELECT, 'select * from test');
+
+# Now grant select to audit and it should be logged
+PgAuditGrantSet($strAuditRole, &COMMAND_SELECT, 'public.test');
+@oyTable = ({&NAME => 'public.test', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_SELECT});
+PgLogExecute(COMMAND_SELECT, 'select * from test', \@oyTable);
+
+# Check columns granted to public and make sure they do not log
+PgAuditGrantReset($strAuditRole, &COMMAND_SELECT, 'public.test');
+
+$strSql = 'GRANT select (name) ON TABLE public.test TO PUBLIC ';
+PgLogExecute(COMMAND_GRANT, $strSql);
+
+PgLogExecute(COMMAND_SELECT, 'select * from test');
+PgLogExecute(COMMAND_SELECT, 'select from test');
+
+# Try a select that does not reference any tables
+PgLogExecute(COMMAND_SELECT, 'select 1, current_timestamp');
+
+# Now try the same in a do block
+$strSql = 'do $$ declare test int; begin select 1 into test; end $$';
+PgLogExecute(COMMAND_DO, $strSql, undef, true, false);
+
+$strSql = 'select 1';
+PgLogExecute(COMMAND_SELECT, $strSql, undef, false, true);
+
+# Insert some data into test and try a loop in a do block
+PgLogExecute(COMMAND_INSERT, 'insert into test (id) values (1)');
+PgLogExecute(COMMAND_INSERT, 'insert into test (id) values (2)');
+PgLogExecute(COMMAND_INSERT, 'insert into test (id) values (3)');
+
+$strSql = 'do $$ ' .
+ 'declare ' .
+ ' result record;' .
+ 'begin ' .
+ ' for result in select id from test loop ' .
+ ' insert into test (id) values (result.id + 100);
' .
+ ' end loop; ' .
+ 'end; $$';
+
+PgLogExecute(COMMAND_DO, $strSql, undef, true, false);
+
+$strSql = 'select id from test';
+PgLogExecute(COMMAND_SELECT, $strSql, undef, false, false);
+
+$strSql = 'insert into test (id) values (result.id + 100)';
+PgLogExecute(COMMAND_INSERT, $strSql, undef, false, false, undef, ",,");
+
+PgLogExecute(COMMAND_INSERT, $strSql, undef, false, false, undef, ",,");
+
+PgLogExecute(COMMAND_INSERT, $strSql, undef, false, false, undef, ",,");
+
+# Test EXECUTE with bind
+$strSql = "select * from test where id = ?";
+my $hStatement = $hDb->prepare($strSql);
+
+$strSql = "select * from test where id = \$1";
+$hStatement->bind_param(1, 101);
+$hStatement->execute();
+
+PgLogExecute(COMMAND_SELECT, $strSql, undef, false, false, undef, "101");
+
+$hStatement->bind_param(1, 103);
+$hStatement->execute();
+
+PgLogExecute(COMMAND_SELECT, $strSql, undef, false, false, undef, "103");
+
+$hStatement->finish();
+
+# Cursors in a function block
+$strSql = "CREATE FUNCTION public.test() RETURNS pg_catalog.int4 LANGUAGE " .
+ "plpgsql VOLATILE CALLED ON NULL INPUT SECURITY INVOKER
COST 100 " .
+ " AS ' declare cur1 cursor for select * from hoge; tmp int;
begin " .
+ "create table hoge (id int); open cur1; fetch cur1 into tmp;
close " .
+ "cur1; return tmp; end'";
+
+PgLogExecute(COMMAND_CREATE_FUNCTION, $strSql, 'public.test()');
+
+$strSql = 'select public.test()';
+PgLogExecute(COMMAND_SELECT, $strSql, undef, true, false);
+PgLogExecute(COMMAND_EXECUTE_FUNCTION, $strSql, 'public.test', false, false);
+
+$strSql = 'CREATE TABLE public.hoge (id pg_catalog.int4 )' .
+ ' WITH (oids=OFF) ';
+PgLogExecute(COMMAND_CREATE_TABLE, $strSql, 'public.hoge', false, false);
+
+$strSql = 'select * from hoge';
+PgLogExecute(COMMAND_SELECT, $strSql, undef, false, true);
+#PgLogExecute(COMMAND_SELECT, 'select public.test()');
+
+# Now try some DDL in a do block
+$strSql = 'do $$ ' .
+ 'begin ' .
+ ' create table test_block (id int); ' .
+ ' drop table test_block; ' .
+ 'end; $$';
+
+PgLogExecute(COMMAND_DO, $strSql, undef, true, false);
+
+$strSql = 'CREATE TABLE public.test_block (id pg_catalog.int4 ) ' .
+ 'WITH (oids=OFF) ';
+PgLogExecute(COMMAND_CREATE_TABLE, $strSql, 'public.test_block', false, false);
+
+$strSql = 'drop table test_block';
+PgLogExecute(COMMAND_DROP_TABLE, $strSql, 'public.test_block', false, false);
+
+# Generate an error in a do block and make sure the stack gets cleaned up
+$strSql = 'do $$ ' .
+ 'begin ' .
+ ' create table bobus.test_block (id int); ' .
+ 'end; $$';
+
+PgLogExecute(COMMAND_DO, $strSql, undef, undef, undef, undef, undef, true);
+# PgLogExecute(COMMAND_SELECT, 'select 1');
+# exit 0;
+
+# Try explain
+PgLogExecute(COMMAND_SELECT, 'explain select 1', undef, true, false);
+PgLogExecute(COMMAND_EXPLAIN, 'explain select 1', undef, false, true);
+
+# Now set grant to a specific column to audit and make sure it logs
+# Make sure the the converse is true
+PgAuditGrantSet($strAuditRole, &COMMAND_SELECT, 'public.test',
+ 'name, description');
+PgLogExecute(COMMAND_SELECT, 'select id from test');
+
+@oyTable = ({&NAME => 'public.test', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_SELECT});
+PgLogExecute(COMMAND_SELECT, 'select name from test', \@oyTable);
+
+# Test alter and drop table statements
+$strSql = 'ALTER TABLE public.test DROP COLUMN description ';
+PgLogExecute(COMMAND_ALTER_TABLE_COLUMN,
+ $strSql, 'public.test.description', true, false);
+PgLogExecute(COMMAND_ALTER_TABLE,
+ $strSql, 'public.test', false, true);
+@oyTable = ({&NAME => 'public.test', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_SELECT});
+PgLogExecute(COMMAND_SELECT, 'select from test', \@oyTable);
+
+$strSql = 'ALTER TABLE public.test RENAME TO test2';
+PgLogExecute(COMMAND_ALTER_TABLE, $strSql, 'public.test2');
+
+$strSql = 'ALTER TABLE public.test2 SET SCHEMA test';
+PgLogExecute(COMMAND_ALTER_TABLE, $strSql, 'test.test2');
+
+$strSql = 'ALTER TABLE test.test2 ADD COLUMN description pg_catalog.text ' .
+ 'COLLATE pg_catalog."default"';
+PgLogExecute(COMMAND_ALTER_TABLE, $strSql, 'test.test2');
+
+$strSql = 'ALTER TABLE test.test2 DROP COLUMN description ';
+PgLogExecute(COMMAND_ALTER_TABLE_COLUMN, $strSql,
+ 'test.test2.description', true, false);
+PgLogExecute(COMMAND_ALTER_TABLE, $strSql,
+ 'test.test2', false, true);
+
+$strSql = 'drop table test.test2';
+PgLogExecute(COMMAND_DROP_TABLE, $strSql, 'test.test2', true, false);
+PgLogExecute(COMMAND_DROP_TABLE_CONSTRAINT, $strSql, 'test_pkey on test.test2',
+ false, false);
+PgLogExecute(COMMAND_DROP_TABLE_INDEX, $strSql, 'test.test_pkey', false, true);
+
+$strSql = "CREATE FUNCTION public.int_add(IN a pg_catalog.int4 , IN b " .
+ "pg_catalog.int4 ) RETURNS pg_catalog.int4 LANGUAGE plpgsql
" .
+ "VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100
AS '" .
+ " begin return a + b; end '";
+PgLogExecute(COMMAND_CREATE_FUNCTION, $strSql,
+ 'public.int_add(integer,integer)');
+PgLogExecute(COMMAND_SELECT, "select int_add(1, 1)",
+ undef, true, false);
+PgLogExecute(COMMAND_EXECUTE_FUNCTION, "select int_add(1, 1)",
+
'public.int_add', false, true);
+
+$strSql = "CREATE AGGREGATE public.sum_test( pg_catalog.int4) " .
+ "(SFUNC=public.int_add, STYPE=pg_catalog.int4, INITCOND='0')";
+PgLogExecute(COMMAND_CREATE_AGGREGATE, $strSql, 'public.sum_test(integer)');
+
+# There's a bug here in deparse:
+$strSql = "ALTER AGGREGATE public.sum_test(integer) RENAME TO sum_test2";
+PgLogExecute(COMMAND_ALTER_AGGREGATE, $strSql, 'public.sum_test2(integer)');
+
+$strSql = "CREATE COLLATION public.collation_test (LC_COLLATE = 'de_DE', " .
+ "LC_CTYPE = 'de_DE')";
+PgLogExecute(COMMAND_CREATE_COLLATION, $strSql, 'public.collation_test');
+
+$strSql = "ALTER COLLATION public.collation_test RENAME TO collation_test2";
+PgLogExecute(COMMAND_ALTER_COLLATION, $strSql, 'public.collation_test2');
+
+$strSql = "CREATE CONVERSION public.conversion_test FOR 'SQL_ASCII' " .
+ "TO 'MULE_INTERNAL' FROM pg_catalog.ascii_to_mic";
+PgLogExecute(COMMAND_CREATE_CONVERSION, $strSql, 'public.conversion_test');
+
+$strSql = "ALTER CONVERSION public.conversion_test RENAME TO conversion_test2";
+PgLogExecute(COMMAND_ALTER_CONVERSION, $strSql, 'public.conversion_test2');
+
+PgLogExecute(COMMAND_CREATE_DATABASE, "CREATE DATABASE database_test");
+PgLogExecute(COMMAND_ALTER_DATABASE,
+ "ALTER DATABASE database_test rename to
database_test2");
+PgLogExecute(COMMAND_DROP_DATABASE, "DROP DATABASE database_test2");
+
+# Make sure there are no more audit events pending in the postgres log
+PgLogWait();
+
+# Stop the database
+if (!$bNoCleanup)
+{
+ PgDrop();
+}
diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml
index a698d0f..5b247a9 100644
--- a/doc/src/sgml/contrib.sgml
+++ b/doc/src/sgml/contrib.sgml
@@ -124,6 +124,7 @@ CREATE EXTENSION <replaceable>module_name</> FROM
unpackaged;
<ree;
&pageinspect;
&passwordcheck;
+ &pgaudit;
&pgbuffercache;
&pgcrypto;
&pgfreespacemap;
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 89fff77..6b0b407 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -125,6 +125,7 @@
<!ENTITY oid2name SYSTEM "oid2name.sgml">
<!ENTITY pageinspect SYSTEM "pageinspect.sgml">
<!ENTITY passwordcheck SYSTEM "passwordcheck.sgml">
+<!ENTITY pgaudit SYSTEM "pgaudit.sgml">
<!ENTITY pgbench SYSTEM "pgbench.sgml">
<!ENTITY pgarchivecleanup SYSTEM "pgarchivecleanup.sgml">
<!ENTITY pgbuffercache SYSTEM "pgbuffercache.sgml">
diff --git a/doc/src/sgml/pgaudit.sgml b/doc/src/sgml/pgaudit.sgml
new file mode 100644
index 0000000..4588619
--- /dev/null
+++ b/doc/src/sgml/pgaudit.sgml
@@ -0,0 +1,347 @@
+<!-- doc/src/sgml/pgaudit.sgml -->
+
+<sect1 id="pgaudit" xreflabel="pgaudit">
+ <title>pg_audit</title>
+
+ <indexterm zone="pgaudit">
+ <primary>pg_audit</primary>
+ </indexterm>
+
+ <para>
+ The <filename>pg_audit</filename> module provides session and object
+ auditing via the standard logging facility. Session and object auditing
are
+ completely independent and can be combined.
+ </para>
+
+ <sect2>
+ <title>Session Auditing</title>
+
+ <para>
+ Session auditing allows the logging of all commands that are executed by
+ a user in the backend. Each command is logged with a single entry and
+ includes the audit type (e.g. <literal>SESSION</literal>), command type
+ (e.g. <literal>CREATE TABLE</literal>, <literal>SELECT</literal>) and
+ statement (e.g. <literal>"select * from test"</literal>).
+
+ Fully-qualified names and object types will be logged for
+ <literal>CREATE</literal>, <literal>UPDATE</literal>, and
+ <literal>DROP</literal> commands on <literal>TABLE</literal>,
+ <literal>MATVIEW</literal>, <literal>VIEW</literal>,
+ <literal>INDEX</literal>, <literal>FOREIGN TABLE</literal>,
+ <literal>COMPOSITE TYPE</literal>, <literal>INDEX</literal>, and
+ <literal>SEQUENCE</literal> objects as well as function calls.
+ </para>
+
+ <sect3>
+ <title>Configuration</title>
+
+ <para>
+ Session logging is controlled by the <literal>pg_audit.log</literal>
+ GUC. There are six classes of commands that are recognized:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>READ</literal> - <literal>SELECT</literal> and
+ <literal>COPY</literal> when the source is a table or query.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>WRITE</literal> - <literal>INSERT</literal>,
+ <literal>UPDATE</literal>, <literal>DELETE</literal>,
+ <literal>TRUNCATE</literal>, and <literal>COPY</literal> when the
+ destination is a table.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>FUNCTION</literal> - Function calls and
+ <literal>DO</literal> blocks.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>DDL</literal> - DDL, plus <literal>VACUUM</literal>,
+ <literal>REINDEX</literal>, and <literal>ANALYZE</literal>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>PARAMETER</literal> - Parameters that were passed for
the statement. Parameters immediately follow the statement text.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>MISC</literal> - Miscellaneous commands, e.g.
+ <literal>DISCARD</literal>, <literal>FETCH</literal>,
+ <literal>CHECKPOINT</literal>.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ Enable session logging for all writes and DDL:
+ <programlisting>
+pg_audit.log = 'write, ddl'
+ </programlisting>
+ </para>
+
+ <para>
+ Enable session logging for all commands except miscellaneous:
+ <programlisting>
+pg_audit.log = 'all, -misc'
+ </programlisting>
+ </para>
+
+ <para>
+ Note that <literal>pg_audit.log</literal> can be set globally (in
+ <filename>postgresql.conf</filename>), at the database level (using
+ <literal>alter database ... set</literal>), or at the role level (using
+ <literal>alter role ... set</literal>).
+ </para>
+ </sect3>
+
+ <sect3>
+ <title>Examples</title>
+
+ <para>
+ Set <literal>pg_audit.log = 'read, ddl'</literal> in
+ <literal>postgresql.conf</literal>.
+ </para>
+
+ <para>
+ SQL:
+ </para>
+
+ <programlisting>
+create table account
+(
+ id int,
+ name text,
+ password text,
+ description text
+);
+
+select *
+ from account;
+
+insert into account (id, name, password, description)
+ values (1, 'user1', 'HASH1', 'blah, blah');
+ </programlisting>
+
+ <para>
+ Log Output:
+ </para>
+
+ <programlisting>
+AUDIT: SESSION,DDL,CREATE TABLE,TABLE,public.account,create table account
+(
+ id int,
+ name text,
+ password text,
+ description text
+);
+AUDIT: SESSION,READ,SELECT,,,select *
+ from account
+ </programlisting>
+ </sect3>
+ </sect2>
+
+ <sect2>
+ <title>Object Auditing</title>
+
+ <para>
+ Object auditing logs commands that affect a particular object. Only
+ <literal>SELECT</literal>, <literal>INSERT</literal>,
+ <literal>UPDATE</literal> and <literal>DELETE</literal> commands are
+ supported.
+ </para>
+
+ <sect3>
+ <title>Configuration</title>
+
+ <para>
+ Object-level auditing is implemented via the roles system. The
+ <literal>pg_audit.role</literal> GUC defines the role that will be used
+ for auditing. An object will be audited when the audit role has
+ permissions for the command executed or inherits the permissions from
+ another role.
+ </para>
+
+ <programlisting>
+postresql.conf: pg_audit.role = 'audit'
+
+grant select, delete
+ on public.account;
+ </programlisting>
+
+ <para>
+ Note that <literal>pg_audit.role</literal> can be set globally (in
+ <filename>postgresql.conf</filename>), at the database level (using
+ <literal>alter database ... set</literal>), or at the role level (using
+ <literal>alter role ... set</literal>).
+ </para>
+ </sect3>
+
+ <sect3>
+ <title>Examples</title>
+
+ <para>
+ Set <literal>pg_audit.role = 'audit'</literal> in
+ <literal>postgresql.conf</literal>.
+ </para>
+
+ <para>
+ SQL:
+ </para>
+
+ <programlisting>
+create table account
+(
+ id int,
+ name text,
+ password text,
+ description text
+);
+
+grant select (password)
+ on public.account
+ to audit;
+
+select id, name
+ from account;
+
+select password
+ from account;
+
+grant update (name, password)
+ on public.account
+ to audit;
+
+update account
+ set description = 'yada, yada';
+
+update account
+ set password = 'HASH2';
+
+create table account_role_map
+(
+ account_id int,
+ role_id int
+);
+
+grant select
+ on public.account_role_map
+ to audit;
+
+select account.password,
+ account_role_map.role_id
+ from account
+ inner join account_role_map
+ on account.id = account_role_map.account_id
+ </programlisting>
+
+ <para>
+ Log Output:
+ </para>
+
+ <programlisting>
+AUDIT: OBJECT,READ,SELECT,TABLE,public.account,select password
+ from account
+AUDIT: OBJECT,WRITE,UPDATE,TABLE,public.account,update account
+ set password = 'HASH2'
+AUDIT: OBJECT,READ,SELECT,TABLE,public.account,select account.password,
+ account_role_map.role_id
+ from account
+ inner join account_role_map
+ on account.id = account_role_map.account_id
+AUDIT: OBJECT,READ,SELECT,TABLE,public.account_role_map,select
account.password,
+ account_role_map.role_id
+ from account
+ inner join account_role_map
+ on account.id = account_role_map.account_id
+ </programlisting>
+ </sect3>
+ </sect2>
+
+ <sect2>
+ <title>Format</title>
+
+ <para>
+ Audit entries are written to the standard logging facility and contain
+ the following columns in comma-separated format:
+
+ <note>
+ <para>
+ Output is compliant CSV format only if the log line prefix portion
+ of each log entry is removed.
+ </para>
+ </note>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>AUDIT_TYPE</literal> - <literal>SESSION</literal> or
+ <literal>OBJECT</literal>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>STATEMENT_ID</literal> - Unique statement ID for this
+ session. Each statement ID represents a backend call.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>SUBSTATEMENT_ID</literal> - Sequential ID for each
+ substatement within the main statement. For example, calling
+ a function from a query.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>CLASS</literal> - <literal>READ</literal>,
+ <literal>WRITE</literal>, <literal>FUNCTION</literal>,
+ <literal>DDL</literal>, or <literal>MISC</literal>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>COMMAND</literal> - <literal>ALTER TABLE</literal>,
+ <literal>SELECT</literal>, <literal>CREATE INDEX</literal>,
+ <literal>UPDATE</literal>, etc.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>OBJECT_TYPE</literal> - <literal>TABLE</literal>,
+ <literal>INDEX</literal>, <literal>VIEW</literal>, etc. Only
+ available for DML and certain DDL commands.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>OBJECT_NAME</literal> - The fully-qualified object name
+ (e.g. public.account). Only available for DML and certain DDL
+ commands.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>STATEMENT</literal> - Statement execute on the backend.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>Authors</title>
+
+ <para>
+ Abhijit Menon-Sen <email>a...@2ndquadrant.com</email>, Ian Barwick
<email>i...@2ndquadrant.com</email>, and David Steele
<email>da...@pgmasters.net</email>.
+ </para>
+ </sect2>
+</sect1>
signature.asc
Description: OpenPGP digital signature
