On 2/2/15 3:49 PM, David Steele wrote:

> The role-base approach being considered may strike some as a misuse of
> the role system, but to my eye it is syntactically very close to how
> Oracle does auditing prior to 12c.  Say you wanted to audit selects on
> the table hr.employee:
>
> Oracle: AUDIT SELECT ON hr.employee;
> pgaudit: GRANT SELECT ON hr.employee TO audit; (assuming audit is the
> role defined by pgaudit.roles)
>
> Object-based auditing in Oracle would be very easy to migrate to the
> grants needed for pgaudit.  In addition, if an AUDIT command were
> introduced later in core, it would be easy to migrate from pgaudit to
> AUDIT assuming the syntax was similar to grant, which seems plausible.

I decided to take a whack at this and see what I could come up with, starting 
with the code in master at https://github.com/2ndQuadrant/pgaudit.

I modified pgaudit.log to work similarly to Oracle's session-level logging, 
meaning user statements are logged instead of tables which are accessed. 
pgaudit.log still has the various classes of commands and only those commands 
which match one of the classes are logged. Note that the pgaudit.log GUC is 
SUSET but can be set at the cluster, database, or user level.

An example - log all statements that create an object or read data:

   DB: connect user postgres, database postgres
  SQL: set pgaudit.log = 'DEFINITION, READ'
  SQL: create user user1

   DB: connect user user1, database postgres
  SQL: create table account
       (
           id int,
           name text,
           password text,
           description text
       );
AUDIT: SESSION,DEFINITION,CREATE TABLE,TABLE,public.account,<sql>

  SQL: select *
         from account;
AUDIT: SESSION,READ,SELECT,,,<statement>

  SQL: insert into account (id, name, password, description)
                    values (1, 'user1', 'HASH1', 'blah, blah');
AUDIT: <nothing logged>

Object auditing is done via the grant system (similar to Oracle object 
auditing), but now there is now a single audit role (defined by the 
pgaudit.role GUC which can also be set at the cluster, database, or user level).

An example - using column-level grants since they are more interesting:

 DB: connect user postgres, database postgres
  SQL: set pgaudit.log = 'NONE'
  SQL: create role audit
  SQL: set pgaudit.role = 'audit'

   DB: connect user user1, database postgres

  SQL: grant select (password)
          on public.account
          to audit;
AUDIT: <nothing logged>

  SQL: select id, name
         from account;
AUDIT: <nothing logged>

  SQL: select password
         from account;
AUDIT: OBJECT,READ,SELECT,TABLE,public.account,<sql>

  SQL: grant update (name, password)
          on public.account
          to audit;
AUDIT: <nothing logged>

  SQL: update account
          set description = 'yada, yada';
AUDIT: <nothing logged>

  SQL: update account
          set password = 'HASH2';
AUDIT: OBJECT,WRITE,UPDATE,TABLE,public.account,<sql>

Session and object auditing can be used together so a statement that does not 
match on an object may still be session logged depending on the settings.

An example - in this case the pgaudit.log GUC will be set on the user and 
grants persist from the previous example.  Another table is added to show how 
that affects logging:

   DB: connect user postgres, database postgres
  SQL: alter role user1 set pgaudit.log = 'READ,WRITE';
AUDIT: <nothing logged>

   DB: connect user user1, database postgres
  SQL: create table account_role_map
       (
           account_id int,
           role_id int
       );
AUDIT: <nothing logged>

  SQL: grant select
          on public.account_role_map
          to audit;
AUDIT: <nothing logged>

  SQL: select account.password, 
              account_role_map.role_id
         from account
              inner join account_role_map
                   on account.id = account_role_map.account_id
AUDIT: SESSION,READ,SELECT,,,<sql>
AUDIT: OBJECT,READ,SELECT,TABLE,public.account,<sql>
AUDIT: OBJECT,READ,SELECT,TABLE,public.account_role_map,<sql>

  SQL: update account
          set description = 'yada, yada';
AUDIT: SESSION,WRITE,UPDATE,,,<sql>

  SQL: update account
          set description = 'yada, yada'
        where password = 'HASH2';
AUDIT: SESSION,WRITE,UPDATE,,,<sql>
AUDIT: OBJECT,WRITE,UPDATE,TABLE,public.account,<sql>

That about covers it.  I'd be happy to create a pull request to contribute the 
code back to 2ndQuadrant.  There are some things I'm still planning to do, but 
I think this draft looks promising.  pgaudit.c is attached.

Thoughts and suggestions are welcome.

-- - David Steele da...@pgmasters.net

/*
 * pgaudit/pgaudit.c
 *
 * Copyright © 2014-2015, PostgreSQL Global Development Group
 *
 * Permission to use, copy, modify, and distribute this software and
 * its documentation for any purpose, without fee, and without a
 * written agreement is hereby granted, provided that the above
 * copyright notice and this paragraph and the following two
 * paragraphs appear in all copies.
 *
 * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,
 * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
 * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
 * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED
 * OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS
 * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,
 * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
 */

