On 3/23/15 1:39 PM, Sawada Masahiko wrote: > On Tue, Mar 24, 2015 at 1:40 AM, David Steele <da...@pgmasters.net> wrote: >> >> I have prepared a patch that brings event triggers and deparse back to >> pg_audit based on the Alvaro's dev/deparse branch at >> git://git.postgresql.org/git/2ndquadrant_bdr.git (commit 0447fc5). I've >> updated the unit tests accordingly. >> >> I've been hesitant to post this patch as it will not work in master >> (though it will compile), but I don't want to hold on to it any longer >> since the end of the CF is nominally just weeks away. If you want to >> run the patch in master, you'll need to disable the >> pg_audit_ddl_command_end trigger. > > Hi, > > I tied to look into latest patch, but got following error. > > masahiko [pg_audit] $ LANG=C make > gcc -Wall -Wmissing-prototypes -Wpointer-arith > -Wdeclaration-after-statement -Wendif-labels > -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing > -fwrapv -g -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -c -o > pg_audit.o pg_audit.c > pg_audit.c: In function 'log_audit_event': > pg_audit.c:456: warning: ISO C90 forbids mixed declarations and code > pg_audit.c: In function 'pg_audit_ddl_command_end': > pg_audit.c:1436: error: 'pg_event_trigger_expand_command' undeclared > (first use in this function) > pg_audit.c:1436: error: (Each undeclared identifier is reported only once > pg_audit.c:1436: error: for each function it appears in.) > make: *** [pg_audit.o] Error 1 > > Am I missing something? >
It's my mistake. I indicated that this would compile under master - but that turns out not to be true because of this function. It will only compile cleanly in Alvaro's branch mentioned above. My apologies - this is why I have been hesitant to post this patch before. You are welcome to try with Alvaro's deparse branch or wait until it has been committed to master. I've attached patch v5 only to cleanup the warnings you saw. -- - 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..65c8ed2
--- /dev/null
+++ b/contrib/pg_audit/pg_audit.c
@@ -0,0 +1,1712 @@
+/*------------------------------------------------------------------------------
+ * 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 pgaudit.role GUC, which contains the
+ * role for grant-based auditing.
+ */
+char *auditRole = NULL;
+
+/*
+ * auditLog is the string value of the pgaudit.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
+ * pgaudit.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;
+
+ 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 uint64 statementTotal = 0;
+static uint64 substatementTotal = 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 the 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;
+
+ /*
+ * 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()
+{
+ /* Error if the stack is already empty */
+ if (auditEventStack == NULL)
+ elog(ERROR, "pg_audit stack is already empty");
+
+ /* Switch the old memory context and delete the audit context */
+ MemoryContextDelete(auditEventStack->contextAudit);
+}
+
+/*
+ * Takes an AuditEvent and returns true or false depending on whether the event
+ * should be logged according to the pgaudit.roles/log settings. If it returns
+ * true, also fills in the name of the LogClass which it is logged under.
+ */
+static bool
+log_check(AuditEvent *e, const char **classname)
+{
+ enum LogClass class = LOG_NONE;
+
+ /* By default put everything in the MISC class. */
+ *classname = CLASS_MISC;
+ class = LOG_MISC;
+
+ /*
+ * Look at the type of the command and decide what LogClass needs to be
+ * enabled for the command to be logged.
+ */
+ switch (e->logStmtLevel)
+ {
+ case LOGSTMT_MOD:
+ *classname = CLASS_WRITE;
+ class = LOG_WRITE;
+ break;
+
+ case LOGSTMT_DDL:
+ *classname = CLASS_DDL;
+ class = LOG_DDL;
+
+ case LOGSTMT_ALL:
+ switch (e->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;
+ }
+
+ /*
+ * We log audit events under the following conditions:
+ *
+ * 1. If the audit role has been explicitly granted permission for
+ * an operation.
+ */
+ if (e->granted)
+ {
+ return true;
+ }
+
+ /* 2. If the event belongs to a class covered by pgaudit.log. */
+ if ((auditLogBitmap & class) == class)
+ {
+ return true;
+ }
+
+ return false;
+}
+
+/*
+ * 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 and, if it log_check(), writes it to the audit log. The
+ * AuditEvent is assumed to be completely filled in by the caller (unknown
+ * values must be set to "" so that they can be logged without error checking).
+ */
+static void
+log_audit_event(AuditEventStackItem *stackItem)
+{
+ const char *classname;
+ MemoryContext contextOld;
+ StringInfoData auditStr;
+
+ /* Check that this event should be logged. */
+ if (!log_check(&stackItem->auditEvent, &classname))
+ 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
+log_acl_check(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
+log_relation_check(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 = log_acl_check(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
+log_attribute_check(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 = log_acl_check(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
+log_attribute_check_any(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 (log_attribute_check(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 &&
+ log_attribute_check(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 (log_relation_check(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 =
+ log_attribute_check_any(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 =
+
log_attribute_check_any(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();
+}
+
+/*
+ * 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
+pgaudit_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
+pgaudit_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
+pgaudit_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();
+ }
+}
+
+/*
+ * Hook ProcessUtility to do session auditing for DDL and utility commands.
+ */
+static void
+pgaudit_ProcessUtility_hook(Node *parsetree,
+ const char *queryString,
+ ProcessUtilityContext
context,
+ ParamListInfo params,
+ DestReceiver *dest,
+ char *completionTag)
+{
+ AuditEventStackItem *stackItem = NULL;
+
+ /* 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();
+
+ 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);
+
+ if (context == PROCESS_UTILITY_TOPLEVEL)
+ {
+ while (auditEventStack != NULL)
+ stack_pop();
+ }
+ else
+ stack_pop();
+ }
+}
+
+/*
+ * 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
+pgaudit_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,
+ "pgaudit_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,
+ "pgaudit_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_pgaudit_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_pgaudit_log.
+ */
+ *extra = f;
+
+ return true;
+}
+
+/*
+ * Set pgaudit_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_pgaudit_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_pgaudit_log,
+ assign_pgaudit_log,
+ NULL);
+
+ /*
+ * Install our hook functions after saving the existing pointers to
preserve
+ * the chain.
+ */
+ next_ExecutorStart_hook = ExecutorStart_hook;
+ ExecutorStart_hook = pgaudit_ExecutorStart_hook;
+
+ next_ExecutorCheckPerms_hook = ExecutorCheckPerms_hook;
+ ExecutorCheckPerms_hook = pgaudit_ExecutorCheckPerms_hook;
+
+ next_ExecutorEnd_hook = ExecutorEnd_hook;
+ ExecutorEnd_hook = pgaudit_ExecutorEnd_hook;
+
+ next_ProcessUtility_hook = ProcessUtility_hook;
+ ProcessUtility_hook = pgaudit_ProcessUtility_hook;
+
+ next_object_access_hook = object_access_hook;
+ object_access_hook = pgaudit_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..f5cbde2
--- /dev/null
+++ b/contrib/pg_audit/test/test.pl
@@ -0,0 +1,1412 @@
+#!/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();
+
+# 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..f9152cd
--- /dev/null
+++ b/doc/src/sgml/pgaudit.sgml
@@ -0,0 +1,335 @@
+<!-- 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 not in compliant CSV format. If machine-readability is
+ required then consider setting
+ <literal>log_destination = 'csvlog'</literal>.
+ </para>
+ </note>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>AUDIT_TYPE</literal> - <literal>SESSION</literal> or
+ <literal>OBJECT</literal>.
+ </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
