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 da...@pgmasters.net
diff --git a/contrib/Makefile b/contrib/Makefile
index 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/.gitignore
new file mode 100644
index 0000000..a5267cf
--- /dev/null
+++ b/contrib/pg_audit/.gitignore
@@ -0,0 +1,5 @@
+log/
+results/
+tmp_check/
+regression.diffs
+regression.out
diff --git a/contrib/pg_audit/Makefile b/contrib/pg_audit/Makefile
new file mode 100644
index 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
+endif
diff --git a/contrib/pg_audit/expected/pg_audit-deparse.out
b/contrib/pg_audit/expected/pg_audit-deparse.out
new file mode 100644
index 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.out
new file mode 100644
index 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.sql
new file mode 100644
index 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.c
new file mode 100644
index 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.conf
new file mode 100644
index 0000000..e9f4a22
--- /dev/null
+++ b/contrib/pg_audit/pg_audit.conf
@@ -0,0 +1 @@
+shared_preload_libraries = pg_audit
diff --git a/contrib/pg_audit/pg_audit.control
b/contrib/pg_audit/pg_audit.control
new file mode 100644
index 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 = true
diff --git a/contrib/pg_audit/sql/pg_audit.sql
b/contrib/pg_audit/sql/pg_audit.sql
new file mode 100644
index 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.sgml
index 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.sgml
index 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.sgml
new file mode 100644
index 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>a...@2ndquadrant.com</email>, Ian Barwick
<email>i...@2ndquadrant.com</email>, and David Steele
<email>da...@pgmasters.net</email>.
+ </para>
+ </sect2>
+</sect1>
signature.asc
Description: OpenPGP digital signature