#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 "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);

Datum pgaudit_func_sql_drop(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(pgaudit_func_sql_drop);

/*
 * pgaudit_roles_str is the string value of the pgaudit.role configuration
 * variable, which contains the role for grant-based auditing.
 */
char *pgaudit_role_str = NULL;

/*
 * pgaudit_log_str is the string value of the pgaudit.log configuration
 * variable, e.g. "read, write, user". Each token corresponds to a flag in enum
 * LogClass below. We convert the list of tokens into a bitmap in pgaudit_log
 * for internal use.
 */
char *pgaudit_log_str = NULL;
static uint64 pgaudit_log = 0;

/* String contants for audit types */
#define AUDIT_TYPE_OBJECT       "OBJECT"
#define AUDIT_TYPE_SESSION      "SESSION"

/* String contants for log classes */
#define CLASS_ADMIN                     "ADMIN"
#define CLASS_CONFIG            "CONFIG"
#define CLASS_DEFINITION        "DEFINITION"
#define CLASS_FUNCTION          "FUNCTION"
#define CLASS_PRIVILEGE         "PRIVILEGE"
#define CLASS_READ                      "READ"
#define CLASS_USER                      "USER"
#define CLASS_WRITE                     "WRITE"

#define CLASS_UNKNOWN           "UNKNOWN";

/* Log class enum */
enum LogClass
{
        LOG_NONE = 0,

        /* SELECT */
        LOG_READ = (1 << 0),

        /* INSERT, UPDATE, DELETE, TRUNCATE */
        LOG_WRITE = (1 << 1),

        /* GRANT, REVOKE, ALTER … */
        LOG_PRIVILEGE = (1 << 2),

        /* CREATE/DROP/ALTER ROLE */
        LOG_USER = (1 << 3),

        /* DDL: CREATE/DROP/ALTER */
        LOG_DEFINITION = (1 << 4),

        /* DDL: CREATE OPERATOR etc. */
        LOG_CONFIG = (1 << 5),

        /* VACUUM, REINDEX, ANALYZE */
        LOG_ADMIN = (1 << 6),

        /* Function execution */
        LOG_FUNCTION = (1 << 7),

        /* Absolutely everything; not available via pgaudit.log */
        LOG_ALL = ~(uint64)0
};

/*
 * 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
{
        NodeTag type;
        const char *command_tag;
        const char *object_type;
        const char *object_name;
        const char *command_text;
        bool granted;
} AuditEvent;

/*
 * Returns the oid of the role specified in pgaudit.role.
 */
static Oid
audit_role_oid()
{
        HeapTuple roleTup;
        Oid oid = InvalidOid;

        ereport(DEBUG1, (errmsg("looking up audit role %s", pgaudit_role_str),
                                errhidestmt(true)));

        roleTup = SearchSysCache1(AUTHNAME, PointerGetDatum(pgaudit_role_str));

        if (HeapTupleIsValid(roleTup))
        {
                oid = HeapTupleGetOid(roleTup);
                ReleaseSysCache(roleTup);
        }

        return oid;
}

/* Returns true if either pgaudit.roles or pgaudit.log is set. */
static inline bool
pgaudit_configured()
{
        return (pgaudit_role_str && *pgaudit_role_str) || pgaudit_log != 0;
}

/*
 * 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
should_be_logged(AuditEvent *e, const char **classname)
{
        enum LogClass class = LOG_NONE;
        char *name;

        /*
         * Look at the type of the command and decide what LogClass needs to be
         * enabled for the command to be logged.
         */
        ereport(DEBUG1, (errmsg("audit event type %d", e->type), 
errhidestmt(true)));
        
        switch (e->type)
        {
                case T_SelectStmt:
                        name = CLASS_READ;
                        class = LOG_READ;
                        break;

                case T_InsertStmt:
                case T_UpdateStmt:
                case T_DeleteStmt:
                case T_TruncateStmt:
                        name = CLASS_WRITE;
                        class = LOG_WRITE;
                        break;

                case T_GrantStmt:
                case T_GrantRoleStmt:
                case T_AlterDefaultPrivilegesStmt:
                case T_AlterOwnerStmt:
                        name = CLASS_PRIVILEGE;
                        class = LOG_PRIVILEGE;
                        break;

                case T_CreateRoleStmt:
                case T_AlterRoleStmt:
                case T_DropRoleStmt:
                        name = CLASS_USER;
                        class = LOG_USER;
                        break;

                case T_AlterTableStmt:
                case T_AlterTableCmd:
                case T_AlterDomainStmt:
                case T_CreateStmt:
                case T_DefineStmt:
                case T_DropStmt:
                case T_CommentStmt:
                case T_IndexStmt:
                case T_LockStmt:
                case T_CreateFunctionStmt:
                case T_AlterFunctionStmt:
                case T_DoStmt:
                case T_RenameStmt:
                case T_RuleStmt:
                case T_ViewStmt:
                case T_CreateDomainStmt:
                case T_CreateTableAsStmt:
                case T_CreateSeqStmt:
                case T_AlterSeqStmt:
                case T_CreateTrigStmt:
                case T_CreateSchemaStmt:
                case T_AlterObjectSchemaStmt:
                case T_CreateEnumStmt:
                case T_CreateRangeStmt:
                case T_AlterEnumStmt:
                case T_RefreshMatViewStmt:
                case T_CreateForeignTableStmt:
                case T_CompositeTypeStmt:
                        name = CLASS_DEFINITION;
                        class = LOG_DEFINITION;
                        break;

                case T_CreatePLangStmt:
                case T_CreateConversionStmt:
                case T_CreateCastStmt:
                case T_CreateOpClassStmt:
                case T_CreateOpFamilyStmt:
                case T_AlterOpFamilyStmt:
                case T_AlterTSDictionaryStmt:
                case T_AlterTSConfigurationStmt:
                        name = CLASS_CONFIG;
                        class = LOG_CONFIG;
                        break;

                case T_ClusterStmt:
                case T_CreatedbStmt:
                case T_DropdbStmt:
                case T_LoadStmt:
                case T_VacuumStmt:
                case T_ExplainStmt:
                case T_VariableSetStmt:
                case T_DiscardStmt:
                case T_ReindexStmt:
                case T_CheckPointStmt:
                case T_AlterDatabaseStmt:
                case T_AlterDatabaseSetStmt:
                case T_AlterRoleSetStmt:
                case T_CreateTableSpaceStmt:
                case T_DropTableSpaceStmt:
                case T_DropOwnedStmt:
                case T_ReassignOwnedStmt:
                case T_CreateFdwStmt:
                case T_AlterFdwStmt:
                case T_CreateForeignServerStmt:
                case T_AlterForeignServerStmt:
                case T_CreateUserMappingStmt:
                case T_AlterUserMappingStmt:
                case T_DropUserMappingStmt:
                case T_AlterTableSpaceOptionsStmt:
                case T_SecLabelStmt:
                case T_CreateExtensionStmt:
                case T_AlterExtensionStmt:
                case T_AlterExtensionContentsStmt:
                case T_CreateEventTrigStmt:
                case T_AlterEventTrigStmt:
                case T_AlterTableMoveAllStmt:
                case T_AlterSystemStmt:
                        name = CLASS_ADMIN;
                        class = LOG_ADMIN;
                        break;

                case T_ExecuteStmt:
                        name = CLASS_FUNCTION;
                        class = LOG_FUNCTION;
                        break;

                /*
                 * Anything that's left out of the list above is just noise, 
and not
                 * very interesting from an auditing perspective. So there's
                 * intentionally no way to enable LOG_ALL.
                 */
                default:
                        name = CLASS_UNKNOWN;
                        class = LOG_ALL;
                        break;
        }

        *classname = name;

        /*
         * We log audit events under the following conditions:
         *
         * 1. If the audit role has been explicitly granted permission for
         *    an operation.
         */
        if (e->granted)
        {
                ereport(DEBUG1, (errmsg("pgaudit based on grants"), 
errhidestmt(true)));

                return true;
        }

        /* 2. If the event belongs to a class covered by pgaudit.log. */
        if ((pgaudit_log & class) == class)
        {
                ereport(DEBUG1, (errmsg("pgaudit based on log setting"),
                        errhidestmt(true)));

                return true;
        }

        return false;
}

