From: David Steele <david@pgmasters.net>
To: Sawada Masahiko <sawada.mshk@gmail.com>
Cc: Tatsuo Ishii <ishii@postgresql.org>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Abhijit Menon-Sen <ams@2ndquadrant.com>
Cc: Fujii Masao <masao.fujii@gmail.com>
Subject: Re: Auditing extension for PostgreSQL (Take 2)
Date: Wed, 15 Apr 2015 13:34:25 -0400
Message-ID: <552EA121.1090603@pgmasters.net> (raw)
In-Reply-To: <CAD21AoCdr6R7T-PuAQf7S70c6qqX1JvBV55idbfcPtz_tEZ5hQ@mail.gmail.com>
References: <552326EE.4060509@pgmasters.net>
<CA+U5nMJkT0ZR3ZD-NhqX=OAfpMJak2ORUE4q=3+zFHay6yuTNw@mail.gmail.com>
<552D2C1A.3060002@pgmasters.net>
<20150415.081354.1167351714960358354.t-ishii@sraoss.co.jp>
<552DA959.2010102@pgmasters.net>
<CAD21AoDUG8G90SUo70JL=AKDA4dXGNb_wAc4-MvxtwvuMRO0kA@mail.gmail.com>
<CAD21AoCdr6R7T-PuAQf7S70c6qqX1JvBV55idbfcPtz_tEZ5hQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 4/15/15 11:30 AM, Sawada Masahiko wrote:
> On Wed, Apr 15, 2015 at 10:52 AM, Sawada Masahiko <sawada.mshk@gmail.com> wrote:
>> I tested v8 patch with CURSOR case I mentioned before, and got
>> segmentation fault again.
>> Here are log messages in my environment,
>>
>> =# select test();
>> LOG: server process (PID 29730) was terminated by signal 11:
>> Segmentation fault
>> DETAIL: Failed process was running: select test();
>> LOG: terminating any other active server processes
>> WARNING: terminating connection because of crash of another server process
>> DETAIL: The postmaster has commanded this server process to roll back
>> the current transaction and exit, because another server process
>> exited abnormally and possibly corrupted shared memory.
>> HINT: In a moment you should be able to reconnect to the database and
>> repeat your command.
>> FATAL: the database system is in recovery mode
>
> I investigated this problem and inform you my result here.
>
> When we execute test() function I mentioned, the three stack items in
> total are stored into auditEventStack.
> The two of them are freed by stack_pop() -> stack_free() (i.g,
> stack_free() is called by stack_pop()).
> One of them is freed by PortalDrop() -> .. ->
> MemoryContextDeleteChildren() -> ... -> stack_free().
> And it is freed at the same time as deleting pg_audit memory context,
> and stack will be completely empty.
>
> But after freeing all items, finish_xact_command() function could call
> PortalDrop(), so ExecutorEnd() function could be called again.
> pg_audit has ExecutorEnd_hook, so postgres tries to free that item.. SEGV.
>
> In my environment, the following change fixes it.
>
> --- pg_audit.c.org 2015-04-15 14:21:07.541866525 +0900
> +++ pg_audit.c 2015-04-15 11:36:53.758877339 +0900
> @@ -1291,7 +1291,7 @@
> standard_ExecutorEnd(queryDesc);
>
> /* Pop the audit event off the stack */
> - if (!internalStatement)
> + if (!internalStatement && auditEventStack != NULL)
> {
> stack_pop(auditEventStack->stackId);
> }
>
> It might be good idea to add Assert() at before calling stack_pop().
>
> I'm not sure this change is exactly correct, but I hope this
> information helps you.
I appreciate you taking the time to look - this is the same conclusion I
came to. I also hardened another area that I thought might be vulnerable.
I've seen this problem with explicit cursors before (and fixed it in
another place a while ago). The memory context that is current in
ExecutorStart is freed before ExecutorEnd is called only in this case as
far as I can tell. I'm not sure this is very consistent behavior.
I have attached patch v9 which fixes this issue as you suggested, but
I'm not completely satisfied with it. It seems like there could be an
unintentional pop from the stack in a case of deeper nesting. This
might not be possible but it's hard to disprove.
I'll think about it some more, but meanwhile this patch addresses the
present issue.
--
- David Steele
david@pgmasters.net
Attachments:pg_audit-v9.patch (text/plain, 130.8K) downloaddiff --git a/contrib/Makefile b/contrib/Makefileindex d63e441..ed9cf6a 100644--- a/contrib/Makefile+++ b/contrib/Makefile@@ -28,6 +28,7 @@ SUBDIRS = \
oid2name \
pageinspect \
passwordcheck \
+ pg_audit \
pg_buffercache \
pg_freespacemap \
pg_prewarm \
diff --git a/contrib/pg_audit/.gitignore b/contrib/pg_audit/.gitignorenew file mode 100644index 0000000..a5267cf--- /dev/null+++ b/contrib/pg_audit/.gitignore@@ -0,0 +1,5 @@+log/+results/+tmp_check/+regression.diffs+regression.outdiff --git a/contrib/pg_audit/Makefile b/contrib/pg_audit/Makefilenew file mode 100644index 0000000..7b36011--- /dev/null+++ b/contrib/pg_audit/Makefile@@ -0,0 +1,21 @@+# pg_audit/Makefile++MODULE = pg_audit+MODULE_big = pg_audit+OBJS = pg_audit.o++EXTENSION = pg_audit+REGRESS = pg_audit+REGRESS_OPTS = --temp-config=$(top_srcdir)/contrib/pg_audit/pg_audit.conf+DATA = pg_audit--1.0.0.sql++ifdef USE_PGXS+PG_CONFIG = pg_config+PGXS := $(shell $(PG_CONFIG) --pgxs)+include $(PGXS)+else+subdir = contrib/pg_audit+top_builddir = ../..+include $(top_builddir)/src/Makefile.global+include $(top_srcdir)/contrib/contrib-global.mk+endifdiff --git a/contrib/pg_audit/expected/pg_audit-deparse.out b/contrib/pg_audit/expected/pg_audit-deparse.outnew file mode 100644index 0000000..e72699c--- /dev/null+++ b/contrib/pg_audit/expected/pg_audit-deparse.out@@ -0,0 +1,921 @@+-- Load pg_audit module+create extension pg_audit;+--+-- Create a superuser role that we know the name of for testing+CREATE USER super SUPERUSER;+\connect contrib_regression super;+--+-- Create auditor role+CREATE ROLE auditor;+--+-- Create first test user+CREATE USER user1;+ALTER ROLE user1 SET pg_audit.log = 'ddl, ROLE';+ALTER ROLE user1 SET pg_audit.log_notice = on;+--+-- Create, select, drop (select will not be audited)+\connect contrib_regression user1+CREATE TABLE public.test (id INT);+NOTICE: AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.test,CREATE TABLE public.test (id INT);+SELECT * FROM test;+ id +----+(0 rows)++DROP TABLE test;+NOTICE: AUDIT: SESSION,2,1,DDL,DROP TABLE,TABLE,public.test,DROP TABLE test;+--+-- Create second test user+\connect contrib_regression super+CREATE USER user2;+ALTER ROLE user2 SET pg_audit.log = 'Read, writE';+ALTER ROLE user2 SET pg_audit.log_notice = on;+ALTER ROLE user2 SET pg_audit.role = auditor;+\connect contrib_regression user2+CREATE TABLE test2 (id INT);+GRANT SELECT ON TABLE public.test2 TO auditor;+--+-- Role-based tests+CREATE TABLE test3+(+ id INT+);+SELECT count(*)+ FROM+(+ SELECT relname+ FROM pg_class+ LIMIT 1+) SUBQUERY;+ count +-------+ 1+(1 row)++SELECT *+ FROM test3, test2;+NOTICE: AUDIT: SESSION,1,1,READ,SELECT,,,"SELECT *+ FROM test3, test2;"+NOTICE: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.test2,"SELECT *+ FROM test3, test2;"+ id | id +----+----+(0 rows)++GRANT INSERT+ ON TABLE public.test3+ TO auditor;+--+-- Create a view to test logging+CREATE VIEW vw_test3 AS+SELECT *+ FROM test3;+GRANT SELECT+ ON vw_test3+ TO auditor;+--+-- Object logged because of:+-- select on vw_test3+-- select on test2+SELECT *+ FROM vw_test3, test2;+NOTICE: AUDIT: SESSION,2,1,READ,SELECT,,,"SELECT *+ FROM vw_test3, test2;"+NOTICE: AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.test2,"SELECT *+ FROM vw_test3, test2;"+NOTICE: AUDIT: OBJECT,2,1,READ,SELECT,VIEW,public.vw_test3,"SELECT *+ FROM vw_test3, test2;"+ id | id +----+----+(0 rows)++--+-- Object logged because of:+-- insert on test3+-- select on test2+WITH CTE AS+(+ SELECT id+ FROM test2+)+INSERT INTO test3+SELECT id+ FROM cte;+NOTICE: AUDIT: SESSION,3,1,WRITE,INSERT,,,"WITH CTE AS+(+ SELECT id+ FROM test2+)+INSERT INTO test3+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,3,1,WRITE,INSERT,TABLE,public.test3,"WITH CTE AS+(+ SELECT id+ FROM test2+)+INSERT INTO test3+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.test2,"WITH CTE AS+(+ SELECT id+ FROM test2+)+INSERT INTO test3+SELECT id+ FROM cte;"+--+-- Object logged because of:+-- insert on test3+WITH CTE AS+(+ INSERT INTO test3 VALUES (1)+ RETURNING id+)+INSERT INTO test2+SELECT id+ FROM cte;+NOTICE: AUDIT: SESSION,4,1,WRITE,INSERT,,,"WITH CTE AS+(+ INSERT INTO test3 VALUES (1)+ RETURNING id+)+INSERT INTO test2+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,4,1,WRITE,INSERT,TABLE,public.test3,"WITH CTE AS+(+ INSERT INTO test3 VALUES (1)+ RETURNING id+)+INSERT INTO test2+SELECT id+ FROM cte;"+GRANT UPDATE ON TABLE public.test2 TO auditor;+--+-- Object logged because of:+-- insert on test3+-- update on test2+WITH CTE AS+(+ UPDATE test2+ SET id = 1+ RETURNING id+)+INSERT INTO test3+SELECT id+ FROM cte;+NOTICE: AUDIT: SESSION,5,1,WRITE,INSERT,,,"WITH CTE AS+(+ UPDATE test2+ SET id = 1+ RETURNING id+)+INSERT INTO test3+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,5,1,WRITE,INSERT,TABLE,public.test3,"WITH CTE AS+(+ UPDATE test2+ SET id = 1+ RETURNING id+)+INSERT INTO test3+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,5,1,WRITE,UPDATE,TABLE,public.test2,"WITH CTE AS+(+ UPDATE test2+ SET id = 1+ RETURNING id+)+INSERT INTO test3+SELECT id+ FROM cte;"+--+-- Object logged because of:+-- insert on test2+WITH CTE AS+(+ INSERT INTO test2 VALUES (1)+ RETURNING id+)+UPDATE test3+ SET id = cte.id+ FROM cte+ WHERE test3.id <> cte.id;+NOTICE: AUDIT: SESSION,6,1,WRITE,UPDATE,,,"WITH CTE AS+(+ INSERT INTO test2 VALUES (1)+ RETURNING id+)+UPDATE test3+ SET id = cte.id+ FROM cte+ WHERE test3.id <> cte.id;"+NOTICE: AUDIT: OBJECT,6,1,WRITE,INSERT,TABLE,public.test2,"WITH CTE AS+(+ INSERT INTO test2 VALUES (1)+ RETURNING id+)+UPDATE test3+ SET id = cte.id+ FROM cte+ WHERE test3.id <> cte.id;"+--+-- Change permissions of user 2 so that only object logging will be done+\connect contrib_regression super+alter role user2 set pg_audit.log = 'NONE';+\connect contrib_regression user2+--+-- Create test4 and add permissions+CREATE TABLE test4+(+ id int,+ name text+);+GRANT SELECT (name)+ ON TABLE public.test4+ TO auditor;+GRANT UPDATE (id)+ ON TABLE public.test4+ TO auditor;+GRANT insert (name)+ ON TABLE public.test4+ TO auditor;+--+-- Not object logged+SELECT id+ FROM public.test4;+ id +----+(0 rows)++--+-- Object logged because of:+-- select (name) on test4+SELECT name+ FROM public.test4;+NOTICE: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.test4,"SELECT name+ FROM public.test4;"+ name +------+(0 rows)++--+-- Not object logged+INSERT INTO public.test4 (id)+ VALUES (1);+--+-- Object logged because of:+-- insert (name) on test4+INSERT INTO public.test4 (name)+ VALUES ('test');+NOTICE: AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,public.test4,"INSERT INTO public.test4 (name)+ VALUES ('test');"+--+-- Not object logged+UPDATE public.test4+ SET name = 'foo';+--+-- Object logged because of:+-- update (id) on test4+UPDATE public.test4+ SET id = 1;+NOTICE: AUDIT: OBJECT,3,1,WRITE,UPDATE,TABLE,public.test4,"UPDATE public.test4+ SET id = 1;"+--+-- Object logged because of:+-- update (name) on test4+-- update (name) takes precedence over select (name) due to ordering+update public.test4 set name = 'foo' where name = 'bar';+NOTICE: AUDIT: OBJECT,4,1,WRITE,UPDATE,TABLE,public.test4,update public.test4 set name = 'foo' where name = 'bar';+--+-- Drop test tables+DROP TABLE test2;+DROP VIEW vw_test3;+DROP TABLE test3;+DROP TABLE test4;+--+-- Change permissions of user 1 so that session logging will be done+\connect contrib_regression super+alter role user1 set pg_audit.log = 'DDL, READ';+\connect contrib_regression user1+--+-- Create table is session logged+CREATE TABLE public.account+(+ id INT,+ name TEXT,+ password TEXT,+ description TEXT+);+NOTICE: AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,"CREATE TABLE public.account+(+ id INT,+ name TEXT,+ password TEXT,+ description TEXT+);"+--+-- Select is session logged+SELECT *+ FROM account;+NOTICE: AUDIT: SESSION,2,1,READ,SELECT,,,"SELECT *+ FROM account;"+ id | name | password | description +----+------+----------+-------------+(0 rows)++--+-- Insert is not logged+INSERT INTO account (id, name, password, description)+ VALUES (1, 'user1', 'HASH1', 'blah, blah');+--+-- Change permissions of user 1 so that only object logging will be done+\connect contrib_regression super+alter role user1 set pg_audit.log = 'none';+alter role user1 set pg_audit.role = 'auditor';+\connect contrib_regression user1+--+-- Auditor grants not logged+GRANT SELECT (password),+ UPDATE (name, password)+ ON TABLE public.account+ TO auditor;+--+-- Not object logged+SELECT id,+ name+ FROM account;+ id | name +----+-------+ 1 | user1+(1 row)++--+-- Object logged because of:+-- select (password) on account+SELECT password+ FROM account;+NOTICE: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,"SELECT password+ FROM account;"+ password +----------+ HASH1+(1 row)++--+-- Not object logged+UPDATE account+ SET description = 'yada, yada';+--+-- Object logged because of:+-- update (password) on account+UPDATE account+ SET password = 'HASH2';+NOTICE: AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account+ SET password = 'HASH2';"+--+-- Change permissions of user 1 so that session relation logging will be done+\connect contrib_regression super+alter role user1 set pg_audit.log_relation = on;+alter role user1 set pg_audit.log = 'read, WRITE';+\connect contrib_regression user1+--+-- Not logged+create table ACCOUNT_ROLE_MAP+(+ account_id INT,+ role_id INT+);+--+-- Auditor grants not logged+GRANT SELECT+ ON TABLE public.account_role_map+ TO auditor;+--+-- Object logged because of:+-- select (password) on account+-- select on account_role_map+-- Session logged on all tables because log = read and log_relation = on+SELECT account.password,+ account_role_map.role_id+ FROM account+ INNER JOIN account_role_map+ on account.id = account_role_map.account_id;+NOTICE: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,"SELECT account.password,+ account_role_map.role_id+ FROM account+ INNER JOIN account_role_map+ on account.id = account_role_map.account_id;"+NOTICE: AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.account,"SELECT account.password,+ account_role_map.role_id+ FROM account+ INNER JOIN account_role_map+ on account.id = account_role_map.account_id;"+NOTICE: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account_role_map,"SELECT account.password,+ account_role_map.role_id+ FROM account+ INNER JOIN account_role_map+ on account.id = account_role_map.account_id;"+NOTICE: AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.account_role_map,"SELECT account.password,+ account_role_map.role_id+ FROM account+ INNER JOIN account_role_map+ on account.id = account_role_map.account_id;"+ password | role_id +----------+---------+(0 rows)++--+-- Object logged because of:+-- select (password) on account+-- Session logged on all tables because log = read and log_relation = on+SELECT password+ FROM account;+NOTICE: AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.account,"SELECT password+ FROM account;"+NOTICE: AUDIT: SESSION,2,1,READ,SELECT,TABLE,public.account,"SELECT password+ FROM account;"+ password +----------+ HASH2+(1 row)++--+-- Not object logged+-- Session logged on all tables because log = read and log_relation = on+UPDATE account+ SET description = 'yada, yada';+NOTICE: AUDIT: SESSION,3,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account+ SET description = 'yada, yada';"+--+-- Object logged because of:+-- select (password) on account (in the where clause)+-- Session logged on all tables because log = read and log_relation = on+UPDATE account+ SET description = 'yada, yada'+ where password = 'HASH2';+NOTICE: AUDIT: OBJECT,4,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account+ SET description = 'yada, yada'+ where password = 'HASH2';"+NOTICE: AUDIT: SESSION,4,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account+ SET description = 'yada, yada'+ where password = 'HASH2';"+--+-- Object logged because of:+-- update (password) on account+-- Session logged on all tables because log = read and log_relation = on+UPDATE account+ SET password = 'HASH2';+NOTICE: AUDIT: OBJECT,5,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account+ SET password = 'HASH2';"+NOTICE: AUDIT: SESSION,5,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account+ SET password = 'HASH2';"+--+-- Change back to superuser to do exhaustive tests+\connect contrib_regression super+SET pg_audit.log = 'ALL';+SET pg_audit.log_notice = ON;+NOTICE: AUDIT: SESSION,2,1,MISC,SET,,,SET pg_audit.log_notice = ON;+SET pg_audit.log_relation = ON;+NOTICE: AUDIT: SESSION,3,1,MISC,SET,,,SET pg_audit.log_relation = ON;+--+-- Simple DO block+DO $$+BEGIN+ raise notice 'test';+END $$;+NOTICE: AUDIT: SESSION,4,1,FUNCTION,DO,,,"DO $$+BEGIN+ raise notice 'test';+END $$;"+NOTICE: test+--+-- Create test schema+CREATE SCHEMA test;+NOTICE: AUDIT: SESSION,5,1,DDL,CREATE SCHEMA,SCHEMA,test,CREATE SCHEMA test;+--+-- Copy pg_class to stdout+COPY account TO stdout;+NOTICE: AUDIT: SESSION,6,1,READ,SELECT,TABLE,public.account,COPY account TO stdout;+1 user1 HASH2 yada, yada+--+-- Create a table from a query+CREATE TABLE test.account_copy AS+SELECT *+ FROM account;+NOTICE: AUDIT: SESSION,7,1,READ,SELECT,TABLE,public.account,"CREATE TABLE test.account_copy AS+SELECT *+ FROM account;"+NOTICE: AUDIT: SESSION,7,1,WRITE,INSERT,TABLE,test.account_copy,"CREATE TABLE test.account_copy AS+SELECT *+ FROM account;"+NOTICE: AUDIT: SESSION,7,2,DDL,CREATE TABLE AS,TABLE,test.account_copy,"CREATE TABLE test.account_copy AS+SELECT *+ FROM account;"+--+-- Copy from stdin to account copy+COPY test.account_copy from stdin;+NOTICE: AUDIT: SESSION,8,1,WRITE,INSERT,TABLE,test.account_copy,COPY test.account_copy from stdin;+--+-- Test prepared statement+PREPARE pgclassstmt (oid) AS+SELECT *+ FROM account+ WHERE id = $1;+NOTICE: AUDIT: SESSION,9,1,READ,PREPARE,,,"PREPARE pgclassstmt (oid) AS+SELECT *+ FROM account+ WHERE id = $1;"+EXECUTE pgclassstmt (1);+NOTICE: AUDIT: SESSION,10,1,READ,SELECT,TABLE,public.account,"PREPARE pgclassstmt (oid) AS+SELECT *+ FROM account+ WHERE id = $1;",1+NOTICE: AUDIT: SESSION,10,2,READ,EXECUTE,,,EXECUTE pgclassstmt (1);+ id | name | password | description +----+-------+----------+-------------+ 1 | user1 | HASH2 | yada, yada+(1 row)++DEALLOCATE pgclassstmt;+NOTICE: AUDIT: SESSION,11,1,MISC,DEALLOCATE,,,DEALLOCATE pgclassstmt;+--+-- Test cursor - no tables will be logged since pg_class is a system table+BEGIN;+NOTICE: AUDIT: SESSION,12,1,MISC,BEGIN,,,BEGIN;+DECLARE ctest SCROLL CURSOR FOR+SELECT count(*)+ FROM+(+ SELECT relname+ FROM pg_class+ LIMIT 1+ ) subquery;+FETCH NEXT FROM ctest;+NOTICE: AUDIT: SESSION,13,1,MISC,FETCH,,,FETCH NEXT FROM ctest;+ count +-------+ 1+(1 row)++CLOSE ctest;+COMMIT;+NOTICE: AUDIT: SESSION,14,1,MISC,COMMIT,,,COMMIT;+--+-- Test prepared insert+CREATE TABLE test.test_insert+(+ id INT+);+NOTICE: AUDIT: SESSION,15,1,DDL,CREATE TABLE,TABLE,test.test_insert,"CREATE TABLE test.test_insert+(+ id INT+);"+PREPARE pgclassstmt (oid) AS+INSERT INTO test.test_insert (id)+ VALUES ($1);+NOTICE: AUDIT: SESSION,16,1,WRITE,PREPARE,,,"PREPARE pgclassstmt (oid) AS+INSERT INTO test.test_insert (id)+ VALUES ($1);"+EXECUTE pgclassstmt (1);+NOTICE: AUDIT: SESSION,17,1,WRITE,INSERT,TABLE,test.test_insert,"PREPARE pgclassstmt (oid) AS+INSERT INTO test.test_insert (id)+ VALUES ($1);",1+NOTICE: AUDIT: SESSION,17,2,WRITE,EXECUTE,,,EXECUTE pgclassstmt (1);+--+-- Check that primary key creation is logged+CREATE TABLE public.test+(+ id INT,+ name TEXT,+ description TEXT,+ CONSTRAINT test_pkey PRIMARY KEY (id)+);+NOTICE: AUDIT: SESSION,18,1,DDL,CREATE INDEX,INDEX,public.test_pkey,"CREATE TABLE public.test+(+ id INT,+ name TEXT,+ description TEXT,+ CONSTRAINT test_pkey PRIMARY KEY (id)+);"+NOTICE: AUDIT: SESSION,18,2,DDL,CREATE TABLE,TABLE,public.test,"CREATE TABLE public.test+(+ id INT,+ name TEXT,+ description TEXT,+ CONSTRAINT test_pkey PRIMARY KEY (id)+);"+NOTICE: AUDIT: SESSION,18,2,DDL,CREATE TABLE,INDEX,public.test_pkey,"CREATE TABLE public.test+(+ id INT,+ name TEXT,+ description TEXT,+ CONSTRAINT test_pkey PRIMARY KEY (id)+);"+--+-- Check that analyze is logged+ANALYZE test;+NOTICE: AUDIT: SESSION,19,1,MISC,ANALYZE,,,ANALYZE test;+--+-- Grants to public should not cause object logging (session logging will+-- still happen)+GRANT SELECT+ ON TABLE public.test+ TO PUBLIC;+NOTICE: AUDIT: SESSION,20,1,ROLE,GRANT,TABLE,,"GRANT SELECT+ ON TABLE public.test+ TO PUBLIC;"+SELECT *+ FROM test;+NOTICE: AUDIT: SESSION,21,1,READ,SELECT,TABLE,public.test,"SELECT *+ FROM test;"+ id | name | description +----+------+-------------+(0 rows)++-- Check that statements without columns log+SELECT+ FROM test;+NOTICE: AUDIT: SESSION,22,1,READ,SELECT,TABLE,public.test,"SELECT+ FROM test;"+--+(0 rows)++SELECT 1,+ current_user;+NOTICE: AUDIT: SESSION,23,1,READ,SELECT,,,"SELECT 1,+ current_user;"+ ?column? | current_user +----------+--------------+ 1 | super+(1 row)++DO $$+DECLARE+ test INT;+BEGIN+ SELECT 1+ INTO test;+END $$;+NOTICE: AUDIT: SESSION,24,1,FUNCTION,DO,,,"DO $$+DECLARE+ test INT;+BEGIN+ SELECT 1+ INTO test;+END $$;"+NOTICE: AUDIT: SESSION,24,2,READ,SELECT,,,SELECT 1+CONTEXT: SQL statement "SELECT 1"+PL/pgSQL function inline_code_block line 5 at SQL statement+explain select 1;+NOTICE: AUDIT: SESSION,25,1,READ,SELECT,,,explain select 1;+NOTICE: AUDIT: SESSION,25,2,MISC,EXPLAIN,,,explain select 1;+ QUERY PLAN +------------------------------------------+ Result (cost=0.00..0.01 rows=1 width=0)+(1 row)++--+-- Test that looks inside of do blocks log+INSERT INTO TEST (id)+ VALUES (1);+NOTICE: AUDIT: SESSION,26,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)+ VALUES (1);"+INSERT INTO TEST (id)+ VALUES (2);+NOTICE: AUDIT: SESSION,27,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)+ VALUES (2);"+INSERT INTO TEST (id)+ VALUES (3);+NOTICE: AUDIT: SESSION,28,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)+ VALUES (3);"+DO $$+DECLARE+ result RECORD;+BEGIN+ FOR result IN+ SELECT id+ FROM test+ LOOP+ INSERT INTO test (id)+ VALUES (result.id + 100);+ END LOOP;+END $$;+NOTICE: AUDIT: SESSION,29,1,FUNCTION,DO,,,"DO $$+DECLARE+ result RECORD;+BEGIN+ FOR result IN+ SELECT id+ FROM test+ LOOP+ INSERT INTO test (id)+ VALUES (result.id + 100);+ END LOOP;+END $$;"+NOTICE: AUDIT: SESSION,29,2,READ,SELECT,TABLE,public.test,"SELECT id+ FROM test"+CONTEXT: PL/pgSQL function inline_code_block line 5 at FOR over SELECT rows+NOTICE: AUDIT: SESSION,29,3,WRITE,INSERT,TABLE,public.test,"INSERT INTO test (id)+ VALUES (result.id + 100)",,,+CONTEXT: SQL statement "INSERT INTO test (id)+ VALUES (result.id + 100)"+PL/pgSQL function inline_code_block line 9 at SQL statement+NOTICE: AUDIT: SESSION,29,4,WRITE,INSERT,TABLE,public.test,"INSERT INTO test (id)+ VALUES (result.id + 100)",,,+CONTEXT: SQL statement "INSERT INTO test (id)+ VALUES (result.id + 100)"+PL/pgSQL function inline_code_block line 9 at SQL statement+NOTICE: AUDIT: SESSION,29,5,WRITE,INSERT,TABLE,public.test,"INSERT INTO test (id)+ VALUES (result.id + 100)",,,+CONTEXT: SQL statement "INSERT INTO test (id)+ VALUES (result.id + 100)"+PL/pgSQL function inline_code_block line 9 at SQL statement+--+-- Test obfuscated dynamic sql for clean logging+DO $$+DECLARE+ table_name TEXT = 'do_table';+BEGIN+ EXECUTE 'CREATE TABLE ' || table_name || ' ("weird name" INT)';+ EXECUTE 'DROP table ' || table_name;+END $$;+NOTICE: AUDIT: SESSION,30,1,FUNCTION,DO,,,"DO $$+DECLARE+ table_name TEXT = 'do_table';+BEGIN+ EXECUTE 'CREATE TABLE ' || table_name || ' (""weird name"" INT)';+ EXECUTE 'DROP table ' || table_name;+END $$;"+NOTICE: AUDIT: SESSION,30,2,DDL,CREATE TABLE,TABLE,public.do_table,"CREATE TABLE do_table (""weird name"" INT)"+CONTEXT: SQL statement "CREATE TABLE do_table ("weird name" INT)"+PL/pgSQL function inline_code_block line 5 at EXECUTE statement+NOTICE: AUDIT: SESSION,30,3,DDL,DROP TABLE,TABLE,public.do_table,DROP table do_table+CONTEXT: SQL statement "DROP table do_table"+PL/pgSQL function inline_code_block line 6 at EXECUTE statement+--+-- Generate an error and make sure the stack gets cleared+DO $$+BEGIN+ CREATE TABLE bogus.test_block+ (+ id INT+ );+END $$;+NOTICE: AUDIT: SESSION,31,1,FUNCTION,DO,,,"DO $$+BEGIN+ CREATE TABLE bogus.test_block+ (+ id INT+ );+END $$;"+ERROR: schema "bogus" does not exist+LINE 1: CREATE TABLE bogus.test_block+ ^+QUERY: CREATE TABLE bogus.test_block+ (+ id INT+ )+CONTEXT: PL/pgSQL function inline_code_block line 3 at SQL statement+--+-- Test alter table statements+ALTER TABLE public.test+ DROP COLUMN description ;+NOTICE: AUDIT: SESSION,32,1,DDL,ALTER TABLE,TABLE COLUMN,public.test.description,"ALTER TABLE public.test+ DROP COLUMN description ;"+NOTICE: AUDIT: SESSION,32,1,DDL,ALTER TABLE,TABLE,public.test,"ALTER TABLE public.test+ DROP COLUMN description ;"+ALTER TABLE public.test+ RENAME TO test2;+NOTICE: AUDIT: SESSION,33,1,DDL,ALTER TABLE,TABLE,public.test2,"ALTER TABLE public.test+ RENAME TO test2;"+ALTER TABLE public.test2+ SET SCHEMA test;+NOTICE: AUDIT: SESSION,34,1,DDL,ALTER TABLE,TABLE,test.test2,"ALTER TABLE public.test2+ SET SCHEMA test;"+ALTER TABLE test.test2+ ADD COLUMN description TEXT;+NOTICE: AUDIT: SESSION,35,1,DDL,ALTER TABLE,TABLE,test.test2,"ALTER TABLE test.test2+ ADD COLUMN description TEXT;"+ALTER TABLE test.test2+ DROP COLUMN description;+NOTICE: AUDIT: SESSION,36,1,DDL,ALTER TABLE,TABLE COLUMN,test.test2.description,"ALTER TABLE test.test2+ DROP COLUMN description;"+NOTICE: AUDIT: SESSION,36,1,DDL,ALTER TABLE,TABLE,test.test2,"ALTER TABLE test.test2+ DROP COLUMN description;"+DROP TABLE test.test2;+NOTICE: AUDIT: SESSION,37,1,DDL,DROP TABLE,TABLE,test.test2,DROP TABLE test.test2;+NOTICE: AUDIT: SESSION,37,1,DDL,DROP TABLE,TABLE CONSTRAINT,test_pkey on test.test2,DROP TABLE test.test2;+NOTICE: AUDIT: SESSION,37,1,DDL,DROP TABLE,INDEX,test.test_pkey,DROP TABLE test.test2;+--+-- Test multiple statements with one semi-colon+CREATE SCHEMA foo+ CREATE TABLE foo.bar (id int)+ CREATE TABLE foo.baz (id int);+NOTICE: AUDIT: SESSION,38,1,DDL,CREATE TABLE,TABLE,foo.bar,"CREATE SCHEMA foo+ CREATE TABLE foo.bar (id int)+ CREATE TABLE foo.baz (id int);"+NOTICE: AUDIT: SESSION,38,2,DDL,CREATE TABLE,TABLE,foo.baz,"CREATE SCHEMA foo+ CREATE TABLE foo.bar (id int)+ CREATE TABLE foo.baz (id int);"+NOTICE: AUDIT: SESSION,38,3,DDL,CREATE SCHEMA,SCHEMA,foo,"CREATE SCHEMA foo+ CREATE TABLE foo.bar (id int)+ CREATE TABLE foo.baz (id int);"+NOTICE: AUDIT: SESSION,38,3,DDL,CREATE SCHEMA,TABLE,foo.bar,"CREATE SCHEMA foo+ CREATE TABLE foo.bar (id int)+ CREATE TABLE foo.baz (id int);"+NOTICE: AUDIT: SESSION,38,3,DDL,CREATE SCHEMA,TABLE,foo.baz,"CREATE SCHEMA foo+ CREATE TABLE foo.bar (id int)+ CREATE TABLE foo.baz (id int);"+--+-- Test aggregate+CREATE FUNCTION public.int_add+(+ a INT,+ b INT+)+ RETURNS INT LANGUAGE plpgsql AS $$+BEGIN+ return a + b;+END $$;+NOTICE: AUDIT: SESSION,39,1,DDL,CREATE FUNCTION,FUNCTION,"public.int_add(integer,integer)","CREATE FUNCTION public.int_add+(+ a INT,+ b INT+)+ RETURNS INT LANGUAGE plpgsql AS $$+BEGIN+ return a + b;+END $$;"+SELECT int_add(1, 1);+NOTICE: AUDIT: SESSION,40,1,READ,SELECT,,,"SELECT int_add(1, 1);"+NOTICE: AUDIT: SESSION,40,2,FUNCTION,EXECUTE,FUNCTION,public.int_add,"SELECT int_add(1, 1);"+ int_add +---------+ 2+(1 row)++CREATE AGGREGATE public.sum_test(INT) (SFUNC=public.int_add, STYPE=INT, INITCOND='0');+NOTICE: AUDIT: SESSION,41,1,DDL,CREATE AGGREGATE,AGGREGATE,public.sum_test(integer),"CREATE AGGREGATE public.sum_test(INT) (SFUNC=public.int_add, STYPE=INT, INITCOND='0');"+ALTER AGGREGATE public.sum_test(integer) RENAME TO sum_test2;+NOTICE: AUDIT: SESSION,42,1,DDL,ALTER AGGREGATE,AGGREGATE,public.sum_test2(integer),ALTER AGGREGATE public.sum_test(integer) RENAME TO sum_test2;+--+-- Test conversion+CREATE CONVERSION public.conversion_test FOR 'SQL_ASCII' TO 'MULE_INTERNAL' FROM pg_catalog.ascii_to_mic;+NOTICE: AUDIT: SESSION,43,1,DDL,CREATE CONVERSION,CONVERSION,public.conversion_test,CREATE CONVERSION public.conversion_test FOR 'SQL_ASCII' TO 'MULE_INTERNAL' FROM pg_catalog.ascii_to_mic;+ALTER CONVERSION public.conversion_test RENAME TO conversion_test2;+NOTICE: AUDIT: SESSION,44,1,DDL,ALTER CONVERSION,CONVERSION,public.conversion_test2,ALTER CONVERSION public.conversion_test RENAME TO conversion_test2;+--+-- Test create/alter/drop database+CREATE DATABASE contrib_regression_pgaudit;+NOTICE: AUDIT: SESSION,45,1,DDL,CREATE DATABASE,,,CREATE DATABASE contrib_regression_pgaudit;+ALTER DATABASE contrib_regression_pgaudit RENAME TO contrib_regression_pgaudit2;+NOTICE: AUDIT: SESSION,46,1,DDL,ALTER DATABASE,,,ALTER DATABASE contrib_regression_pgaudit RENAME TO contrib_regression_pgaudit2;+DROP DATABASE contrib_regression_pgaudit2;+NOTICE: AUDIT: SESSION,47,1,DDL,DROP DATABASE,,,DROP DATABASE contrib_regression_pgaudit2;+--+-- Test that frees a memory context earlier than expected+CREATE TABLE hoge+(+ id int+);+NOTICE: AUDIT: SESSION,48,1,DDL,CREATE TABLE,TABLE,public.hoge,"CREATE TABLE hoge+(+ id int+);"+CREATE FUNCTION test()+ RETURNS INT AS $$+DECLARE+ cur1 cursor for select * from hoge;+ tmp int;+BEGIN+ OPEN cur1;+ FETCH cur1 into tmp;+ RETURN tmp;+END $$+LANGUAGE plpgsql ;+NOTICE: AUDIT: SESSION,49,1,DDL,CREATE FUNCTION,FUNCTION,public.test(),"CREATE FUNCTION test()+ RETURNS INT AS $$+DECLARE+ cur1 cursor for select * from hoge;+ tmp int;+BEGIN+ OPEN cur1;+ FETCH cur1 into tmp;+ RETURN tmp;+END $$+LANGUAGE plpgsql ;"+SELECT test();+NOTICE: AUDIT: SESSION,50,1,READ,SELECT,,,SELECT test();+NOTICE: AUDIT: SESSION,50,2,FUNCTION,EXECUTE,FUNCTION,public.test,SELECT test();+NOTICE: AUDIT: SESSION,50,3,READ,SELECT,TABLE,public.hoge,select * from hoge+CONTEXT: PL/pgSQL function test() line 6 at OPEN+ test +------+ +(1 row)+diff --git a/contrib/pg_audit/expected/pg_audit.out b/contrib/pg_audit/expected/pg_audit.outnew file mode 100644index 0000000..467d848--- /dev/null+++ b/contrib/pg_audit/expected/pg_audit.out@@ -0,0 +1,904 @@+-- Load pg_audit module+create extension pg_audit;+--+-- Create a superuser role that we know the name of for testing+CREATE USER super SUPERUSER;+\connect contrib_regression super;+--+-- Create auditor role+CREATE ROLE auditor;+--+-- Create first test user+CREATE USER user1;+ALTER ROLE user1 SET pg_audit.log = 'ddl, ROLE';+ALTER ROLE user1 SET pg_audit.log_notice = on;+--+-- Create, select, drop (select will not be audited)+\connect contrib_regression user1+CREATE TABLE public.test (id INT);+NOTICE: AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.test,CREATE TABLE public.test (id INT);+SELECT * FROM test;+ id +----+(0 rows)++DROP TABLE test;+NOTICE: AUDIT: SESSION,2,1,DDL,DROP TABLE,TABLE,public.test,DROP TABLE test;+--+-- Create second test user+\connect contrib_regression super+CREATE USER user2;+ALTER ROLE user2 SET pg_audit.log = 'Read, writE';+ALTER ROLE user2 SET pg_audit.log_notice = on;+ALTER ROLE user2 SET pg_audit.role = auditor;+\connect contrib_regression user2+CREATE TABLE test2 (id INT);+GRANT SELECT ON TABLE public.test2 TO auditor;+--+-- Role-based tests+CREATE TABLE test3+(+ id INT+);+SELECT count(*)+ FROM+(+ SELECT relname+ FROM pg_class+ LIMIT 1+) SUBQUERY;+ count +-------+ 1+(1 row)++SELECT *+ FROM test3, test2;+NOTICE: AUDIT: SESSION,1,1,READ,SELECT,,,"SELECT *+ FROM test3, test2;"+NOTICE: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.test2,"SELECT *+ FROM test3, test2;"+ id | id +----+----+(0 rows)++GRANT INSERT+ ON TABLE public.test3+ TO auditor;+--+-- Create a view to test logging+CREATE VIEW vw_test3 AS+SELECT *+ FROM test3;+GRANT SELECT+ ON vw_test3+ TO auditor;+--+-- Object logged because of:+-- select on vw_test3+-- select on test2+SELECT *+ FROM vw_test3, test2;+NOTICE: AUDIT: SESSION,2,1,READ,SELECT,,,"SELECT *+ FROM vw_test3, test2;"+NOTICE: AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.test2,"SELECT *+ FROM vw_test3, test2;"+NOTICE: AUDIT: OBJECT,2,1,READ,SELECT,VIEW,public.vw_test3,"SELECT *+ FROM vw_test3, test2;"+ id | id +----+----+(0 rows)++--+-- Object logged because of:+-- insert on test3+-- select on test2+WITH CTE AS+(+ SELECT id+ FROM test2+)+INSERT INTO test3+SELECT id+ FROM cte;+NOTICE: AUDIT: SESSION,3,1,WRITE,INSERT,,,"WITH CTE AS+(+ SELECT id+ FROM test2+)+INSERT INTO test3+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,3,1,WRITE,INSERT,TABLE,public.test3,"WITH CTE AS+(+ SELECT id+ FROM test2+)+INSERT INTO test3+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.test2,"WITH CTE AS+(+ SELECT id+ FROM test2+)+INSERT INTO test3+SELECT id+ FROM cte;"+--+-- Object logged because of:+-- insert on test3+WITH CTE AS+(+ INSERT INTO test3 VALUES (1)+ RETURNING id+)+INSERT INTO test2+SELECT id+ FROM cte;+NOTICE: AUDIT: SESSION,4,1,WRITE,INSERT,,,"WITH CTE AS+(+ INSERT INTO test3 VALUES (1)+ RETURNING id+)+INSERT INTO test2+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,4,1,WRITE,INSERT,TABLE,public.test3,"WITH CTE AS+(+ INSERT INTO test3 VALUES (1)+ RETURNING id+)+INSERT INTO test2+SELECT id+ FROM cte;"+GRANT UPDATE ON TABLE public.test2 TO auditor;+--+-- Object logged because of:+-- insert on test3+-- update on test2+WITH CTE AS+(+ UPDATE test2+ SET id = 1+ RETURNING id+)+INSERT INTO test3+SELECT id+ FROM cte;+NOTICE: AUDIT: SESSION,5,1,WRITE,INSERT,,,"WITH CTE AS+(+ UPDATE test2+ SET id = 1+ RETURNING id+)+INSERT INTO test3+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,5,1,WRITE,INSERT,TABLE,public.test3,"WITH CTE AS+(+ UPDATE test2+ SET id = 1+ RETURNING id+)+INSERT INTO test3+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,5,1,WRITE,UPDATE,TABLE,public.test2,"WITH CTE AS+(+ UPDATE test2+ SET id = 1+ RETURNING id+)+INSERT INTO test3+SELECT id+ FROM cte;"+--+-- Object logged because of:+-- insert on test2+WITH CTE AS+(+ INSERT INTO test2 VALUES (1)+ RETURNING id+)+UPDATE test3+ SET id = cte.id+ FROM cte+ WHERE test3.id <> cte.id;+NOTICE: AUDIT: SESSION,6,1,WRITE,UPDATE,,,"WITH CTE AS+(+ INSERT INTO test2 VALUES (1)+ RETURNING id+)+UPDATE test3+ SET id = cte.id+ FROM cte+ WHERE test3.id <> cte.id;"+NOTICE: AUDIT: OBJECT,6,1,WRITE,INSERT,TABLE,public.test2,"WITH CTE AS+(+ INSERT INTO test2 VALUES (1)+ RETURNING id+)+UPDATE test3+ SET id = cte.id+ FROM cte+ WHERE test3.id <> cte.id;"+--+-- Change permissions of user 2 so that only object logging will be done+\connect contrib_regression super+alter role user2 set pg_audit.log = 'NONE';+\connect contrib_regression user2+--+-- Create test4 and add permissions+CREATE TABLE test4+(+ id int,+ name text+);+GRANT SELECT (name)+ ON TABLE public.test4+ TO auditor;+GRANT UPDATE (id)+ ON TABLE public.test4+ TO auditor;+GRANT insert (name)+ ON TABLE public.test4+ TO auditor;+--+-- Not object logged+SELECT id+ FROM public.test4;+ id +----+(0 rows)++--+-- Object logged because of:+-- select (name) on test4+SELECT name+ FROM public.test4;+NOTICE: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.test4,"SELECT name+ FROM public.test4;"+ name +------+(0 rows)++--+-- Not object logged+INSERT INTO public.test4 (id)+ VALUES (1);+--+-- Object logged because of:+-- insert (name) on test4+INSERT INTO public.test4 (name)+ VALUES ('test');+NOTICE: AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,public.test4,"INSERT INTO public.test4 (name)+ VALUES ('test');"+--+-- Not object logged+UPDATE public.test4+ SET name = 'foo';+--+-- Object logged because of:+-- update (id) on test4+UPDATE public.test4+ SET id = 1;+NOTICE: AUDIT: OBJECT,3,1,WRITE,UPDATE,TABLE,public.test4,"UPDATE public.test4+ SET id = 1;"+--+-- Object logged because of:+-- update (name) on test4+-- update (name) takes precedence over select (name) due to ordering+update public.test4 set name = 'foo' where name = 'bar';+NOTICE: AUDIT: OBJECT,4,1,WRITE,UPDATE,TABLE,public.test4,update public.test4 set name = 'foo' where name = 'bar';+--+-- Drop test tables+DROP TABLE test2;+DROP VIEW vw_test3;+DROP TABLE test3;+DROP TABLE test4;+--+-- Change permissions of user 1 so that session logging will be done+\connect contrib_regression super+alter role user1 set pg_audit.log = 'DDL, READ';+\connect contrib_regression user1+--+-- Create table is session logged+CREATE TABLE public.account+(+ id INT,+ name TEXT,+ password TEXT,+ description TEXT+);+NOTICE: AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,"CREATE TABLE public.account+(+ id INT,+ name TEXT,+ password TEXT,+ description TEXT+);"+--+-- Select is session logged+SELECT *+ FROM account;+NOTICE: AUDIT: SESSION,2,1,READ,SELECT,,,"SELECT *+ FROM account;"+ id | name | password | description +----+------+----------+-------------+(0 rows)++--+-- Insert is not logged+INSERT INTO account (id, name, password, description)+ VALUES (1, 'user1', 'HASH1', 'blah, blah');+--+-- Change permissions of user 1 so that only object logging will be done+\connect contrib_regression super+alter role user1 set pg_audit.log = 'none';+alter role user1 set pg_audit.role = 'auditor';+\connect contrib_regression user1+--+-- Auditor grants not logged+GRANT SELECT (password),+ UPDATE (name, password)+ ON TABLE public.account+ TO auditor;+--+-- Not object logged+SELECT id,+ name+ FROM account;+ id | name +----+-------+ 1 | user1+(1 row)++--+-- Object logged because of:+-- select (password) on account+SELECT password+ FROM account;+NOTICE: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,"SELECT password+ FROM account;"+ password +----------+ HASH1+(1 row)++--+-- Not object logged+UPDATE account+ SET description = 'yada, yada';+--+-- Object logged because of:+-- update (password) on account+UPDATE account+ SET password = 'HASH2';+NOTICE: AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account+ SET password = 'HASH2';"+--+-- Change permissions of user 1 so that session relation logging will be done+\connect contrib_regression super+alter role user1 set pg_audit.log_relation = on;+alter role user1 set pg_audit.log = 'read, WRITE';+\connect contrib_regression user1+--+-- Not logged+create table ACCOUNT_ROLE_MAP+(+ account_id INT,+ role_id INT+);+--+-- Auditor grants not logged+GRANT SELECT+ ON TABLE public.account_role_map+ TO auditor;+--+-- Object logged because of:+-- select (password) on account+-- select on account_role_map+-- Session logged on all tables because log = read and log_relation = on+SELECT account.password,+ account_role_map.role_id+ FROM account+ INNER JOIN account_role_map+ on account.id = account_role_map.account_id;+NOTICE: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,"SELECT account.password,+ account_role_map.role_id+ FROM account+ INNER JOIN account_role_map+ on account.id = account_role_map.account_id;"+NOTICE: AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.account,"SELECT account.password,+ account_role_map.role_id+ FROM account+ INNER JOIN account_role_map+ on account.id = account_role_map.account_id;"+NOTICE: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account_role_map,"SELECT account.password,+ account_role_map.role_id+ FROM account+ INNER JOIN account_role_map+ on account.id = account_role_map.account_id;"+NOTICE: AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.account_role_map,"SELECT account.password,+ account_role_map.role_id+ FROM account+ INNER JOIN account_role_map+ on account.id = account_role_map.account_id;"+ password | role_id +----------+---------+(0 rows)++--+-- Object logged because of:+-- select (password) on account+-- Session logged on all tables because log = read and log_relation = on+SELECT password+ FROM account;+NOTICE: AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.account,"SELECT password+ FROM account;"+NOTICE: AUDIT: SESSION,2,1,READ,SELECT,TABLE,public.account,"SELECT password+ FROM account;"+ password +----------+ HASH2+(1 row)++--+-- Not object logged+-- Session logged on all tables because log = read and log_relation = on+UPDATE account+ SET description = 'yada, yada';+NOTICE: AUDIT: SESSION,3,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account+ SET description = 'yada, yada';"+--+-- Object logged because of:+-- select (password) on account (in the where clause)+-- Session logged on all tables because log = read and log_relation = on+UPDATE account+ SET description = 'yada, yada'+ where password = 'HASH2';+NOTICE: AUDIT: OBJECT,4,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account+ SET description = 'yada, yada'+ where password = 'HASH2';"+NOTICE: AUDIT: SESSION,4,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account+ SET description = 'yada, yada'+ where password = 'HASH2';"+--+-- Object logged because of:+-- update (password) on account+-- Session logged on all tables because log = read and log_relation = on+UPDATE account+ SET password = 'HASH2';+NOTICE: AUDIT: OBJECT,5,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account+ SET password = 'HASH2';"+NOTICE: AUDIT: SESSION,5,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account+ SET password = 'HASH2';"+--+-- Change back to superuser to do exhaustive tests+\connect contrib_regression super+SET pg_audit.log = 'ALL';+SET pg_audit.log_notice = ON;+NOTICE: AUDIT: SESSION,2,1,MISC,SET,,,SET pg_audit.log_notice = ON;+SET pg_audit.log_relation = ON;+NOTICE: AUDIT: SESSION,3,1,MISC,SET,,,SET pg_audit.log_relation = ON;+--+-- Simple DO block+DO $$+BEGIN+ raise notice 'test';+END $$;+NOTICE: AUDIT: SESSION,4,1,FUNCTION,DO,,,"DO $$+BEGIN+ raise notice 'test';+END $$;"+NOTICE: test+--+-- Create test schema+CREATE SCHEMA test;+NOTICE: AUDIT: SESSION,5,1,DDL,CREATE SCHEMA,,,CREATE SCHEMA test;+--+-- Copy pg_class to stdout+COPY account TO stdout;+NOTICE: AUDIT: SESSION,6,1,READ,SELECT,TABLE,public.account,COPY account TO stdout;+1 user1 HASH2 yada, yada+--+-- Create a table from a query+CREATE TABLE test.account_copy AS+SELECT *+ FROM account;+NOTICE: AUDIT: SESSION,7,1,READ,SELECT,TABLE,public.account,"CREATE TABLE test.account_copy AS+SELECT *+ FROM account;"+NOTICE: AUDIT: SESSION,7,1,WRITE,INSERT,TABLE,test.account_copy,"CREATE TABLE test.account_copy AS+SELECT *+ FROM account;"+NOTICE: AUDIT: SESSION,7,2,DDL,CREATE TABLE AS,,,"CREATE TABLE test.account_copy AS+SELECT *+ FROM account;"+--+-- Copy from stdin to account copy+COPY test.account_copy from stdin;+NOTICE: AUDIT: SESSION,8,1,WRITE,INSERT,TABLE,test.account_copy,COPY test.account_copy from stdin;+--+-- Test prepared statement+PREPARE pgclassstmt (oid) AS+SELECT *+ FROM account+ WHERE id = $1;+NOTICE: AUDIT: SESSION,9,1,READ,PREPARE,,,"PREPARE pgclassstmt (oid) AS+SELECT *+ FROM account+ WHERE id = $1;"+EXECUTE pgclassstmt (1);+NOTICE: AUDIT: SESSION,10,1,READ,SELECT,TABLE,public.account,"PREPARE pgclassstmt (oid) AS+SELECT *+ FROM account+ WHERE id = $1;",1+NOTICE: AUDIT: SESSION,10,2,READ,EXECUTE,,,EXECUTE pgclassstmt (1);+ id | name | password | description +----+-------+----------+-------------+ 1 | user1 | HASH2 | yada, yada+(1 row)++DEALLOCATE pgclassstmt;+NOTICE: AUDIT: SESSION,11,1,MISC,DEALLOCATE,,,DEALLOCATE pgclassstmt;+--+-- Test cursor - no tables will be logged since pg_class is a system table+BEGIN;+NOTICE: AUDIT: SESSION,12,1,MISC,BEGIN,,,BEGIN;+DECLARE ctest SCROLL CURSOR FOR+SELECT count(*)+ FROM+(+ SELECT relname+ FROM pg_class+ LIMIT 1+ ) subquery;+FETCH NEXT FROM ctest;+NOTICE: AUDIT: SESSION,13,1,MISC,FETCH,,,FETCH NEXT FROM ctest;+ count +-------+ 1+(1 row)++CLOSE ctest;+COMMIT;+NOTICE: AUDIT: SESSION,14,1,MISC,COMMIT,,,COMMIT;+--+-- Test prepared insert+CREATE TABLE test.test_insert+(+ id INT+);+NOTICE: AUDIT: SESSION,15,1,DDL,CREATE TABLE,TABLE,test.test_insert,"CREATE TABLE test.test_insert+(+ id INT+);"+PREPARE pgclassstmt (oid) AS+INSERT INTO test.test_insert (id)+ VALUES ($1);+NOTICE: AUDIT: SESSION,16,1,WRITE,PREPARE,,,"PREPARE pgclassstmt (oid) AS+INSERT INTO test.test_insert (id)+ VALUES ($1);"+EXECUTE pgclassstmt (1);+NOTICE: AUDIT: SESSION,17,1,WRITE,INSERT,TABLE,test.test_insert,"PREPARE pgclassstmt (oid) AS+INSERT INTO test.test_insert (id)+ VALUES ($1);",1+NOTICE: AUDIT: SESSION,17,2,WRITE,EXECUTE,,,EXECUTE pgclassstmt (1);+--+-- Check that primary key creation is logged+CREATE TABLE public.test+(+ id INT,+ name TEXT,+ description TEXT,+ CONSTRAINT test_pkey PRIMARY KEY (id)+);+NOTICE: AUDIT: SESSION,18,1,DDL,CREATE INDEX,INDEX,public.test_pkey,"CREATE TABLE public.test+(+ id INT,+ name TEXT,+ description TEXT,+ CONSTRAINT test_pkey PRIMARY KEY (id)+);"+NOTICE: AUDIT: SESSION,18,2,DDL,CREATE TABLE,TABLE,public.test,"CREATE TABLE public.test+(+ id INT,+ name TEXT,+ description TEXT,+ CONSTRAINT test_pkey PRIMARY KEY (id)+);"+--+-- Check that analyze is logged+ANALYZE test;+NOTICE: AUDIT: SESSION,19,1,MISC,ANALYZE,,,ANALYZE test;+--+-- Grants to public should not cause object logging (session logging will+-- still happen)+GRANT SELECT+ ON TABLE public.test+ TO PUBLIC;+NOTICE: AUDIT: SESSION,20,1,ROLE,GRANT,,,"GRANT SELECT+ ON TABLE public.test+ TO PUBLIC;"+SELECT *+ FROM test;+NOTICE: AUDIT: SESSION,21,1,READ,SELECT,TABLE,public.test,"SELECT *+ FROM test;"+ id | name | description +----+------+-------------+(0 rows)++-- Check that statements without columns log+SELECT+ FROM test;+NOTICE: AUDIT: SESSION,22,1,READ,SELECT,TABLE,public.test,"SELECT+ FROM test;"+--+(0 rows)++SELECT 1,+ current_user;+NOTICE: AUDIT: SESSION,23,1,READ,SELECT,,,"SELECT 1,+ current_user;"+ ?column? | current_user +----------+--------------+ 1 | super+(1 row)++DO $$+DECLARE+ test INT;+BEGIN+ SELECT 1+ INTO test;+END $$;+NOTICE: AUDIT: SESSION,24,1,FUNCTION,DO,,,"DO $$+DECLARE+ test INT;+BEGIN+ SELECT 1+ INTO test;+END $$;"+NOTICE: AUDIT: SESSION,24,2,READ,SELECT,,,SELECT 1+CONTEXT: SQL statement "SELECT 1"+PL/pgSQL function inline_code_block line 5 at SQL statement+explain select 1;+NOTICE: AUDIT: SESSION,25,1,READ,SELECT,,,explain select 1;+NOTICE: AUDIT: SESSION,25,2,MISC,EXPLAIN,,,explain select 1;+ QUERY PLAN +------------------------------------------+ Result (cost=0.00..0.01 rows=1 width=0)+(1 row)++--+-- Test that looks inside of do blocks log+INSERT INTO TEST (id)+ VALUES (1);+NOTICE: AUDIT: SESSION,26,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)+ VALUES (1);"+INSERT INTO TEST (id)+ VALUES (2);+NOTICE: AUDIT: SESSION,27,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)+ VALUES (2);"+INSERT INTO TEST (id)+ VALUES (3);+NOTICE: AUDIT: SESSION,28,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)+ VALUES (3);"+DO $$+DECLARE+ result RECORD;+BEGIN+ FOR result IN+ SELECT id+ FROM test+ LOOP+ INSERT INTO test (id)+ VALUES (result.id + 100);+ END LOOP;+END $$;+NOTICE: AUDIT: SESSION,29,1,FUNCTION,DO,,,"DO $$+DECLARE+ result RECORD;+BEGIN+ FOR result IN+ SELECT id+ FROM test+ LOOP+ INSERT INTO test (id)+ VALUES (result.id + 100);+ END LOOP;+END $$;"+NOTICE: AUDIT: SESSION,29,2,READ,SELECT,TABLE,public.test,"SELECT id+ FROM test"+CONTEXT: PL/pgSQL function inline_code_block line 5 at FOR over SELECT rows+NOTICE: AUDIT: SESSION,29,3,WRITE,INSERT,TABLE,public.test,"INSERT INTO test (id)+ VALUES (result.id + 100)",,,+CONTEXT: SQL statement "INSERT INTO test (id)+ VALUES (result.id + 100)"+PL/pgSQL function inline_code_block line 9 at SQL statement+NOTICE: AUDIT: SESSION,29,4,WRITE,INSERT,TABLE,public.test,"INSERT INTO test (id)+ VALUES (result.id + 100)",,,+CONTEXT: SQL statement "INSERT INTO test (id)+ VALUES (result.id + 100)"+PL/pgSQL function inline_code_block line 9 at SQL statement+NOTICE: AUDIT: SESSION,29,5,WRITE,INSERT,TABLE,public.test,"INSERT INTO test (id)+ VALUES (result.id + 100)",,,+CONTEXT: SQL statement "INSERT INTO test (id)+ VALUES (result.id + 100)"+PL/pgSQL function inline_code_block line 9 at SQL statement+--+-- Test obfuscated dynamic sql for clean logging+DO $$+DECLARE+ table_name TEXT = 'do_table';+BEGIN+ EXECUTE 'CREATE TABLE ' || table_name || ' ("weird name" INT)';+ EXECUTE 'DROP table ' || table_name;+END $$;+NOTICE: AUDIT: SESSION,30,1,FUNCTION,DO,,,"DO $$+DECLARE+ table_name TEXT = 'do_table';+BEGIN+ EXECUTE 'CREATE TABLE ' || table_name || ' (""weird name"" INT)';+ EXECUTE 'DROP table ' || table_name;+END $$;"+NOTICE: AUDIT: SESSION,30,2,DDL,CREATE TABLE,TABLE,public.do_table,"CREATE TABLE do_table (""weird name"" INT)"+CONTEXT: SQL statement "CREATE TABLE do_table ("weird name" INT)"+PL/pgSQL function inline_code_block line 5 at EXECUTE statement+NOTICE: AUDIT: SESSION,30,3,DDL,DROP TABLE,TABLE,public.do_table,DROP table do_table+CONTEXT: SQL statement "DROP table do_table"+PL/pgSQL function inline_code_block line 6 at EXECUTE statement+--+-- Generate an error and make sure the stack gets cleared+DO $$+BEGIN+ CREATE TABLE bogus.test_block+ (+ id INT+ );+END $$;+NOTICE: AUDIT: SESSION,31,1,FUNCTION,DO,,,"DO $$+BEGIN+ CREATE TABLE bogus.test_block+ (+ id INT+ );+END $$;"+ERROR: schema "bogus" does not exist+LINE 1: CREATE TABLE bogus.test_block+ ^+QUERY: CREATE TABLE bogus.test_block+ (+ id INT+ )+CONTEXT: PL/pgSQL function inline_code_block line 3 at SQL statement+--+-- Test alter table statements+ALTER TABLE public.test+ DROP COLUMN description ;+NOTICE: AUDIT: SESSION,32,1,DDL,ALTER TABLE,TABLE COLUMN,public.test.description,"ALTER TABLE public.test+ DROP COLUMN description ;"+ALTER TABLE public.test+ RENAME TO test2;+NOTICE: AUDIT: SESSION,33,1,DDL,ALTER TABLE,TABLE,public.test,"ALTER TABLE public.test+ RENAME TO test2;"+ALTER TABLE public.test2+ SET SCHEMA test;+NOTICE: AUDIT: SESSION,34,1,DDL,ALTER TABLE,INDEX,public.test_pkey,"ALTER TABLE public.test2+ SET SCHEMA test;"+ALTER TABLE test.test2+ ADD COLUMN description TEXT;+NOTICE: AUDIT: SESSION,35,1,DDL,ALTER TABLE,TABLE,test.test2,"ALTER TABLE test.test2+ ADD COLUMN description TEXT;"+ALTER TABLE test.test2+ DROP COLUMN description;+NOTICE: AUDIT: SESSION,36,1,DDL,ALTER TABLE,TABLE COLUMN,test.test2.description,"ALTER TABLE test.test2+ DROP COLUMN description;"+DROP TABLE test.test2;+NOTICE: AUDIT: SESSION,37,1,DDL,DROP TABLE,TABLE,test.test2,DROP TABLE test.test2;+NOTICE: AUDIT: SESSION,37,1,DDL,DROP TABLE,TABLE CONSTRAINT,test_pkey on test.test2,DROP TABLE test.test2;+NOTICE: AUDIT: SESSION,37,1,DDL,DROP TABLE,INDEX,test.test_pkey,DROP TABLE test.test2;+--+-- Test multiple statements with one semi-colon+CREATE SCHEMA foo+ CREATE TABLE foo.bar (id int)+ CREATE TABLE foo.baz (id int);+NOTICE: AUDIT: SESSION,38,1,DDL,CREATE TABLE,TABLE,foo.bar,"CREATE SCHEMA foo+ CREATE TABLE foo.bar (id int)+ CREATE TABLE foo.baz (id int);"+NOTICE: AUDIT: SESSION,38,2,DDL,CREATE TABLE,TABLE,foo.baz,"CREATE SCHEMA foo+ CREATE TABLE foo.bar (id int)+ CREATE TABLE foo.baz (id int);"+NOTICE: AUDIT: SESSION,38,3,DDL,CREATE SCHEMA,,,"CREATE SCHEMA foo+ CREATE TABLE foo.bar (id int)+ CREATE TABLE foo.baz (id int);"+--+-- Test aggregate+CREATE FUNCTION public.int_add+(+ a INT,+ b INT+)+ RETURNS INT LANGUAGE plpgsql AS $$+BEGIN+ return a + b;+END $$;+NOTICE: AUDIT: SESSION,39,1,DDL,CREATE FUNCTION,,,"CREATE FUNCTION public.int_add+(+ a INT,+ b INT+)+ RETURNS INT LANGUAGE plpgsql AS $$+BEGIN+ return a + b;+END $$;"+SELECT int_add(1, 1);+NOTICE: AUDIT: SESSION,40,1,READ,SELECT,,,"SELECT int_add(1, 1);"+NOTICE: AUDIT: SESSION,40,2,FUNCTION,EXECUTE,FUNCTION,public.int_add,"SELECT int_add(1, 1);"+ int_add +---------+ 2+(1 row)++CREATE AGGREGATE public.sum_test(INT) (SFUNC=public.int_add, STYPE=INT, INITCOND='0');+NOTICE: AUDIT: SESSION,41,1,DDL,CREATE AGGREGATE,,,"CREATE AGGREGATE public.sum_test(INT) (SFUNC=public.int_add, STYPE=INT, INITCOND='0');"+ALTER AGGREGATE public.sum_test(integer) RENAME TO sum_test2;+NOTICE: AUDIT: SESSION,42,1,DDL,ALTER AGGREGATE,,,ALTER AGGREGATE public.sum_test(integer) RENAME TO sum_test2;+--+-- Test conversion+CREATE CONVERSION public.conversion_test FOR 'SQL_ASCII' TO 'MULE_INTERNAL' FROM pg_catalog.ascii_to_mic;+NOTICE: AUDIT: SESSION,43,1,DDL,CREATE CONVERSION,,,CREATE CONVERSION public.conversion_test FOR 'SQL_ASCII' TO 'MULE_INTERNAL' FROM pg_catalog.ascii_to_mic;+ALTER CONVERSION public.conversion_test RENAME TO conversion_test2;+NOTICE: AUDIT: SESSION,44,1,DDL,ALTER CONVERSION,,,ALTER CONVERSION public.conversion_test RENAME TO conversion_test2;+--+-- Test create/alter/drop database+CREATE DATABASE contrib_regression_pgaudit;+NOTICE: AUDIT: SESSION,45,1,DDL,CREATE DATABASE,,,CREATE DATABASE contrib_regression_pgaudit;+ALTER DATABASE contrib_regression_pgaudit RENAME TO contrib_regression_pgaudit2;+NOTICE: AUDIT: SESSION,46,1,DDL,ALTER DATABASE,,,ALTER DATABASE contrib_regression_pgaudit RENAME TO contrib_regression_pgaudit2;+DROP DATABASE contrib_regression_pgaudit2;+NOTICE: AUDIT: SESSION,47,1,DDL,DROP DATABASE,,,DROP DATABASE contrib_regression_pgaudit2;+--+-- Test that frees a memory context earlier than expected+CREATE TABLE hoge+(+ id int+);+NOTICE: AUDIT: SESSION,48,1,DDL,CREATE TABLE,TABLE,public.hoge,"CREATE TABLE hoge+(+ id int+);"+CREATE FUNCTION test()+ RETURNS INT AS $$+DECLARE+ cur1 cursor for select * from hoge;+ tmp int;+BEGIN+ OPEN cur1;+ FETCH cur1 into tmp;+ RETURN tmp;+END $$+LANGUAGE plpgsql ;+NOTICE: AUDIT: SESSION,49,1,DDL,CREATE FUNCTION,,,"CREATE FUNCTION test()+ RETURNS INT AS $$+DECLARE+ cur1 cursor for select * from hoge;+ tmp int;+BEGIN+ OPEN cur1;+ FETCH cur1 into tmp;+ RETURN tmp;+END $$+LANGUAGE plpgsql ;"+SELECT test();+NOTICE: AUDIT: SESSION,50,1,READ,SELECT,,,SELECT test();+NOTICE: AUDIT: SESSION,50,2,FUNCTION,EXECUTE,FUNCTION,public.test,SELECT test();+NOTICE: AUDIT: SESSION,50,3,READ,SELECT,TABLE,public.hoge,select * from hoge+CONTEXT: PL/pgSQL function test() line 6 at OPEN+ test +------+ +(1 row)+diff --git a/contrib/pg_audit/pg_audit--1.0.0.sql b/contrib/pg_audit/pg_audit--1.0.0.sqlnew file mode 100644index 0000000..9d9ee83--- /dev/null+++ b/contrib/pg_audit/pg_audit--1.0.0.sql@@ -0,0 +1,22 @@+/* pg_audit/pg_audit--1.0.0.sql */++-- complain if script is sourced in psql, rather than via CREATE EXTENSION+\echo Use "CREATE EXTENSION pg_audit" to load this file.\quit++CREATE FUNCTION pg_audit_ddl_command_end()+ RETURNS event_trigger+ LANGUAGE C+ AS 'MODULE_PATHNAME', 'pg_audit_ddl_command_end';++CREATE EVENT TRIGGER pg_audit_ddl_command_end+ ON ddl_command_end+ EXECUTE PROCEDURE pg_audit_ddl_command_end();++CREATE FUNCTION pg_audit_sql_drop()+ RETURNS event_trigger+ LANGUAGE C+ AS 'MODULE_PATHNAME', 'pg_audit_sql_drop';++CREATE EVENT TRIGGER pg_audit_sql_drop+ ON sql_drop+ EXECUTE PROCEDURE pg_audit_sql_drop();diff --git a/contrib/pg_audit/pg_audit.c b/contrib/pg_audit/pg_audit.cnew file mode 100644index 0000000..9dd2d7c--- /dev/null+++ b/contrib/pg_audit/pg_audit.c@@ -0,0 +1,1790 @@+/*------------------------------------------------------------------------------+ * 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);++/*+ * Event trigger prototypes+ */+Datum pg_audit_ddl_command_end(PG_FUNCTION_ARGS);+Datum pg_audit_sql_drop(PG_FUNCTION_ARGS);++PG_FUNCTION_INFO_V1(pg_audit_ddl_command_end);+PG_FUNCTION_INFO_V1(pg_audit_sql_drop);++/*+ * auditRole is the string value of the pg_audit.role GUC, which contains the+ * role for grant-based auditing.+ */+char *auditRole = NULL;++/*+ * auditLog is the string value of the pg_audit.log GUC, e.g. "read, write, ddl"+ * (it's not used by the module but is required by DefineCustomStringVariable).+ * Each token corresponds to a flag in enum LogClass below. We convert the list+ * of tokens into a bitmap in auditLogBitmap for internal use.+ */+char *auditLog = NULL;+static uint64 auditLogBitmap = 0;++/*+ * auditLogRelation controls whether all relations are logged for READ and+ * WRITE classes during session logging.+ */+bool auditLogRelation = false;++/*+ * auditLogNotice raises a notice as well as logging via the standard facility.+ * This is primarily for the benefit of testing.+ */+bool auditLogNotice = false;++/*+ * String constants for audit types - used when logging to distinguish session+ * vs. object auditing.+ */+#define AUDIT_TYPE_OBJECT "OBJECT"+#define AUDIT_TYPE_SESSION "SESSION"++/*+ * String constants for log classes - used when processing tokens in the+ * pg_audit.log GUC.+ */+#define CLASS_DDL "DDL"+#define CLASS_FUNCTION "FUNCTION"+#define CLASS_MISC "MISC"+#define CLASS_PARAMETER "PARAMETER"+#define CLASS_READ "READ"+#define CLASS_ROLE "ROLE"+#define CLASS_WRITE "WRITE"++#define CLASS_ALL "ALL"+#define CLASS_NONE "NONE"++/* Log class enum used to represent bits in auditLogBitmap */+enum LogClass+{+ LOG_NONE = 0,++ /* DDL: CREATE/DROP/ALTER */+ LOG_DDL = (1 << 1),++ /* Function execution */+ LOG_FUNCTION = (1 << 2),++ /* Statements not covered by another class */+ LOG_MISC = (1 << 3),++ /* Function execution */+ LOG_PARAMETER = (1 << 4),++ /* SELECT */+ LOG_READ = (1 << 5),++ /* GRANT, REVOKE, CREATE/ALTER/DROP ROLE */+ LOG_ROLE = (1 << 6),++ /* INSERT, UPDATE, DELETE, TRUNCATE */+ LOG_WRITE = (1 << 7),++ /* Absolutely everything */+ LOG_ALL = ~(uint64)0+};++/* String constants for logging commands */+#define COMMAND_DELETE "DELETE"+#define COMMAND_EXECUTE "EXECUTE"+#define COMMAND_INSERT "INSERT"+#define COMMAND_UPDATE "UPDATE"+#define COMMAND_SELECT "SELECT"++#define COMMAND_ALTER_ROLE "ALTER ROLE"+#define COMMAND_DROP_ROLE "CREATE ROLE"++#define COMMAND_UNKNOWN "UNKNOWN"++/* String constants for logging object types */+#define OBJECT_TYPE_COMPOSITE_TYPE "COMPOSITE TYPE"+#define OBJECT_TYPE_FOREIGN_TABLE "FOREIGN TABLE"+#define OBJECT_TYPE_FUNCTION "FUNCTION"+#define OBJECT_TYPE_INDEX "INDEX"+#define OBJECT_TYPE_TABLE "TABLE"+#define OBJECT_TYPE_TOASTVALUE "TOASTVALUE"+#define OBJECT_TYPE_MATVIEW "MATERIALIZED VIEW"+#define OBJECT_TYPE_SEQUENCE "SEQUENCE"+#define OBJECT_TYPE_VIEW "VIEW"++#define OBJECT_TYPE_UNKNOWN "UNKNOWN"++/*+ * An AuditEvent represents an operation that potentially affects a single+ * object. If a statement affects multiple objects multiple AuditEvents must be+ * created to represent it.+ */+typedef struct+{+ int64 statementId;+ int64 substatementId;++ LogStmtLevel logStmtLevel;+ NodeTag commandTag;+ const char *command;+ const char *objectType;+ char *objectName;+ const char *commandText;+ ParamListInfo paramList;++ bool granted;+ bool logged;+} AuditEvent;++/*+ * A simple FIFO queue to keep track of the current stack of audit events.+ */+typedef struct AuditEventStackItem+{+ struct AuditEventStackItem *next;++ AuditEvent auditEvent;++ int64 stackId;++ MemoryContext contextAudit;+ MemoryContextCallback contextCallback;+} AuditEventStackItem;++AuditEventStackItem *auditEventStack = NULL;++/*+ * Track when an internal statement is running so it is not logged+ */+static bool internalStatement = false;++/*+ * Track running total for statements and substatements and whether or not+ * anything has been logged since this statement began.+ */+static int64 statementTotal = 0;+static int64 substatementTotal = 0;+static int64 stackTotal = 0;++static bool statementLogged = false;++/*+ * Stack functions+ *+ * Audit events can go down to multiple levels so a stack is maintained to keep+ * track of them.+ */++/*+ * Respond to callbacks registered with MemoryContextRegisterResetCallback().+ * Removes the event(s) off the stack that have become obsolete once the+ * MemoryContext has been freed. The callback should always be freeing the top+ * of the stack, but the code is tolerant of out-of-order callbacks.+ */+static void+stack_free(void *stackFree)+{+ AuditEventStackItem *nextItem = auditEventStack;++ /* Only process if the stack contains items */+ while (nextItem != NULL)+ {+ /* Check if this item matches the item to be freed */+ if (nextItem == (AuditEventStackItem *)stackFree)+ {+ /* Move top of stack to the item after the freed item */+ auditEventStack = nextItem->next;++ /* If the stack is not empty */+ if (auditEventStack == NULL)+ {+ /* Reset internal statement in case of error */+ internalStatement = false;++ /* Reset sub statement total */+ substatementTotal = 0;++ /* Reset statement logged flag total */+ statementLogged = false;+ }++ return;+ }++ /* Still looking, test the next item */+ nextItem = nextItem->next;+ }+}++/*+ * Push a new audit event onto the stack and create a new memory context to+ * store it.+ */+static AuditEventStackItem *+stack_push()+{+ MemoryContext contextAudit;+ MemoryContext contextOld;+ AuditEventStackItem *stackItem;++ /* Create a new memory context */+ contextAudit = AllocSetContextCreate(CurrentMemoryContext,+ "pg_audit stack context",+ ALLOCSET_DEFAULT_MINSIZE,+ ALLOCSET_DEFAULT_INITSIZE,+ ALLOCSET_DEFAULT_MAXSIZE);+ contextOld = MemoryContextSwitchTo(contextAudit);++ /* Allocate the stack item */+ stackItem = palloc0(sizeof(AuditEventStackItem));++ /* Store memory contexts */+ stackItem->contextAudit = contextAudit;++ /* If item already on stack then push it down */+ if (auditEventStack != NULL)+ stackItem->next = auditEventStack;+ else+ stackItem->next = NULL;++ /*+ * Create the unique stackId - used to keep the stack sane when memory+ * contexts are freed unexpectedly.+ */+ stackItem->stackId = ++stackTotal;++ /*+ * Setup a callback in case an error happens. stack_free() will truncate+ * the stack at this item.+ */+ stackItem->contextCallback.func = stack_free;+ stackItem->contextCallback.arg = (void *)stackItem;+ MemoryContextRegisterResetCallback(contextAudit,+ &stackItem->contextCallback);++ /* Push item on the stack */+ auditEventStack = stackItem;++ /* Return to the old memory context */+ MemoryContextSwitchTo(contextOld);++ /* Return the stack item */+ return stackItem;+}++/*+ * Pop an audit event from the stack by deleting the memory context that+ * contains it. The callback to stack_free() does the actual pop.+ */+static void+stack_pop(int64 stackId)+{+ /* Make sure what we want to delete is at the top of the stack */+ if (auditEventStack != NULL && auditEventStack->stackId == stackId)+ {+ MemoryContextDelete(auditEventStack->contextAudit);+ }+}++/*+ * Appends a properly quoted CSV field to StringInfo.+ */+static void+append_valid_csv(StringInfoData *buffer, const char *appendStr)+{+ const char *pChar;++ /*+ * If the append string is null then return. NULL fields are not quoted+ * in CSV+ */+ if (appendStr == NULL)+ return;++ /* Only format for CSV if appendStr contains: ", comma, \n, \r */+ if (strstr(appendStr, ",") || strstr(appendStr, "\"") ||+ strstr(appendStr, "\n") || strstr(appendStr, "\r"))+ {+ appendStringInfoCharMacro(buffer, '"');++ for (pChar = appendStr; *pChar; pChar++)+ {+ if (*pChar == '"') /* double single quotes */+ appendStringInfoCharMacro(buffer, *pChar);++ appendStringInfoCharMacro(buffer, *pChar);+ }++ appendStringInfoCharMacro(buffer, '"');+ }+ /* Else just append */+ else+ {+ appendStringInfoString(buffer, appendStr);+ }+}++/*+ * Takes an AuditEvent, classifies it, then logs it if permissions were granted+ * via roles or if the statement belongs in a class that is being logged.+ */+static void+log_audit_event(AuditEventStackItem *stackItem)+{+ MemoryContext contextOld;+ StringInfoData auditStr;++ /* By default put everything in the MISC class. */+ enum LogClass class = LOG_MISC;+ const char *className = CLASS_MISC;++ /* Classify the statement using log stmt level and the command tag */+ switch (stackItem->auditEvent.logStmtLevel)+ {+ /* All mods go in WRITE class */+ case LOGSTMT_MOD:+ className = CLASS_WRITE;+ class = LOG_WRITE;+ break;++ /* Separate ROLE from other DDL statements */+ case LOGSTMT_DDL:+ /* Identify role statements */+ if (stackItem->auditEvent.commandTag == T_GrantStmt ||+ stackItem->auditEvent.commandTag == T_GrantRoleStmt ||+ stackItem->auditEvent.commandTag == T_CreateRoleStmt ||+ (stackItem->auditEvent.commandTag == T_RenameStmt &&+ pg_strcasecmp(stackItem->auditEvent.command,+ COMMAND_ALTER_ROLE) == 0) ||+ (stackItem->auditEvent.commandTag == T_DropStmt &&+ pg_strcasecmp(stackItem->auditEvent.command,+ COMMAND_DROP_ROLE) == 0) ||+ stackItem->auditEvent.commandTag == T_DropRoleStmt ||+ stackItem->auditEvent.commandTag == T_AlterRoleStmt ||+ stackItem->auditEvent.commandTag == T_AlterRoleSetStmt)+ {+ className = CLASS_ROLE;+ class = LOG_ROLE;+ }+ /* Else log as DDL */+ else+ {+ className = CLASS_DDL;+ class = LOG_DDL;+ }++ /* Figure out the rest */+ case LOGSTMT_ALL:+ switch (stackItem->auditEvent.commandTag)+ {+ /* READ statements */+ case T_CopyStmt:+ case T_SelectStmt:+ case T_PrepareStmt:+ case T_PlannedStmt:+ case T_ExecuteStmt:+ className = CLASS_READ;+ class = LOG_READ;+ break;++ /* Reindex is DDL (because cluster is DDL) */+ case T_ReindexStmt:+ className = CLASS_DDL;+ class = LOG_DDL;+ break;++ /* FUNCTION statements */+ case T_DoStmt:+ className = CLASS_FUNCTION;+ class = LOG_FUNCTION;+ break;++ default:+ break;+ }+ break;++ case LOGSTMT_NONE:+ break;+ }++ /*+ * Only log the statement if:+ *+ * 1. If permissions were granted via roles+ * 2. The statement belongs to a class that is being logged+ */+ if (!stackItem->auditEvent.granted && !(auditLogBitmap & class))+ return;++ /* Use audit memory context in case something is not freed */+ contextOld = MemoryContextSwitchTo(stackItem->contextAudit);++ /* Set statement and substatement Ids */+ if (stackItem->auditEvent.statementId == 0)+ {+ /* If nothing has been logged yet then create a new statement Id */+ if (!statementLogged)+ {+ statementTotal++;+ statementLogged = true;+ }++ stackItem->auditEvent.statementId = statementTotal;+ stackItem->auditEvent.substatementId = ++substatementTotal;+ }++ /* Create the audit string */+ initStringInfo(&auditStr);++ append_valid_csv(&auditStr, stackItem->auditEvent.command);+ appendStringInfoCharMacro(&auditStr, ',');++ append_valid_csv(&auditStr, stackItem->auditEvent.objectType);+ appendStringInfoCharMacro(&auditStr, ',');++ append_valid_csv(&auditStr, stackItem->auditEvent.objectName);+ appendStringInfoCharMacro(&auditStr, ',');++ append_valid_csv(&auditStr, stackItem->auditEvent.commandText);++ /* If parameter logging is turned on and there are parameters to log */+ if (auditLogBitmap & LOG_PARAMETER &&+ stackItem->auditEvent.paramList != NULL &&+ stackItem->auditEvent.paramList->numParams > 0 &&+ !IsAbortedTransactionBlockState())+ {+ ParamListInfo paramList = stackItem->auditEvent.paramList;+ int paramIdx;++ /* Iterate through all params */+ for (paramIdx = 0; paramIdx < paramList->numParams; paramIdx++)+ {+ ParamExternData *prm = ¶mList->params[paramIdx];+ Oid typeOutput;+ bool typeIsVarLena;+ char *paramStr;++ /* Add a comma for each param */+ appendStringInfoCharMacro(&auditStr, ',');++ /* Skip this param if null or if oid is invalid */+ if (prm->isnull || !OidIsValid(prm->ptype))+ {+ continue;+ }++ /* Output the string */+ getTypeOutputInfo(prm->ptype, &typeOutput, &typeIsVarLena);+ paramStr = OidOutputFunctionCall(typeOutput, prm->value);++ append_valid_csv(&auditStr, paramStr);+ pfree(paramStr);+ }+ }++ /* Log the audit string */+ elog(auditLogNotice ? NOTICE : LOG,+ "AUDIT: %s,%ld,%ld,%s,%s",+ stackItem->auditEvent.granted ?+ AUDIT_TYPE_OBJECT : AUDIT_TYPE_SESSION,+ stackItem->auditEvent.statementId,+ stackItem->auditEvent.substatementId,+ className, auditStr.data);++ /* Mark the audit event as logged */+ stackItem->auditEvent.logged = true;++ /* Switch back to the old memory context */+ MemoryContextSwitchTo(contextOld);+}++/*+ * Check if the role or any inherited role has any permission in the mask. The+ * public role is excluded from this check and superuser permissions are not+ * considered.+ */+static bool+audit_on_acl(Datum aclDatum,+ Oid auditOid,+ AclMode mask)+{+ bool result = false;+ Acl *acl;+ AclItem *aclItemData;+ int aclIndex;+ int aclTotal;++ /* Detoast column's ACL if necessary */+ acl = DatumGetAclP(aclDatum);++ /* Get the acl list and total */+ aclTotal = ACL_NUM(acl);+ aclItemData = ACL_DAT(acl);++ /* Check privileges granted directly to auditOid */+ for (aclIndex = 0; aclIndex < aclTotal; aclIndex++)+ {+ AclItem *aclItem = &aclItemData[aclIndex];++ if (aclItem->ai_grantee == auditOid &&+ aclItem->ai_privs & mask)+ {+ result = true;+ break;+ }+ }++ /*+ * Check privileges granted indirectly via role memberships. We do this in+ * a separate pass to minimize expensive indirect membership tests. In+ * particular, it's worth testing whether a given ACL entry grants any+ * privileges still of interest before we perform the has_privs_of_role+ * test.+ */+ if (!result)+ {+ for (aclIndex = 0; aclIndex < aclTotal; aclIndex++)+ {+ AclItem *aclItem = &aclItemData[aclIndex];++ /* Don't test public or auditOid (it has been tested already) */+ if (aclItem->ai_grantee == ACL_ID_PUBLIC ||+ aclItem->ai_grantee == auditOid)+ continue;++ /*+ * Check that the role has the required privileges and that it is+ * inherited by auditOid.+ */+ if (aclItem->ai_privs & mask &&+ has_privs_of_role(auditOid, aclItem->ai_grantee))+ {+ result = true;+ break;+ }+ }+ }++ /* if we have a detoasted copy, free it */+ if (acl && (Pointer) acl != DatumGetPointer(aclDatum))+ pfree(acl);++ return result;+}++/*+ * Check if a role has any of the permissions in the mask on a relation.+ */+static bool+audit_on_relation(Oid relOid,+ Oid auditOid,+ AclMode mask)+{+ bool result = false;+ HeapTuple tuple;+ Datum aclDatum;+ bool isNull;++ /* Get relation tuple from pg_class */+ tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relOid));++ /* Return false if tuple is not valid */+ if (!HeapTupleIsValid(tuple))+ return false;++ /* Get the relation's ACL */+ aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,+ &isNull);++ /* If not null then test */+ if (!isNull)+ result = audit_on_acl(aclDatum, auditOid, mask);++ /* Free the relation tuple */+ ReleaseSysCache(tuple);++ return result;+}++/*+ * Check if a role has any of the permissions in the mask on an attribute.+ */+static bool+audit_on_attribute(Oid relOid,+ AttrNumber attNum,+ Oid auditOid,+ AclMode mask)+{+ bool result = false;+ HeapTuple attTuple;+ Datum aclDatum;+ bool isNull;++ /* Get the attribute's ACL */+ attTuple = SearchSysCache2(ATTNUM,+ ObjectIdGetDatum(relOid),+ Int16GetDatum(attNum));++ /* Return false if attribute is invalid */+ if (!HeapTupleIsValid(attTuple))+ return false;++ /* Only process attribute that have not been dropped */+ if (!((Form_pg_attribute) GETSTRUCT(attTuple))->attisdropped)+ {+ aclDatum = SysCacheGetAttr(ATTNUM, attTuple, Anum_pg_attribute_attacl,+ &isNull);++ if (!isNull)+ result = audit_on_acl(aclDatum, auditOid, mask);+ }++ /* Free attribute */+ ReleaseSysCache(attTuple);++ return result;+}++/*+ * Check if a role has any of the permissions in the mask on an attribute in+ * the provided set. If the set is empty, then all valid attributes in the+ * relation will be tested.+ */+static bool+audit_on_any_attribute(Oid relOid,+ Oid auditOid,+ Bitmapset *attributeSet,+ AclMode mode)+{+ bool result = false;+ AttrNumber col;+ Bitmapset *tmpSet;++ /* If bms is empty then check for any column match */+ if (bms_is_empty(attributeSet))+ {+ HeapTuple classTuple;+ AttrNumber nattrs;+ AttrNumber curr_att;++ /* Get relation to determine total attribute */+ classTuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relOid));++ if (!HeapTupleIsValid(classTuple))+ return false;++ nattrs = ((Form_pg_class) GETSTRUCT(classTuple))->relnatts;+ ReleaseSysCache(classTuple);++ /* Check each column */+ for (curr_att = 1; curr_att <= nattrs; curr_att++)+ {+ if (audit_on_attribute(relOid, curr_att, auditOid, mode))+ return true;+ }+ }++ /* bms_first_member is destructive, so make a copy before using it. */+ tmpSet = bms_copy(attributeSet);++ /* Check each column */+ while ((col = bms_first_member(tmpSet)) >= 0)+ {+ col += FirstLowInvalidHeapAttributeNumber;++ if (col != InvalidAttrNumber &&+ audit_on_attribute(relOid, col, auditOid, mode))+ {+ result = true;+ break;+ }+ }++ /* Free the column set */+ bms_free(tmpSet);++ return result;+}++/*+ * Create AuditEvents for SELECT/DML operations via executor permissions checks.+ */+static void+log_select_dml(Oid auditOid, List *rangeTabls)+{+ ListCell *lr;+ bool first = true;+ bool found = false;++ /* Do not log if this is an internal statement */+ if (internalStatement)+ return;++ foreach(lr, rangeTabls)+ {+ Oid relOid;+ Relation rel;+ RangeTblEntry *rte = lfirst(lr);++ /* We only care about tables, and can ignore subqueries etc. */+ if (rte->rtekind != RTE_RELATION)+ continue;++ found = true;++ /*+ * Filter out any system relations+ */+ relOid = rte->relid;+ rel = relation_open(relOid, NoLock);++ if (IsSystemNamespace(RelationGetNamespace(rel)))+ {+ relation_close(rel, NoLock);+ continue;+ }++ /*+ * 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)+ {+ auditEventStack->auditEvent.logStmtLevel = LOGSTMT_MOD;+ auditEventStack->auditEvent.commandTag = T_InsertStmt;+ auditEventStack->auditEvent.command = COMMAND_INSERT;+ }+ else if (rte->requiredPerms & ACL_UPDATE)+ {+ auditEventStack->auditEvent.logStmtLevel = LOGSTMT_MOD;+ auditEventStack->auditEvent.commandTag = T_UpdateStmt;+ auditEventStack->auditEvent.command = COMMAND_UPDATE;+ }+ else if (rte->requiredPerms & ACL_DELETE)+ {+ auditEventStack->auditEvent.logStmtLevel = LOGSTMT_MOD;+ auditEventStack->auditEvent.commandTag = T_DeleteStmt;+ auditEventStack->auditEvent.command = COMMAND_DELETE;+ }+ else if (rte->requiredPerms & ACL_SELECT)+ {+ auditEventStack->auditEvent.logStmtLevel = LOGSTMT_ALL;+ auditEventStack->auditEvent.commandTag = T_SelectStmt;+ auditEventStack->auditEvent.command = COMMAND_SELECT;+ }+ else+ {+ auditEventStack->auditEvent.logStmtLevel = LOGSTMT_ALL;+ auditEventStack->auditEvent.commandTag = T_Invalid;+ auditEventStack->auditEvent.command = COMMAND_UNKNOWN;+ }++ /*+ * Fill values in the event struct that are required for session+ * logging.+ */+ auditEventStack->auditEvent.granted = false;++ /*+ * If this is the first rte then session log unless auditLogRelation+ * is set.+ */+ if (first && !auditLogRelation)+ {+ auditEventStack->auditEvent.objectName = "";+ auditEventStack->auditEvent.objectType = "";++ log_audit_event(auditEventStack);++ first = false;+ }++ /* Get the relation type */+ switch (rte->relkind)+ {+ case RELKIND_RELATION:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_TABLE;+ break;++ case RELKIND_INDEX:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_INDEX;+ break;++ case RELKIND_SEQUENCE:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_SEQUENCE;+ break;++ case RELKIND_TOASTVALUE:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_TOASTVALUE;+ break;++ case RELKIND_VIEW:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_VIEW;+ break;++ case RELKIND_COMPOSITE_TYPE:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_COMPOSITE_TYPE;+ break;++ case RELKIND_FOREIGN_TABLE:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_FOREIGN_TABLE;+ break;++ case RELKIND_MATVIEW:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_MATVIEW;+ break;++ default:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_UNKNOWN;+ break;+ }++ /* Get the relation name */+ auditEventStack->auditEvent.objectName =+ quote_qualified_identifier(get_namespace_name(+ RelationGetNamespace(rel)),+ RelationGetRelationName(rel));+ relation_close(rel, NoLock);++ /* Perform object auditing only if the audit role is valid */+ if (auditOid != InvalidOid)+ {+ AclMode auditPerms = (ACL_SELECT | ACL_UPDATE | ACL_INSERT) &+ rte->requiredPerms;++ /*+ * If any of the required permissions for the relation are granted+ * to the audit role then audit the relation+ */+ if (audit_on_relation(relOid, auditOid, auditPerms))+ {+ auditEventStack->auditEvent.granted = true;+ }++ /*+ * Else check if the audit role has column-level permissions for+ * select, insert, or update.+ */+ else if (auditPerms != 0)+ {+ /*+ * Check the select columns to see if the audit role has+ * priveleges on any of them.+ */+ if (auditPerms & ACL_SELECT)+ {+ auditEventStack->auditEvent.granted =+ audit_on_any_attribute(relOid, auditOid,+ rte->selectedCols,+ ACL_SELECT);+ }++ /*+ * Check the modified columns to see if the audit role has+ * privileges on any of them.+ */+ if (!auditEventStack->auditEvent.granted)+ {+ auditPerms &= (ACL_INSERT | ACL_UPDATE);++ if (auditPerms)+ {+ auditEventStack->auditEvent.granted =+ audit_on_any_attribute(relOid, auditOid,+ rte->modifiedCols,+ auditPerms);+ }+ }+ }+ }++ /* Do relation level logging if a grant was found */+ if (auditEventStack->auditEvent.granted)+ {+ auditEventStack->auditEvent.logged = false;+ log_audit_event(auditEventStack);+ }++ /* Do relation level logging if auditLogRelation is set */+ if (auditLogRelation)+ {+ auditEventStack->auditEvent.logged = false;+ auditEventStack->auditEvent.granted = false;+ log_audit_event(auditEventStack);+ }++ pfree(auditEventStack->auditEvent.objectName);+ }++ /*+ * If no tables were found that means that RangeTbls was empty or all+ * relations were in the system schema. In that case still log a+ * session record.+ */+ if (!found)+ {+ auditEventStack->auditEvent.granted = false;+ auditEventStack->auditEvent.logged = false;++ log_audit_event(auditEventStack);+ }+}++/*+ * Create AuditEvents for certain kinds of CREATE, ALTER, and DELETE statements+ * where the object can be logged.+ */+static void+log_create_alter_drop(Oid classId,+ Oid objectId)+{+ /* Only perform when class is relation */+ if (classId == RelationRelationId)+ {+ Relation rel;+ Form_pg_class class;++ /* Open the relation */+ rel = relation_open(objectId, NoLock);++ /* Filter out any system relations */+ if (IsToastNamespace(RelationGetNamespace(rel)))+ {+ relation_close(rel, NoLock);+ return;+ }++ /* Get rel information and close it */+ class = RelationGetForm(rel);+ auditEventStack->auditEvent.objectName =+ quote_qualified_identifier(get_namespace_name(+ RelationGetNamespace(rel)),+ RelationGetRelationName(rel));+ relation_close(rel, NoLock);++ /* Set object type based on relkind */+ switch (class->relkind)+ {+ case RELKIND_RELATION:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_TABLE;+ break;++ case RELKIND_INDEX:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_INDEX;+ break;++ case RELKIND_SEQUENCE:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_SEQUENCE;+ break;++ case RELKIND_VIEW:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_VIEW;+ break;++ case RELKIND_COMPOSITE_TYPE:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_COMPOSITE_TYPE;+ break;++ case RELKIND_FOREIGN_TABLE:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_FOREIGN_TABLE;+ break;++ case RELKIND_MATVIEW:+ auditEventStack->auditEvent.objectType =+ OBJECT_TYPE_MATVIEW;+ break;++ /*+ * Any other cases will be handled by log_utility_command().+ */+ default:+ return;+ break;+ }+ }+}++/*+ * 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;+ AuditEventStackItem *stackItem;++ /* Get info about the function. */+ proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(objectId));++ if (!proctup)+ elog(ERROR, "cache lookup failed for function %u", objectId);+ proc = (Form_pg_proc) GETSTRUCT(proctup);++ /*+ * Logging execution of all pg_catalog functions would make the log+ * unusably noisy.+ */+ if (IsSystemNamespace(proc->pronamespace))+ {+ ReleaseSysCache(proctup);+ return;+ }++ /* Push audit event onto the stack */+ stackItem = stack_push();++ /* Generate the fully-qualified function name. */+ stackItem->auditEvent.objectName =+ quote_qualified_identifier(get_namespace_name(proc->pronamespace),+ NameStr(proc->proname));+ ReleaseSysCache(proctup);++ /* Log the function call */+ stackItem->auditEvent.logStmtLevel = LOGSTMT_ALL;+ stackItem->auditEvent.commandTag = T_DoStmt;+ stackItem->auditEvent.command = COMMAND_EXECUTE;+ stackItem->auditEvent.objectType = OBJECT_TYPE_FUNCTION;+ stackItem->auditEvent.commandText = stackItem->next->auditEvent.commandText;++ log_audit_event(stackItem);++ /* Pop audit event from the stack */+ stack_pop(stackItem->stackId);+}++/*+ * 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:+ if (auditLogBitmap & LOG_FUNCTION)+ log_function_execute(objectId);+ break;++ /* Log create */+ case OAT_POST_CREATE:+ if (auditLogBitmap & LOG_DDL)+ {+ ObjectAccessPostCreate *pc = arg;++ if (pc->is_internal)+ return;++ log_create_alter_drop(classId, objectId);+ }+ break;++ /* Log alter */+ case OAT_POST_ALTER:+ if (auditLogBitmap & LOG_DDL)+ {+ ObjectAccessPostAlter *pa = arg;++ if (pa->is_internal)+ return;++ log_create_alter_drop(classId, objectId);+ }+ break;++ /* Log drop */+ case OAT_DROP:+ if (auditLogBitmap & LOG_DDL)+ {+ ObjectAccessDrop *drop = arg;++ if (drop->dropflags & PERFORM_DELETION_INTERNAL)+ return;++ log_create_alter_drop(classId, objectId);+ }+ break;++ /* All others processed by log_utility_command() */+ default:+ break;+ }+}++/*+ * Hook functions+ */+static ExecutorCheckPerms_hook_type next_ExecutorCheckPerms_hook = NULL;+static ProcessUtility_hook_type next_ProcessUtility_hook = NULL;+static object_access_hook_type next_object_access_hook = NULL;+static ExecutorStart_hook_type next_ExecutorStart_hook = NULL;+static ExecutorEnd_hook_type next_ExecutorEnd_hook = NULL;++/*+ * Hook ExecutorStart to get the query text and basic command type for queries+ * that do not contain a table so can't be idenitified accurately in+ * ExecutorCheckPerms.+ */+static void+pg_audit_ExecutorStart_hook(QueryDesc *queryDesc, int eflags)+{+ AuditEventStackItem *stackItem = NULL;++ if (!internalStatement)+ {+ /* Allocate the audit event */+ stackItem = stack_push();++ /* Initialize command */+ switch (queryDesc->operation)+ {+ case CMD_SELECT:+ stackItem->auditEvent.logStmtLevel = LOGSTMT_ALL;+ stackItem->auditEvent.commandTag = T_SelectStmt;+ stackItem->auditEvent.command = COMMAND_SELECT;+ break;++ case CMD_INSERT:+ stackItem->auditEvent.logStmtLevel = LOGSTMT_MOD;+ stackItem->auditEvent.commandTag = T_InsertStmt;+ stackItem->auditEvent.command = COMMAND_INSERT;+ break;++ case CMD_UPDATE:+ stackItem->auditEvent.logStmtLevel = LOGSTMT_MOD;+ stackItem->auditEvent.commandTag = T_UpdateStmt;+ stackItem->auditEvent.command = COMMAND_UPDATE;+ break;++ case CMD_DELETE:+ stackItem->auditEvent.logStmtLevel = LOGSTMT_MOD;+ stackItem->auditEvent.commandTag = T_DeleteStmt;+ stackItem->auditEvent.command = COMMAND_DELETE;+ break;++ default:+ stackItem->auditEvent.logStmtLevel = LOGSTMT_ALL;+ stackItem->auditEvent.commandTag = T_Invalid;+ stackItem->auditEvent.command = COMMAND_UNKNOWN;+ break;+ }++ /* Initialize the audit event */+ stackItem->auditEvent.objectName = "";+ stackItem->auditEvent.objectType = "";+ stackItem->auditEvent.commandText = queryDesc->sourceText;+ stackItem->auditEvent.paramList = queryDesc->params;+ }++ /* Call the previous hook or standard function */+ if (next_ExecutorStart_hook)+ next_ExecutorStart_hook(queryDesc, eflags);+ else+ standard_ExecutorStart(queryDesc, eflags);+}++/*+ * Hook ExecutorCheckPerms to do session and object auditing for DML.+ */+static bool+pg_audit_ExecutorCheckPerms_hook(List *rangeTabls, bool abort)+{+ Oid auditOid;++ /* Get the audit oid if the role exists. */+ auditOid = get_role_oid(auditRole, true);++ /* Log DML if the audit role is valid or session logging is enabled. */+ if ((auditOid != InvalidOid || auditLogBitmap != 0) &&+ !IsAbortedTransactionBlockState())+ log_select_dml(auditOid, rangeTabls);++ /* Call the next hook function. */+ if (next_ExecutorCheckPerms_hook &&+ !(*next_ExecutorCheckPerms_hook) (rangeTabls, abort))+ return false;++ return true;+}++/*+ * Hook ExecutorEnd to pop statement audit event off the stack.+ */+static void+pg_audit_ExecutorEnd_hook(QueryDesc *queryDesc)+{+ /* Call the next hook or standard function */+ if (next_ExecutorEnd_hook)+ next_ExecutorEnd_hook(queryDesc);+ else+ standard_ExecutorEnd(queryDesc);++ /* Pop the audit event off the stack */+ if (!internalStatement && auditEventStack)+ {+ stack_pop(auditEventStack->stackId);+ }+}++/*+ * Hook ProcessUtility to do session auditing for DDL and utility commands.+ */+static void+pg_audit_ProcessUtility_hook(Node *parsetree,+ const char *queryString,+ ProcessUtilityContext context,+ ParamListInfo params,+ DestReceiver *dest,+ char *completionTag)+{+ AuditEventStackItem *stackItem = NULL;+ int64 stackId;++ /* Allocate the audit event */+ if (!IsAbortedTransactionBlockState())+ {+ /* Process top level utility statement */+ if (context == PROCESS_UTILITY_TOPLEVEL)+ {+ if (auditEventStack != NULL)+ elog(ERROR, "pg_audit stack is not empty");++ /* Set params */+ stackItem = stack_push();+ stackItem->auditEvent.paramList = params;+ }+ else+ stackItem = stack_push();++ stackId = stackItem->stackId;+ stackItem->auditEvent.logStmtLevel = GetCommandLogLevel(parsetree);+ stackItem->auditEvent.commandTag = nodeTag(parsetree);+ stackItem->auditEvent.command = CreateCommandTag(parsetree);+ stackItem->auditEvent.objectName = "";+ stackItem->auditEvent.objectType = "";+ stackItem->auditEvent.commandText = queryString;++ /*+ * If this is a DO block log it before calling the next ProcessUtility+ * hook.+ */+ if (auditLogBitmap != 0 &&+ stackItem->auditEvent.commandTag == T_DoStmt &&+ !IsAbortedTransactionBlockState())+ {+ log_audit_event(stackItem);+ }+ }++ /* 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);++ /*+ * Process the audit event if there is one. Also check that this event was+ * not popped off the stack by a memory context being freed elsewhere.+ */+ if (stackItem != NULL && auditEventStack != NULL &&+ auditEventStack->stackId == stackId)+ {+ /* Log the utility command if logging is on, the command has not already+ * been logged by another hook, and the transaction is not aborted. */+ if (auditLogBitmap != 0 && !auditEventStack->auditEvent.logged &&+ !IsAbortedTransactionBlockState())+ log_audit_event(auditEventStack);++ stack_pop(stackId);+ }+}++/*+ * Hook object_access_hook to provide fully-qualified object names for execute,+ * create, drop, and alter commands. Most of the audit information is filled in+ * by log_utility_command().+ */+static void+pg_audit_object_access_hook(ObjectAccessType access,+ Oid classId,+ Oid objectId,+ int subId,+ void *arg)+{+ if (auditLogBitmap != 0 && !IsAbortedTransactionBlockState() &&+ auditLogBitmap & (LOG_DDL | LOG_FUNCTION) && auditEventStack)+ log_object_access(access, classId, objectId, subId, arg);++ if (next_object_access_hook)+ (*next_object_access_hook) (access, classId, objectId, subId, arg);+}++/*+ * Event trigger functions+ */++/*+ * Supply additional data for (non drop) statements that have event trigger+ * support and can be deparsed.+ */+Datum+pg_audit_ddl_command_end(PG_FUNCTION_ARGS)+{+#ifdef DEPARSE+ /* Continue only if session DDL logging is enabled */+ if (auditLogBitmap & LOG_DDL)+ {+ EventTriggerData *eventData;+ int result, row;+ TupleDesc spiTupDesc;+ const char *query;+ MemoryContext contextQuery;+ MemoryContext contextOld;++ /* Be sure the module was loaded */+ if (!auditEventStack)+ {+ elog(ERROR, "pg_audit not loaded before call to pg_audit_ddl_command_end()");+ }++ /* This is an internal statement - do not log it */+ internalStatement = true;++ /* Make sure the fuction was fired as a trigger */+ if (!CALLED_AS_EVENT_TRIGGER(fcinfo))+ elog(ERROR, "not fired by event trigger manager");++ /* Switch memory context */+ contextQuery = AllocSetContextCreate(+ CurrentMemoryContext,+ "pg_audit_func_ddl_command_end temporary context",+ ALLOCSET_DEFAULT_MINSIZE,+ ALLOCSET_DEFAULT_INITSIZE,+ ALLOCSET_DEFAULT_MAXSIZE);+ contextOld = MemoryContextSwitchTo(contextQuery);++ /* Get information about triggered events */+ eventData = (EventTriggerData *) fcinfo->context;++ auditEventStack->auditEvent.logStmtLevel =+ GetCommandLogLevel(eventData->parsetree);+ auditEventStack->auditEvent.commandTag =+ nodeTag(eventData->parsetree);+ auditEventStack->auditEvent.command =+ CreateCommandTag(eventData->parsetree);++ /* Return objects affected by the (non drop) DDL statement */+ query = "SELECT UPPER(object_type), identity\n"+ " FROM pg_event_trigger_ddl_commands()";++ /* Attempt to connect */+ result = SPI_connect();++ if (result < 0)+ elog(ERROR, "pg_audit_ddl_command_end: SPI_connect returned %d",+ result);++ /* Execute the query */+ result = SPI_execute(query, true, 0);++ if (result != SPI_OK_SELECT)+ elog(ERROR, "pg_audit_ddl_command_end: SPI_execute returned %d",+ result);++ /* Iterate returned rows */+ spiTupDesc = SPI_tuptable->tupdesc;++ for (row = 0; row < SPI_processed; row++)+ {+ HeapTuple spiTuple;++ spiTuple = SPI_tuptable->vals[row];++ /* Supply object name and type for audit event */+ auditEventStack->auditEvent.objectType =+ SPI_getvalue(spiTuple, spiTupDesc, 1);+ auditEventStack->auditEvent.objectName =+ SPI_getvalue(spiTuple, spiTupDesc, 2);++ /* Log the audit event */+ log_audit_event(auditEventStack);+ }++ /* Complete the query */+ SPI_finish();++ /* Switch to the old memory context */+ MemoryContextSwitchTo(contextOld);+ MemoryContextDelete(contextQuery);++ /* No longer in an internal statement */+ internalStatement = false;+ }+#endif++ PG_RETURN_NULL();+}++/*+ * Supply additional data for drop statements that have event trigger support.+ */+Datum+pg_audit_sql_drop(PG_FUNCTION_ARGS)+{+ if (auditLogBitmap & LOG_DDL)+ {+ int result, row;+ TupleDesc spiTupDesc;+ const char *query;+ MemoryContext contextQuery;+ MemoryContext contextOld;++ /* Be sure the module was loaded */+ if (!auditEventStack)+ {+ elog(ERROR, "pg_audit not loaded before call to pg_audit_sql_drop()");+ }++ /* This is an internal statement - do not log it */+ internalStatement = true;++ /* Make sure the fuction was fired as a trigger */+ if (!CALLED_AS_EVENT_TRIGGER(fcinfo))+ elog(ERROR, "not fired by event trigger manager");++ /* Switch memory context */+ contextQuery = AllocSetContextCreate(+ CurrentMemoryContext,+ "pg_audit_func_ddl_command_end temporary context",+ ALLOCSET_DEFAULT_MINSIZE,+ ALLOCSET_DEFAULT_INITSIZE,+ ALLOCSET_DEFAULT_MAXSIZE);+ contextOld = MemoryContextSwitchTo(contextQuery);++ /* Return objects affected by the drop statement */+ query = "SELECT classid, objid, objsubid, UPPER(object_type),\n"+ " schema_name, object_name, object_identity\n"+ " FROM pg_event_trigger_dropped_objects()";++ /* Attempt to connect */+ result = SPI_connect();++ if (result < 0)+ elog(ERROR, "pg_audit_ddl_drop: SPI_connect returned %d",+ result);++ /* Execute the query */+ result = SPI_execute(query, true, 0);++ if (result != SPI_OK_SELECT)+ elog(ERROR, "pg_audit_ddl_drop: SPI_execute returned %d",+ result);++ /* Iterate returned rows */+ spiTupDesc = SPI_tuptable->tupdesc;++ for (row = 0; row < SPI_processed; row++)+ {+ HeapTuple spiTuple;+ char *schemaName;++ spiTuple = SPI_tuptable->vals[row];++ auditEventStack->auditEvent.objectType =+ SPI_getvalue(spiTuple, spiTupDesc, 4);+ schemaName = SPI_getvalue(spiTuple, spiTupDesc, 5);++ if (!(pg_strcasecmp(auditEventStack->auditEvent.objectType,+ "TYPE") == 0 ||+ pg_strcasecmp(schemaName, "pg_toast") == 0))+ {+ auditEventStack->auditEvent.objectName =+ SPI_getvalue(spiTuple, spiTupDesc, 7);++ log_audit_event(auditEventStack);+ }+ }++ /* Complete the query */+ SPI_finish();++ /* Switch to the old memory context */+ MemoryContextSwitchTo(contextOld);+ MemoryContextDelete(contextQuery);++ /* No longer in an internal statement */+ internalStatement = false;+ }++ PG_RETURN_NULL();+}++/*+ * GUC check and assign functions+ */++/*+ * Take a pg_audit.log value such as "read, write, dml", verify that each of the+ * comma-separated tokens corresponds to a LogClass value, and convert them into+ * a bitmap that log_audit_event can check.+ */+static bool+check_pg_audit_log(char **newval, void **extra, GucSource source)+{+ List *flags;+ char *rawval;+ ListCell *lt;+ uint64 *f;++ /* Make sure newval is a comma-separated list of tokens. */+ rawval = pstrdup(*newval);+ if (!SplitIdentifierString(rawval, ',', &flags))+ {+ GUC_check_errdetail("List syntax is invalid");+ list_free(flags);+ pfree(rawval);+ return false;+ }++ /*+ * Check that we recognise each token, and add it to the bitmap we're+ * building up in a newly-allocated uint64 *f.+ */+ f = (uint64 *) malloc(sizeof(uint64));+ if (!f)+ return false;+ *f = 0;++ foreach(lt, flags)+ {+ bool subtract = false;+ uint64 class;++ /* Retrieve a token */+ char *token = (char *)lfirst(lt);++ /* If token is preceded by -, then the token is subtractive. */+ if (strstr(token, "-") == token)+ {+ token = token + 1;+ subtract = true;+ }++ /* Test each token. */+ if (pg_strcasecmp(token, CLASS_NONE) == 0)+ class = LOG_NONE;+ else if (pg_strcasecmp(token, CLASS_ALL) == 0)+ class = LOG_ALL;+ else if (pg_strcasecmp(token, CLASS_DDL) == 0)+ class = LOG_DDL;+ else if (pg_strcasecmp(token, CLASS_FUNCTION) == 0)+ class = LOG_FUNCTION;+ else if (pg_strcasecmp(token, CLASS_MISC) == 0)+ class = LOG_MISC;+ else if (pg_strcasecmp(token, CLASS_PARAMETER) == 0)+ class = LOG_PARAMETER;+ else if (pg_strcasecmp(token, CLASS_READ) == 0)+ class = LOG_READ;+ else if (pg_strcasecmp(token, CLASS_ROLE) == 0)+ class = LOG_ROLE;+ else if (pg_strcasecmp(token, CLASS_WRITE) == 0)+ class = LOG_WRITE;+ else+ {+ free(f);+ pfree(rawval);+ list_free(flags);+ return false;+ }++ /* Add or subtract class bits from the log bitmap. */+ if (subtract)+ *f &= ~class;+ else+ *f |= class;+ }++ pfree(rawval);+ list_free(flags);++ /*+ * Store the bitmap for assign_pg_audit_log.+ */+ *extra = f;++ return true;+}++/*+ * Set pg_audit_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_pg_audit_log(const char *newval, void *extra)+{+ if (extra)+ auditLogBitmap = *(uint64 *)extra;+}++/*+ * Define GUC variables and install hooks upon module load.+ */+void+_PG_init(void)+{+ if (IsUnderPostmaster)+ ereport(ERROR,+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),+ errmsg("pg_audit must be loaded via shared_preload_libraries")));++ /*+ * pg_audit.role = "audit"+ *+ * Defines a role to be used for auditing.+ */+ DefineCustomStringVariable("pg_audit.role",+ "Enable object auditing for role",+ NULL,+ &auditRole,+ "",+ PGC_SUSET,+ GUC_NOT_IN_SAMPLE,+ NULL, NULL, NULL);++ /*+ * pg_audit.log = "read, write, ddl"+ *+ * Controls what classes of commands are logged.+ */+ DefineCustomStringVariable("pg_audit.log",+ "Enable session auditing for classes of commands",+ NULL,+ &auditLog,+ "none",+ PGC_SUSET,+ GUC_LIST_INPUT | GUC_NOT_IN_SAMPLE,+ check_pg_audit_log,+ assign_pg_audit_log,+ NULL);++ /*+ * pg_audit.log_relation = on+ *+ * Controls whether relations get separate log entries during session+ * logging of READ and WRITE classes. This works as if all relations in the+ * database had been added to the audit role and provides a shortcut when+ * really detailed logging of absolutely every relation is required.+ */+ DefineCustomBoolVariable("pg_audit.log_relation",+ "Enable session relation logging",+ NULL,+ &auditLogRelation,+ false,+ PGC_SUSET,+ GUC_NOT_IN_SAMPLE,+ NULL, NULL, NULL);+++ /*+ * pg_audit.log_notice = on+ *+ * Audit logging is raised as notices that can be seen on the client. This is+ * intended for testing purposes.+ */+ DefineCustomBoolVariable("pg_audit.log_notice",+ "Raise a notice when logging",+ NULL,+ &auditLogNotice,+ false,+ PGC_SUSET,+ GUC_NOT_IN_SAMPLE,+ NULL, NULL, NULL);++ /*+ * Install our hook functions after saving the existing pointers to preserve+ * the chain.+ */+ next_ExecutorStart_hook = ExecutorStart_hook;+ ExecutorStart_hook = pg_audit_ExecutorStart_hook;++ next_ExecutorCheckPerms_hook = ExecutorCheckPerms_hook;+ ExecutorCheckPerms_hook = pg_audit_ExecutorCheckPerms_hook;++ next_ExecutorEnd_hook = ExecutorEnd_hook;+ ExecutorEnd_hook = pg_audit_ExecutorEnd_hook;++ next_ProcessUtility_hook = ProcessUtility_hook;+ ProcessUtility_hook = pg_audit_ProcessUtility_hook;++ next_object_access_hook = object_access_hook;+ object_access_hook = pg_audit_object_access_hook;+}diff --git a/contrib/pg_audit/pg_audit.conf b/contrib/pg_audit/pg_audit.confnew file mode 100644index 0000000..e9f4a22--- /dev/null+++ b/contrib/pg_audit/pg_audit.conf@@ -0,0 +1 @@+shared_preload_libraries = pg_auditdiff --git a/contrib/pg_audit/pg_audit.control b/contrib/pg_audit/pg_audit.controlnew file mode 100644index 0000000..6730c68--- /dev/null+++ b/contrib/pg_audit/pg_audit.control@@ -0,0 +1,5 @@+# pg_audit extension+comment = 'provides auditing functionality'+default_version = '1.0.0'+module_pathname = '$libdir/pg_audit'+relocatable = truediff --git a/contrib/pg_audit/sql/pg_audit.sql b/contrib/pg_audit/sql/pg_audit.sqlnew file mode 100644index 0000000..9b11d1a--- /dev/null+++ b/contrib/pg_audit/sql/pg_audit.sql@@ -0,0 +1,556 @@+-- Load pg_audit module+create extension pg_audit;++--+-- Create a superuser role that we know the name of for testing+CREATE USER super SUPERUSER;+\connect contrib_regression super;++--+-- Create auditor role+CREATE ROLE auditor;++--+-- Create first test user+CREATE USER user1;+ALTER ROLE user1 SET pg_audit.log = 'ddl, ROLE';+ALTER ROLE user1 SET pg_audit.log_notice = on;++--+-- Create, select, drop (select will not be audited)+\connect contrib_regression user1+CREATE TABLE public.test (id INT);+SELECT * FROM test;+DROP TABLE test;++--+-- Create second test user+\connect contrib_regression super++CREATE USER user2;+ALTER ROLE user2 SET pg_audit.log = 'Read, writE';+ALTER ROLE user2 SET pg_audit.log_notice = on;+ALTER ROLE user2 SET pg_audit.role = auditor;++\connect contrib_regression user2+CREATE TABLE test2 (id INT);+GRANT SELECT ON TABLE public.test2 TO auditor;++--+-- Role-based tests+CREATE TABLE test3+(+ id INT+);++SELECT count(*)+ FROM+(+ SELECT relname+ FROM pg_class+ LIMIT 1+) SUBQUERY;++SELECT *+ FROM test3, test2;++GRANT INSERT+ ON TABLE public.test3+ TO auditor;++--+-- Create a view to test logging+CREATE VIEW vw_test3 AS+SELECT *+ FROM test3;++GRANT SELECT+ ON vw_test3+ TO auditor;++--+-- Object logged because of:+-- select on vw_test3+-- select on test2+SELECT *+ FROM vw_test3, test2;++--+-- Object logged because of:+-- insert on test3+-- select on test2+WITH CTE AS+(+ SELECT id+ FROM test2+)+INSERT INTO test3+SELECT id+ FROM cte;++--+-- Object logged because of:+-- insert on test3+WITH CTE AS+(+ INSERT INTO test3 VALUES (1)+ RETURNING id+)+INSERT INTO test2+SELECT id+ FROM cte;++GRANT UPDATE ON TABLE public.test2 TO auditor;++--+-- Object logged because of:+-- insert on test3+-- update on test2+WITH CTE AS+(+ UPDATE test2+ SET id = 1+ RETURNING id+)+INSERT INTO test3+SELECT id+ FROM cte;++--+-- Object logged because of:+-- insert on test2+WITH CTE AS+(+ INSERT INTO test2 VALUES (1)+ RETURNING id+)+UPDATE test3+ SET id = cte.id+ FROM cte+ WHERE test3.id <> cte.id;++--+-- Change permissions of user 2 so that only object logging will be done+\connect contrib_regression super+alter role user2 set pg_audit.log = 'NONE';++\connect contrib_regression user2++--+-- Create test4 and add permissions+CREATE TABLE test4+(+ id int,+ name text+);++GRANT SELECT (name)+ ON TABLE public.test4+ TO auditor;++GRANT UPDATE (id)+ ON TABLE public.test4+ TO auditor;++GRANT insert (name)+ ON TABLE public.test4+ TO auditor;++--+-- Not object logged+SELECT id+ FROM public.test4;++--+-- Object logged because of:+-- select (name) on test4+SELECT name+ FROM public.test4;++--+-- Not object logged+INSERT INTO public.test4 (id)+ VALUES (1);++--+-- Object logged because of:+-- insert (name) on test4+INSERT INTO public.test4 (name)+ VALUES ('test');++--+-- Not object logged+UPDATE public.test4+ SET name = 'foo';++--+-- Object logged because of:+-- update (id) on test4+UPDATE public.test4+ SET id = 1;++--+-- Object logged because of:+-- update (name) on test4+-- update (name) takes precedence over select (name) due to ordering+update public.test4 set name = 'foo' where name = 'bar';++--+-- Drop test tables+DROP TABLE test2;+DROP VIEW vw_test3;+DROP TABLE test3;+DROP TABLE test4;++--+-- Change permissions of user 1 so that session logging will be done+\connect contrib_regression super+alter role user1 set pg_audit.log = 'DDL, READ';+\connect contrib_regression user1++--+-- Create table is session logged+CREATE TABLE public.account+(+ id INT,+ name TEXT,+ password TEXT,+ description TEXT+);++--+-- Select is session logged+SELECT *+ FROM account;++--+-- Insert is not logged+INSERT INTO account (id, name, password, description)+ VALUES (1, 'user1', 'HASH1', 'blah, blah');++--+-- Change permissions of user 1 so that only object logging will be done+\connect contrib_regression super+alter role user1 set pg_audit.log = 'none';+alter role user1 set pg_audit.role = 'auditor';+\connect contrib_regression user1++--+-- Auditor grants not logged+GRANT SELECT (password),+ UPDATE (name, password)+ ON TABLE public.account+ TO auditor;++--+-- Not object logged+SELECT id,+ name+ FROM account;++--+-- Object logged because of:+-- select (password) on account+SELECT password+ FROM account;++--+-- Not object logged+UPDATE account+ SET description = 'yada, yada';++--+-- Object logged because of:+-- update (password) on account+UPDATE account+ SET password = 'HASH2';++--+-- Change permissions of user 1 so that session relation logging will be done+\connect contrib_regression super+alter role user1 set pg_audit.log_relation = on;+alter role user1 set pg_audit.log = 'read, WRITE';+\connect contrib_regression user1++--+-- Not logged+create table ACCOUNT_ROLE_MAP+(+ account_id INT,+ role_id INT+);++--+-- Auditor grants not logged+GRANT SELECT+ ON TABLE public.account_role_map+ TO auditor;++--+-- Object logged because of:+-- select (password) on account+-- select on account_role_map+-- Session logged on all tables because log = read and log_relation = on+SELECT account.password,+ account_role_map.role_id+ FROM account+ INNER JOIN account_role_map+ on account.id = account_role_map.account_id;++--+-- Object logged because of:+-- select (password) on account+-- Session logged on all tables because log = read and log_relation = on+SELECT password+ FROM account;++--+-- Not object logged+-- Session logged on all tables because log = read and log_relation = on+UPDATE account+ SET description = 'yada, yada';++--+-- Object logged because of:+-- select (password) on account (in the where clause)+-- Session logged on all tables because log = read and log_relation = on+UPDATE account+ SET description = 'yada, yada'+ where password = 'HASH2';++--+-- Object logged because of:+-- update (password) on account+-- Session logged on all tables because log = read and log_relation = on+UPDATE account+ SET password = 'HASH2';++--+-- Change back to superuser to do exhaustive tests+\connect contrib_regression super+SET pg_audit.log = 'ALL';+SET pg_audit.log_notice = ON;+SET pg_audit.log_relation = ON;++--+-- Simple DO block+DO $$+BEGIN+ raise notice 'test';+END $$;++--+-- Create test schema+CREATE SCHEMA test;++--+-- Copy pg_class to stdout+COPY account TO stdout;++--+-- Create a table from a query+CREATE TABLE test.account_copy AS+SELECT *+ FROM account;++--+-- Copy from stdin to account copy+COPY test.account_copy from stdin;+1 user1 HASH2 yada, yada+\.++--+-- Test prepared statement+PREPARE pgclassstmt (oid) AS+SELECT *+ FROM account+ WHERE id = $1;++EXECUTE pgclassstmt (1);+DEALLOCATE pgclassstmt;++--+-- Test cursor - no tables will be logged since pg_class is a system table+BEGIN;++DECLARE ctest SCROLL CURSOR FOR+SELECT count(*)+ FROM+(+ SELECT relname+ FROM pg_class+ LIMIT 1+ ) subquery;++FETCH NEXT FROM ctest;+CLOSE ctest;+COMMIT;++--+-- Test prepared insert+CREATE TABLE test.test_insert+(+ id INT+);++PREPARE pgclassstmt (oid) AS+INSERT INTO test.test_insert (id)+ VALUES ($1);+EXECUTE pgclassstmt (1);++--+-- Check that primary key creation is logged+CREATE TABLE public.test+(+ id INT,+ name TEXT,+ description TEXT,+ CONSTRAINT test_pkey PRIMARY KEY (id)+);++--+-- Check that analyze is logged+ANALYZE test;++--+-- Grants to public should not cause object logging (session logging will+-- still happen)+GRANT SELECT+ ON TABLE public.test+ TO PUBLIC;++SELECT *+ FROM test;++-- Check that statements without columns log+SELECT+ FROM test;++SELECT 1,+ current_user;++DO $$+DECLARE+ test INT;+BEGIN+ SELECT 1+ INTO test;+END $$;++explain select 1;++--+-- Test that looks inside of do blocks log+INSERT INTO TEST (id)+ VALUES (1);+INSERT INTO TEST (id)+ VALUES (2);+INSERT INTO TEST (id)+ VALUES (3);++DO $$+DECLARE+ result RECORD;+BEGIN+ FOR result IN+ SELECT id+ FROM test+ LOOP+ INSERT INTO test (id)+ VALUES (result.id + 100);+ END LOOP;+END $$;++--+-- Test obfuscated dynamic sql for clean logging+DO $$+DECLARE+ table_name TEXT = 'do_table';+BEGIN+ EXECUTE 'CREATE TABLE ' || table_name || ' ("weird name" INT)';+ EXECUTE 'DROP table ' || table_name;+END $$;++--+-- Generate an error and make sure the stack gets cleared+DO $$+BEGIN+ CREATE TABLE bogus.test_block+ (+ id INT+ );+END $$;++--+-- Test alter table statements+ALTER TABLE public.test+ DROP COLUMN description ;++ALTER TABLE public.test+ RENAME TO test2;++ALTER TABLE public.test2+ SET SCHEMA test;++ALTER TABLE test.test2+ ADD COLUMN description TEXT;++ALTER TABLE test.test2+ DROP COLUMN description;++DROP TABLE test.test2;++--+-- Test multiple statements with one semi-colon+CREATE SCHEMA foo+ CREATE TABLE foo.bar (id int)+ CREATE TABLE foo.baz (id int);++--+-- Test aggregate+CREATE FUNCTION public.int_add+(+ a INT,+ b INT+)+ RETURNS INT LANGUAGE plpgsql AS $$+BEGIN+ return a + b;+END $$;++SELECT int_add(1, 1);++CREATE AGGREGATE public.sum_test(INT) (SFUNC=public.int_add, STYPE=INT, INITCOND='0');+ALTER AGGREGATE public.sum_test(integer) RENAME TO sum_test2;++--+-- Test conversion+CREATE CONVERSION public.conversion_test FOR 'SQL_ASCII' TO 'MULE_INTERNAL' FROM pg_catalog.ascii_to_mic;+ALTER CONVERSION public.conversion_test RENAME TO conversion_test2;++--+-- Test create/alter/drop database+CREATE DATABASE contrib_regression_pgaudit;+ALTER DATABASE contrib_regression_pgaudit RENAME TO contrib_regression_pgaudit2;+DROP DATABASE contrib_regression_pgaudit2;++--+-- Test that frees a memory context earlier than expected+CREATE TABLE hoge+(+ id int+);++CREATE FUNCTION test()+ RETURNS INT AS $$+DECLARE+ cur1 cursor for select * from hoge;+ tmp int;+BEGIN+ OPEN cur1;+ FETCH cur1 into tmp;+ RETURN tmp;+END $$+LANGUAGE plpgsql ;++SELECT test();diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgmlindex 5773095..20a4e62 100644--- a/doc/src/sgml/contrib.sgml+++ b/doc/src/sgml/contrib.sgml@@ -124,6 +124,7 @@ CREATE EXTENSION <replaceable>module_name</> FROM unpackaged;
<ree;
&pageinspect;
&passwordcheck;
+ &pgaudit;
&pgbuffercache;
&pgcrypto;
&pgfreespacemap;
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgmlindex ab935a6..7f39f42 100644--- a/doc/src/sgml/filelist.sgml+++ b/doc/src/sgml/filelist.sgml@@ -125,6 +125,7 @@
<!ENTITY oid2name SYSTEM "oid2name.sgml">
<!ENTITY pageinspect SYSTEM "pageinspect.sgml">
<!ENTITY passwordcheck SYSTEM "passwordcheck.sgml">
+<!ENTITY pgaudit SYSTEM "pgaudit.sgml">
<!ENTITY pgbuffercache SYSTEM "pgbuffercache.sgml">
<!ENTITY pgcrypto SYSTEM "pgcrypto.sgml">
<!ENTITY pgfreespacemap SYSTEM "pgfreespacemap.sgml">
diff --git a/doc/src/sgml/pgaudit.sgml b/doc/src/sgml/pgaudit.sgmlnew file mode 100644index 0000000..ba04e7b--- /dev/null+++ b/doc/src/sgml/pgaudit.sgml@@ -0,0 +1,614 @@+<!-- doc/src/sgml/pgaudit.sgml -->++<sect1 id="pgaudit" xreflabel="pgaudit">+ <title>pg_audit</title>++ <indexterm zone="pgaudit">+ <primary>pg_audit</primary>+ </indexterm>++ <para>+ The <filename>pg_audit</filename> extenstion provides detailed session+ and/or object audit logging via the standard logging facility. The goal+ is to provide the tools needed to produce audit logs required to pass any+ goverment, financial, or ISO certification audit.+ </para>++ <para>+ An audit is an official inspection of an individual's or organization's+ accounts, typically by an independent body. The information gathered by+ <filename>pg_audit</filename> is properly called an audit trail or audit+ log. The term audit log is used in this documentation.+ </para>++ <sect2>+ <title>Why <literal>pg_audit</>?</title>+ + <para>+ Basic statement logging can be provided by the standard logging facility+ using <literal>log_statements = all</>. This is acceptable for monitoring+ and other usages but does not provide the level of detail generally+ required for an audit. It is not enough to have a list of all the+ operations performed against the database. It must also be possible to+ find particular statements that are of interest to an auditor.+ </para>++ <para>+ For example, an auditor may want to verify that a particular table was+ created inside a documented maintence window. This might seem like a+ simple job for grep, but what if you are presented with something like+ this (intentionally obfuscated) example:+ </para>++ <programlisting>+DO $$+BEGIN+ EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';+END $$;+ </programlisting>+ + <para>+ Standard logging will give you this:+ </para>++ <programlisting>+LOG: statement: DO $$+BEGIN+ EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';+END $$;+ </programlisting>+ + <para>+ It appears that finding the table of interest may require some knowledge+ of the code in cases where tables are created dynamically. This is not+ ideal since it would be preferrable to just search on the table name.+ This is where <literal>pg_audit</> comes in. For the same input,+ it will produce this output in the log:+ </para>++ <programlisting>+AUDIT: SESSION,33,1,FUNCTION,DO,,,"DO $$+BEGIN+ EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';+END $$;"+AUDIT: SESSION,33,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT)+ </programlisting>++ <para>+ Not only is the <literal>DO</> block logged, but substatement 2 contains+ the full text of the <literal>CREATE TABLE</> with the statement type,+ object type, and full-qualified name to make searches easy.+ </para>++ <para>+ When logging <literal>SELECT</> and <literal>DML</> statements,+ <literal>pg_audit</> can be configured to log a separate entry for each+ relation referenced in a statement. No parsing is required to find all+ statements that touch a particular table. In fact, the goal is that the+ statement text is provided primarily for deep forensics and should not be+ the directly required for any search.+ </para>+ </sect2>++ <sect2>+ <title>Usage Considerations</title>+ + <para>+ Depending on settings, it is possible for <literal>pg_audit</literal> to+ generate an enormous volume of logging. Be careful to determine+ exactly what needs to be audit logged in your environment to avoid+ logging too much.+ </para>+ + <para>+ For example, when working in an OLAP environment it would probably not be+ wise to audit log inserts into a large fact table. The size of the log+ file will likely be many times the actual data size of the inserts because+ the log file is expressed as text. Since logs are generally stored with+ the OS this may lead to disk space being exhausted very+ quickly. In cases where it is not possible to limit audit logging to+ certain tables, be sure to assess the performance impact while testing+ and allocate plenty of space on the log volume. This may also be true for+ OLTP environments. Even if the insert volume is not as high, the+ performance impact of audit logging may still noticeably affect latency.+ </para>+ + <para>+ To limit the number of relations audit logged for <literal>SELECT</>+ and <literal>DML</> statments, consider using object audit logging+ (see <xref linkend="pgaudit-object-audit-logging">). Object audit logging+ allows selection of the relations to be logged allowing for reduction+ of the overall log volume. However, when new relations are added they+ must be explicitly added to object audit logging. A programmatic+ solution where specified tables are excluded from logging and all others+ are included may be a good option in this case.+ </para>+ </sect2>+ + <sect2>+ <title>Settings</title>+ + <para>+ Settings may be modified only by a superuser. Allowing normal users to+ change their settings would defeat the point of an audit log.+ </para>+ + <para>+ Settings can be specified globally (in+ <filename>postgresql.conf</filename> or using+ <literal>ALTER SYSTEM ... SET</>), at the database level (using+ <literal>ALTER DATABASE ... SET</literal>), or at the role level (using+ <literal>ALTER ROLE ... SET</literal>). Note that settings are not+ inherited through normal role inheritance and <literal>SET ROLE</> will+ not alter a user's <literal>pg_audit</> settings. This is a limitation+ of the roles system and not inherent to <literal>pg_audit</>.+ </para>+ + <para>+ The <literal>pg_audit</> extension must be loaded in+ <xref linkend="guc-shared-preload-libraries">. Otherwise, an error+ will be raised at load time and no audit logging will occur.+ </para>++ <variablelist>+ <varlistentry id="guc-pgaudit-log" xreflabel="pg_audit.log">+ <term><varname>pg_audit.log</varname> (<type>string</type>)+ <indexterm>+ <primary><varname>pg_audit.log</> configuration parameter</primary>+ </indexterm>+ </term>+ <listitem>+ <para>+ Specifies which classes of statements will be logged by session+ audit logging. Possible values are:+ </para>++ <itemizedlist>+ <listitem>+ <para>+ <literal>READ</literal> - <literal>SELECT</literal> and+ <literal>COPY</literal> when the source is a relation or a+ query.+ </para>+ </listitem>+ <listitem>+ <para>+ <literal>WRITE</literal> - <literal>INSERT</literal>,+ <literal>UPDATE</literal>, <literal>DELETE</literal>,+ <literal>TRUNCATE</literal>, and <literal>COPY</literal> when the+ destination is a relation.+ </para>+ </listitem>+ <listitem>+ <para>+ <literal>FUNCTION</literal> - Function calls and+ <literal>DO</literal> blocks.+ </para>+ </listitem>+ <listitem>+ <para>+ <literal>ROLE</literal> - Statements related to roles and+ privileges: <literal>GRANT</literal>,+ <literal>REVOKE</literal>,+ <literal>CREATE/ALTER/DROP ROLE</literal>.+ </para>+ </listitem>+ <listitem>+ <para>+ <literal>DDL</literal> - All <literal>DDL</> that is not included+ in the <literal>ROLE</> class plus <literal>REINDEX</>.+ </para>+ </listitem>+ <listitem>+ <para>+ <literal>PARAMETER</literal> - Parameters that were passed for the+ statement. Parameters immediately follow the statement text.+ </para>+ </listitem>+ <listitem>+ <para>+ <literal>MISC</literal> - Miscellaneous commands, e.g.+ <literal>DISCARD</literal>, <literal>FETCH</literal>,+ <literal>CHECKPOINT</literal>, <literal>VACUUM</literal>.+ </para>+ </listitem>+ </itemizedlist>+ + <para>+ Multiple classes can be provided using a comma-separated list and+ classes can be subtracted by prefacing the class with a+ <literal>-</> sign (see <xref linkend="pgaudit-session-audit-logging">).+ The default is <literal>none</>.+ </para>+ </listitem>+ </varlistentry>++ <varlistentry id="guc-pgaudit-log-notice" xreflabel="pg_audit.log_notice">+ <term><varname>pg_audit.log_notice</varname> (<type>boolean</type>)+ <indexterm>+ <primary><varname>pg_audit.log_notice</> configuration parameter</primary>+ </indexterm>+ </term>+ <listitem>+ <para>+ Specifies that the audit log messages should be raised as+ <literal>NOTICE</> instead of <literal>LOG</>. The primary+ advantage is that <literal>NOTICE</> messages can be exposed+ through the user interface. This setting is used for regression+ testing and may also be useful to end users for testing. It is not+ intended to be used in a production environment as it will leak+ which statements are being logged to the user. The default is+ <literal>off</>.+ </para>+ </listitem>+ </varlistentry>++ <varlistentry id="guc-pgaudit-log-relation" xreflabel="pg_audit.log_relation">+ <term><varname>pg_audit.log_relation</varname> (<type>boolean</type>)+ <indexterm>+ <primary><varname>pg_audit.log_relation</> configuration parameter</primary>+ </indexterm>+ </term>+ <listitem>+ <para>+ Specifies whether session audit logging should create a separate+ log entry for each relation (<literal>TABLE</>, <literal>VIEW</>,+ etc.) referenced in a <literal>SELECT</> or <literal>DML</>+ statement. This is a useful shortcut for exhaustive logging+ without using object audit logging. The default is+ <literal>off</>.+ </para>+ </listitem>+ </varlistentry>++ <varlistentry id="guc-pgaudit-role" xreflabel="pg_audit.role">+ <term><varname>pg_audit.role</varname> (<type>string</type>)+ <indexterm>+ <primary><varname>pg_audit.role</> configuration parameter</primary>+ </indexterm>+ </term>+ <listitem>+ <para>+ Specifies the master role to use for object audit logging. Muliple+ audit roles can be defined by granting them to the master role.+ This allows multiple groups to be in charge of different aspects+ of audit logging. There is no default.+ </para>+ </listitem>+ </varlistentry>+ </variablelist>+ </sect2>++ <sect2 id="pgaudit-session-audit-logging">+ <title>Session Audit Logging</title>++ <para>+ Session audit logging provides detailed logs of all statements executed+ by a user in the backend.+ </para>++ <sect3>+ <title>Configuration</title>++ <para>+ Session logging is enabled with the <xref linkend="guc-pgaudit-log">+ setting.++ Enable session logging for all <literal>DML</> and <literal>DDL</> and+ log all relations in <literal>DML</> statements:+ <programlisting>+set pg_audit.log = 'write, ddl';+set pg_audit.log_relation = on;+ </programlisting>+ </para>++ <para>+ Enable session logging for all commands except <literal>MISC</> and+ raise audit log messages as <literal>NOTICE</>:+ <programlisting>+set pg_audit.log = 'all, -misc';+set pg_audit.log_notice = on;+ </programlisting>+ </para>+ </sect3>++ <sect3>+ <title>Example</title>++ <para>+ In this example session audit logging is used to for logging+ <literal>DDL</> and <literal>SELECT</> statements. Note that the+ insert statement is not logged since the <literal>WRITE</> class+ is not enabled+ </para>++ <para>+ SQL:+ </para>+ <programlisting>+set pg_audit.log = 'read, ddl';++create table account+(+ id int,+ name text,+ password text,+ description text+);++insert into account (id, name, password, description)+ values (1, 'user1', 'HASH1', 'blah, blah');++select *+ from account;+ </programlisting>++ <para>+ Log Output:+ </para>++ <programlisting>+AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account+(+ id int,+ name text,+ password text,+ description text+);+AUDIT: SESSION,2,1,READ,SELECT,,,select *+ from account+ </programlisting>+ </sect3>+ </sect2>++ <sect2 id="pgaudit-object-audit-logging">+ <title>Object Auditing</title>++ <para>+ Object audit logging logs statements that affect a particular relation.+ Only <literal>SELECT</>, <literal>INSERT</>, <literal>UPDATE</> and+ <literal>DELETE</> commands are supported. <literal>TRUNCATE</> is not+ included because there is no specific privilege for it.+ </para>++ <sect3>+ <title>Configuration</title>++ <para>+ Object-level audit logging is implemented via the roles system. The+ <xref linkend="guc-pgaudit-role"> setting defines the role that+ will be used for audit logging. A relation (<literal>TABLE</>,+ <literal>VIEW</>, etc.) will be audit logged when the audit role has+ permissions for the command executed or inherits the permissions from+ another role. This allows you to effectively have multiple audit roles+ even though there is a single master role in any context.+ </para>++ <para>+ Set <xref linkend="guc-pgaudit-role"> to <literal>auditor</> and+ grant <literal>SELECT</> and <literal>DELETE</> privileges on the+ <literal>account</> table. Any <literal>SELECT</> or+ <literal>DELETE</> statements on <literal>account</> will now be+ logged:+ </para>+ + <programlisting>+set pg_audit.role = 'auditor';++grant select, delete+ on public.account+ to auditor;+ </programlisting>+ </sect3>++ <sect3>+ <title>Example</title>++ <para>+ In this example object audit logging is used to illustrate how a+ granular approach may be taken towards logging of <literal>SELECT</>+ and <literal>DML</> statements. Note that logging on the+ <literal>account</> table is controlled by column-level permissions,+ while logging on <literal>account_role_map</> is table-level.+ </para>++ <para>+ SQL:+ </para>++ <programlisting>+set pg_audit.role = 'auditor';++create table account+(+ id int,+ name text,+ password text,+ description text+);++grant select (password)+ on public.account+ to auditor;++select id, name+ from account;++select password+ from account;++grant update (name, password)+ on public.account+ to auditor;++update account+ set description = 'yada, yada';++update account+ set password = 'HASH2';++create table account_role_map+(+ account_id int,+ role_id int+);++grant select+ on public.account_role_map+ to auditor;++select account.password,+ account_role_map.role_id+ from account+ inner join account_role_map+ on account.id = account_role_map.account_id+ </programlisting>++ <para>+ Log Output:+ </para>++ <programlisting>+AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,select password+ from account+AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,update account+ set password = 'HASH2'+AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account,select account.password,+ account_role_map.role_id+ from account+ inner join account_role_map+ on account.id = account_role_map.account_id+AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account_role_map,select account.password,+ account_role_map.role_id+ from account+ inner join account_role_map+ on account.id = account_role_map.account_id+ </programlisting>+ </sect3>+ </sect2>++ <sect2>+ <title>Format</title>++ <para>+ Audit entries are written to the standard logging facility and contain+ the following columns in comma-separated format:++ <note>+ <para>+ Output is compliant CSV format only if the log line prefix portion+ of each log entry is removed.+ </para>+ </note>++ <itemizedlist>+ <listitem>+ <para>+ <literal>AUDIT_TYPE</> - <literal>SESSION</> or+ <literal>OBJECT</>.+ </para>+ </listitem>+ <listitem>+ <para>+ <literal>STATEMENT_ID</> - Unique statement ID for this session.+ Each statement ID represents a backend call. Statement IDs are+ sequental even if some statements are not logged. There may be+ multiple entries for a statement ID when more than one relation+ is logged.+ </para>+ </listitem>+ <listitem>+ <para>+ <literal>SUBSTATEMENT_ID</> - Sequential ID for each+ substatement within the main statement. For example, calling+ a function from a query. Substatement IDs are continuous+ even if some substatements are not logged. There may be multiple+ entries for a substatement ID when more than one relation is logged.+ </para>+ </listitem>+ <listitem>+ <para>+ <literal>CLASS</> - e.g. (<literal>READ</>,+ <literal>ROLE</>) (see <xref linkend="guc-pgaudit-log">).+ </para>+ </listitem>+ <listitem>+ <para>+ <literal>COMMAND</> - e.g. <literal>ALTER TABLE</>,+ <literal>SELECT</>.+ </para>+ </listitem>+ <listitem>+ <para>+ <literal>OBJECT_TYPE</> - <literal>TABLE</>,+ <literal>INDEX</>, <literal>VIEW</>, etc.+ Available for <literal>SELECT</>, <literal>DML</> and most+ <literal>DDL</> statements.+ </para>+ </listitem>+ <listitem>+ <para>+ <literal>OBJECT_NAME</> - The fully-qualified object name+ (e.g. public.account). Available for <literal>SELECT</>,+ <literal>DML</> and most <literal>DDL</> statements.+ </para>+ </listitem>+ <listitem>+ <para>+ <literal>STATEMENT</> - Statement executed on the backend.+ </para>+ </listitem>+ </itemizedlist>+ </para>++ <para>+ Use <xref linkend="guc-log-line-prefix"> to add any other fields that+ are needed to satisfy your audit log requirements. A typical log line+ prefix might be <literal>'%m %u %d: '</> which would provide the date/time,+ user name, and database name for each audit log.+ </para>+ </sect2>++ <sect2>+ <title>Caveats</title>+ + <itemizedlist>+ <listitem>+ <para>+ Object renames are logged under the name they were renamed to.+ For example, renaming a table will produce the following result:+ </para>++ <programlisting>+ALTER TABLE test RENAME TO test2;+ +AUDIT: SESSION,36,1,DDL,ALTER TABLE,TABLE,public.test2,ALTER TABLE test RENAME TO test2+ </programlisting>+ </listitem>+ + <listitem>+ <para>+ It is possible to have a command logged more than once. For example,+ when a table is created with a primary key specified at creation time+ the index for the primary key will be logged independently and another+ audit log will be made for the index under the create entry. The+ multiple entries will however be contained within one statement ID.+ </para>+ </listitem>++ <listitem>+ <para>+ Autovacuum and Autoanalyze are not logged, nor are they intended to be.+ </para>+ </listitem>+ </itemizedlist>+ </sect2>++ <sect2>+ <title>Authors</title>++ <para>+ Abhijit Menon-Sen <email>ams@2ndQuadrant.com</email>, Ian Barwick <email>ian@2ndQuadrant.com</email>, and David Steele <email>david@pgmasters.net</email>.+ </para>+ </sect2>+</sect1>
signature.asc (application/pgp-signature, 818B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: david@pgmasters.net, sawada.mshk@gmail.com, ishii@postgresql.org, simon@2ndquadrant.com, ams@2ndquadrant.com, masao.fujii@gmail.com
Subject: Re: Auditing extension for PostgreSQL (Take 2)
In-Reply-To: <552EA121.1090603@pgmasters.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox