From: David Steele <david@pgmasters.net>
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: simon@2ndQuadrant.com
Cc: pgsql-hackers@postgresql.org
Cc: sawada.mshk@gmail.com
Cc: peter_e@gmx.net
Cc: alvherre@2ndquadrant.com
Cc: ams@2ndQuadrant.com
Cc: masao.fujii@gmail.com
Subject: Re: Auditing extension for PostgreSQL (Take 2)
Date: Tue, 14 Apr 2015 19:57:13 -0400
Message-ID: <552DA959.2010102@pgmasters.net> (raw)
In-Reply-To: <20150415.081354.1167351714960358354.t-ishii@sraoss.co.jp>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 4/14/15 7:13 PM, Tatsuo Ishii wrote:
> This patch does not apply cleanly due to the moving of pgbench (patch
> to filelist.sgml failed).
Thank you for pointing that out!
Ironic that it was the commit directly after the one I was testing with
that broke the patch. It appears the end of the last CF is a very bad
time to be behind HEAD.
Fixed in attached v8 patch.
--
- David Steele
david@pgmasters.net
Attachments:pg_audit-v8.patch (text/plain, 129.2K) 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..66fd20d--- /dev/null+++ b/contrib/pg_audit/expected/pg_audit-deparse.out@@ -0,0 +1,897 @@+-- 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;+--+-- 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,2,1,WRITE,INSERT,,,"WITH CTE AS+(+ SELECT id+ FROM test2+)+INSERT INTO test3+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,public.test3,"WITH CTE AS+(+ SELECT id+ FROM test2+)+INSERT INTO test3+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,2,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,3,1,WRITE,INSERT,,,"WITH CTE AS+(+ INSERT INTO test3 VALUES (1)+ RETURNING id+)+INSERT INTO test2+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,3,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,4,1,WRITE,INSERT,,,"WITH CTE AS+(+ UPDATE test2+ SET id = 1+ RETURNING id+)+INSERT INTO test3+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,4,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,4,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,5,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,5,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 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;+NOTICE: AUDIT: SESSION,13,1,READ,DECLARE CURSOR,,,"DECLARE ctest SCROLL CURSOR FOR+SELECT count(*)+ FROM+(+ SELECT relname+ FROM pg_class+ LIMIT 1+ ) subquery;"+FETCH NEXT FROM ctest;+NOTICE: AUDIT: SESSION,14,1,MISC,FETCH,,,FETCH NEXT FROM ctest;+ count +-------+ 1+(1 row)++CLOSE ctest;+NOTICE: AUDIT: SESSION,15,1,MISC,CLOSE CURSOR,,,CLOSE ctest;+COMMIT;+NOTICE: AUDIT: SESSION,15,2,MISC,COMMIT,,,COMMIT;+--+-- Test prepared insert+CREATE TABLE test.test_insert+(+ id INT+);+NOTICE: AUDIT: SESSION,16,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,17,1,WRITE,PREPARE,,,"PREPARE pgclassstmt (oid) AS+INSERT INTO test.test_insert (id)+ VALUES ($1);"+EXECUTE pgclassstmt (1);+NOTICE: AUDIT: SESSION,18,1,WRITE,INSERT,TABLE,test.test_insert,"PREPARE pgclassstmt (oid) AS+INSERT INTO test.test_insert (id)+ VALUES ($1);",1+NOTICE: AUDIT: SESSION,18,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,19,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,19,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,19,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,20,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,21,1,ROLE,GRANT,TABLE,,"GRANT SELECT+ ON TABLE public.test+ TO PUBLIC;"+SELECT *+ FROM test;+NOTICE: AUDIT: SESSION,22,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,23,1,READ,SELECT,TABLE,public.test,"SELECT+ FROM test;"+--+(0 rows)++SELECT 1,+ current_user;+NOTICE: AUDIT: SESSION,24,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,25,1,FUNCTION,DO,,,"DO $$+DECLARE+ test INT;+BEGIN+ SELECT 1+ INTO test;+END $$;"+NOTICE: AUDIT: SESSION,25,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,26,1,READ,SELECT,,,explain select 1;+NOTICE: AUDIT: SESSION,26,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,27,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)+ VALUES (1);"+INSERT INTO TEST (id)+ VALUES (2);+NOTICE: AUDIT: SESSION,28,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)+ VALUES (2);"+INSERT INTO TEST (id)+ VALUES (3);+NOTICE: AUDIT: SESSION,29,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,30,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,30,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,30,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,30,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,30,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 cursors and functions in a do block+CREATE FUNCTION public.test()+ RETURNS INT LANGUAGE plpgsql AS $$+DECLARE+ cur1 CURSOR FOR SELECT * FROM test;+ tmp INT;+BEGIN+ OPEN cur1;+ FETCH cur1 INTO tmp;+ CLOSE cur1;+ RETURN tmp;+end $$;+NOTICE: AUDIT: SESSION,31,1,DDL,CREATE FUNCTION,FUNCTION,public.test(),"CREATE FUNCTION public.test()+ RETURNS INT LANGUAGE plpgsql AS $$+DECLARE+ cur1 CURSOR FOR SELECT * FROM test;+ tmp INT;+BEGIN+ OPEN cur1;+ FETCH cur1 INTO tmp;+ CLOSE cur1;+ RETURN tmp;+end $$;"+SELECT public.test();+NOTICE: AUDIT: SESSION,32,1,READ,SELECT,,,SELECT public.test();+NOTICE: AUDIT: SESSION,32,2,FUNCTION,EXECUTE,FUNCTION,public.test,SELECT public.test();+NOTICE: AUDIT: SESSION,32,3,READ,SELECT,TABLE,public.test,SELECT * FROM test+CONTEXT: PL/pgSQL function test() line 6 at OPEN+ test +------+ 1+(1 row)++--+-- 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,33,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,33,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,33,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,34,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,35,1,DDL,ALTER TABLE,TABLE COLUMN,public.test.description,"ALTER TABLE public.test+ DROP COLUMN description ;"+NOTICE: AUDIT: SESSION,35,1,DDL,ALTER TABLE,TABLE,public.test,"ALTER TABLE public.test+ DROP COLUMN description ;"+ALTER TABLE public.test+ RENAME TO test2;+NOTICE: AUDIT: SESSION,36,1,DDL,ALTER TABLE,TABLE,public.test2,"ALTER TABLE public.test+ RENAME TO test2;"+ALTER TABLE public.test2+ SET SCHEMA test;+NOTICE: AUDIT: SESSION,37,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,38,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,39,1,DDL,ALTER TABLE,TABLE COLUMN,test.test2.description,"ALTER TABLE test.test2+ DROP COLUMN description;"+NOTICE: AUDIT: SESSION,39,1,DDL,ALTER TABLE,TABLE,test.test2,"ALTER TABLE test.test2+ DROP COLUMN description;"+DROP TABLE test.test2;+NOTICE: AUDIT: SESSION,40,1,DDL,DROP TABLE,TABLE,test.test2,DROP TABLE test.test2;+NOTICE: AUDIT: SESSION,40,1,DDL,DROP TABLE,TABLE CONSTRAINT,test_pkey on test.test2,DROP TABLE test.test2;+NOTICE: AUDIT: SESSION,40,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,41,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,41,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,41,3,DDL,CREATE SCHEMA,SCHEMA,foo,"CREATE SCHEMA foo+ CREATE TABLE foo.bar (id int)+ CREATE TABLE foo.baz (id int);"+NOTICE: AUDIT: SESSION,41,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,41,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,42,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,43,1,READ,SELECT,,,"SELECT int_add(1, 1);"+NOTICE: AUDIT: SESSION,43,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,44,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,45,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,46,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,47,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,48,1,DDL,CREATE DATABASE,,,CREATE DATABASE contrib_regression_pgaudit;+ALTER DATABASE contrib_regression_pgaudit RENAME TO contrib_regression_pgaudit2;+NOTICE: AUDIT: SESSION,49,1,DDL,ALTER DATABASE,,,ALTER DATABASE contrib_regression_pgaudit RENAME TO contrib_regression_pgaudit2;+DROP DATABASE contrib_regression_pgaudit2;+NOTICE: AUDIT: SESSION,50,1,DDL,DROP DATABASE,,,DROP DATABASE contrib_regression_pgaudit2;diff --git a/contrib/pg_audit/expected/pg_audit.out b/contrib/pg_audit/expected/pg_audit.outnew file mode 100644index 0000000..987d43a--- /dev/null+++ b/contrib/pg_audit/expected/pg_audit.out@@ -0,0 +1,880 @@+-- 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;+--+-- 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,2,1,WRITE,INSERT,,,"WITH CTE AS+(+ SELECT id+ FROM test2+)+INSERT INTO test3+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,public.test3,"WITH CTE AS+(+ SELECT id+ FROM test2+)+INSERT INTO test3+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,2,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,3,1,WRITE,INSERT,,,"WITH CTE AS+(+ INSERT INTO test3 VALUES (1)+ RETURNING id+)+INSERT INTO test2+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,3,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,4,1,WRITE,INSERT,,,"WITH CTE AS+(+ UPDATE test2+ SET id = 1+ RETURNING id+)+INSERT INTO test3+SELECT id+ FROM cte;"+NOTICE: AUDIT: OBJECT,4,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,4,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,5,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,5,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 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;+NOTICE: AUDIT: SESSION,13,1,READ,DECLARE CURSOR,,,"DECLARE ctest SCROLL CURSOR FOR+SELECT count(*)+ FROM+(+ SELECT relname+ FROM pg_class+ LIMIT 1+ ) subquery;"+FETCH NEXT FROM ctest;+NOTICE: AUDIT: SESSION,14,1,MISC,FETCH,,,FETCH NEXT FROM ctest;+ count +-------+ 1+(1 row)++CLOSE ctest;+NOTICE: AUDIT: SESSION,15,1,MISC,CLOSE CURSOR,,,CLOSE ctest;+COMMIT;+NOTICE: AUDIT: SESSION,15,2,MISC,COMMIT,,,COMMIT;+--+-- Test prepared insert+CREATE TABLE test.test_insert+(+ id INT+);+NOTICE: AUDIT: SESSION,16,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,17,1,WRITE,PREPARE,,,"PREPARE pgclassstmt (oid) AS+INSERT INTO test.test_insert (id)+ VALUES ($1);"+EXECUTE pgclassstmt (1);+NOTICE: AUDIT: SESSION,18,1,WRITE,INSERT,TABLE,test.test_insert,"PREPARE pgclassstmt (oid) AS+INSERT INTO test.test_insert (id)+ VALUES ($1);",1+NOTICE: AUDIT: SESSION,18,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,19,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,19,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,20,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,21,1,ROLE,GRANT,,,"GRANT SELECT+ ON TABLE public.test+ TO PUBLIC;"+SELECT *+ FROM test;+NOTICE: AUDIT: SESSION,22,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,23,1,READ,SELECT,TABLE,public.test,"SELECT+ FROM test;"+--+(0 rows)++SELECT 1,+ current_user;+NOTICE: AUDIT: SESSION,24,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,25,1,FUNCTION,DO,,,"DO $$+DECLARE+ test INT;+BEGIN+ SELECT 1+ INTO test;+END $$;"+NOTICE: AUDIT: SESSION,25,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,26,1,READ,SELECT,,,explain select 1;+NOTICE: AUDIT: SESSION,26,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,27,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)+ VALUES (1);"+INSERT INTO TEST (id)+ VALUES (2);+NOTICE: AUDIT: SESSION,28,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)+ VALUES (2);"+INSERT INTO TEST (id)+ VALUES (3);+NOTICE: AUDIT: SESSION,29,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,30,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,30,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,30,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,30,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,30,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 cursors and functions in a do block+CREATE FUNCTION public.test()+ RETURNS INT LANGUAGE plpgsql AS $$+DECLARE+ cur1 CURSOR FOR SELECT * FROM test;+ tmp INT;+BEGIN+ OPEN cur1;+ FETCH cur1 INTO tmp;+ CLOSE cur1;+ RETURN tmp;+end $$;+NOTICE: AUDIT: SESSION,31,1,DDL,CREATE FUNCTION,,,"CREATE FUNCTION public.test()+ RETURNS INT LANGUAGE plpgsql AS $$+DECLARE+ cur1 CURSOR FOR SELECT * FROM test;+ tmp INT;+BEGIN+ OPEN cur1;+ FETCH cur1 INTO tmp;+ CLOSE cur1;+ RETURN tmp;+end $$;"+SELECT public.test();+NOTICE: AUDIT: SESSION,32,1,READ,SELECT,,,SELECT public.test();+NOTICE: AUDIT: SESSION,32,2,FUNCTION,EXECUTE,FUNCTION,public.test,SELECT public.test();+NOTICE: AUDIT: SESSION,32,3,READ,SELECT,TABLE,public.test,SELECT * FROM test+CONTEXT: PL/pgSQL function test() line 6 at OPEN+ test +------+ 1+(1 row)++--+-- 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,33,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,33,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,33,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,34,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,35,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,36,1,DDL,ALTER TABLE,TABLE,public.test,"ALTER TABLE public.test+ RENAME TO test2;"+ALTER TABLE public.test2+ SET SCHEMA test;+NOTICE: AUDIT: SESSION,37,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,38,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,39,1,DDL,ALTER TABLE,TABLE COLUMN,test.test2.description,"ALTER TABLE test.test2+ DROP COLUMN description;"+DROP TABLE test.test2;+NOTICE: AUDIT: SESSION,40,1,DDL,DROP TABLE,TABLE,test.test2,DROP TABLE test.test2;+NOTICE: AUDIT: SESSION,40,1,DDL,DROP TABLE,TABLE CONSTRAINT,test_pkey on test.test2,DROP TABLE test.test2;+NOTICE: AUDIT: SESSION,40,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,41,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,41,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,41,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,42,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,43,1,READ,SELECT,,,"SELECT int_add(1, 1);"+NOTICE: AUDIT: SESSION,43,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,44,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,45,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,46,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,47,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,48,1,DDL,CREATE DATABASE,,,CREATE DATABASE contrib_regression_pgaudit;+ALTER DATABASE contrib_regression_pgaudit RENAME TO contrib_regression_pgaudit2;+NOTICE: AUDIT: SESSION,49,1,DDL,ALTER DATABASE,,,ALTER DATABASE contrib_regression_pgaudit RENAME TO contrib_regression_pgaudit2;+DROP DATABASE contrib_regression_pgaudit2;+NOTICE: AUDIT: SESSION,50,1,DDL,DROP DATABASE,,,DROP DATABASE contrib_regression_pgaudit2;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..b73fcbf--- /dev/null+++ b/contrib/pg_audit/pg_audit.c@@ -0,0 +1,1786 @@+/*------------------------------------------------------------------------------+ * 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)+ {+ 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. */+ if (stackItem != NULL)+ {+ /* 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 && !stackItem->auditEvent.logged &&+ !IsAbortedTransactionBlockState())+ log_audit_event(stackItem);++ 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..2a0436b--- /dev/null+++ b/contrib/pg_audit/sql/pg_audit.sql@@ -0,0 +1,533 @@+-- 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;++--+-- 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 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 cursors and functions in a do block+CREATE FUNCTION public.test()+ RETURNS INT LANGUAGE plpgsql AS $$+DECLARE+ cur1 CURSOR FOR SELECT * FROM test;+ tmp INT;+BEGIN+ OPEN cur1;+ FETCH cur1 INTO tmp;+ CLOSE cur1;+ RETURN tmp;+end $$;++SELECT public.test();++--+-- 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;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..dea10a8--- /dev/null+++ b/doc/src/sgml/pgaudit.sgml@@ -0,0 +1,613 @@+<!-- 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 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. An object 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.+ </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, ishii@postgresql.org, simon@2ndQuadrant.com, sawada.mshk@gmail.com, peter_e@gmx.net, alvherre@2ndquadrant.com, ams@2ndQuadrant.com, masao.fujii@gmail.com
Subject: Re: Auditing extension for PostgreSQL (Take 2)
In-Reply-To: <552DA959.2010102@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