/*
 * Takes an AuditEvent and, if it should_be_logged(), 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)
{
        Oid userid;
        const char *timestamp;
        const char *database;
        const char *username;
        const char *eusername;
        const char *classname;

        userid = GetSessionUserId();

        if (!should_be_logged(e, &classname))
                return;

        timestamp = timestamptz_to_str(GetCurrentTimestamp());
        database = get_database_name(MyDatabaseId);
        username = GetUserNameFromId(userid);
        eusername = GetUserNameFromId(GetUserId());

        /*
         * XXX We only support logging via ereport(). In future, we may log
         * to a separate file or a table.
         */
        ereport(LOG,
                        (errmsg("AUDIT,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s",
                                        timestamp, database, username, 
eusername,
                                        e->granted ? AUDIT_TYPE_OBJECT : 
AUDIT_TYPE_SESSION,
                                        classname, e->command_tag, 
e->object_type, e->object_name,
                                        e->command_text),
                         errhidestmt(true)));
}

/*
 * Create AuditEvents for DML operations via executor permissions checks.
 */
static void
log_dml(Oid auditOid, List *rangeTabls, bool abort_on_violation)
{
        ListCell *lr;
        bool first = true;

        foreach(lr, rangeTabls)
        {
                Oid relOid;
                Relation rel;
                RangeTblEntry *rte = lfirst(lr);
                char *relname;
                const char *tag;
                const char *reltype;
                NodeTag type;
                AuditEvent e;

                /* We only care about tables, and can ignore subqueries etc. */
                if (rte->rtekind != RTE_RELATION)
                        continue;

                /*
                 * Filter out any system relations and get the fully-qualified 
name.
                 */
                relOid = rte->relid;
                rel = relation_open(relOid, NoLock);
                
                if (IsSystemNamespace(RelationGetNamespace(rel)))
                {
                        relation_close(rel, NoLock);
                        return;
                }
                
                relname = quote_qualified_identifier(get_namespace_name(
                                                                                
         RelationGetNamespace(rel)),
                                                                                
         RelationGetRelationName(rel));
                relation_close(rel, NoLock);

                /*
                 * We don't have access to the parsetree here, so we have to 
generate
                 * the node type, object type, and command tag by decoding
                 * rte->requiredPerms and rte->relkind.
                 */
                if (rte->requiredPerms & ACL_INSERT)
                {
                        tag = "INSERT";
                        type = T_InsertStmt;
                }
                else if (rte->requiredPerms & ACL_UPDATE)
                {
                        tag = "UPDATE";
                        type = T_UpdateStmt;
                }
                else if (rte->requiredPerms & ACL_DELETE)
                {
                        tag = "DELETE";
                        type = T_DeleteStmt;
                }
                else if (rte->requiredPerms & ACL_SELECT)
                {
                        tag = "SELECT";
                        type = T_SelectStmt;
                }
                else
                {
                        tag = "UNKNOWN";
                        type = T_Invalid;
                }

                switch (rte->relkind)
                {
                        case RELKIND_RELATION:
                                reltype = "TABLE";
                                break;

                        case RELKIND_INDEX:
                                reltype = "INDEX";
                                break;

                        case RELKIND_SEQUENCE:
                                reltype = "SEQUENCE";
                                break;

                        case RELKIND_TOASTVALUE:
                                reltype = "TOASTVALUE";
                                break;

                        case RELKIND_VIEW:
                                reltype = "VIEW";
                                break;

                        case RELKIND_COMPOSITE_TYPE:
                                reltype = "COMPOSITE_TYPE";
                                break;

                        case RELKIND_FOREIGN_TABLE:
                                reltype = "FOREIGN_TABLE";
                                break;

                        case RELKIND_MATVIEW:
                                reltype = "MATVIEW";
                                break;

                        default:
                                reltype = "UNKNOWN";
                                break;
                }

                /* 
                 * Fill values in the event struct that are required for session
                 * logging.
                 */
                e.type = type;
                e.command_tag = tag;
                e.granted = false;
                e.command_text = debug_query_string ? debug_query_string : "";

                /*
                 * If this is the first rte then session log.
                 */
                if (first)
                {
                        e.object_name = "";
                        e.object_type = "";

                        log_audit_event(&e);

                        first = false;
                }

                /* Perform object auditing only if the audit role is valid */
                if (auditOid != InvalidOid)
                {
                        /* Fill event values that are required for object 
logging */
                        e.object_name = relname;
                        e.object_type = reltype;

                        ereport(DEBUG1, (errmsg("checking table %s, sql %s", 
e.object_name,
                                                e.command_text), 
errhidestmt(true)));

                        /*
                         * If any of the required permissions for the relation 
are granted
                         * to the audit role then audit the relation
                         */
                        if (pg_class_aclmask(relOid, auditOid, 
rte->requiredPerms,
                                                                 ACLMASK_ANY) 
!= 0)
                        {
                                ereport(DEBUG1, (errmsg("GOT HERE"), 
errhidestmt(true)));
                                e.granted = true;
                        }

                        /*
                         * Else check if the audit role has column-level 
permissions for
                         * select, insert, or update.
                         */
                        else if ((rte->requiredPerms &
                                         (ACL_SELECT | ACL_INSERT | 
ACL_UPDATE)) != 0)
                        {
                                AttrNumber col;
                                Bitmapset *tmpset;

                                /* 
                                 * Check the select columns to see if the audit 
role has
                                 * priveleges on any of them. Adapted from 
ExecCheckRTEPerms.
                                 */
                                if (rte->requiredPerms & ACL_SELECT)
                                {
                                        if (bms_is_empty(rte->selectedCols))
                                        {
                                                if 
(pg_attribute_aclcheck_all(relOid, auditOid, ACL_SELECT,
                                                                                
                          ACLMASK_ANY) == ACLCHECK_OK)
                                                        e.granted = true;
                                        }

                                        if (!e.granted)
                                        {
                                                tmpset = 
bms_copy(rte->selectedCols);

                                                while ((col = 
bms_first_member(tmpset)) >= 0 && !e.granted)
                                                {
                                                        col += 
FirstLowInvalidHeapAttributeNumber;

                                                        if (col == 
InvalidAttrNumber)
                                                        {
                                                                if 
(pg_attribute_aclcheck_all(relOid, auditOid,
                                                                                
                                          ACL_SELECT,
                                                                                
                                          ACLMASK_ALL) == ACLCHECK_OK)
                                                                        
e.granted = true;
                                                        }
                                                        else
                                                        {
                                                                if 
(pg_attribute_aclcheck(relOid, col, auditOid,
                                                                                
                                  ACL_SELECT) == ACLCHECK_OK)
                                                                        
e.granted = true;
                                                        }
                                                }

                                                bms_free(tmpset);
                                        }
                                }

                                /*
                                 * Check the modified columns to see if the 
audit role has
                                 * privileges on any of them. Adapted from 
ExecCheckRTEPerm.
                                 */
                                if (rte->requiredPerms & (ACL_INSERT | 
ACL_UPDATE) && !e.granted)
                                {
                                        if (bms_is_empty(rte->modifiedCols))
                                        {
                                                if 
(pg_attribute_aclcheck_all(relOid, auditOid,
                                                                                
                          (ACL_UPDATE | ACL_INSERT) &
                                                                                
                           rte->requiredPerms,
                                                                                
                          ACLMASK_ANY) != ACLCHECK_OK)
                                                        e.granted = true;
                                        }

                                        if (!e.granted)
                                        {
                                                tmpset = 
bms_copy(rte->modifiedCols);
                                        
                                                while ((col = 
bms_first_member(tmpset)) >= 0)
                                                {
                                                        col += 
FirstLowInvalidHeapAttributeNumber;

                                                        if (col != 
InvalidAttrNumber)
                                                        {
                                                                if 
(pg_attribute_aclcheck(relOid, col, auditOid,
                                                                                
                                  (ACL_UPDATE | ACL_INSERT)
                                                                                
                                   & rte->requiredPerms) == ACLCHECK_OK)
                                                                        
e.granted = true;
                                                        }
                                                }

                                                bms_free(tmpset);
                                        }
                                }
                        }
                }

                /* Only do relation level logging if a grant was found. */
                if (e.granted)
                {
                        log_audit_event(&e);
                }

                pfree(relname);
        }
}

