pgaudit ======= This is the initial version of an auditing module for Postgres. It collects audit events from various sources and logs them in CSV format including a timestamp, user information, details of objects affected (if any), and the fully-qualified command text (whenever available). All DDL, DML (including SELECT), and utility commands are supported. These are categorised as described below, and audit logging for each group of commands may be enabled or disabled by the superuser. Once enabled, however, audit logging may not be disabled by a user. The categories of commands are defined as follows: read Commands that read database objects (SELECT) write DML commands that modify database objects (e.g. INSERT) privilege DCL commands that are related to access privileges (e.g. GRANT/REVOKE) user DDL commands that are related to database users (e.g. CREATE/DROP/ALTER ROLE) definition User-level DDL commands (e.g. CREATE TABLE) config Administrator-level commands that change the database configuration (e.g. CREATE LANGUAGE, CREATE OPERATOR CLASS) admin Administrator-level commands that are not configuration related (e.g. CLUSTER, VACUUM, REINDEX) function Function execution This code is released under the PostgreSQL licence, as given at http://www.postgresql.org/about/licence/ Copyright is novated to the PostgreSQL Global Development Group. What about log_statement = 'all'? --------------------------------- 1. pgaudit logs fully-qualified names A query like «delete from x» in the log file can be interpreted only with reference to the current search_path setting. In contrast, this module always logs fully-qualified object names, e.g. "public.x". For DDL commands that have appropriate event trigger support, we log an unambigous representation of the command text, not just the query string as supplied by the user. 2. pgaudit creates a log entry for each affected object A query that refers to multiple objects results in a log entry for each object involved, so the effects of «select * from some_view» can be seen rather than inferred. Searching for all accesses to a particular table is also straightforward. 3. pgaudit provides finer-grained control over what events are logged With log_statement, one may select none, ddl, mod, or all. With pgaudit, individual groups of commands may be selected for logging. Want to log only GRANT/REVOKE operations? You can. Installation ------------ The latest pgaudit code is available at https://github.com/2ndQuadrant/pgaudit This module will work with Postgres 9.3 and 9.4 (not yet released at the time of writing), but it needs updated event trigger code in order to log a complete, unambiguous representation of DDL commands. We hope that the necessary event trigger code will be available in 9.5, but until then you will have to build your own Postgres to see pgaudit at its best. The necessary code is available in the deparse branch of git://git.postgresql.org/git/2ndquadrant_bdr.git First, build and install Postgres as usual from the deparse branch. Copy pgaudit into contrib/pgaudit and edit the Makefile to uncomment the line that defines "USE_DEPARSE_FUNCTIONS". Then run "make install". If you want to use it against an earlier version of Postgres, just run "make USE_PGXS=1 install" in the pgaudit directory. Once the module is installed, edit postgresql.conf and set: shared_preload_libraries = 'pgaudit' Then start the server and run: CREATE EXTENSION pgaudit; Configuration ------------- Audit logging is controlled by the pgaudit.log configuration variable, which may be set to a comma-separated list of tokens identifying what classes of commands to log. For example, pgaudit.log = 'read, write, user' pgaudit.log may be set to an empty string or "none" to disable logging, or to any combination of the following logging classes: read, write, privilege, user, definition, config, admin, function These classes are defined above. See the "CLASSES" file for a complete list of commands corresponding to each logging class. pgaudit.log may be set in postgresql.conf (to apply globally), or as a per-database or per-user setting: ALTER DATABASE xxx SET pgaudit.log = '…' or: ALTER ROLE xxx SET pgaudit.log = '…' Log format ---------- We log audit events in CSV format with the following fields: [AUDIT],,,,, ,,,, *class* is the name of a logging class (READ, WRITE, etc.) *tag* is the command tag (e.g. SELECT) *object type* is the type of object affected, if any (e.g. TABLE) *object id* is some way to identify the affected object, usually a fully-qualified name *command text* is the full text of the command. Note that not all fields are always available. Here are some examples of log output: LOG: [AUDIT],2014-04-30 17:13:55.202854+09,auditdb,ianb,ianb,DEFINITION,CREATE TABLE,TABLE,public.x,CREATE TABLE public.x (a pg_catalog.int4 , b pg_catalog.int4 ) WITH (oids=OFF) LOG: [AUDIT],2014-04-30 17:14:06.548923+09,auditdb,ianb,ianb,WRITE,INSERT,TABLE,public.x,INSERT INTO x VALUES(1,1); LOG: [AUDIT],2014-04-30 17:14:21.221879+09,auditdb,ianb,ianb,READ,SELECT,TABLE,public.x,SELECT * FROM x; LOG: [AUDIT],2014-04-30 17:15:25.620213+09,auditdb,ianb,ianb,READ,SELECT,VIEW,public.v_x,SELECT * from v_x; LOG: [AUDIT],2014-04-30 17:15:25.620262+09,auditdb,ianb,ianb,READ,SELECT,TABLE,public.x,SELECT * from v_x; LOG: [AUDIT],2014-04-30 17:16:00.849868+09,auditdb,ianb,ianb,WRITE,UPDATE,TABLE,public.x,UPDATE x SET a=a+1; LOG: [AUDIT],2014-04-30 17:16:18.291452+09,auditdb,ianb,ianb,ADMIN,VACUUM,,,VACUUM x; LOG: [AUDIT],2014-04-30 17:18:01.08291+09,auditdb,ianb,ianb,DEFINITION,CREATE FUNCTION,FUNCTION,public.func_x(),CREATE FUNCTION public.func_x() RETURNS pg_catalog.int4 LANGUAGE sql VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100.000000 AS $dprs_$SELECT a FROM x LIMIT 1;$dprs_$ LOG: [AUDIT],2014-04-30 17:18:09.694755+09,auditdb,ianb,ianb,FUNCTION,EXECUTE,FUNCTION,public.func_x,SELECT * FROM func_x(); LOG: [AUDIT],2014-04-30 17:18:09.694865+09,auditdb,ianb,ianb,READ,SELECT,TABLE,public.x,SELECT * FROM func_x(); LOG: [AUDIT],2014-04-30 17:18:33.703007+09,auditdb,ianb,ianb,WRITE,DELETE,VIEW,public.v_x,DELETE FROM v_x; LOG: [AUDIT],2014-04-30 17:18:33.703051+09,auditdb,ianb,ianb,WRITE,DELETE,TABLE,public.x,DELETE FROM v_x; LOG: [AUDIT],2014-04-30 17:19:54.811244+09,auditdb,ianb,ianb,ADMIN,SET,,,set role ams; LOG: [AUDIT],2014-04-30 17:19:57.039979+09,auditdb,ianb,ams,WRITE,INSERT,VIEW,public.v_x,INSERT INTO v_x VALUES(1,2); LOG: [AUDIT],2014-04-30 17:19:57.040014+09,auditdb,ianb,ams,WRITE,INSERT,TABLE,public.x,INSERT INTO v_x VALUES(1,2); LOG: [AUDIT],2014-04-30 17:20:02.059415+09,auditdb,ianb,ams,ADMIN,SET,,,SET role ianb; LOG: [AUDIT],2014-04-30 17:20:09.840261+09,auditdb,ianb,ianb,DEFINITION,ALTER TABLE,TABLE,public.x,ALTER TABLE public.x ADD COLUMN c pg_catalog.int4 LOG: [AUDIT],2014-04-30 17:23:58.920342+09,auditdb,ianb,ianb,ADMIN,ALTER ROLE,,,ALTER USER ams SET search_path = 'foo'; Design overview --------------- We collect audit events from event triggers for any operations with event trigger support. (For some commands, this also gives us the unambiguous deparsed command representation.) Other DDL and utility commands are collected by a utility hook, and DML and SELECT events are collected by an executor hook. See DESIGN for more details and future improvements. Known problems -------------- Statements are audit-logged even if the transaction they're in is later rolled back. This is sometimes desirable (e.g. with SELECT), but makes it more difficult to tell what happened. Some utility statements are audit-logged even though they subsequently fail (e.g. «set shared_buffers = '32MB'»). Deparsed query text is not yet available for DROP events. Some bugs of varying severity in the deparse code have been reported upstream. Some have been fixed already, but the code is under active development, and other bugs still await fixes. Bug reports and other feedback are welcome. Authors ------- Ian Barwick Abhijit Menon-Sen The research leading to these results has received funding from the European Union's Seventh Framework Programme (FP7/2007-2013) under grant agreement n° 318633. http://axleproject.eu