On 2/23/15 10:59 AM, David Steele wrote: > On 2/17/15 10:34 AM, Stephen Frost wrote: >> There seems to be a number of places which are 'pgaudit' and a bunch >> that are 'pg_audit'. I'm guessing you were thinking 'pg_audit', but >> it'd be good to clean up and make them all consistent. > > Fixed, though I still left the file name as pgaudit.sgml since all but > one module follow that convention.
It turns out there are a few places where _ is not allowed. I've reverted a few places to fix the doc build while maintaining the name as pg_audit in the visible docs. >> Perhaps I missed it, but it'd be good to point out that GUCs can be set >> at various levels. I know we probably say that somewhere else, but it's >> particularly relevant for this. I added notes as suggested. Patch v3 is attached. -- - 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..2eee3b9
--- /dev/null
+++ b/contrib/pg_audit/pg_audit--1.0.0.sql
@@ -0,0 +1,4 @@
+/* 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
diff --git a/contrib/pg_audit/pg_audit.c b/contrib/pg_audit/pg_audit.c
new file mode 100644
index 0000000..ead65a8
--- /dev/null
+++ b/contrib/pg_audit/pg_audit.c
@@ -0,0 +1,1102 @@
+/*------------------------------------------------------------------------------
+ * 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);
+
+/*
+ * 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_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,
+
+ /* SELECT */
+ LOG_READ = (1 << 0),
+
+ /* INSERT, UPDATE, DELETE, TRUNCATE */
+ LOG_WRITE = (1 << 1),
+
+ /* DDL: CREATE/DROP/ALTER */
+ LOG_DDL = (1 << 2),
+
+ /* Function execution */
+ LOG_FUNCTION = (1 << 4),
+
+ /* Function execution */
+ LOG_MISC = (1 << 5),
+
+ /* 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 an underlying command affects multiple objects multiple
+ * AuditEvents must be created to represent it.
+ */
+typedef struct
+{
+ LogStmtLevel logStmtLevel;
+ NodeTag commandTag;
+ const char *command;
+ const char *objectType;
+ char *objectName;
+ const char *commandText;
+ bool granted;
+} AuditEvent;
+
+/*
+ * Set if a function below log_utility_command() has logged the event -
prevents
+ * more than one function from logging when the event could be logged in
+ * multiple places.
+ */
+bool utilityCommandLogged = false;
+bool utilityCommandInProgress = false;
+AuditEvent utilityAuditEvent;
+
+/*
+ * 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;
+}
+
+/*
+ * 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(AuditEvent *e)
+{
+ const char *classname;
+
+ /* Check that this event should be logged. */
+ if (!log_check(e, &classname))
+ return;
+
+ /* Log via ereport(). */
+ ereport(LOG,
+ (errmsg("AUDIT: %s,%s,%s,%s,%s,%s",
+ e->granted ? AUDIT_TYPE_OBJECT :
AUDIT_TYPE_SESSION,
+ classname, e->command, e->objectType,
e->objectName,
+ e->commandText),
+ errhidestmt(true)));
+}
+
+/*
+ * 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 DML operations via executor permissions checks.
+ */
+static void
+log_dml(Oid auditOid, List *rangeTabls)
+{
+ ListCell *lr;
+ bool first = true;
+ AuditEvent auditEvent;
+
+ 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;
+
+ /*
+ * Filter out any system relations
+ */
+ relOid = rte->relid;
+ rel = relation_open(relOid, NoLock);
+
+ if (IsSystemNamespace(RelationGetNamespace(rel)))
+ {
+ relation_close(rel, NoLock);
+ return;
+ }
+
+ /*
+ * 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.
+ */
+ auditEvent.logStmtLevel = LOGSTMT_MOD;
+
+ if (rte->requiredPerms & ACL_INSERT)
+ {
+ auditEvent.commandTag = T_InsertStmt;
+ auditEvent.command = COMMAND_INSERT;
+ }
+ else if (rte->requiredPerms & ACL_UPDATE)
+ {
+ auditEvent.commandTag = T_UpdateStmt;
+ auditEvent.command = COMMAND_UPDATE;
+ }
+ else if (rte->requiredPerms & ACL_DELETE)
+ {
+ auditEvent.commandTag = T_DeleteStmt;
+ auditEvent.command = COMMAND_DELETE;
+ }
+ else if (rte->requiredPerms & ACL_SELECT)
+ {
+ auditEvent.logStmtLevel = LOGSTMT_ALL;
+ auditEvent.commandTag = T_SelectStmt;
+ auditEvent.command = COMMAND_SELECT;
+ }
+ else
+ {
+ auditEvent.commandTag = T_Invalid;
+ auditEvent.command = COMMAND_UNKNOWN;
+ }
+
+ /*
+ * Fill values in the event struct that are required for session
+ * logging.
+ */
+ auditEvent.granted = false;
+ auditEvent.commandText = debug_query_string;
+
+ /* If this is the first rte then session log */
+ if (first)
+ {
+ auditEvent.objectName = "";
+ auditEvent.objectType = "";
+
+ log_audit_event(&auditEvent);
+
+ first = false;
+ }
+
+ /* Get the relation type */
+ switch (rte->relkind)
+ {
+ case RELKIND_RELATION:
+ auditEvent.objectType = OBJECT_TYPE_TABLE;
+ break;
+
+ case RELKIND_INDEX:
+ auditEvent.objectType = OBJECT_TYPE_INDEX;
+ break;
+
+ case RELKIND_SEQUENCE:
+ auditEvent.objectType = OBJECT_TYPE_SEQUENCE;
+ break;
+
+ case RELKIND_TOASTVALUE:
+ auditEvent.objectType = OBJECT_TYPE_TOASTVALUE;
+ break;
+
+ case RELKIND_VIEW:
+ auditEvent.objectType = OBJECT_TYPE_VIEW;
+ break;
+
+ case RELKIND_COMPOSITE_TYPE:
+ auditEvent.objectType =
OBJECT_TYPE_COMPOSITE_TYPE;
+ break;
+
+ case RELKIND_FOREIGN_TABLE:
+ auditEvent.objectType =
OBJECT_TYPE_FOREIGN_TABLE;
+ break;
+
+ case RELKIND_MATVIEW:
+ auditEvent.objectType = OBJECT_TYPE_MATVIEW;
+ break;
+
+ default:
+ auditEvent.objectType = OBJECT_TYPE_UNKNOWN;
+ break;
+ }
+
+ /* Get the relation name */
+ 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))
+ {
+ 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)
+ {
+ 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 (!auditEvent.granted)
+ {
+ auditPerms &= (ACL_INSERT | ACL_UPDATE);
+
+ if (auditPerms)
+ {
+ auditEvent.granted =
+
log_attribute_check_any(relOid, auditOid,
+
rte->modifiedCols,
+
auditPerms);
+ }
+ }
+ }
+ }
+
+ /* Only do relation level logging if a grant was found. */
+ if (auditEvent.granted)
+ {
+ log_audit_event(&auditEvent);
+ }
+
+ pfree(auditEvent.objectName);
+ }
+
+ /*
+ * If the first flag was never set to false, then rangeTabls was empty.
In
+ * this case log a session select statement.
+ */
+ if (first && !utilityCommandInProgress)
+ {
+ auditEvent.logStmtLevel = LOGSTMT_ALL;
+ auditEvent.commandTag = T_SelectStmt;
+ auditEvent.command = COMMAND_SELECT;
+ auditEvent.granted = false;
+ auditEvent.commandText = debug_query_string;
+ auditEvent.objectName = "";
+ auditEvent.objectType = "";
+
+ log_audit_event(&auditEvent);
+ }
+}
+
+/*
+ * 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);
+ utilityAuditEvent.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:
+ utilityAuditEvent.objectType =
OBJECT_TYPE_TABLE;
+ break;
+
+ case RELKIND_INDEX:
+ utilityAuditEvent.objectType =
OBJECT_TYPE_INDEX;
+ break;
+
+ case RELKIND_SEQUENCE:
+ utilityAuditEvent.objectType =
OBJECT_TYPE_SEQUENCE;
+ break;
+
+ case RELKIND_VIEW:
+ utilityAuditEvent.objectType = OBJECT_TYPE_VIEW;
+ break;
+
+ case RELKIND_COMPOSITE_TYPE:
+ utilityAuditEvent.objectType =
OBJECT_TYPE_COMPOSITE_TYPE;
+ break;
+
+ case RELKIND_FOREIGN_TABLE:
+ utilityAuditEvent.objectType =
OBJECT_TYPE_FOREIGN_TABLE;
+ break;
+
+ case RELKIND_MATVIEW:
+ utilityAuditEvent.objectType =
OBJECT_TYPE_MATVIEW;
+ break;
+
+ /*
+ * Any other cases will be handled by
log_utility_command().
+ */
+ default:
+ return;
+ break;
+ }
+
+ /* Log the event */
+ log_audit_event(&utilityAuditEvent);
+ utilityCommandLogged = true;
+ }
+}
+
+/*
+ * 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;
+
+ /* 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;
+ }
+
+ /* Generate the fully-qualified function name. */
+ utilityAuditEvent.objectName =
+
quote_qualified_identifier(get_namespace_name(proc->pronamespace),
+
NameStr(proc->proname));
+ ReleaseSysCache(proctup);
+
+ /* Log the event */
+ utilityAuditEvent.logStmtLevel = LOGSTMT_ALL;
+ utilityAuditEvent.commandTag = T_DoStmt;
+ utilityAuditEvent.command = COMMAND_EXECUTE;
+ utilityAuditEvent.objectType = OBJECT_TYPE_FUNCTION;
+ utilityAuditEvent.commandText = debug_query_string;
+
+ log_audit_event(&utilityAuditEvent);
+ utilityCommandLogged = true;
+}
+
+/*
+ * 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:
+ log_function_execute(objectId);
+ break;
+
+ /* Log create. */
+ case OAT_POST_CREATE:
+ {
+ ObjectAccessPostCreate *pc = arg;
+
+ if (pc->is_internal)
+ return;
+
+ log_create_alter_drop(classId, objectId);
+ }
+ break;
+
+ /* Log alter. */
+ case OAT_POST_ALTER:
+ {
+ ObjectAccessPostAlter *pa = arg;
+
+ if (pa->is_internal)
+ return;
+
+ log_create_alter_drop(classId, objectId);
+ }
+ break;
+
+ /* Log drop. */
+ case OAT_DROP:
+ {
+ 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;
+
+/*
+ * 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_dml(auditOid, rangeTabls);
+
+ /* Call the next hook function. */
+ if (next_ExecutorCheckPerms_hook &&
+ !(*next_ExecutorCheckPerms_hook) (rangeTabls, abort))
+ return false;
+
+ return true;
+}
+
+/*
+ * 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)
+{
+ /* Create the utility audit event. */
+ utilityCommandLogged = false;
+ utilityCommandInProgress = true;
+
+ utilityAuditEvent.logStmtLevel = GetCommandLogLevel(parsetree);
+ utilityAuditEvent.commandTag = nodeTag(parsetree);
+ utilityAuditEvent.command = CreateCommandTag(parsetree);
+ utilityAuditEvent.objectName = "";
+ utilityAuditEvent.objectType = "";
+ utilityAuditEvent.commandText = debug_query_string;
+ utilityAuditEvent.granted = false;
+
+ /* 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);
+
+ /* 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 && !utilityCommandLogged &&
+ !IsAbortedTransactionBlockState())
+ {
+ log_audit_event(&utilityAuditEvent);
+ }
+
+ utilityCommandInProgress = false;
+}
+
+/*
+ * 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())
+ log_object_access(access, classId, objectId, subId, arg);
+
+ if (next_object_access_hook)
+ (*next_object_access_hook) (access, classId, objectId, subId,
arg);
+}
+
+/*
+ * 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_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_LIST_INPUT |
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_ExecutorCheckPerms_hook = ExecutorCheckPerms_hook;
+ ExecutorCheckPerms_hook = pgaudit_ExecutorCheckPerms_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..0b39082
--- /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/pgaudit'
+relocatable = true
diff --git a/contrib/pg_audit/test/test.pl b/contrib/pg_audit/test/test.pl
new file mode 100755
index 0000000..dcb9fda
--- /dev/null
+++ b/contrib/pg_audit/test/test.pl
@@ -0,0 +1,1243 @@
+#!/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_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_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_INDEX => 'DROP TABLE INDEX',
+ 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_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_FUNCTION => 'FUNCTION',
+ TYPE_INDEX => 'INDEX',
+ TYPE_TABLE => 'TABLE',
+ 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_NONE},
+ &COMMAND_ALTER_DATABASE => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_NONE},
+ &COMMAND_ALTER_COLLATION => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_NONE},
+ &COMMAND_ALTER_CONVERSION => {&CLASS => &CLASS_DDL, &TYPE =>
&TYPE_NONE},
+ &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_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_NONE},
+ &COMMAND_CREATE_CONVERSION => {&CLASS => &CLASS_DDL, &TYPE =>
&TYPE_NONE},
+ &COMMAND_CREATE_COLLATION => {&CLASS => &CLASS_DDL, &TYPE =>
&TYPE_NONE},
+ &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_NONE},
+ &COMMAND_CREATE_ROLE => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_NONE},
+ &COMMAND_CREATE_SCHEMA => {&CLASS => &CLASS_DDL, &TYPE => &TYPE_NONE},
+ &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_INDEX => {&CLASS => &CLASS_DDL, &TYPE =>
&TYPE_INDEX,
+ &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_NONE},
+ &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_NONE},
+ &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;
+
+ # Set defaults
+ $bExecute = defined($bExecute) ? $bExecute : true;
+ $bWait = defined($bWait) ? $bWait : true;
+ $bLogSql = defined($bLogSql) ? $bLogSql : true;
+
+ if ($bExecute)
+ {
+ PgExecuteOnly($strSql);
+ }
+
+ PgLogExpect($strCommand, $bLogSql ? $strSql : '', $oData);
+
+ if ($bWait)
+ {
+ PgLogWait();
+ }
+}
+
+################################################################################
+# PgLogExpect
+################################################################################
+sub PgLogExpect
+{
+ my $strCommand = shift;
+ my $strSql = shift;
+ my $oData = shift;
+
+ # If oData is false then no logging
+ if (defined($oData) && ref($oData) eq '' && !$oData)
+ {
+ return;
+ }
+
+ # 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 = $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 = ${$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;
+
+ # 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_destination='stderr,csvlog'" .
+ # " -c logging_collector=on" .
+ (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 " . lc(${strPrivilege}) .
+
(defined($strColumn) ? " (${strColumn})" : '') .
+ " on
${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 " . lc(${strPrivilege}) .
+ (defined($strColumn) ? " (${strColumn})" : '')
.
+ " on ${strObject} from ${strRole}");
+
+ delete($oAuditGrantHash{$strRole}{$strObject}{$strPrivilege});
+}
+
+################################################################################
+# Main
+################################################################################
+my @oyTable; # Store table info for select, insert, update, delete
+
+# 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');
+PgLogExecute(COMMAND_CREATE_TABLE, 'create table test (id int)',
'public.test');
+PgLogExecute(COMMAND_SELECT, 'select * from test');
+
+PgLogExecute(COMMAND_DROP_TABLE, 'drop table test', '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();
+
+# Now 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));
+
+# !!! Trying to build test to exclude function calls (did not work)
+# PgLogExecute(COMMAND_CREATE_ROLE, 'create role func_owner');
+# PgAuditLogSet(CONTEXT_ROLE, 'func_owner');
+# PgLogExecute(COMMAND_SET, 'set role func_owner');
+# PgLogExecute(COMMAND_CREATE_FUNCTION, 'CREATE FUNCTION func_test(a int)' .
+# '
returns int as $$ begin return a + 1;' .
+# ' end
$$language plpgsql security definer');
+# PgLogExecute(COMMAND_GRANT, 'grant execute on function func_test(int) to
user1');
+
+PgSetUser('user1');
+
+PgLogExecute(COMMAND_CREATE_TABLE,
+ 'create table account (id int, name text, password
text,' .
+ ' description text)', '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>");
+
+# Test function without auditing (did not work)
+# PgLogExecute(COMMAND_SELECT, 'select func_test(1)');
+
+# 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; \$\$;");
+PgLogExecute(COMMAND_CREATE_SCHEMA, "create schema test");
+
+# Test COPY
+PgLogExecute(COMMAND_COPY_TO,
+ "COPY pg_class to '" . abs_path($strTestPath) .
"/class.out'");
+PgLogExecute(COMMAND_CREATE_TABLE_AS,
+ "CREATE TABLE test.pg_class as select * from pg_class",
+ 'test.pg_class', true, false);
+PgLogExecute(COMMAND_INSERT,
+ "CREATE TABLE test.pg_class as select * from pg_class",
+ undef, false, true);
+PgLogExecute(COMMAND_INSERT,
+ "COPY test.pg_class from '" . abs_path($strTestPath) .
+ "/class.out'", undef, true, false);
+PgLogExecute(COMMAND_COPY_FROM,
+ "COPY test.pg_class from '" . abs_path($strTestPath) .
+ "/class.out'", 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
+PgLogExecute(COMMAND_CREATE_TABLE,
+ 'create table test.test_insert (id int)',
'test.test_insert');
+PgLogExecute(COMMAND_PREPARE_WRITE,
+ 'PREPARE pgclassstmt (oid) as insert' .
+ ' into test.test_insert (id) values ($1)');
+PgLogExecute(COMMAND_INSERT,
+ 'EXECUTE pgclassstmt (1)', undef, true, false);
+PgLogExecute(COMMAND_EXECUTE_WRITE,
+ 'EXECUTE pgclassstmt (1)', undef, false, true);
+
+# Create a table with a primary key
+PgLogExecute(COMMAND_CREATE_TABLE,
+ 'create table test (id int primary key, name text,' .
+ 'description text)',
+ 'public.test', true, false);
+PgLogExecute(COMMAND_CREATE_INDEX,
+ 'create table test (id int primary key, name text,' .
+ 'description text)',
+ 'public.test_pkey', false, true);
+PgLogExecute(COMMAND_ANALYZE, 'analyze test');
+
+# Grant select to public - this should have no affect on auditing
+PgLogExecute(COMMAND_GRANT, 'grant select on public.test to public');
+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');
+PgLogExecute(COMMAND_GRANT, 'grant select (name) on public.test to public');
+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');
+
+# Try explain
+PgLogExecute(COMMAND_EXPLAIN, 'explain select 1');
+
+# 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);
+
+PgLogExecute(COMMAND_ALTER_TABLE,
+ 'alter table test drop description', 'public.test');
+@oyTable = ({&NAME => 'public.test', &TYPE => &TYPE_TABLE,
+ &COMMAND => &COMMAND_SELECT});
+PgLogExecute(COMMAND_SELECT, 'select from test', \@oyTable);
+
+PgLogExecute(COMMAND_ALTER_TABLE,
+ 'alter table test rename to test2', 'public.test');
+PgLogExecute(COMMAND_ALTER_TABLE,
+ 'alter table test2 set schema test', 'public.test2',
true, false);
+PgLogExecute(COMMAND_ALTER_TABLE_INDEX, 'alter table test2 set schema test',
+
'public.test_pkey', false, true);
+PgLogExecute(COMMAND_ALTER_TABLE, 'alter table test.test2 add description
text',
+ 'test.test2');
+PgLogExecute(COMMAND_ALTER_TABLE, 'alter table test.test2 drop description',
+ 'test.test2');
+PgLogExecute(COMMAND_DROP_TABLE_INDEX, 'drop table test.test2',
+
'test.test_pkey', false, false);
+PgLogExecute(COMMAND_DROP_TABLE, 'drop table test.test2',
+ 'test.test2',
true, true);
+
+PgLogExecute(COMMAND_CREATE_FUNCTION, 'CREATE FUNCTION int_add(a int, b int)' .
+ '
returns int as $$ begin return a + b;' .
+ ' end
$$language plpgsql');
+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);
+
+PgLogExecute(COMMAND_CREATE_AGGREGATE, "CREATE AGGREGATE sum_test (int)" .
+ " (sfunc = int_add,
stype = int, initcond = 0)");
+PgLogExecute(COMMAND_ALTER_AGGREGATE,
+ "ALTER AGGREGATE sum_test (int) rename to sum_test2");
+
+PgLogExecute(COMMAND_CREATE_COLLATION,
+ "CREATE COLLATION collation_test FROM \"de_DE\"");
+PgLogExecute(COMMAND_ALTER_COLLATION,
+ "ALTER COLLATION collation_test rename to
collation_test2");
+
+PgLogExecute(COMMAND_CREATE_CONVERSION,
+ "CREATE CONVERSION conversion_test FOR 'SQL_ASCII' TO".
+ " 'MULE_INTERNAL' FROM ascii_to_mic");
+PgLogExecute(COMMAND_ALTER_CONVERSION,
+ "ALTER CONVERSION conversion_test rename to
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 f03b72a..e4f0bdc 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -124,6 +124,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..d62d45f
--- /dev/null
+++ b/doc/src/sgml/pgaudit.sgml
@@ -0,0 +1,330 @@
+<!-- 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 five 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>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