/*
 * Create AuditEvents for utility commands that are not supported by event
 * triggers, particularly those which affect global objects.
 */
static void
log_utility_command(Node *parsetree,
                                        const char *queryString,
                                        ProcessUtilityContext context,
                                        ParamListInfo params,
                                        DestReceiver *dest,
                                        char *completionTag)
{
        AuditEvent e;
        bool supported_stmt = true;

        /*
         * If the statement (and, for some statements, the object type) is
         * supported by event triggers, then we don't need to log anything.
         * Otherwise, we log the query string.
         *
         * The following logic is copied from standard_ProcessUtility in
         * tcop/utility.c, and will need to be changed if event trigger
         * support is expanded to other commands (if not, the command
         * will be logged twice).
         */
        switch (nodeTag(parsetree))
        {
                /*
                 * The following statements are never supported by event
                 * triggers.
                 */
                case T_DoStmt:
                case T_CreateTableSpaceStmt:
                case T_DropTableSpaceStmt:
                case T_AlterTableSpaceOptionsStmt:
                case T_TruncateStmt:
                case T_CommentStmt:
                case T_SecLabelStmt:
                case T_GrantStmt:
                case T_GrantRoleStmt:
                case T_CreatedbStmt:
                case T_AlterDatabaseStmt:
                case T_AlterDatabaseSetStmt:
                case T_DropdbStmt:
                case T_LoadStmt:
                case T_ClusterStmt:
                case T_VacuumStmt:
                case T_ExplainStmt:
                case T_VariableSetStmt:
                case T_DiscardStmt:
                case T_CreateEventTrigStmt:
                case T_AlterEventTrigStmt:
                case T_CreateRoleStmt:
                case T_AlterRoleStmt:
                case T_AlterRoleSetStmt:
                case T_DropRoleStmt:
                case T_ReassignOwnedStmt:
                case T_LockStmt:
                case T_CheckPointStmt:
                case T_ReindexStmt:
                case T_AlterTableMoveAllStmt:
                case T_AlterSystemStmt:

                /*
                 * The following statements are supported only by the 
ddl_command_end
                 * event trigger. (This list is from ProcessUtilitySlow.)
                 */
                case T_CreateSchemaStmt:
                case T_AlterDomainStmt:
                case T_DefineStmt:
                case T_CreateExtensionStmt:
                case T_AlterExtensionStmt:
                case T_AlterExtensionContentsStmt:
                case T_CreateFdwStmt:
                case T_AlterFdwStmt:
                case T_CreateForeignServerStmt:
                case T_AlterForeignServerStmt:
                case T_CreateUserMappingStmt:
                case T_AlterUserMappingStmt:
                case T_DropUserMappingStmt:
                case T_CreateEnumStmt:
                case T_CreateRangeStmt:
                case T_AlterEnumStmt:
                case T_CreateFunctionStmt:
                case T_AlterFunctionStmt:
                case T_RuleStmt:
                case T_CreateTrigStmt:
                case T_CreatePLangStmt:
                case T_CreateDomainStmt:
                case T_CreateConversionStmt:
                case T_CreateCastStmt:
                case T_CreateOpClassStmt:
                case T_CreateOpFamilyStmt:
                case T_AlterOpFamilyStmt:
                case T_AlterTSDictionaryStmt:
                case T_AlterTSConfigurationStmt:
                case T_RenameStmt:
                case T_AlterOwnerStmt:
                case T_DropOwnedStmt:
                case T_AlterDefaultPrivilegesStmt:
                        supported_stmt = false;
                        break;

                /*
                 * Exclude any ALTER <object> SET SCHEMA statements which will 
be
                 * handled by log_object_access() in 9.3 and 9.4
                 */
                case  T_AlterObjectSchemaStmt:
                {
                        AlterObjectSchemaStmt *stmt = (AlterObjectSchemaStmt *) 
parsetree;

                        switch(stmt->objectType)
                        {
                                case OBJECT_FOREIGN_TABLE:
                                case OBJECT_INDEX:
                                case OBJECT_MATVIEW:
                                case OBJECT_SEQUENCE:
                                case OBJECT_TABLE:
                                case OBJECT_TYPE:
                                case OBJECT_VIEW:
                                        break;
                                        
                                default:
                                        supported_stmt = false;
                        }
                }
                break;

                /*
                 * The following statements are supported by event triggers for 
certain
                 * object types. We can always use DROP support, but the others 
are
                 * dependent on the ddl_command_end trigger.
                 */
                case T_DropStmt:
                        {
                                DropStmt   *stmt = (DropStmt *) parsetree;

                                if 
(!EventTriggerSupportsObjectType(stmt->removeType))
                                        supported_stmt = false;
                        }
                        break;

                /*
                 * All other statement types have event trigger support, or we 
don't
                 * care about them at all.
                 */
                default:
                        break;
        }

        if (supported_stmt)
                return;

        e.type = nodeTag(parsetree);
        e.object_name = "";
        e.object_type = "";
        e.command_tag = CreateCommandTag(parsetree);
        e.command_text = queryString;
        e.granted = false;

        log_audit_event(&e);
}

