I've posted a couple of messages over the last few weeks about the work I've been doing on the pg_audit extension. The lack of response could be due to either universal acclaim or complete apathy, but in any case I think this is a very important topic so I want to give it another try.
I've extensively reworked the code that was originally submitted by 2ndQuandrant. This is not an indictment of their work, but rather an attempt to redress concerns that were expressed by members of the community. I've used core functions to determine how audit events should be classified and simplified and tightened the code wherever possible. I've removed deparse and event triggers and opted for methods that rely only on existing hooks. In my last message I provided numerous examples of configuration, usage, and output which I hoped would alleviate concerns of complexity. I've also written a ton of unit tests to make sure that the code works as expected. Auditing has been a concern everywhere I've used or introduced PostgreSQL. Over time I've developed a reasonably comprehensive (but complex) system of triggers, tables and functions to provide auditing for customers. While this has addressed most requirements, there are always questions of auditing aborted transactions, DDL changes, configurability, etc. which I have been unable to satisfy. There has been some discussion of whether or not this module needs to be in contrib. One reason customers trust contrib is that the modules are assumed to be held to a higher standard than code found on GitHub. This has already been pointed out. But I believe another important reason is that they know packages will be made available for a variety of platforms, and bugs are more likely to be experienced by many users and not just a few (or one). For this reason my policy is not to run custom-compiled code in production. This is especially true for something as mission critical as a relational database. I realize this is not an ideal solution. Everybody (including me) wants something that is in core with real policies and more options. It's something that I am personally really eager to work on. But the reality is that's not going to happen for 9.5 and probably not for 9.6 either. Meanwhile, I believe the lack of some form of auditing is harming adoption of PostgreSQL, especially in the financial and government sectors. The patch I've attached satisfies the requirements that I've had from customers in the past. I'm confident that if it gets out into the wild it will bring all kinds of criticism and comments which will be valuable in designing a robust in-core solution. I'm submitting this patch to the Commitfest. I'll do everything I can to address the concerns of the community and I'm happy to provide more examples as needed. I'm hoping the sgml docs I've provided with the patch will cover any questions, but of course feedback is always appreciated. -- - 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..b3914ac
--- /dev/null
+++ b/contrib/pg_audit/pg_audit.c
@@ -0,0 +1,1099 @@
+/*------------------------------------------------------------------------------
+ * 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.
+ *
+ * Copyright (c) 2014-2015, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/pg_prewarm/pg_prewarm.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 contants for audit types - used when logging to distinguish session
+ * vs. object auditing.
+ */
+#define AUDIT_TYPE_OBJECT "OBJECT"
+#define AUDIT_TYPE_SESSION "SESSION"
+
+/*
+ * String contants 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 contants 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"
+
+/*
+ * This module collects AuditEvents from various sources (event triggers, and
+ * executor/utility hooks) and passes them to the log_audit_event() function.
+ *
+ * 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;
+AuditEvent utilityAuditEvent;
+
+/*
+ * Returns the oid of the role specified in pgaudit.role.
+ */
+static Oid
+audit_role_oid()
+{
+ HeapTuple roleTup;
+ Oid oid = InvalidOid;
+
+ roleTup = SearchSysCache1(AUTHNAME, PointerGetDatum(auditRole));
+
+ if (HeapTupleIsValid(roleTup))
+ {
+ oid = HeapTupleGetOid(roleTup);
+ ReleaseSysCache(roleTup);
+ }
+
+ return oid;
+}
+
+/*
+ * 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;
+ }
+ }
+
+ /* Make a copy of the column set */
+ 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;
+
+ foreach(lr, rangeTabls)
+ {
+ Oid relOid;
+ Relation rel;
+ RangeTblEntry *rte = lfirst(lr);
+ AuditEvent auditEvent;
+
+ /* 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);
+ }
+}
+
+/*
+ * 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 = audit_role_oid();
+
+ if ((auditOid != InvalidOid || auditLogBitmap != 0) &&
+ !IsAbortedTransactionBlockState())
+ log_dml(auditOid, rangeTabls);
+
+ 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;
+
+ 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);
+ }
+}
+
+/*
+ * 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 pgaudit.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("pgaudit must be loaded via
shared_preload_libraries")));
+
+ /*
+ * pgaudit.role = "role1"
+ *
+ * This variable defines a role to be used for auditing.
+ */
+ DefineCustomStringVariable("pgaudit.role",
+ "Enable auditing for
role",
+ NULL,
+ &auditRole,
+ "",
+ PGC_SUSET,
+ GUC_LIST_INPUT |
GUC_NOT_IN_SAMPLE,
+ NULL, NULL, NULL);
+
+ /*
+ * pgaudit.log = "read, write, ddl"
+ *
+ * This variables controls what classes of commands are logged.
+ */
+ DefineCustomStringVariable("pgaudit.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..3a89d50
--- /dev/null
+++ b/contrib/pg_audit/test/test.pl
@@ -0,0 +1,1220 @@
+#!/usr/bin/perl
+################################################################################
+# test.pl - pgAudit 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_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 pgaudit.log GUCS
+my %oAuditGrantHash; # Hash to store pgaudit grants
+
+my $strCurrentAuditLog; # pgaudit.log setting that Postgres was
started with
+my $strTemporaryAuditLog; # pgaudit.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_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
pgaudit.log='${strCurrentAuditLog}'" : '') .
+ " -c pgaudit.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 pgaudit.log = '" .
+ ArrayToString(@stryClass) . "'";
+ $strTemporaryAuditLog = ArrayToString(@stryClass);
+ }
+ elsif ($strContext eq CONTEXT_ROLE)
+ {
+ $strCommand = COMMAND_ALTER_ROLE_SET;
+ $strSql = "alter role ${strName} set pgaudit.log = '" .
+ ArrayToString(@stryClass) . "'";
+ }
+ else
+ {
+ confess "unable to set pgaudit.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));
+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>");
+
+# Now tests for object logging
+&log("\nObject Audit:\n");
+
+PgSetUser('postgres');
+PgAuditLogSet(CONTEXT_GLOBAL, undef, (CLASS_NONE));
+PgExecute("set pgaudit.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 pgaudit.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');
+
+# 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_EXECUTE_FUNCTION, "select int_add(1, 1)",
+
'public.int_add');
+
+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..f3f4ab9
--- /dev/null
+++ b/doc/src/sgml/pgaudit.sgml
@@ -0,0 +1,316 @@
+<!-- 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>pgaudit.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>
+pgaudit.log = 'write, ddl'
+ </programlisting>
+ </para>
+
+ <para>
+ Enable session logging for all commands except miscellaneous:
+ <programlisting>
+pgaudit.log = 'all, -misc'
+ </programlisting>
+ </para>
+ </sect3>
+
+ <sect3>
+ <title>Examples</title>
+
+ <para>
+ Set <literal>pgaudit.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>pgaudit.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: pgaudit.role = 'audit'
+
+grant select, delete
+ on public.account;
+ </programlisting>
+ </sect3>
+
+ <sect3>
+ <title>Examples</title>
+
+ <para>
+ Set <literal>pgaudit.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>Author</title>
+
+ <para>
+ David Steele <email>da...@pgmasters.net</email>
+ </para>
+ </sect2>
+</sect1>
signature.asc
Description: OpenPGP digital signature
