Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YPvQ2-0003GV-Ef for pgsql-hackers@arkaria.postgresql.org; Mon, 23 Feb 2015 15:59:38 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YPvQ1-0002jc-2x for pgsql-hackers@arkaria.postgresql.org; Mon, 23 Feb 2015 15:59:37 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YPvPy-0002jE-1B for pgsql-hackers@postgresql.org; Mon, 23 Feb 2015 15:59:34 +0000 Received: from linode.thelabyrinth.net ([198.58.117.137] helo=mail.thelabyrinth.net) by makus.postgresql.org with esmtps (TLS1.1:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YPvPr-00012J-0a for pgsql-hackers@postgresql.org; Mon, 23 Feb 2015 15:59:31 +0000 Received: from theseus.local (c-50-186-230-40.hsd1.fl.comcast.net [50.186.230.40]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) (Authenticated sender: dsteele) by mail.thelabyrinth.net (Postfix) with ESMTPSA id 671E112D2D; Mon, 23 Feb 2015 15:59:21 +0000 (UTC) Message-ID: <54EB4E56.7050102@pgmasters.net> Date: Mon, 23 Feb 2015 10:59:18 -0500 From: David Steele User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10; rv:31.0) Gecko/20100101 Thunderbird/31.4.0 MIME-Version: 1.0 To: Stephen Frost CC: pgsql-hackers@postgresql.org, Abhijit Menon-Sen Subject: Re: Auditing extension for PostgreSQL (Take 2) References: <54E005CC.1060605@pgmasters.net> <20150217153402.GQ6717@tamriel.snowman.net> In-Reply-To: <20150217153402.GQ6717@tamriel.snowman.net> OpenPGP: id=A2739CF3 Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="pQwTnlWsPAbpChDbU8RfFsJuewiDNTonh" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --pQwTnlWsPAbpChDbU8RfFsJuewiDNTonh Content-Type: multipart/mixed; boundary="------------040206060005060405030308" This is a multi-part message in MIME format. --------------040206060005060405030308 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable Hi Stephen, Thanks for your review. All fixed except for comments below: On 2/17/15 10:34 AM, Stephen Frost wrote: >> + /* >> + * Check privileges granted indirectly via role memberships. We do t= his in >> + * a separate pass to minimize expensive indirect membership tests. = In >> + * particular, it's worth testing whether a given ACL entry grants a= ny >> + * privileges still of interest before we perform the has_privs_of_r= ole >> + * test. >> + */ >=20 > I'm a bit on the fence about this.. Can you provide a use-case where > doing this makes sense..? Does this mean I could grant admin_role1 to > audit and then get auditing on everything that user1 has access to? > That seems like it might be useful for environments where such roles > already exist though it might end up covering more than is intended.. The idea is that if there are already ready-made roles to be audited then they don't need to be reconstituted for the audit role. You could just do: grant admin_role to audit; grant user_role to audit; Of course, we could list multiple roles in the pg_audit.role GUC, but I thought this would be easier to use and maintain since there was some worry about GUCs being fragile when they refer to database objects. >> +#####################################################################= ########### >> +# test.pl - pgAudit Unit Tests >> +#####################################################################= ########### >=20 > This is definitiely nice.. >=20 >> 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 @@ >> + >> + >> + >> + pg_audit >=20 > 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. >> + >> + Session auditing allows the logging of all commands that are ex= ecuted by >> + a user in the backend. Each command is logged with a single en= try and >> + includes the audit type (e.g. SESSION), comm= and type >> + (e.g. CREATE TABLE, SELECT) and >> + statement (e.g. "select * from test"). >> + >> + Fully-qualified names and object types will be logged for >> + CREATE, UPDATE, and >> + DROP commands on TABLE, >> + MATVIEW, VIEW, >> + INDEX, FOREIGN TABLE, >> + COMPOSITE TYPE, INDEX, an= d >> + SEQUENCE objects as well as function calls. >> + >=20 > Ah, you do have a list of what objects you get fully qualified names > for, nice. Are there obvious omissions from that list..? If so, we > might be able to change what happens with objectAccess to include them.= =2E It seems like these are the objects where having a name really matters. I'm more interested in using the deparse code to handle fully-qualified names for additional objects rather than adding hooks. >> + >> + Examples >> + >> + >> + Set pgaudit.log =3D 'read, ddl' in >> + postgresql.conf. >> + >=20 > 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. Yes, it's very relevant for this patch. Do you think it's enough to call out the functionality, or should I provide examples? Maybe a separate section just for this concept? Patch v2 is attached for all changes except the doc change above. --=20 - David Steele david@pgmasters.net --------------040206060005060405030308 Content-Type: text/plain; charset=UTF-8; x-mac-type="0"; x-mac-creator="0"; name="pg_audit-v2.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="pg_audit-v2.patch" diff --git a/contrib/Makefile b/contrib/Makefile index 195d447..d8e75f4 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -29,6 +29,7 @@ SUBDIRS =3D \ 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 =3D pg_audit +MODULE_big =3D pg_audit +OBJS =3D pg_audit.o + +EXTENSION =3D pg_audit + +DATA =3D pg_audit--1.0.0.sql + +ifdef USE_PGXS +PG_CONFIG =3D pg_config +PGXS :=3D $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir =3D contrib/pg_audit +top_builddir =3D ../.. +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_a= udit--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 EXTENSI= ON +\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 =3D NULL; + +/* + * auditLog is the string value of the pgaudit.log GUC, e.g. "read, writ= e, ddl" + * (it's not used by the module but is required by DefineCustomStringVar= iable). + * Each token corresponds to a flag in enum LogClass below. We convert t= he list + * of tokens into a bitmap in auditLogBitmap for internal use. + */ +char *auditLog =3D NULL; +static uint64 auditLogBitmap =3D 0; + +/* + * String constants for audit types - used when logging to distinguish s= ession + * 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 =3D 0, + + /* SELECT */ + LOG_READ =3D (1 << 0), + + /* INSERT, UPDATE, DELETE, TRUNCATE */ + LOG_WRITE =3D (1 << 1), + + /* DDL: CREATE/DROP/ALTER */ + LOG_DDL =3D (1 << 2), + + /* Function execution */ + LOG_FUNCTION =3D (1 << 4), + + /* Function execution */ + LOG_MISC =3D (1 << 5), + + /* Absolutely everything */ + LOG_ALL =3D ~(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 sing= le + * 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 =3D false; +bool utilityCommandInProgress =3D false; +AuditEvent utilityAuditEvent; + +/* + * Takes an AuditEvent and returns true or false depending on whether th= e event + * should be logged according to the pgaudit.roles/log settings. If it r= eturns + * true, also fills in the name of the LogClass which it is logged under= =2E + */ +static bool +log_check(AuditEvent *e, const char **classname) +{ + enum LogClass class =3D LOG_NONE; + + /* By default put everything in the MISC class. */ + *classname =3D CLASS_MISC; + class =3D 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 =3D CLASS_WRITE; + class =3D LOG_WRITE; + break; + + case LOGSTMT_DDL: + *classname =3D CLASS_DDL; + class =3D LOG_DDL; + + case LOGSTMT_ALL: + switch (e->commandTag) + { + case T_CopyStmt: + case T_SelectStmt: + case T_PrepareStmt: + case T_PlannedStmt: + case T_ExecuteStmt: + *classname =3D CLASS_READ; + class =3D LOG_READ; + break; + + case T_VacuumStmt: + case T_ReindexStmt: + *classname =3D CLASS_DDL; + class =3D LOG_DDL; + break; + + case T_DoStmt: + *classname =3D CLASS_FUNCTION; + class =3D 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) =3D=3D class) + { + return true; + } + + return false; +} + +/* + * Takes an AuditEvent and, if it log_check(), writes it to the audit lo= g. The + * AuditEvent is assumed to be completely filled in by the caller (unkno= wn + * values must be set to "" so that they can be logged without error che= cking). + */ +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 mas= k. 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 =3D false; + Acl *acl; + AclItem *aclItemData; + int aclIndex; + int aclTotal; + + /* Detoast column's ACL if necessary */ + acl =3D DatumGetAclP(aclDatum); + + /* Get the acl list and total */ + aclTotal =3D ACL_NUM(acl); + aclItemData =3D ACL_DAT(acl); + + /* Check privileges granted directly to auditOid */ + for (aclIndex =3D 0; aclIndex < aclTotal; aclIndex++) + { + AclItem *aclItem =3D &aclItemData[aclIndex]; + + if (aclItem->ai_grantee =3D=3D auditOid && + aclItem->ai_privs & mask) + { + result =3D 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 =3D 0; aclIndex < aclTotal; aclIndex++) + { + AclItem *aclItem =3D &aclItemData[aclIndex]; + + /* Don't test public or auditOid (it has been tested already) */ + if (aclItem->ai_grantee =3D=3D ACL_ID_PUBLIC || + aclItem->ai_grantee =3D=3D 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 =3D true; + break; + } + } + } + + /* if we have a detoasted copy, free it */ + if (acl && (Pointer) acl !=3D 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 =3D false; + HeapTuple tuple; + Datum aclDatum; + bool isNull; + + /* Get relation tuple from pg_class */ + tuple =3D SearchSysCache1(RELOID, ObjectIdGetDatum(relOid)); + + /* Return false if tuple is not valid */ + if (!HeapTupleIsValid(tuple)) + return false; + + /* Get the relation's ACL */ + aclDatum =3D SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl, + &isNull); + + /* If not null then test */ + if (!isNull) + result =3D 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 attribut= e. + */ +static bool +log_attribute_check(Oid relOid, + AttrNumber attNum, + Oid auditOid, + AclMode mask) +{ + bool result =3D false; + HeapTuple attTuple; + Datum aclDatum; + bool isNull; + + /* Get the attribute's ACL */ + attTuple =3D 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 =3D SysCacheGetAttr(ATTNUM, attTuple, Anum_pg_attribute_attac= l, + &isNull); + + if (!isNull) + result =3D 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 attribut= e 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 =3D 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 =3D SearchSysCache1(RELOID, ObjectIdGetDatum(relOid)); + + if (!HeapTupleIsValid(classTuple)) + return false; + + nattrs =3D ((Form_pg_class) GETSTRUCT(classTuple))->relnatts; + ReleaseSysCache(classTuple); + + /* Check each column */ + for (curr_att =3D 1; curr_att <=3D 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 =3D bms_copy(attributeSet); + + /* Check each column */ + while ((col =3D bms_first_member(tmpSet)) >=3D 0) + { + col +=3D FirstLowInvalidHeapAttributeNumber; + + if (col !=3D InvalidAttrNumber && + log_attribute_check(relOid, col, auditOid, mode)) + { + result =3D true; + break; + } + } + + /* Free the column set */ + bms_free(tmpSet); + + return result; +} + +/* + * Create AuditEvents for DML operations via executor permissions checks= =2E + */ +static void +log_dml(Oid auditOid, List *rangeTabls) +{ + ListCell *lr; + bool first =3D true; + AuditEvent auditEvent; + + foreach(lr, rangeTabls) + { + Oid relOid; + Relation rel; + RangeTblEntry *rte =3D lfirst(lr); + + /* We only care about tables, and can ignore subqueries etc. */ + if (rte->rtekind !=3D RTE_RELATION) + continue; + + /* + * Filter out any system relations + */ + relOid =3D rte->relid; + rel =3D 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 =3D LOGSTMT_MOD; + + if (rte->requiredPerms & ACL_INSERT) + { + auditEvent.commandTag =3D T_InsertStmt; + auditEvent.command =3D COMMAND_INSERT; + } + else if (rte->requiredPerms & ACL_UPDATE) + { + auditEvent.commandTag =3D T_UpdateStmt; + auditEvent.command =3D COMMAND_UPDATE; + } + else if (rte->requiredPerms & ACL_DELETE) + { + auditEvent.commandTag =3D T_DeleteStmt; + auditEvent.command =3D COMMAND_DELETE; + } + else if (rte->requiredPerms & ACL_SELECT) + { + auditEvent.logStmtLevel =3D LOGSTMT_ALL; + auditEvent.commandTag =3D T_SelectStmt; + auditEvent.command =3D COMMAND_SELECT; + } + else + { + auditEvent.commandTag =3D T_Invalid; + auditEvent.command =3D COMMAND_UNKNOWN; + } + + /* + * Fill values in the event struct that are required for session + * logging. + */ + auditEvent.granted =3D false; + auditEvent.commandText =3D debug_query_string; + + /* If this is the first rte then session log */ + if (first) + { + auditEvent.objectName =3D ""; + auditEvent.objectType =3D ""; + + log_audit_event(&auditEvent); + + first =3D false; + } + + /* Get the relation type */ + switch (rte->relkind) + { + case RELKIND_RELATION: + auditEvent.objectType =3D OBJECT_TYPE_TABLE; + break; + + case RELKIND_INDEX: + auditEvent.objectType =3D OBJECT_TYPE_INDEX; + break; + + case RELKIND_SEQUENCE: + auditEvent.objectType =3D OBJECT_TYPE_SEQUENCE; + break; + + case RELKIND_TOASTVALUE: + auditEvent.objectType =3D OBJECT_TYPE_TOASTVALUE; + break; + + case RELKIND_VIEW: + auditEvent.objectType =3D OBJECT_TYPE_VIEW; + break; + + case RELKIND_COMPOSITE_TYPE: + auditEvent.objectType =3D OBJECT_TYPE_COMPOSITE_TYPE; + break; + + case RELKIND_FOREIGN_TABLE: + auditEvent.objectType =3D OBJECT_TYPE_FOREIGN_TABLE; + break; + + case RELKIND_MATVIEW: + auditEvent.objectType =3D OBJECT_TYPE_MATVIEW; + break; + + default: + auditEvent.objectType =3D OBJECT_TYPE_UNKNOWN; + break; + } + + /* Get the relation name */ + auditEvent.objectName =3D + 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 !=3D InvalidOid) + { + AclMode auditPerms =3D (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 =3D true; + } + + /* + * Else check if the audit role has column-level permissions for + * select, insert, or update. + */ + else if (auditPerms !=3D 0) + { + /* + * Check the select columns to see if the audit role has + * priveleges on any of them. + */ + if (auditPerms & ACL_SELECT) + { + auditEvent.granted =3D + 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 &=3D (ACL_INSERT | ACL_UPDATE); + + if (auditPerms) + { + auditEvent.granted =3D + 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 =3D LOGSTMT_ALL; + auditEvent.commandTag =3D T_SelectStmt; + auditEvent.command =3D COMMAND_SELECT; + auditEvent.granted =3D false; + auditEvent.commandText =3D debug_query_string; + auditEvent.objectName =3D ""; + auditEvent.objectType =3D ""; + + log_audit_event(&auditEvent); + } +} + +/* + * Create AuditEvents for certain kinds of CREATE, ALTER, and DELETE sta= tements + * where the object can be logged. + */ +static void +log_create_alter_drop(Oid classId, + Oid objectId) +{ + /* Only perform when class is relation */ + if (classId =3D=3D RelationRelationId) + { + Relation rel; + Form_pg_class class; + + /* Open the relation */ + rel =3D 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 =3D RelationGetForm(rel); + utilityAuditEvent.objectName =3D + 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 =3D OBJECT_TYPE_TABLE; + break; + + case RELKIND_INDEX: + utilityAuditEvent.objectType =3D OBJECT_TYPE_INDEX; + break; + + case RELKIND_SEQUENCE: + utilityAuditEvent.objectType =3D OBJECT_TYPE_SEQUENCE; + break; + + case RELKIND_VIEW: + utilityAuditEvent.objectType =3D OBJECT_TYPE_VIEW; + break; + + case RELKIND_COMPOSITE_TYPE: + utilityAuditEvent.objectType =3D OBJECT_TYPE_COMPOSITE_TYPE; + break; + + case RELKIND_FOREIGN_TABLE: + utilityAuditEvent.objectType =3D OBJECT_TYPE_FOREIGN_TABLE; + break; + + case RELKIND_MATVIEW: + utilityAuditEvent.objectType =3D 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 =3D 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 =3D SearchSysCache1(PROCOID, ObjectIdGetDatum(objectId)); + + if (!proctup) + elog(ERROR, "cache lookup failed for function %u", objectId); + proc =3D (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 =3D + quote_qualified_identifier(get_namespace_name(proc->pronamespace), + NameStr(proc->proname)); + ReleaseSysCache(proctup); + + /* Log the event */ + utilityAuditEvent.logStmtLevel =3D LOGSTMT_ALL; + utilityAuditEvent.commandTag =3D T_DoStmt; + utilityAuditEvent.command =3D COMMAND_EXECUTE; + utilityAuditEvent.objectType =3D OBJECT_TYPE_FUNCTION; + utilityAuditEvent.commandText =3D debug_query_string; + + log_audit_event(&utilityAuditEvent); + utilityCommandLogged =3D 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 =3D arg; + + if (pc->is_internal) + return; + + log_create_alter_drop(classId, objectId); + } + break; + + /* Log alter. */ + case OAT_POST_ALTER: + { + ObjectAccessPostAlter *pa =3D arg; + + if (pa->is_internal) + return; + + log_create_alter_drop(classId, objectId); + } + break; + + /* Log drop. */ + case OAT_DROP: + { + ObjectAccessDrop *drop =3D 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 =3D NUL= L; +static ProcessUtility_hook_type next_ProcessUtility_hook =3D NULL; +static object_access_hook_type next_object_access_hook =3D 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 =3D get_role_oid(auditRole, true); + + /* Log DML if the audit role is valid or session logging is enabled. */= + if ((auditOid !=3D InvalidOid || auditLogBitmap !=3D 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 comman= ds. + */ +static void +pgaudit_ProcessUtility_hook(Node *parsetree, + const char *queryString, + ProcessUtilityContext context, + ParamListInfo params, + DestReceiver *dest, + char *completionTag) +{ + /* Create the utility audit event. */ + utilityCommandLogged =3D false; + utilityCommandInProgress =3D true; + + utilityAuditEvent.logStmtLevel =3D GetCommandLogLevel(parsetree); + utilityAuditEvent.commandTag =3D nodeTag(parsetree); + utilityAuditEvent.command =3D CreateCommandTag(parsetree); + utilityAuditEvent.objectName =3D ""; + utilityAuditEvent.objectType =3D ""; + utilityAuditEvent.commandText =3D debug_query_string; + utilityAuditEvent.granted =3D 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 alread= y + * been logged by another hook, and the transaction is not aborted */ + if (auditLogBitmap !=3D 0 && !utilityCommandLogged && + !IsAbortedTransactionBlockState()) + { + log_audit_event(&utilityAuditEvent); + } + + utilityCommandInProgress =3D false; +} + +/* + * Hook object_access_hook to provide fully-qualified object names for e= xecute, + * create, drop, and alter commands. Most of the audit information is f= illed in + * by log_utility_command(). + */ +static void +pgaudit_object_access_hook(ObjectAccessType access, + Oid classId, + Oid objectId, + int subId, + void *arg) +{ + if (auditLogBitmap !=3D 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 eac= h of the + * comma-separated tokens corresponds to a LogClass value, and convert t= hem 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 =3D 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 =3D (uint64 *) malloc(sizeof(uint64)); + if (!f) + return false; + *f =3D 0; + + foreach(lt, flags) + { + bool subtract =3D false; + uint64 class; + + /* Retrieve a token */ + char *token =3D (char *)lfirst(lt); + + /* If token is preceded by -, then then token is subtractive. */ + if (strstr(token, "-") =3D=3D token) + { + token =3D token + 1; + subtract =3D true; + } + + /* Test each token. */ + if (pg_strcasecmp(token, CLASS_NONE) =3D=3D 0) + class =3D LOG_NONE; + else if (pg_strcasecmp(token, CLASS_ALL) =3D=3D 0) + class =3D LOG_ALL; + else if (pg_strcasecmp(token, CLASS_DDL) =3D=3D 0) + class =3D LOG_DDL; + else if (pg_strcasecmp(token, CLASS_FUNCTION) =3D=3D 0) + class =3D LOG_FUNCTION; + else if (pg_strcasecmp(token, CLASS_MISC) =3D=3D 0) + class =3D LOG_MISC; + else if (pg_strcasecmp(token, CLASS_READ) =3D=3D 0) + class =3D LOG_READ; + else if (pg_strcasecmp(token, CLASS_WRITE) =3D=3D 0) + class =3D LOG_WRITE; + else + { + free(f); + pfree(rawval); + list_free(flags); + return false; + } + + /* Add or subtract class bits from the log bitmap. */ + if (subtract) + *f &=3D ~class; + else + *f |=3D class; + } + + pfree(rawval); + list_free(flags); + + /* + * Store the bitmap for assign_pgaudit_log. + */ + *extra =3D f; + + return true; +} + +/* + * Set pgaudit_log from extra (ignoring newval, which has already been c= onverted + * 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 =3D *(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 =3D "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 =3D "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 pre= serve + * the chain. + */ + next_ExecutorCheckPerms_hook =3D ExecutorCheckPerms_hook; + ExecutorCheckPerms_hook =3D pgaudit_ExecutorCheckPerms_hook; + + next_ProcessUtility_hook =3D ProcessUtility_hook; + ProcessUtility_hook =3D pgaudit_ProcessUtility_hook; + + next_object_access_hook =3D object_access_hook; + object_access_hook =3D pgaudit_object_access_hook; +} diff --git a/contrib/pg_audit/pg_audit.control b/contrib/pg_audit/pg_audi= t.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 =3D 'provides auditing functionality' +default_version =3D '1.0.0' +module_pathname =3D '$libdir/pgaudit' +relocatable =3D true diff --git a/contrib/pg_audit/test/test.pl b/contrib/pg_audit/test/test.p= l 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 =3D> 1, + false =3D> 0 +}; + +use constant +{ + CONTEXT_GLOBAL =3D> 'GLOBAL', + CONTEXT_DATABASE =3D> 'DATABASE', + CONTEXT_ROLE =3D> 'ROLE' +}; + +use constant +{ + CLASS =3D> 'CLASS', + + CLASS_DDL =3D> 'DDL', + CLASS_FUNCTION =3D> 'FUNCTION', + CLASS_MISC =3D> 'MISC', + CLASS_READ =3D> 'READ', + CLASS_WRITE =3D> 'WRITE', + + CLASS_ALL =3D> 'ALL', + CLASS_NONE =3D> 'NONE' +}; + +use constant +{ + COMMAND =3D> 'COMMAND', + COMMAND_LOG =3D> 'COMMAND_LOG', + + COMMAND_ANALYZE =3D> 'ANALYZE', + COMMAND_ALTER_AGGREGATE =3D> 'ALTER AGGREGATE', + COMMAND_ALTER_COLLATION =3D> 'ALTER COLLATION', + COMMAND_ALTER_CONVERSION =3D> 'ALTER CONVERSION', + COMMAND_ALTER_DATABASE =3D> 'ALTER DATABASE', + COMMAND_ALTER_ROLE =3D> 'ALTER ROLE', + COMMAND_ALTER_ROLE_SET =3D> 'ALTER ROLE SET', + COMMAND_ALTER_TABLE =3D> 'ALTER TABLE', + COMMAND_ALTER_TABLE_INDEX =3D> 'ALTER TABLE INDEX', + COMMAND_BEGIN =3D> 'BEGIN', + COMMAND_CLOSE =3D> 'CLOSE CURSOR', + COMMAND_COMMIT =3D> 'COMMIT', + COMMAND_COPY =3D> 'COPY', + COMMAND_COPY_TO =3D> 'COPY TO', + COMMAND_COPY_FROM =3D> 'COPY FROM', + COMMAND_CREATE_AGGREGATE =3D> 'CREATE AGGREGATE', + COMMAND_CREATE_COLLATION =3D> 'CREATE COLLATION', + COMMAND_CREATE_CONVERSION =3D> 'CREATE CONVERSION', + COMMAND_CREATE_DATABASE =3D> 'CREATE DATABASE', + COMMAND_CREATE_INDEX =3D> 'CREATE INDEX', + COMMAND_DEALLOCATE =3D> 'DEALLOCATE', + COMMAND_DECLARE_CURSOR =3D> 'DECLARE CURSOR', + COMMAND_DO =3D> 'DO', + COMMAND_DISCARD_ALL =3D> 'DISCARD ALL', + COMMAND_CREATE_FUNCTION =3D> 'CREATE FUNCTION', + COMMAND_CREATE_ROLE =3D> 'CREATE ROLE', + COMMAND_CREATE_SCHEMA =3D> 'CREATE SCHEMA', + COMMAND_CREATE_TABLE =3D> 'CREATE TABLE', + COMMAND_CREATE_TABLE_AS =3D> 'CREATE TABLE AS', + COMMAND_DROP_DATABASE =3D> 'DROP DATABASE', + COMMAND_DROP_SCHEMA =3D> 'DROP SCHEMA', + COMMAND_DROP_TABLE =3D> 'DROP TABLE', + COMMAND_DROP_TABLE_INDEX =3D> 'DROP TABLE INDEX', + COMMAND_DROP_TABLE_TYPE =3D> 'DROP TABLE TYPE', + COMMAND_EXECUTE =3D> 'EXECUTE', + COMMAND_EXECUTE_READ =3D> 'EXECUTE READ', + COMMAND_EXECUTE_WRITE =3D> 'EXECUTE WRITE', + COMMAND_EXECUTE_FUNCTION =3D> 'EXECUTE FUNCTION', + COMMAND_EXPLAIN =3D> 'EXPLAIN', + COMMAND_FETCH =3D> 'FETCH', + COMMAND_GRANT =3D> 'GRANT', + COMMAND_INSERT =3D> 'INSERT', + COMMAND_PREPARE =3D> 'PREPARE', + COMMAND_PREPARE_READ =3D> 'PREPARE READ', + COMMAND_PREPARE_WRITE =3D> 'PREPARE WRITE', + COMMAND_REVOKE =3D> 'REVOKE', + COMMAND_SELECT =3D> 'SELECT', + COMMAND_SET =3D> 'SET', + COMMAND_UPDATE =3D> 'UPDATE' +}; + +use constant +{ + TYPE =3D> 'TYPE', + TYPE_NONE =3D> '', + + TYPE_FUNCTION =3D> 'FUNCTION', + TYPE_INDEX =3D> 'INDEX', + TYPE_TABLE =3D> 'TABLE', + TYPE_TYPE =3D> 'TYPE' +}; + +use constant +{ + NAME =3D> 'NAME' +}; + +########################################################################= ######## +# Command line parameters +########################################################################= ######## +my $strPgSqlBin =3D '../../../../bin/bin'; # Path of PG binaries to use = for + # this test +my $strTestPath =3D '../../../../data'; # Path where testing will occur= +my $iDefaultPort =3D 6000; # Default port to run Postgres on +my $bHelp =3D false; # Display help +my $bQuiet =3D false; # Supress output except for errors +my $bNoCleanup =3D false; # Cleanup database on exit + +GetOptions ('q|quiet' =3D> \$bQuiet, + 'no-cleanup' =3D> \$bNoCleanup, + 'help' =3D> \$bHelp, + 'pgsql-bin=3Ds' =3D> \$strPgSqlBin, + 'test-path=3Ds' =3D> \$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 =3D ''; # The expected log compared with grepping AUD= IT + # entries from the postgres log. + +my $strDatabase =3D 'postgres'; # Connected database (modified by PgSetD= atabase) +my $strUser =3D 'postgres'; # Connected user (modified by PgSetUser) +my $strAuditRole =3D '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 =3D +(&COMMAND_ANALYZE =3D> { + &CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE_NONE}, + &COMMAND_ALTER_AGGREGATE =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE= _NONE}, + &COMMAND_ALTER_DATABASE =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE_= NONE}, + &COMMAND_ALTER_COLLATION =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE= _NONE}, + &COMMAND_ALTER_CONVERSION =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYP= E_NONE}, + &COMMAND_ALTER_ROLE =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE_NONE= }, + &COMMAND_ALTER_ROLE_SET =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE_= NONE, + &COMMAND =3D> &COMMAND_ALTER_ROLE}, + &COMMAND_ALTER_TABLE =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE_TAB= LE}, + &COMMAND_ALTER_TABLE_INDEX =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TY= PE_INDEX, + &COMMAND =3D> &COMMAND_ALTER_TABLE}, + &COMMAND_BEGIN =3D> {&CLASS =3D> &CLASS_MISC, &TYPE =3D> &TYPE_NONE}, + &COMMAND_CLOSE =3D> {&CLASS =3D> &CLASS_MISC, &TYPE =3D> &TYPE_NONE}, + &COMMAND_COMMIT =3D> {&CLASS =3D> &CLASS_MISC, &TYPE =3D> &TYPE_NONE}, + &COMMAND_COPY_FROM =3D> {&CLASS =3D> &CLASS_WRITE, &TYPE =3D> &TYPE_NON= E, + &COMMAND =3D> &COMMAND_COPY}, + &COMMAND_COPY_TO =3D> {&CLASS =3D> &CLASS_READ, &TYPE =3D> &TYPE_NONE, + &COMMAND =3D> &COMMAND_COPY}, + &COMMAND_CREATE_AGGREGATE =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYP= E_NONE}, + &COMMAND_CREATE_CONVERSION =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TY= PE_NONE}, + &COMMAND_CREATE_COLLATION =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYP= E_NONE}, + &COMMAND_CREATE_DATABASE =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE= _NONE}, + &COMMAND_CREATE_INDEX =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE_IN= DEX}, + &COMMAND_DEALLOCATE =3D> {&CLASS =3D> &CLASS_MISC, &TYPE =3D> &TYPE_NON= E}, + &COMMAND_DECLARE_CURSOR =3D> {&CLASS =3D> &CLASS_READ, &TYPE =3D> &TYPE= _NONE}, + &COMMAND_DO =3D> {&CLASS =3D> &CLASS_FUNCTION, &TYPE =3D> &TYPE_NONE}, + &COMMAND_DISCARD_ALL =3D> {&CLASS =3D> &CLASS_MISC, &TYPE =3D> &TYPE_NO= NE}, + &COMMAND_CREATE_FUNCTION =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE= _NONE}, + &COMMAND_CREATE_ROLE =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE_NON= E}, + &COMMAND_CREATE_SCHEMA =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE_N= ONE}, + &COMMAND_CREATE_TABLE =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE_TA= BLE}, + &COMMAND_CREATE_TABLE_AS =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE= _TABLE}, + &COMMAND_DROP_DATABASE =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE_N= ONE}, + &COMMAND_DROP_SCHEMA =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE_NON= E}, + &COMMAND_DROP_TABLE =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE_TABL= E}, + &COMMAND_DROP_TABLE_INDEX =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYP= E_INDEX, + &COMMAND =3D> &COMMAND_DROP_TABLE}, + &COMMAND_DROP_TABLE_TYPE =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE= _TYPE, + &COMMAND =3D> &COMMAND_DROP_TABLE}, + &COMMAND_EXECUTE_READ =3D> {&CLASS =3D> &CLASS_READ, &TYPE =3D> &TYPE_N= ONE, + &COMMAND =3D> &COMMAND_EXECUTE}, + &COMMAND_EXECUTE_WRITE =3D> {&CLASS =3D> &CLASS_WRITE, &TYPE =3D> &TYPE= _NONE, + &COMMAND =3D> &COMMAND_EXECUTE}, + &COMMAND_EXECUTE_FUNCTION =3D> {&CLASS =3D> &CLASS_FUNCTION, + &TYPE =3D> &TYPE_FUNCTION, &COMMAND =3D> &COMMAND_EXECUTE}, + &COMMAND_EXPLAIN =3D> {&CLASS =3D> &CLASS_MISC, &TYPE =3D> &TYPE_NONE},= + &COMMAND_FETCH =3D> {&CLASS =3D> &CLASS_MISC, &TYPE =3D> &TYPE_NONE}, + &COMMAND_GRANT =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE_NONE}, + &COMMAND_PREPARE_READ =3D> {&CLASS =3D> &CLASS_READ, &TYPE =3D> &TYPE_N= ONE, + &COMMAND =3D> &COMMAND_PREPARE}, + &COMMAND_PREPARE_WRITE =3D> {&CLASS =3D> &CLASS_WRITE, &TYPE =3D> &TYPE= _NONE, + &COMMAND =3D> &COMMAND_PREPARE}, + &COMMAND_INSERT =3D> {&CLASS =3D> &CLASS_WRITE, &TYPE =3D> &TYPE_NONE},= + &COMMAND_REVOKE =3D> {&CLASS =3D> &CLASS_DDL, &TYPE =3D> &TYPE_NONE}, + &COMMAND_SELECT =3D> {&CLASS =3D> &CLASS_READ, &TYPE =3D> &TYPE_NONE}, + &COMMAND_SET =3D> {&CLASS =3D> &CLASS_MISC, &TYPE =3D> &TYPE_NONE}, + &COMMAND_UPDATE =3D> {&CLASS =3D> &CLASS_WRITE, &TYPE =3D> &TYPE_NONE} +); + +########################################################################= ######## +# CommandExecute +########################################################################= ######## +sub CommandExecute +{ + my $strCommand =3D shift; + my $bSuppressError =3D shift; + + # Set default + $bSuppressError =3D defined($bSuppressError) ? $bSuppressError : false;= + + # Run the command + my $iResult =3D system($strCommand); + + if ($iResult !=3D 0 && !$bSuppressError) + { + confess "command '${strCommand}' failed with error ${iResult}"; + } +} + +########################################################################= ######## +# log +########################################################################= ######## +sub log +{ + my $strMessage =3D shift; + my $bError =3D shift; + + # Set default + $bError =3D defined($bError) ? $bError : false; + + if (!$bQuiet) + { + print "${strMessage}\n"; + } + + if ($bError) + { + exit 1; + } +} + +########################################################################= ######## +# ArrayToString +########################################################################= ######## +sub ArrayToString +{ + my @stryArray =3D @_; + + my $strResult =3D ''; + + for (my $iIndex =3D 0; $iIndex < @stryArray; $iIndex++) + { + if ($iIndex !=3D 0) + { + $strResult .=3D ', '; + } + + $strResult .=3D $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 =3D shift; + + # Set default + $iPort =3D defined($iPort) ? $iPort : $iDefaultPort; + + # Log Connection + &log(" DB: connect user ${strUser}, database ${strDatabase}"); + + # Disconnect user session + PgDisconnect(); + + # Connect to the db + $hDb =3D DBI->connect("dbi:Pg:dbname=3D${strDatabase};port=3D${iPort};h= ost=3D/tmp", + $strUser, undef, + {AutoCommit =3D> 1, RaiseError =3D> 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 =3D shift; + + # Log the statement + &log(" SQL: ${strSql}"); + + # Execute the statement + my $hStatement =3D $hDb->prepare($strSql); + + $hStatement->execute(); + $hStatement->finish(); +} + +########################################################################= ######## +# PgExecuteOnly +########################################################################= ######## +sub PgExecuteOnly +{ + my $strSql =3D shift; + + # Log the statement + &log(" SQL: ${strSql}"); + + # Execute the statement + $hDb->do($strSql); +} + +########################################################################= ######## +# PgSetDatabase +########################################################################= ######## +sub PgSetDatabase +{ + my $strDatabaseParam =3D shift; + + # Stop and start the database to reset pgconf entries + PgStop(); + PgStart(); + + # Execute the statement + $strDatabase =3D $strDatabaseParam; + PgConnect(); +} + +########################################################################= ######## +# PgSetUser +########################################################################= ######## +sub PgSetUser +{ + my $strUserParam =3D shift; + + $strUser =3D $strUserParam; + + # Stop and start the database to reset pgconf entries + if ((defined($strTemporaryAuditLog) && !defined($strCurrentAuditLog)) |= | + (defined($strCurrentAuditLog) && !defined($strTemporaryAuditLog)) || + $strCurrentAuditLog ne $strTemporaryAuditLog) + { + $strCurrentAuditLog =3D $strTemporaryAuditLog; + + PgStop(); + PgStart(); + } + else + { + # Execute the statement + PgConnect(); + } +} + +########################################################################= ######## +# SaveString +########################################################################= ######## +sub SaveString +{ + my $strFile =3D shift; + my $strString =3D 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 =3D shift; + my $strSql =3D shift; + my $oData =3D shift; + my $bExecute =3D shift; + my $bWait =3D shift; + my $bLogSql =3D shift; + + # Set defaults + $bExecute =3D defined($bExecute) ? $bExecute : true; + $bWait =3D defined($bWait) ? $bWait : true; + $bLogSql =3D defined($bLogSql) ? $bLogSql : true; + + if ($bExecute) + { + PgExecuteOnly($strSql); + } + + PgLogExpect($strCommand, $bLogSql ? $strSql : '', $oData); + + if ($bWait) + { + PgLogWait(); + } +} + +########################################################################= ######## +# PgLogExpect +########################################################################= ######## +sub PgLogExpect +{ + my $strCommand =3D shift; + my $strSql =3D shift; + my $oData =3D 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 =3D $oCommandHash{$strCommand}{&CLASS}; + + if (!defined($strClass)) + { + confess "class is not defined for command ${strCommand}"; + } + + # Make sure object type is defined + my $strObjectType =3D $oCommandHash{$strCommand}{&TYPE}; + + if (!defined($strObjectType)) + { + confess "object type is not defined for command ${strCommand}"; + } + + # Check for command override + my $strCommandLog =3D $strCommand; + + if ($oCommandHash{$strCommand}{&COMMAND}) + { + $strCommandLog =3D $oCommandHash{$strCommand}{&COMMAND}; + } + + my $strObjectName =3D ''; + + if (defined($oData) && ref($oData) ne 'ARRAY') + { + $strObjectName =3D $oData; + } + + my $strLog .=3D "SESSION,${strClass},${strCommandLog}," . + "${strObjectType},${strObjectName},${strSql}"; + &log("AUDIT: ${strLog}"); + + $strLogExpected .=3D "${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 =3D ${$oTableHash}{&NAME}; + my $strCommandLog =3D ${$oTableHash}{&COMMAND}; + + if (defined($oAuditGrantHash{$strAuditRole} + {$strObjectName}{$strCommandLog})) + { + my $strCommandLog =3D defined(${$oTableHash}{&COMMAND_LOG}) ? + ${$oTableHash}{&COMMAND_LOG} : $strCommandLog; + my $strClass =3D $oCommandHash{$strCommandLog}{&CLASS}; + my $strObjectType =3D ${$oTableHash}{&TYPE}; + + my $strLog .=3D "OBJECT,${strClass},${strCommandLog}," . + "${strObjectType},${strObjectName},${strSql}"; + &log("AUDIT: ${strLog}"); + + $strLogExpected .=3D "${strLog}\n"; + } + } + + $oData =3D undef; + } +} + +########################################################################= ######## +# PgShouldLog +########################################################################= ######## +sub PgShouldLog +{ + my $strCommand =3D shift; + + # Make sure class is defined + my $strClass =3D $oCommandHash{$strCommand}{&CLASS}; + + if (!defined($strClass)) + { + confess "class is not defined for command ${strCommand}"; + } + + # Check logging for the role + my $bLog =3D undef; + + if (defined($oAuditLogHash{&CONTEXT_ROLE}{$strUser})) + { + $bLog =3D $oAuditLogHash{&CONTEXT_ROLE}{$strUser}{$strClass}; + } + + # Else check logging for the db + elsif (defined($oAuditLogHash{&CONTEXT_DATABASE}{$strDatabase})) + { + $bLog =3D $oAuditLogHash{&CONTEXT_DATABASE}{$strDatabase}{$strClass}; + } + + # Else check logging for global + elsif (defined($oAuditLogHash{&CONTEXT_GLOBAL}{&CONTEXT_GLOBAL})) + { + $bLog =3D $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 =3D capture("grep 'LOG: AUDIT: '" . + " ${strTestPath}/postgresql.log"); + }; + + # If an error was returned, continue if it was 1, otherwise confess + if ($@) + { + my $iExitStatus =3D $? >> 8; + + if ($iExitStatus !=3D 1) + { + confess "grep returned ${iExitStatus}"; + } + + $strLogActual =3D ''; + } + + # Strip the AUDIT and timestamp from the actual log + $strLogActual =3D~ 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 =3D shift; + + # Set default + $strPath =3D defined($strPath) ? $strPath : $strTestPath; + + # Stop the cluster + PgStop(true, $strPath); + + # Remove the directory + CommandExecute("rm -rf ${strTestPath}"); +} + +########################################################################= ######## +# PgCreate +########################################################################= ######## +sub PgCreate +{ + my $strPath =3D shift; + + # Set default + $strPath =3D defined($strPath) ? $strPath : $strTestPath; + + CommandExecute("${strPgSqlBin}/initdb -D ${strPath} -U ${strUser}" . + ' -A trust > /dev/null'); +} + +########################################################################= ######## +# PgStop +########################################################################= ######## +sub PgStop +{ + my $bImmediate =3D shift; + my $strPath =3D shift; + + # Set default + $strPath =3D defined($strPath) ? $strPath : $strTestPath; + $bImmediate =3D 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 =3D shift; + my $strPath =3D shift; + + # Set default + $iPort =3D defined($iPort) ? $iPort : $iDefaultPort; + $strPath =3D 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=3D${iPort}" . + " -c unix_socket_directories=3D'/tmp'" . + " -c shared_preload_libraries=3D'pg_audit'" . + " -c log_min_messages=3Ddebug1" . + " -c log_line_prefix=3D'prefix '" . + # " -c log_destination=3D'stderr,csvlog'" . + # " -c logging_collector=3Don" . + (defined($strCurrentAuditLog) ? + " -c pg_audit.log=3D'${strCurrentAuditLog}'" : '') . + " -c pg_audit.role=3D'${strAuditRole}'" . + " -c log_connections=3Don" . + "\" -D ${strPath} -l ${strPath}/postgresql.log -w -s"); + + # Connect user session + PgConnect(); +} + +########################################################################= ######## +# PgAuditLogSet +########################################################################= ######## +sub PgAuditLogSet +{ + my $strContext =3D shift; + my $strName =3D shift; + my @stryClass =3D @_; + + # Create SQL to set the GUC + my $strCommand; + my $strSql; + + if ($strContext eq CONTEXT_GLOBAL) + { + $strCommand =3D COMMAND_SET; + $strSql =3D "set pg_audit.log =3D '" . + ArrayToString(@stryClass) . "'"; + $strTemporaryAuditLog =3D ArrayToString(@stryClass); + } + elsif ($strContext eq CONTEXT_ROLE) + { + $strCommand =3D COMMAND_ALTER_ROLE_SET; + $strSql =3D "alter role ${strName} set pg_audit.log =3D '" . + 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 =3D 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} =3D true; + $oAuditLogHash{$strContext}{$strName}{&CLASS_FUNCTION} =3D true; + $oAuditLogHash{$strContext}{$strName}{&CLASS_MISC} =3D true; + $oAuditLogHash{$strContext}{$strName}{&CLASS_READ} =3D true; + $oAuditLogHash{$strContext}{$strName}{&CLASS_WRITE} =3D true; + } + + if (index($strClass, '-') =3D=3D 0) + { + $strClass =3D substr($strClass, 1); + + delete($oAuditLogHash{$strContext}{$strName}{$strClass}); + } + else + { + $oAuditLogHash{$strContext}{$strName}{$strClass} =3D true; + } + } + + PgLogExecute($strCommand, $strSql); +} + +########################################################################= ######## +# PgAuditGrantSet +########################################################################= ######## +sub PgAuditGrantSet +{ + my $strRole =3D shift; + my $strPrivilege =3D shift; + my $strObject =3D shift; + my $strColumn =3D shift; + + # Create SQL to set the grant + PgLogExecute(COMMAND_GRANT, "grant " . lc(${strPrivilege}) . + (defined($strColumn) ? " (${strColumn})" : '') . + " on ${strObject} to ${strRole}"); + + $oAuditGrantHash{$strRole}{$strObject}{$strPrivilege} =3D true; +} + +########################################################################= ######## +# PgAuditGrantReset +########################################################################= ######## +sub PgAuditGrantReset +{ + my $strRole =3D shift; + my $strPrivilege =3D shift; + my $strObject =3D shift; + my $strColumn =3D 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= =2Etest'); +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 =3D ({&NAME =3D> 'public.test3', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_SELECT}, + {&NAME =3D> 'public.test2', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_SELECT}); +PgLogExecute(COMMAND_SELECT, 'select * from test3, test2', + \@oyTable); + +# Various CTE combinations +PgAuditGrantSet($strAuditRole, &COMMAND_INSERT, 'public.test3'); + +@oyTable =3D ({&NAME =3D> 'public.test3', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_INSERT}, + {&NAME =3D> 'public.test2', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_SELECT}); +PgLogExecute(COMMAND_INSERT, + 'with cte as (select id from test2)' . + ' insert into test3 select id from cte', + \@oyTable); + +@oyTable =3D ({&NAME =3D> 'public.test2', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_INSERT}, + {&NAME =3D> 'public.test3', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &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 =3D ({&NAME =3D> 'public.test3', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_INSERT}, + {&NAME =3D> 'public.test2', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_UPDATE}); +PgLogExecute(COMMAND_INSERT, + 'with cte as (update test2 set id =3D 1 returning id)' . + ' insert into test3 select id from cte', + \@oyTable); + +@oyTable =3D ({&NAME =3D> 'public.test3', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_UPDATE}, + {&NAME =3D> 'public.test2', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_INSERT}, + {&NAME =3D> 'public.test2', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_SELECT, &COMMAND_LOG =3D> &COMMAND_INSERT}); +PgLogExecute(COMMAND_UPDATE, + 'with cte as (insert into test2 values (1) returning id)' . + ' update test3 set id =3D 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 =3D (); +PgLogExecute(COMMAND_SELECT, 'select id from public.test4', + \@oyTable); + +@oyTable =3D ({&NAME =3D> 'public.test4', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_SELECT}); +PgLogExecute(COMMAND_SELECT, 'select name from public.test4', + \@oyTable); + +# Insert +@oyTable =3D (); +PgLogExecute(COMMAND_INSERT, 'insert into public.test4 (id) values (1)',= + \@oyTable); + +@oyTable =3D ({&NAME =3D> 'public.test4', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_INSERT}); +PgLogExecute(COMMAND_INSERT, "insert into public.test4 (name) values ('t= est')", + \@oyTable); + +# Update +@oyTable =3D (); +PgLogExecute(COMMAND_UPDATE, "update public.test4 set name =3D 'foo'", + \@oyTable); + +@oyTable =3D ({&NAME =3D> 'public.test4', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_UPDATE}); +PgLogExecute(COMMAND_UPDATE, "update public.test4 set id =3D 1", + \@oyTable); + +@oyTable =3D ({&NAME =3D> 'public.test4', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_SELECT, &COMMAND_LOG =3D> &COMMAND_UP= DATE}); +PgLogExecute(COMMAND_UPDATE, + "update public.test4 set name =3D 'foo' where name =3D '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 l= ogging +# 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: "); + +# 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 =3D 'audit'"); +PgSetUser('user1'); + +PgAuditGrantSet($strAuditRole, &COMMAND_SELECT, 'public.account', 'passw= ord'); + +@oyTable =3D (); +PgLogExecute(COMMAND_SELECT, 'select id, name from account', + \@oyTable); +&log("AUDIT: "); + +@oyTable =3D ({&NAME =3D> 'public.account', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_SELECT}); +PgLogExecute(COMMAND_SELECT, 'select password from account', + \@oyTable); + +PgAuditGrantSet($strAuditRole, &COMMAND_UPDATE, + 'public.account', 'name, password'); + +@oyTable =3D (); +PgLogExecute(COMMAND_UPDATE, "update account set description =3D 'yada, = yada'", + \@oyTable); +&log("AUDIT: "); + +@oyTable =3D ({&NAME =3D> 'public.account', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_UPDATE}); +PgLogExecute(COMMAND_UPDATE, "update account set password =3D '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 =3D ({&NAME =3D> 'public.account', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_SELECT}, + {&NAME =3D> 'public.account_role_map', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_SELECT}); +PgLogExecute(COMMAND_SELECT, + 'select account.password, account_role_map.role_id from account' . + ' inner join account_role_map' . + ' on account.id =3D account_role_map.account_id', + \@oyTable); + +@oyTable =3D ({&NAME =3D> 'public.account', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_SELECT}); +PgLogExecute(COMMAND_SELECT, 'select password from account', + \@oyTable); + +@oyTable =3D (); +PgLogExecute(COMMAND_UPDATE, "update account set description =3D 'yada, = yada'", + \@oyTable); +&log("AUDIT: "); + +@oyTable =3D ({&NAME =3D> 'public.account', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_SELECT, &COMMAND_LOG =3D> &COMMAND_U= PDATE}); +PgLogExecute(COMMAND_UPDATE, + "update account set description =3D 'yada, yada'" . + " where password =3D 'HASH2'", + \@oyTable); + +@oyTable =3D ({&NAME =3D> 'public.account', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_UPDATE}); +PgLogExecute(COMMAND_UPDATE, "update account set password =3D '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 =3D '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 =3D $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 =3D ({&NAME =3D> 'public.test', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &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 publi= c'); +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 =3D ({&NAME =3D> 'public.test', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &COMMAND_SELECT}); +PgLogExecute(COMMAND_SELECT, 'select name from test', \@oyTable); + +PgLogExecute(COMMAND_ALTER_TABLE, + 'alter table test drop description', 'public.test'); +@oyTable =3D ({&NAME =3D> 'public.test', &TYPE =3D> &TYPE_TABLE, + &COMMAND =3D> &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 te= st', + 'public.test_pkey', false, true); +PgLogExecute(COMMAND_ALTER_TABLE, 'alter table test.test2 add descriptio= n text', + 'test.test2'); +PgLogExecute(COMMAND_ALTER_TABLE, 'alter table test.test2 drop descripti= on', + '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 =3D int_add, stype =3D int, initcond =3D 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 module_name FROM unp= ackaged; <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 @@ + diff --git a/doc/src/sgml/pgaudit.sgml b/doc/src/sgml/pgaudit.sgml new file mode 100644 index 0000000..78565de --- /dev/null +++ b/doc/src/sgml/pgaudit.sgml @@ -0,0 +1,316 @@ + + + + pg_audit + + + pg_audit + + + + The pg_audit module provides session and object= + auditing via the standard logging facility. Session and object audi= ting are + completely independent and can be combined. + + + + Session Auditing + + + Session auditing allows the logging of all commands that are execu= ted by + a user in the backend. Each command is logged with a single entry= and + includes the audit type (e.g. SESSION), command= type + (e.g. CREATE TABLE, SELECT) = and + statement (e.g. "select * from test"). + + Fully-qualified names and object types will be logged for + CREATE, UPDATE, and + DROP commands on TABLE, + MATVIEW, VIEW, + INDEX, FOREIGN TABLE, + COMPOSITE TYPE, INDEX, and + SEQUENCE objects as well as function calls. + + + + Configuration + + + Session logging is controlled by the pg_audit.log + GUC. There are five classes of commands that are recognized: + + + + + READ - SELECT and + COPY when the source is a table or quer= y. + + + + + WRITE - INSERT, + UPDATE, DELETE, + TRUNCATE, and COPY w= hen the + destination is a table. + + + + + FUNCTION - Function calls and + DO blocks. + + + + + DDL - DDL, plus VACUUM, + REINDEX, and ANALYZE= =2E + + + + + MISC - Miscellaneous commands, e.g. + DISCARD, FETCH, + CHECKPOINT. + + + + + + + Enable session logging for all writes and DDL: + +pg_audit.log =3D 'write, ddl' + + + + + Enable session logging for all commands except miscellaneous: + +pg_audit.log =3D 'all, -misc' + + + + + + Examples + + + Set pg_audit.log =3D 'read, ddl' in + postgresql.conf. + + + + SQL: + + + +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'); + + + + Log Output: + + + +AUDIT: SESSION,DDL,CREATE TABLE,TABLE,public.account,create table accoun= t +( + id int, + name text, + password text, + description text +); +AUDIT: SESSION,READ,SELECT,,,select * + from account + + + + + + Object Auditing + + + Object auditing logs commands that affect a particular object. On= ly + SELECT, INSERT, + UPDATE and DELETE commands a= re + supported. + + + + Configuration + + + Object-level auditing is implemented via the roles system. The + pg_audit.role 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. + + + +postresql.conf: pg_audit.role =3D 'audit' + +grant select, delete + on public.account; + + + + + Examples + + + Set pg_audit.role =3D 'audit' in + postgresql.conf. + + + + SQL: + + + +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 =3D 'yada, yada'; + +update account + set password =3D '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 =3D account_role_map.account_id + + + + Log Output: + + + +AUDIT: OBJECT,READ,SELECT,TABLE,public.account,select password + from account +AUDIT: OBJECT,WRITE,UPDATE,TABLE,public.account,update account + set password =3D '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 =3D account_role_map.account_id +AUDIT: OBJECT,READ,SELECT,TABLE,public.account_role_map,select account.p= assword, + account_role_map.role_id + from account + inner join account_role_map + on account.id =3D account_role_map.account_id + + + + + + Format + + + Audit entries are written to the standard logging facility and con= tain + the following columns in comma-separated format: + + + + Output is not in compliant CSV format. If machine-readability= is + required then consider setting + log_destination =3D 'csvlog'. + + + + + + + AUDIT_TYPE - SESSION o= r + OBJECT. + + + + + CLASS - READ, + WRITE, FUNCTION, + DDL, or MISC. + + + + + COMMAND - ALTER TABLE,= + SELECT, CREATE INDEX, + UPDATE, etc. + + + + + OBJECT_TYPE - TABLE, + INDEX, VIEW, etc. Onl= y + available for DML and certain DDL commands. + + + + + OBJECT_NAME - The fully-qualified object = name + (e.g. public.account). Only available for DML and certain D= DL + commands. + + + + + STATEMENT - Statement execute on the back= end. + + + + + + + + Author + + + David Steele david@pgmasters.net + + + --------------040206060005060405030308-- --pQwTnlWsPAbpChDbU8RfFsJuewiDNTonh Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v2 iQIcBAEBCAAGBQJU605XAAoJEIA2uIJQ5SFAYZAQAJ148B9EaPgJqTUHUVbMRgrT alvRQ8+s4IZ3DC6NAXqQyedwLP8N0OVznCw4PSy6hg6Hvu8PrMl8uXOiyJqxIklp BwpCtb2FIi/gVHH5s1RbgcS7qO0PwuEyRbcf10dtJ0nq/nHmM9ch0c+VjdoQ66KH NuPxx1QUnzv6lM5xPKlfIqaoUMinfS6ubTQD0qJ412eIN26+2PLEwAdltKP0Sk82 lgaKsIrZB+eb+bJCUfESDsL4EukEm8LiC4cTUhcHYxhjiiNHDidrX8D4CQOGejdR 5fMD/dWe80TRBh8McL3ptp/0s42TjITJ5qalLWlwFlgxbFenv12WzYGUzZt1NPl/ Qn5AJ/mN1F1rukjO7fLXfzwyNDbsGl0ZuFykWcIrmq0frh0J0teup61hwEGebT9y xcKUSze/gz9GEvmix7xv5djcb45ODb2QhhdhCAA/IvaTdynUKGCAhwyY14Acr9Oa 5e5wC6dO2dG7XLL8l9LNJqQcCo5UG8QVrUARhom3fpHyQN8YMuAon4HX2p+obWnH hQrHGhoj6hxLRYbxpX6tD8jq17zg+9nCuj+1/S6syN/cU1xAKwwYzO/+4RiAM8S1 7wVimUtp7h2ahSKztLPCCcbXqfwxkS6XNk0aAXa8LAyMLpmcEk1S8/0Kdn/5x/ov JynnV0T89zzIYeECSKrm =t1xJ -----END PGP SIGNATURE----- --pQwTnlWsPAbpChDbU8RfFsJuewiDNTonh--