/*
 * Create AuditEvents for certain kinds of CREATE and ALTER statements as
 * detected by log_object_access() in lieu of event trigger support.
 */
static void
log_create_or_alter(bool create,
                                        Oid classId,
                                        Oid objectId,
                                        int subId)
{
        AuditEvent e;
        NodeTag type;
        const char *tag;
        const char *name;
        const char *objtype;

        switch (classId)
        {
                case RelationRelationId:
                {
                        Relation rel;
                        Form_pg_class class;
                        char *relnsp;
                        char *relname;

                        rel = relation_open(objectId, NoLock);
                        class = RelationGetForm(rel);
                        relnsp = get_namespace_name(RelationGetNamespace(rel));
                        relname = RelationGetRelationName(rel);
                        name = quote_qualified_identifier(relnsp, relname);

                        switch (class->relkind)
                        {
                                case RELKIND_RELATION:
                                        objtype = "TABLE";
                                        type = create ? T_CreateStmt : 
T_AlterTableStmt;
                                        tag = create ? "CREATE TABLE" : "ALTER 
TABLE";
                                        break;

                                case RELKIND_INDEX:
                                        objtype = "INDEX";
                                        type = T_IndexStmt;
                                        tag = create ? "CREATE INDEX" : "ALTER 
INDEX";
                                        break;

                                case RELKIND_SEQUENCE:
                                        objtype = "SEQUENCE";
                                        type = create ? T_CreateStmt : 
T_AlterSeqStmt;
                                        tag = create ? "CREATE SEQUENCE" : 
"ALTER SEQUENCE";
                                        break;

                                case RELKIND_VIEW:
                                        objtype = "VIEW";
                                        /* T_ViewStmt covers both CREATE and 
ALTER */
                                        type = T_ViewStmt;
                                        tag = create ? "CREATE VIEW" : "ALTER 
VIEW";
                                        break;

                                case RELKIND_COMPOSITE_TYPE:
                                        objtype = "TYPE";
                                        /* T_CompositeTypeStmt covers both 
CREATE and ALTER */
                                        type = T_CompositeTypeStmt;
                                        tag = create ? "CREATE TYPE" : "ALTER 
TYPE";
                                        break;

                                case RELKIND_FOREIGN_TABLE:
                                        objtype = "FOREIGN TABLE";
                                        /* There is no T_AlterForeignTableStmt 
*/
                                        type = T_CreateForeignTableStmt;
                                        tag = "CREATE FOREIGN TABLE";
                                        break;

                                case RELKIND_MATVIEW:
                                        objtype = "MATERIALIZED VIEW";
                                        /* Pretend that materialized views are 
a kind of table */
                                        type = create ? T_CreateStmt : 
T_AlterTableStmt;
                                        tag = create ? "CREATE MATERIALIZED 
VIEW" :
                                                       "ALTER MATERIALIZED 
VIEW";
                                        break;

                                /*
                                 * XXX Are there any other RELKIND_xxx cases 
that we
                                 * need to handle here?
                                 */
                                default:
                                        objtype = "UNKNOWN";
                                        type = T_Invalid;
                                        tag = "";
                                        break;
                        }

                        relation_close(rel, NoLock);
                }
                break;

                /*
                 * Leave it to the ProcessUtility_hook to handle all other 
commands.
                 * There's not much to improve on "create database foo", for 
example.
                 */
                default:
                        return;
                        break;
    }

        /* Load the AuditEvent struct */
        e.type = type;
        e.object_name = name;
        e.object_type = objtype;
        e.command_tag = tag;
        e.granted = false;
        e.command_text = debug_query_string ? debug_query_string : "";

        /* Log the event */
        log_audit_event(&e);
}

/*
 * 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;
        const char *name;
        AuditEvent e;

        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;
        }

        name = 
quote_qualified_identifier(get_namespace_name(proc->pronamespace),
                                                                          
NameStr(proc->proname));
        ReleaseSysCache(proctup);

        e.type = T_ExecuteStmt;
        e.object_name = name;
        e.object_type = "FUNCTION";
        e.command_tag = "EXECUTE";
        e.granted = false;
        e.command_text = debug_query_string ? debug_query_string : "";

        log_audit_event(&e);
}

/*
 * 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)
        {
                case OAT_FUNCTION_EXECUTE:
                        log_function_execute(objectId);
                        break;

                /*
                 * We use OAT_POST_{CREATE,ALTER} only to provide limited
                 * support for certain CREATE/ALTER commands in the absence of
                 * usable event trigger support.
                 */
                case OAT_POST_CREATE:
                {
                        ObjectAccessPostCreate *pc = arg;

                        if (pc->is_internal)
                                return;

                        log_create_or_alter(access == OAT_POST_CREATE, classId,
                                                                objectId, 
subId);
                }
                break;

                case OAT_POST_ALTER:
                        {
                                ObjectAccessPostAlter *pa = arg;

                                if (pa->is_internal)
                                        return;

                                log_create_or_alter(access == OAT_POST_CREATE, 
classId,
                                                                        
objectId, subId);
                        }
                        break;

                default:
                case OAT_DROP:
                case OAT_NAMESPACE_SEARCH:
                        /* Not relevant to our purposes. */
                        break;
        }
}

/*
 * Event trigger functions
 */

/*
 * A sql_drop event trigger to build AuditEvents for dropped objects.
 */
Datum
pgaudit_func_sql_drop(PG_FUNCTION_ARGS)
{
        EventTriggerData *trigdata;
        TupleDesc                 spi_tupdesc;
        int               ret, row;
        const char               *query_dropped_objects;

        MemoryContext tmpcontext;
        MemoryContext oldcontext;

        if (!pgaudit_configured())
                PG_RETURN_NULL();

        if (!CALLED_AS_EVENT_TRIGGER(fcinfo))
                elog(ERROR, "not fired by event trigger manager");

        /*
         * This query returns a list of objects dropped by the command which no
         * longer exist, and thus cannot be looked up). With no support for
         * deparsing the command, the best we can do is log the object names.
         */
        query_dropped_objects =
                "SELECT classid, objid, objsubid, UPPER(object_type), 
schema_name, "
                " object_name, object_identity"
                "  FROM pg_event_trigger_dropped_objects()";

        tmpcontext = AllocSetContextCreate(CurrentMemoryContext,
                                                                           
"pgaudit_func_sql_drop temporary context",
                                                                           
ALLOCSET_DEFAULT_MINSIZE,
                                                                           
ALLOCSET_DEFAULT_INITSIZE,
                                                                           
ALLOCSET_DEFAULT_MAXSIZE);
        oldcontext = MemoryContextSwitchTo(tmpcontext);

        ret = SPI_connect();
        if (ret < 0)
                elog(ERROR, "pgaudit_func_sql_drop: SPI_connect returned %d", 
ret);

        ret = SPI_execute(query_dropped_objects, true, 0);
        if (ret != SPI_OK_SELECT)
                elog(ERROR, "pgaudit_func_sql_drop: SPI_execute returned %d", 
ret);

        spi_tupdesc = SPI_tuptable->tupdesc;

        trigdata = (EventTriggerData *) fcinfo->context;

        for (row = 0; row < SPI_processed; row++)
        {
                AuditEvent e;
                HeapTuple  spi_tuple;

                spi_tuple = SPI_tuptable->vals[row];

                e.type = nodeTag(trigdata->parsetree);
                e.object_name = SPI_getvalue(spi_tuple, spi_tupdesc, 7);
                e.object_type = SPI_getvalue(spi_tuple, spi_tupdesc, 4);
                e.command_tag = trigdata->tag;
                e.command_text = "";
                e.granted = false;

                log_audit_event(&e);
        }

        SPI_finish();
        MemoryContextSwitchTo(oldcontext);
        MemoryContextDelete(tmpcontext);
        PG_RETURN_NULL();
}

/*
 * Hook functions
 *
 * These functions (which are installed by _PG_init, below) just call
 * pgaudit logging functions before continuing the chain of hooks. We
 * must not call any logging functions from an aborted transaction.
 */
static ExecutorCheckPerms_hook_type next_ExecutorCheckPerms_hook = NULL;
static ProcessUtility_hook_type next_ProcessUtility_hook = NULL;
static object_access_hook_type next_object_access_hook = NULL;

static bool
pgaudit_ExecutorCheckPerms_hook(List *rangeTabls, bool abort)
{
        Oid auditOid = audit_role_oid();

        if (!IsAbortedTransactionBlockState() &&
                (auditOid != InvalidOid || pgaudit_configured()))
                log_dml(auditOid, rangeTabls, abort);

        if (next_ExecutorCheckPerms_hook &&
                !(*next_ExecutorCheckPerms_hook) (rangeTabls, abort))
                return false;

        return true;
}

static void
pgaudit_ProcessUtility_hook(Node *parsetree,
                                                        const char *queryString,
                                                        ProcessUtilityContext 
context,
                                                        ParamListInfo params,
                                                        DestReceiver *dest,
                                                        char *completionTag)
{
        if (!IsAbortedTransactionBlockState() && pgaudit_configured())
                log_utility_command(parsetree, queryString, context,
                                                        params, dest, 
completionTag);

        if (next_ProcessUtility_hook)
                (*next_ProcessUtility_hook) (parsetree, queryString, context,
                                                                         
params, dest, completionTag);
        else
                standard_ProcessUtility(parsetree, queryString, context,
                                                                params, dest, 
completionTag);
}

static void
pgaudit_object_access_hook(ObjectAccessType access,
                                                   Oid classId,
                                                   Oid objectId,
                                                   int subId,
                                                   void *arg)
{
        if (!IsAbortedTransactionBlockState() && pgaudit_configured())
                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, user", 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 we have 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)
        {
                char *token = (char *)lfirst(lt);

                if (pg_strcasecmp(token, "none") == 0)
                        /* Nothing to do. If "none" occurs in combination with 
other
                         * tokens, it's ignored. */
                        ;
                else if (pg_strcasecmp(token, CLASS_READ) == 0)
                        *f |= LOG_READ;
                else if (pg_strcasecmp(token, CLASS_WRITE) == 0)
                        *f |= LOG_WRITE;
                else if (pg_strcasecmp(token, CLASS_PRIVILEGE) == 0)
                        *f |= LOG_PRIVILEGE;
                else if (pg_strcasecmp(token, CLASS_USER) == 0)
                        *f |= LOG_USER;
                else if (pg_strcasecmp(token, CLASS_DEFINITION) == 0)
                        *f |= LOG_DEFINITION;
                else if (pg_strcasecmp(token, CLASS_CONFIG) == 0)
                        *f |= LOG_CONFIG;
                else if (pg_strcasecmp(token, CLASS_ADMIN) == 0)
                        *f |= LOG_ADMIN;
                else if (pg_strcasecmp(token, CLASS_FUNCTION) == 0)
                        *f |= LOG_FUNCTION;
                else
                {
                        free(f);
                        pfree(rawval);
                        list_free(flags);
                        return false;
                }
        }

        pfree(rawval);
        list_free(flags);

        /*
         * All is well, 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)
{
    ereport(DEBUG1, (errmsg("pgaudit log assign %s", newval), 
errhidestmt(true)));
    
        if (extra)
                pgaudit_log = *(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,
                                                           &pgaudit_role_str,
                                                           "",
                                                           PGC_SUSET,
                                                           GUC_LIST_INPUT | 
GUC_NOT_IN_SAMPLE,
                                                           NULL, NULL, NULL);

        /*
         * pgaudit.log = "read, write, user"
         *
         * This variables controls what classes of commands are logged.
         */
        DefineCustomStringVariable("pgaudit.log",
                                                           "Enable auditing for 
classes of commands",
                                                           NULL,
                                                           &pgaudit_log_str,
                                                           "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;
}

Attachment: signature.asc
Description: OpenPGP digital signature

Reply via email to