Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YiAhz-0000b7-2b for pgsql-hackers@arkaria.postgresql.org; Tue, 14 Apr 2015 23:57:35 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YiAhx-00052l-LB for pgsql-hackers@arkaria.postgresql.org; Tue, 14 Apr 2015 23:57:33 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YiAhs-00052b-Kq for pgsql-hackers@postgresql.org; Tue, 14 Apr 2015 23:57:29 +0000 Received: from linode.thelabyrinth.net ([2600:3c00::f03c:91ff:fe69:8d76] helo=mail.thelabyrinth.net) by makus.postgresql.org with esmtps (TLS1.1:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YiAhj-0004U8-9w; Tue, 14 Apr 2015 23:57:26 +0000 Received: from theseus-media.home.thelabyrinth.net (unknown [69.80.101.217]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) (Authenticated sender: dsteele) by mail.thelabyrinth.net (Postfix) with ESMTPSA id CB63E139C2; Tue, 14 Apr 2015 23:57:14 +0000 (UTC) Message-ID: <552DA959.2010102@pgmasters.net> Date: Tue, 14 Apr 2015 19:57:13 -0400 From: David Steele User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10; rv:31.0) Gecko/20100101 Thunderbird/31.6.0 MIME-Version: 1.0 To: Tatsuo Ishii CC: simon@2ndQuadrant.com, pgsql-hackers@postgresql.org, sawada.mshk@gmail.com, peter_e@gmx.net, alvherre@2ndquadrant.com, ams@2ndQuadrant.com, masao.fujii@gmail.com Subject: Re: Auditing extension for PostgreSQL (Take 2) References: <552326EE.4060509@pgmasters.net> <552D2C1A.3060002@pgmasters.net> <20150415.081354.1167351714960358354.t-ishii@sraoss.co.jp> In-Reply-To: <20150415.081354.1167351714960358354.t-ishii@sraoss.co.jp> Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="64GIwcHh8oPtBC3J7onH0G1JEGmAosI5n" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --64GIwcHh8oPtBC3J7onH0G1JEGmAosI5n Content-Type: multipart/mixed; boundary="------------090907090206020300080306" This is a multi-part message in MIME format. --------------090907090206020300080306 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable 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. --=20 - David Steele david@pgmasters.net --------------090907090206020300080306 Content-Type: text/plain; charset=UTF-8; x-mac-type="0"; x-mac-creator="0"; name="pg_audit-v8.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="pg_audit-v8.patch" diff --git a/contrib/Makefile b/contrib/Makefile index d63e441..ed9cf6a 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -28,6 +28,7 @@ SUBDIRS =3D \ 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 =3D pg_audit +MODULE_big =3D pg_audit +OBJS =3D pg_audit.o + +EXTENSION =3D pg_audit +REGRESS =3D pg_audit +REGRESS_OPTS =3D --temp-config=3D$(top_srcdir)/contrib/pg_audit/pg_audit= =2Econf +DATA =3D pg_audit--1.0.0.sql + +ifdef USE_PGXS +PG_CONFIG =3D pg_config +PGXS :=3D $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir =3D contrib/pg_audit +top_builddir =3D ../.. +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 =3D 'ddl, ROLE'; +ALTER ROLE user1 SET pg_audit.log_notice =3D 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 TA= BLE public.test (id INT); +SELECT * FROM test; + id=20 +---- +(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 =3D 'Read, writE'; +ALTER ROLE user2 SET pg_audit.log_notice =3D on; +ALTER ROLE user2 SET pg_audit.role =3D 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=20 +------- + 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=20 +----+---- +(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 =3D 1 + RETURNING id +) +INSERT INTO test3 +SELECT id + FROM cte; +NOTICE: AUDIT: SESSION,4,1,WRITE,INSERT,,,"WITH CTE AS +( + UPDATE test2 + SET id =3D 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 =3D 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 =3D 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 =3D 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 =3D 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 =3D 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 =3D '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=20 +---- +(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=20 +------ +(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 =3D 'foo'; +-- +-- Object logged because of: +-- update (id) on test4 +UPDATE public.test4 + SET id =3D 1; +NOTICE: AUDIT: OBJECT,3,1,WRITE,UPDATE,TABLE,public.test4,"UPDATE publi= c.test4 + SET id =3D 1;" +-- +-- Object logged because of: +-- update (name) on test4 +-- update (name) takes precedence over select (name) due to ordering +update public.test4 set name =3D 'foo' where name =3D 'bar'; +NOTICE: AUDIT: OBJECT,4,1,WRITE,UPDATE,TABLE,public.test4,update public= =2Etest4 set name =3D 'foo' where name =3D '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 =3D '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,"CREAT= E 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=20 +----+------+----------+------------- +(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 =3D 'none'; +alter role user1 set pg_audit.role =3D '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 =20 +----+------- + 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 pass= word + FROM account;" + password=20 +---------- + HASH1 +(1 row) + +-- +-- Not object logged +UPDATE account + SET description =3D 'yada, yada'; +-- +-- Object logged because of: +-- update (password) on account +UPDATE account + SET password =3D 'HASH2'; +NOTICE: AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,"UPDATE acc= ount + SET password =3D '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 =3D on; +alter role user1 set pg_audit.log =3D '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 =3D read and log_relation =3D= on +SELECT account.password, + account_role_map.role_id + FROM account + INNER JOIN account_role_map + on account.id =3D account_role_map.account_id; +NOTICE: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,"SELECT acco= unt.password, + account_role_map.role_id + FROM account + INNER JOIN account_role_map + on account.id =3D account_role_map.account_id;" +NOTICE: AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.account,"SELECT acc= ount.password, + account_role_map.role_id + FROM account + INNER JOIN account_role_map + on account.id =3D account_role_map.account_id;" +NOTICE: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account_role_map,"SE= LECT account.password, + account_role_map.role_id + FROM account + INNER JOIN account_role_map + on account.id =3D account_role_map.account_id;" +NOTICE: AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.account_role_map,"S= ELECT account.password, + account_role_map.role_id + FROM account + INNER JOIN account_role_map + on account.id =3D account_role_map.account_id;" + password | role_id=20 +----------+--------- +(0 rows) + +-- +-- Object logged because of: +-- select (password) on account +-- Session logged on all tables because log =3D read and log_relation =3D= on +SELECT password + FROM account; +NOTICE: AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.account,"SELECT pass= word + FROM account;" +NOTICE: AUDIT: SESSION,2,1,READ,SELECT,TABLE,public.account,"SELECT pas= sword + FROM account;" + password=20 +---------- + HASH2 +(1 row) + +-- +-- Not object logged +-- Session logged on all tables because log =3D read and log_relation =3D= on +UPDATE account + SET description =3D 'yada, yada'; +NOTICE: AUDIT: SESSION,3,1,WRITE,UPDATE,TABLE,public.account,"UPDATE ac= count + SET description =3D 'yada, yada';" +-- +-- Object logged because of: +-- select (password) on account (in the where clause) +-- Session logged on all tables because log =3D read and log_relation =3D= on +UPDATE account + SET description =3D 'yada, yada' + where password =3D 'HASH2'; +NOTICE: AUDIT: OBJECT,4,1,WRITE,UPDATE,TABLE,public.account,"UPDATE acc= ount + SET description =3D 'yada, yada' + where password =3D 'HASH2';" +NOTICE: AUDIT: SESSION,4,1,WRITE,UPDATE,TABLE,public.account,"UPDATE ac= count + SET description =3D 'yada, yada' + where password =3D 'HASH2';" +-- +-- Object logged because of: +-- update (password) on account +-- Session logged on all tables because log =3D read and log_relation =3D= on +UPDATE account + SET password =3D 'HASH2'; +NOTICE: AUDIT: OBJECT,5,1,WRITE,UPDATE,TABLE,public.account,"UPDATE acc= ount + SET password =3D 'HASH2';" +NOTICE: AUDIT: SESSION,5,1,WRITE,UPDATE,TABLE,public.account,"UPDATE ac= count + SET password =3D 'HASH2';" +-- +-- Change back to superuser to do exhaustive tests +\connect contrib_regression super +SET pg_audit.log =3D 'ALL'; +SET pg_audit.log_notice =3D ON; +NOTICE: AUDIT: SESSION,2,1,MISC,SET,,,SET pg_audit.log_notice =3D ON; +SET pg_audit.log_relation =3D ON; +NOTICE: AUDIT: SESSION,3,1,MISC,SET,,,SET pg_audit.log_relation =3D 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 accoun= t 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 TAB= LE 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 te= st.account_copy from stdin; +-- +-- Test prepared statement +PREPARE pgclassstmt (oid) AS +SELECT * + FROM account + WHERE id =3D $1; +NOTICE: AUDIT: SESSION,9,1,READ,PREPARE,,,"PREPARE pgclassstmt (oid) AS= +SELECT * + FROM account + WHERE id =3D $1;" +EXECUTE pgclassstmt (1); +NOTICE: AUDIT: SESSION,10,1,READ,SELECT,TABLE,public.account,"PREPARE p= gclassstmt (oid) AS +SELECT * + FROM account + WHERE id =3D $1;",1 +NOTICE: AUDIT: SESSION,10,2,READ,EXECUTE,,,EXECUTE pgclassstmt (1); + id | name | password | description=20 +----+-------+----------+------------- + 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 tab= le +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=20 +------- + 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,"CR= EATE 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,"PREPAR= E 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,"CR= EATE 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,"CR= EATE 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 wil= l +-- 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=20 +----+------+------------- +(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=20 +----------+-------------- + 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 =20 +------------------------------------------ + Result (cost=3D0.00..0.01 rows=3D1 width=3D0) +(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,SELEC= T 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=20 +------ + 1 +(1 row) + +-- +-- Test obfuscated dynamic sql for clean logging +DO $$ +DECLARE + table_name TEXT =3D '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 =3D '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,"CRE= ATE 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 t= able 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.de= scription,"ALTER TABLE public.test + DROP COLUMN description ;" +NOTICE: AUDIT: SESSION,35,1,DDL,ALTER TABLE,TABLE,public.test,"ALTER TA= BLE public.test + DROP COLUMN description ;" +ALTER TABLE public.test + RENAME TO test2; +NOTICE: AUDIT: SESSION,36,1,DDL,ALTER TABLE,TABLE,public.test2,"ALTER T= ABLE public.test + RENAME TO test2;" +ALTER TABLE public.test2 + SET SCHEMA test; +NOTICE: AUDIT: SESSION,37,1,DDL,ALTER TABLE,TABLE,test.test2,"ALTER TAB= LE 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 TAB= LE 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.des= cription,"ALTER TABLE test.test2 + DROP COLUMN description;" +NOTICE: AUDIT: SESSION,39,1,DDL,ALTER TABLE,TABLE,test.test2,"ALTER TAB= LE 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 o= n test.test2,DROP TABLE test.test2; +NOTICE: AUDIT: SESSION,40,1,DDL,DROP TABLE,INDEX,test.test_pkey,DROP TA= BLE 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 SCHE= MA 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 SCHE= MA 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 SCH= EMA 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 SCH= EMA 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_ad= d(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,"S= ELECT int_add(1, 1);" + int_add=20 +--------- + 2 +(1 row) + +CREATE AGGREGATE public.sum_test(INT) (SFUNC=3Dpublic.int_add, STYPE=3DI= NT, INITCOND=3D'0'); +NOTICE: AUDIT: SESSION,44,1,DDL,CREATE AGGREGATE,AGGREGATE,public.sum_t= est(integer),"CREATE AGGREGATE public.sum_test(INT) (SFUNC=3Dpublic.int_a= dd, STYPE=3DINT, INITCOND=3D'0');" +ALTER AGGREGATE public.sum_test(integer) RENAME TO sum_test2; +NOTICE: AUDIT: SESSION,45,1,DDL,ALTER AGGREGATE,AGGREGATE,public.sum_te= st2(integer),ALTER AGGREGATE public.sum_test(integer) RENAME TO sum_test2= ; +-- +-- Test conversion +CREATE CONVERSION public.conversion_test FOR 'SQL_ASCII' TO 'MULE_INTERN= AL' FROM pg_catalog.ascii_to_mic; +NOTICE: AUDIT: SESSION,46,1,DDL,CREATE CONVERSION,CONVERSION,public.con= version_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.conv= ersion_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 contr= ib_regression_pgaudit; +ALTER DATABASE contrib_regression_pgaudit RENAME TO contrib_regression_p= gaudit2; +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_r= egression_pgaudit2; diff --git a/contrib/pg_audit/expected/pg_audit.out b/contrib/pg_audit/ex= pected/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 =3D 'ddl, ROLE'; +ALTER ROLE user1 SET pg_audit.log_notice =3D 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 TA= BLE public.test (id INT); +SELECT * FROM test; + id=20 +---- +(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 =3D 'Read, writE'; +ALTER ROLE user2 SET pg_audit.log_notice =3D on; +ALTER ROLE user2 SET pg_audit.role =3D 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=20 +------- + 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=20 +----+---- +(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 =3D 1 + RETURNING id +) +INSERT INTO test3 +SELECT id + FROM cte; +NOTICE: AUDIT: SESSION,4,1,WRITE,INSERT,,,"WITH CTE AS +( + UPDATE test2 + SET id =3D 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 =3D 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 =3D 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 =3D 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 =3D 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 =3D 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 =3D '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=20 +---- +(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=20 +------ +(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 =3D 'foo'; +-- +-- Object logged because of: +-- update (id) on test4 +UPDATE public.test4 + SET id =3D 1; +NOTICE: AUDIT: OBJECT,3,1,WRITE,UPDATE,TABLE,public.test4,"UPDATE publi= c.test4 + SET id =3D 1;" +-- +-- Object logged because of: +-- update (name) on test4 +-- update (name) takes precedence over select (name) due to ordering +update public.test4 set name =3D 'foo' where name =3D 'bar'; +NOTICE: AUDIT: OBJECT,4,1,WRITE,UPDATE,TABLE,public.test4,update public= =2Etest4 set name =3D 'foo' where name =3D '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 =3D '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,"CREAT= E 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=20 +----+------+----------+------------- +(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 =3D 'none'; +alter role user1 set pg_audit.role =3D '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 =20 +----+------- + 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 pass= word + FROM account;" + password=20 +---------- + HASH1 +(1 row) + +-- +-- Not object logged +UPDATE account + SET description =3D 'yada, yada'; +-- +-- Object logged because of: +-- update (password) on account +UPDATE account + SET password =3D 'HASH2'; +NOTICE: AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,"UPDATE acc= ount + SET password =3D '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 =3D on; +alter role user1 set pg_audit.log =3D '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 =3D read and log_relation =3D= on +SELECT account.password, + account_role_map.role_id + FROM account + INNER JOIN account_role_map + on account.id =3D account_role_map.account_id; +NOTICE: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,"SELECT acco= unt.password, + account_role_map.role_id + FROM account + INNER JOIN account_role_map + on account.id =3D account_role_map.account_id;" +NOTICE: AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.account,"SELECT acc= ount.password, + account_role_map.role_id + FROM account + INNER JOIN account_role_map + on account.id =3D account_role_map.account_id;" +NOTICE: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account_role_map,"SE= LECT account.password, + account_role_map.role_id + FROM account + INNER JOIN account_role_map + on account.id =3D account_role_map.account_id;" +NOTICE: AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.account_role_map,"S= ELECT account.password, + account_role_map.role_id + FROM account + INNER JOIN account_role_map + on account.id =3D account_role_map.account_id;" + password | role_id=20 +----------+--------- +(0 rows) + +-- +-- Object logged because of: +-- select (password) on account +-- Session logged on all tables because log =3D read and log_relation =3D= on +SELECT password + FROM account; +NOTICE: AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.account,"SELECT pass= word + FROM account;" +NOTICE: AUDIT: SESSION,2,1,READ,SELECT,TABLE,public.account,"SELECT pas= sword + FROM account;" + password=20 +---------- + HASH2 +(1 row) + +-- +-- Not object logged +-- Session logged on all tables because log =3D read and log_relation =3D= on +UPDATE account + SET description =3D 'yada, yada'; +NOTICE: AUDIT: SESSION,3,1,WRITE,UPDATE,TABLE,public.account,"UPDATE ac= count + SET description =3D 'yada, yada';" +-- +-- Object logged because of: +-- select (password) on account (in the where clause) +-- Session logged on all tables because log =3D read and log_relation =3D= on +UPDATE account + SET description =3D 'yada, yada' + where password =3D 'HASH2'; +NOTICE: AUDIT: OBJECT,4,1,WRITE,UPDATE,TABLE,public.account,"UPDATE acc= ount + SET description =3D 'yada, yada' + where password =3D 'HASH2';" +NOTICE: AUDIT: SESSION,4,1,WRITE,UPDATE,TABLE,public.account,"UPDATE ac= count + SET description =3D 'yada, yada' + where password =3D 'HASH2';" +-- +-- Object logged because of: +-- update (password) on account +-- Session logged on all tables because log =3D read and log_relation =3D= on +UPDATE account + SET password =3D 'HASH2'; +NOTICE: AUDIT: OBJECT,5,1,WRITE,UPDATE,TABLE,public.account,"UPDATE acc= ount + SET password =3D 'HASH2';" +NOTICE: AUDIT: SESSION,5,1,WRITE,UPDATE,TABLE,public.account,"UPDATE ac= count + SET password =3D 'HASH2';" +-- +-- Change back to superuser to do exhaustive tests +\connect contrib_regression super +SET pg_audit.log =3D 'ALL'; +SET pg_audit.log_notice =3D ON; +NOTICE: AUDIT: SESSION,2,1,MISC,SET,,,SET pg_audit.log_notice =3D ON; +SET pg_audit.log_relation =3D ON; +NOTICE: AUDIT: SESSION,3,1,MISC,SET,,,SET pg_audit.log_relation =3D 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 accoun= t 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 TAB= LE 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.acc= ount_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 te= st.account_copy from stdin; +-- +-- Test prepared statement +PREPARE pgclassstmt (oid) AS +SELECT * + FROM account + WHERE id =3D $1; +NOTICE: AUDIT: SESSION,9,1,READ,PREPARE,,,"PREPARE pgclassstmt (oid) AS= +SELECT * + FROM account + WHERE id =3D $1;" +EXECUTE pgclassstmt (1); +NOTICE: AUDIT: SESSION,10,1,READ,SELECT,TABLE,public.account,"PREPARE p= gclassstmt (oid) AS +SELECT * + FROM account + WHERE id =3D $1;",1 +NOTICE: AUDIT: SESSION,10,2,READ,EXECUTE,,,EXECUTE pgclassstmt (1); + id | name | password | description=20 +----+-------+----------+------------- + 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 tab= le +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=20 +------- + 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,"CR= EATE 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,"PREPAR= E 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,"CR= EATE 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 wil= l +-- 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=20 +----+------+------------- +(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=20 +----------+-------------- + 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 =20 +------------------------------------------ + Result (cost=3D0.00..0.01 rows=3D1 width=3D0) +(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 publ= ic.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,SELEC= T 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=20 +------ + 1 +(1 row) + +-- +-- Test obfuscated dynamic sql for clean logging +DO $$ +DECLARE + table_name TEXT =3D '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 =3D '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,"CRE= ATE 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 t= able 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.de= scription,"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 TA= BLE public.test + RENAME TO test2;" +ALTER TABLE public.test2 + SET SCHEMA test; +NOTICE: AUDIT: SESSION,37,1,DDL,ALTER TABLE,INDEX,public.test_pkey,"ALT= ER 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 TAB= LE 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.des= cription,"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 o= n test.test2,DROP TABLE test.test2; +NOTICE: AUDIT: SESSION,40,1,DDL,DROP TABLE,INDEX,test.test_pkey,DROP TA= BLE 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 SCHE= MA 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 SCHE= MA 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 publ= ic.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,"S= ELECT int_add(1, 1);" + int_add=20 +--------- + 2 +(1 row) + +CREATE AGGREGATE public.sum_test(INT) (SFUNC=3Dpublic.int_add, STYPE=3DI= NT, INITCOND=3D'0'); +NOTICE: AUDIT: SESSION,44,1,DDL,CREATE AGGREGATE,,,"CREATE AGGREGATE pu= blic.sum_test(INT) (SFUNC=3Dpublic.int_add, STYPE=3DINT, INITCOND=3D'0');= " +ALTER AGGREGATE public.sum_test(integer) RENAME TO sum_test2; +NOTICE: AUDIT: SESSION,45,1,DDL,ALTER AGGREGATE,,,ALTER AGGREGATE publi= c.sum_test(integer) RENAME TO sum_test2; +-- +-- Test conversion +CREATE CONVERSION public.conversion_test FOR 'SQL_ASCII' TO 'MULE_INTERN= AL' FROM pg_catalog.ascii_to_mic; +NOTICE: AUDIT: SESSION,46,1,DDL,CREATE CONVERSION,,,CREATE CONVERSION p= ublic.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 pub= lic.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 contr= ib_regression_pgaudit; +ALTER DATABASE contrib_regression_pgaudit RENAME TO contrib_regression_p= gaudit2; +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_r= egression_pgaudit2; diff --git a/contrib/pg_audit/pg_audit--1.0.0.sql b/contrib/pg_audit/pg_a= udit--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 EXTENSI= ON +\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 contain= s the + * role for grant-based auditing. + */ +char *auditRole =3D NULL; + +/* + * auditLog is the string value of the pg_audit.log GUC, e.g. "read, wri= te, ddl" + * (it's not used by the module but is required by DefineCustomStringVar= iable). + * Each token corresponds to a flag in enum LogClass below. We convert t= he list + * of tokens into a bitmap in auditLogBitmap for internal use. + */ +char *auditLog =3D NULL; +static uint64 auditLogBitmap =3D 0; + +/* + * auditLogRelation controls whether all relations are logged for READ a= nd + * WRITE classes during session logging. + */ +bool auditLogRelation =3D false; + +/* + * auditLogNotice raises a notice as well as logging via the standard fa= cility. + * This is primarily for the benefit of testing. + */ +bool auditLogNotice =3D false; + +/* + * String constants for audit types - used when logging to distinguish s= ession + * 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 =3D 0, + + /* DDL: CREATE/DROP/ALTER */ + LOG_DDL =3D (1 << 1), + + /* Function execution */ + LOG_FUNCTION =3D (1 << 2), + + /* Statements not covered by another class */ + LOG_MISC =3D (1 << 3), + + /* Function execution */ + LOG_PARAMETER =3D (1 << 4), + + /* SELECT */ + LOG_READ =3D (1 << 5), + + /* GRANT, REVOKE, CREATE/ALTER/DROP ROLE */ + LOG_ROLE =3D (1 << 6), + + /* INSERT, UPDATE, DELETE, TRUNCATE */ + LOG_WRITE =3D (1 << 7), + + /* Absolutely everything */ + LOG_ALL =3D ~(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 sing= le + * 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 event= s. + */ +typedef struct AuditEventStackItem +{ + struct AuditEventStackItem *next; + + AuditEvent auditEvent; + + int64 stackId; + + MemoryContext contextAudit; + MemoryContextCallback contextCallback; +} AuditEventStackItem; + +AuditEventStackItem *auditEventStack =3D NULL; + +/* + * Track when an internal statement is running so it is not logged + */ +static bool internalStatement =3D false; + +/* + * Track running total for statements and substatements and whether or n= ot + * anything has been logged since this statement began. + */ +static int64 statementTotal =3D 0; +static int64 substatementTotal =3D 0; +static int64 stackTotal =3D 0; + +static bool statementLogged =3D 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 MemoryContextRegisterResetCallba= ck(). + * 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 =3D auditEventStack; + + /* Only process if the stack contains items */ + while (nextItem !=3D NULL) + { + /* Check if this item matches the item to be freed */ + if (nextItem =3D=3D (AuditEventStackItem *)stackFree) + { + /* Move top of stack to the item after the freed item */ + auditEventStack =3D nextItem->next; + + /* If the stack is not empty */ + if (auditEventStack =3D=3D NULL) + { + /* Reset internal statement in case of error */ + internalStatement =3D false; + + /* Reset sub statement total */ + substatementTotal =3D 0; + + /* Reset statement logged flag total */ + statementLogged =3D false; + } + + return; + } + + /* Still looking, test the next item */ + nextItem =3D 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 =3D AllocSetContextCreate(CurrentMemoryContext, + "pg_audit stack context", + ALLOCSET_DEFAULT_MINSIZE, + ALLOCSET_DEFAULT_INITSIZE, + ALLOCSET_DEFAULT_MAXSIZE); + contextOld =3D MemoryContextSwitchTo(contextAudit); + + /* Allocate the stack item */ + stackItem =3D palloc0(sizeof(AuditEventStackItem)); + + /* Store memory contexts */ + stackItem->contextAudit =3D contextAudit; + + /* If item already on stack then push it down */ + if (auditEventStack !=3D NULL) + stackItem->next =3D auditEventStack; + else + stackItem->next =3D NULL; + + /* + * Create the unique stackId - used to keep the stack sane when memory + * contexts are freed unexpectedly. + */ + stackItem->stackId =3D ++stackTotal; + + /* + * Setup a callback in case an error happens. stack_free() will trunca= te + * the stack at this item. + */ + stackItem->contextCallback.func =3D stack_free; + stackItem->contextCallback.arg =3D (void *)stackItem; + MemoryContextRegisterResetCallback(contextAudit, + &stackItem->contextCallback); + + /* Push item on the stack */ + auditEventStack =3D 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 !=3D NULL && auditEventStack->stackId =3D=3D stackI= d) + { + 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 quote= d + * in CSV + */ + if (appendStr =3D=3D 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 =3D appendStr; *pChar; pChar++) + { + if (*pChar =3D=3D '"') /* 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= =2E + */ +static void +log_audit_event(AuditEventStackItem *stackItem) +{ + MemoryContext contextOld; + StringInfoData auditStr; + + /* By default put everything in the MISC class. */ + enum LogClass class =3D LOG_MISC; + const char *className =3D 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 =3D CLASS_WRITE; + class =3D LOG_WRITE; + break; + + /* Separate ROLE from other DDL statements */ + case LOGSTMT_DDL: + /* Identify role statements */ + if (stackItem->auditEvent.commandTag =3D=3D T_GrantStmt || + stackItem->auditEvent.commandTag =3D=3D T_GrantRoleStmt || + stackItem->auditEvent.commandTag =3D=3D T_CreateRoleStmt || + (stackItem->auditEvent.commandTag =3D=3D T_RenameStmt && + pg_strcasecmp(stackItem->auditEvent.command, + COMMAND_ALTER_ROLE) =3D=3D 0) || + (stackItem->auditEvent.commandTag =3D=3D T_DropStmt && + pg_strcasecmp(stackItem->auditEvent.command, + COMMAND_DROP_ROLE) =3D=3D 0) || + stackItem->auditEvent.commandTag =3D=3D T_DropRoleStmt || + stackItem->auditEvent.commandTag =3D=3D T_AlterRoleStmt || + stackItem->auditEvent.commandTag =3D=3D T_AlterRoleSetStmt) + { + className =3D CLASS_ROLE; + class =3D LOG_ROLE; + } + /* Else log as DDL */ + else + { + className =3D CLASS_DDL; + class =3D 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 =3D CLASS_READ; + class =3D LOG_READ; + break; + + /* Reindex is DDL (because cluster is DDL) */ + case T_ReindexStmt: + className =3D CLASS_DDL; + class =3D LOG_DDL; + break; + + /* FUNCTION statements */ + case T_DoStmt: + className =3D CLASS_FUNCTION; + class =3D 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 =3D MemoryContextSwitchTo(stackItem->contextAudit); + + /* Set statement and substatement Ids */ + if (stackItem->auditEvent.statementId =3D=3D 0) + { + /* If nothing has been logged yet then create a new statement Id */ + if (!statementLogged) + { + statementTotal++; + statementLogged =3D true; + } + + stackItem->auditEvent.statementId =3D statementTotal; + stackItem->auditEvent.substatementId =3D ++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 !=3D NULL && + stackItem->auditEvent.paramList->numParams > 0 && + !IsAbortedTransactionBlockState()) + { + ParamListInfo paramList =3D stackItem->auditEvent.paramList; + int paramIdx; + + /* Iterate through all params */ + for (paramIdx =3D 0; paramIdx < paramList->numParams; paramIdx++) + { + ParamExternData *prm =3D ¶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 =3D 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 =3D true; + + /* Switch back to the old memory context */ + MemoryContextSwitchTo(contextOld); +} + +/* + * Check if the role or any inherited role has any permission in the mas= k. 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 =3D false; + Acl *acl; + AclItem *aclItemData; + int aclIndex; + int aclTotal; + + /* Detoast column's ACL if necessary */ + acl =3D DatumGetAclP(aclDatum); + + /* Get the acl list and total */ + aclTotal =3D ACL_NUM(acl); + aclItemData =3D ACL_DAT(acl); + + /* Check privileges granted directly to auditOid */ + for (aclIndex =3D 0; aclIndex < aclTotal; aclIndex++) + { + AclItem *aclItem =3D &aclItemData[aclIndex]; + + if (aclItem->ai_grantee =3D=3D auditOid && + aclItem->ai_privs & mask) + { + result =3D 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 =3D 0; aclIndex < aclTotal; aclIndex++) + { + AclItem *aclItem =3D &aclItemData[aclIndex]; + + /* Don't test public or auditOid (it has been tested already) */ + if (aclItem->ai_grantee =3D=3D ACL_ID_PUBLIC || + aclItem->ai_grantee =3D=3D 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 =3D true; + break; + } + } + } + + /* if we have a detoasted copy, free it */ + if (acl && (Pointer) acl !=3D 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 =3D false; + HeapTuple tuple; + Datum aclDatum; + bool isNull; + + /* Get relation tuple from pg_class */ + tuple =3D SearchSysCache1(RELOID, ObjectIdGetDatum(relOid)); + + /* Return false if tuple is not valid */ + if (!HeapTupleIsValid(tuple)) + return false; + + /* Get the relation's ACL */ + aclDatum =3D SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl, + &isNull); + + /* If not null then test */ + if (!isNull) + result =3D 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 attribut= e. + */ +static bool +audit_on_attribute(Oid relOid, + AttrNumber attNum, + Oid auditOid, + AclMode mask) +{ + bool result =3D false; + HeapTuple attTuple; + Datum aclDatum; + bool isNull; + + /* Get the attribute's ACL */ + attTuple =3D 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 =3D SysCacheGetAttr(ATTNUM, attTuple, Anum_pg_attribute_attac= l, + &isNull); + + if (!isNull) + result =3D 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 attribut= e 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 =3D 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 =3D SearchSysCache1(RELOID, ObjectIdGetDatum(relOid)); + + if (!HeapTupleIsValid(classTuple)) + return false; + + nattrs =3D ((Form_pg_class) GETSTRUCT(classTuple))->relnatts; + ReleaseSysCache(classTuple); + + /* Check each column */ + for (curr_att =3D 1; curr_att <=3D 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 =3D bms_copy(attributeSet); + + /* Check each column */ + while ((col =3D bms_first_member(tmpSet)) >=3D 0) + { + col +=3D FirstLowInvalidHeapAttributeNumber; + + if (col !=3D InvalidAttrNumber && + audit_on_attribute(relOid, col, auditOid, mode)) + { + result =3D 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 =3D true; + bool found =3D false; + + /* Do not log if this is an internal statement */ + if (internalStatement) + return; + + foreach(lr, rangeTabls) + { + Oid relOid; + Relation rel; + RangeTblEntry *rte =3D lfirst(lr); + + /* We only care about tables, and can ignore subqueries etc. */ + if (rte->rtekind !=3D RTE_RELATION) + continue; + + found =3D true; + + /* + * Filter out any system relations + */ + relOid =3D rte->relid; + rel =3D 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 =3D LOGSTMT_MOD; + auditEventStack->auditEvent.commandTag =3D T_InsertStmt; + auditEventStack->auditEvent.command =3D COMMAND_INSERT; + } + else if (rte->requiredPerms & ACL_UPDATE) + { + auditEventStack->auditEvent.logStmtLevel =3D LOGSTMT_MOD; + auditEventStack->auditEvent.commandTag =3D T_UpdateStmt; + auditEventStack->auditEvent.command =3D COMMAND_UPDATE; + } + else if (rte->requiredPerms & ACL_DELETE) + { + auditEventStack->auditEvent.logStmtLevel =3D LOGSTMT_MOD; + auditEventStack->auditEvent.commandTag =3D T_DeleteStmt; + auditEventStack->auditEvent.command =3D COMMAND_DELETE; + } + else if (rte->requiredPerms & ACL_SELECT) + { + auditEventStack->auditEvent.logStmtLevel =3D LOGSTMT_ALL; + auditEventStack->auditEvent.commandTag =3D T_SelectStmt; + auditEventStack->auditEvent.command =3D COMMAND_SELECT; + } + else + { + auditEventStack->auditEvent.logStmtLevel =3D LOGSTMT_ALL; + auditEventStack->auditEvent.commandTag =3D T_Invalid; + auditEventStack->auditEvent.command =3D COMMAND_UNKNOWN; + } + + /* + * Fill values in the event struct that are required for session + * logging. + */ + auditEventStack->auditEvent.granted =3D false; + + /* + * If this is the first rte then session log unless auditLogRelation + * is set. + */ + if (first && !auditLogRelation) + { + auditEventStack->auditEvent.objectName =3D ""; + auditEventStack->auditEvent.objectType =3D ""; + + log_audit_event(auditEventStack); + + first =3D false; + } + + /* Get the relation type */ + switch (rte->relkind) + { + case RELKIND_RELATION: + auditEventStack->auditEvent.objectType =3D + OBJECT_TYPE_TABLE; + break; + + case RELKIND_INDEX: + auditEventStack->auditEvent.objectType =3D + OBJECT_TYPE_INDEX; + break; + + case RELKIND_SEQUENCE: + auditEventStack->auditEvent.objectType =3D + OBJECT_TYPE_SEQUENCE; + break; + + case RELKIND_TOASTVALUE: + auditEventStack->auditEvent.objectType =3D + OBJECT_TYPE_TOASTVALUE; + break; + + case RELKIND_VIEW: + auditEventStack->auditEvent.objectType =3D + OBJECT_TYPE_VIEW; + break; + + case RELKIND_COMPOSITE_TYPE: + auditEventStack->auditEvent.objectType =3D + OBJECT_TYPE_COMPOSITE_TYPE; + break; + + case RELKIND_FOREIGN_TABLE: + auditEventStack->auditEvent.objectType =3D + OBJECT_TYPE_FOREIGN_TABLE; + break; + + case RELKIND_MATVIEW: + auditEventStack->auditEvent.objectType =3D + OBJECT_TYPE_MATVIEW; + break; + + default: + auditEventStack->auditEvent.objectType =3D + OBJECT_TYPE_UNKNOWN; + break; + } + + /* Get the relation name */ + auditEventStack->auditEvent.objectName =3D + 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 !=3D InvalidOid) + { + AclMode auditPerms =3D (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 =3D true; + } + + /* + * Else check if the audit role has column-level permissions for + * select, insert, or update. + */ + else if (auditPerms !=3D 0) + { + /* + * Check the select columns to see if the audit role has + * priveleges on any of them. + */ + if (auditPerms & ACL_SELECT) + { + auditEventStack->auditEvent.granted =3D + 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 &=3D (ACL_INSERT | ACL_UPDATE); + + if (auditPerms) + { + auditEventStack->auditEvent.granted =3D + 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 =3D false; + log_audit_event(auditEventStack); + } + + /* Do relation level logging if auditLogRelation is set */ + if (auditLogRelation) + { + auditEventStack->auditEvent.logged =3D false; + auditEventStack->auditEvent.granted =3D 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 =3D false; + auditEventStack->auditEvent.logged =3D false; + + log_audit_event(auditEventStack); + } +} + +/* + * Create AuditEvents for certain kinds of CREATE, ALTER, and DELETE sta= tements + * where the object can be logged. + */ +static void +log_create_alter_drop(Oid classId, + Oid objectId) +{ + /* Only perform when class is relation */ + if (classId =3D=3D RelationRelationId) + { + Relation rel; + Form_pg_class class; + + /* Open the relation */ + rel =3D 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 =3D RelationGetForm(rel); + auditEventStack->auditEvent.objectName =3D + 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 =3D + OBJECT_TYPE_TABLE; + break; + + case RELKIND_INDEX: + auditEventStack->auditEvent.objectType =3D + OBJECT_TYPE_INDEX; + break; + + case RELKIND_SEQUENCE: + auditEventStack->auditEvent.objectType =3D + OBJECT_TYPE_SEQUENCE; + break; + + case RELKIND_VIEW: + auditEventStack->auditEvent.objectType =3D + OBJECT_TYPE_VIEW; + break; + + case RELKIND_COMPOSITE_TYPE: + auditEventStack->auditEvent.objectType =3D + OBJECT_TYPE_COMPOSITE_TYPE; + break; + + case RELKIND_FOREIGN_TABLE: + auditEventStack->auditEvent.objectType =3D + OBJECT_TYPE_FOREIGN_TABLE; + break; + + case RELKIND_MATVIEW: + auditEventStack->auditEvent.objectType =3D + 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 =3D SearchSysCache1(PROCOID, ObjectIdGetDatum(objectId)); + + if (!proctup) + elog(ERROR, "cache lookup failed for function %u", objectId); + proc =3D (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 =3D stack_push(); + + /* Generate the fully-qualified function name. */ + stackItem->auditEvent.objectName =3D + quote_qualified_identifier(get_namespace_name(proc->pronamespace), + NameStr(proc->proname)); + ReleaseSysCache(proctup); + + /* Log the function call */ + stackItem->auditEvent.logStmtLevel =3D LOGSTMT_ALL; + stackItem->auditEvent.commandTag =3D T_DoStmt; + stackItem->auditEvent.command =3D COMMAND_EXECUTE; + stackItem->auditEvent.objectType =3D OBJECT_TYPE_FUNCTION; + stackItem->auditEvent.commandText =3D stackItem->next->auditEvent.comma= ndText; + + 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 =3D 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 =3D arg; + + if (pa->is_internal) + return; + + log_create_alter_drop(classId, objectId); + } + break; + + /* Log drop */ + case OAT_DROP: + if (auditLogBitmap & LOG_DDL) + { + ObjectAccessDrop *drop =3D 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 =3D NUL= L; +static ProcessUtility_hook_type next_ProcessUtility_hook =3D NULL; +static object_access_hook_type next_object_access_hook =3D NULL; +static ExecutorStart_hook_type next_ExecutorStart_hook =3D NULL; +static ExecutorEnd_hook_type next_ExecutorEnd_hook =3D NULL; + +/* + * Hook ExecutorStart to get the query text and basic command type for q= ueries + * 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 =3D NULL; + + if (!internalStatement) + { + /* Allocate the audit event */ + stackItem =3D stack_push(); + + /* Initialize command */ + switch (queryDesc->operation) + { + case CMD_SELECT: + stackItem->auditEvent.logStmtLevel =3D LOGSTMT_ALL; + stackItem->auditEvent.commandTag =3D T_SelectStmt; + stackItem->auditEvent.command =3D COMMAND_SELECT; + break; + + case CMD_INSERT: + stackItem->auditEvent.logStmtLevel =3D LOGSTMT_MOD; + stackItem->auditEvent.commandTag =3D T_InsertStmt; + stackItem->auditEvent.command =3D COMMAND_INSERT; + break; + + case CMD_UPDATE: + stackItem->auditEvent.logStmtLevel =3D LOGSTMT_MOD; + stackItem->auditEvent.commandTag =3D T_UpdateStmt; + stackItem->auditEvent.command =3D COMMAND_UPDATE; + break; + + case CMD_DELETE: + stackItem->auditEvent.logStmtLevel =3D LOGSTMT_MOD; + stackItem->auditEvent.commandTag =3D T_DeleteStmt; + stackItem->auditEvent.command =3D COMMAND_DELETE; + break; + + default: + stackItem->auditEvent.logStmtLevel =3D LOGSTMT_ALL; + stackItem->auditEvent.commandTag =3D T_Invalid; + stackItem->auditEvent.command =3D COMMAND_UNKNOWN; + break; + } + + /* Initialize the audit event */ + stackItem->auditEvent.objectName =3D ""; + stackItem->auditEvent.objectType =3D ""; + stackItem->auditEvent.commandText =3D queryDesc->sourceText; + stackItem->auditEvent.paramList =3D 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 =3D get_role_oid(auditRole, true); + + /* Log DML if the audit role is valid or session logging is enabled. */= + if ((auditOid !=3D InvalidOid || auditLogBitmap !=3D 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 comman= ds. + */ +static void +pg_audit_ProcessUtility_hook(Node *parsetree, + const char *queryString, + ProcessUtilityContext context, + ParamListInfo params, + DestReceiver *dest, + char *completionTag) +{ + AuditEventStackItem *stackItem =3D NULL; + int64 stackId; + + /* Allocate the audit event */ + if (!IsAbortedTransactionBlockState()) + { + /* Process top level utility statement */ + if (context =3D=3D PROCESS_UTILITY_TOPLEVEL) + { + if (auditEventStack !=3D NULL) + elog(ERROR, "pg_audit stack is not empty"); + + /* Set params */ + stackItem =3D stack_push(); + stackItem->auditEvent.paramList =3D params; + } + else + stackItem =3D stack_push(); + + stackId =3D stackItem->stackId; + stackItem->auditEvent.logStmtLevel =3D GetCommandLogLevel(parsetree); + stackItem->auditEvent.commandTag =3D nodeTag(parsetree); + stackItem->auditEvent.command =3D CreateCommandTag(parsetree); + stackItem->auditEvent.objectName =3D ""; + stackItem->auditEvent.objectType =3D ""; + stackItem->auditEvent.commandText =3D queryString; + + /* + * If this is a DO block log it before calling the next ProcessUtility= + * hook. + */ + if (auditLogBitmap !=3D 0 && + stackItem->auditEvent.commandTag =3D=3D 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 !=3D NULL) + { + /* Log the utility command if logging is on, the command has not alrea= dy + * been logged by another hook, and the transaction is not aborted. */= + if (auditLogBitmap !=3D 0 && !stackItem->auditEvent.logged && + !IsAbortedTransactionBlockState()) + log_audit_event(stackItem); + + stack_pop(stackId); + } +} + +/* + * Hook object_access_hook to provide fully-qualified object names for e= xecute, + * create, drop, and alter commands. Most of the audit information is f= illed in + * by log_utility_command(). + */ +static void +pg_audit_object_access_hook(ObjectAccessType access, + Oid classId, + Oid objectId, + int subId, + void *arg) +{ + if (auditLogBitmap !=3D 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 trig= ger + * 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 =3D 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 =3D AllocSetContextCreate( + CurrentMemoryContext, + "pg_audit_func_ddl_command_end temporary context", + ALLOCSET_DEFAULT_MINSIZE, + ALLOCSET_DEFAULT_INITSIZE, + ALLOCSET_DEFAULT_MAXSIZE); + contextOld =3D MemoryContextSwitchTo(contextQuery); + + /* Get information about triggered events */ + eventData =3D (EventTriggerData *) fcinfo->context; + + auditEventStack->auditEvent.logStmtLevel =3D + GetCommandLogLevel(eventData->parsetree); + auditEventStack->auditEvent.commandTag =3D + nodeTag(eventData->parsetree); + auditEventStack->auditEvent.command =3D + CreateCommandTag(eventData->parsetree); + + /* Return objects affected by the (non drop) DDL statement */ + query =3D "SELECT UPPER(object_type), identity\n" + " FROM pg_event_trigger_ddl_commands()"; + + /* Attempt to connect */ + result =3D SPI_connect(); + + if (result < 0) + elog(ERROR, "pg_audit_ddl_command_end: SPI_connect returned %d", + result); + + /* Execute the query */ + result =3D SPI_execute(query, true, 0); + + if (result !=3D SPI_OK_SELECT) + elog(ERROR, "pg_audit_ddl_command_end: SPI_execute returned %d", + result); + + /* Iterate returned rows */ + spiTupDesc =3D SPI_tuptable->tupdesc; + + for (row =3D 0; row < SPI_processed; row++) + { + HeapTuple spiTuple; + + spiTuple =3D SPI_tuptable->vals[row]; + + /* Supply object name and type for audit event */ + auditEventStack->auditEvent.objectType =3D + SPI_getvalue(spiTuple, spiTupDesc, 1); + auditEventStack->auditEvent.objectName =3D + 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 =3D false; + } +#endif + + PG_RETURN_NULL(); +} + +/* + * Supply additional data for drop statements that have event trigger su= pport. + */ +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 =3D 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 =3D AllocSetContextCreate( + CurrentMemoryContext, + "pg_audit_func_ddl_command_end temporary context", + ALLOCSET_DEFAULT_MINSIZE, + ALLOCSET_DEFAULT_INITSIZE, + ALLOCSET_DEFAULT_MAXSIZE); + contextOld =3D MemoryContextSwitchTo(contextQuery); + + /* Return objects affected by the drop statement */ + query =3D "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 =3D SPI_connect(); + + if (result < 0) + elog(ERROR, "pg_audit_ddl_drop: SPI_connect returned %d", + result); + + /* Execute the query */ + result =3D SPI_execute(query, true, 0); + + if (result !=3D SPI_OK_SELECT) + elog(ERROR, "pg_audit_ddl_drop: SPI_execute returned %d", + result); + + /* Iterate returned rows */ + spiTupDesc =3D SPI_tuptable->tupdesc; + + for (row =3D 0; row < SPI_processed; row++) + { + HeapTuple spiTuple; + char *schemaName; + + spiTuple =3D SPI_tuptable->vals[row]; + + auditEventStack->auditEvent.objectType =3D + SPI_getvalue(spiTuple, spiTupDesc, 4); + schemaName =3D SPI_getvalue(spiTuple, spiTupDesc, 5); + + if (!(pg_strcasecmp(auditEventStack->auditEvent.objectType, + "TYPE") =3D=3D 0 || + pg_strcasecmp(schemaName, "pg_toast") =3D=3D 0)) + { + auditEventStack->auditEvent.objectName =3D + 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 =3D false; + } + + PG_RETURN_NULL(); +} + +/* + * GUC check and assign functions + */ + +/* + * Take a pg_audit.log value such as "read, write, dml", verify that eac= h of the + * comma-separated tokens corresponds to a LogClass value, and convert t= hem 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 =3D 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 =3D (uint64 *) malloc(sizeof(uint64)); + if (!f) + return false; + *f =3D 0; + + foreach(lt, flags) + { + bool subtract =3D false; + uint64 class; + + /* Retrieve a token */ + char *token =3D (char *)lfirst(lt); + + /* If token is preceded by -, then the token is subtractive. */ + if (strstr(token, "-") =3D=3D token) + { + token =3D token + 1; + subtract =3D true; + } + + /* Test each token. */ + if (pg_strcasecmp(token, CLASS_NONE) =3D=3D 0) + class =3D LOG_NONE; + else if (pg_strcasecmp(token, CLASS_ALL) =3D=3D 0) + class =3D LOG_ALL; + else if (pg_strcasecmp(token, CLASS_DDL) =3D=3D 0) + class =3D LOG_DDL; + else if (pg_strcasecmp(token, CLASS_FUNCTION) =3D=3D 0) + class =3D LOG_FUNCTION; + else if (pg_strcasecmp(token, CLASS_MISC) =3D=3D 0) + class =3D LOG_MISC; + else if (pg_strcasecmp(token, CLASS_PARAMETER) =3D=3D 0) + class =3D LOG_PARAMETER; + else if (pg_strcasecmp(token, CLASS_READ) =3D=3D 0) + class =3D LOG_READ; + else if (pg_strcasecmp(token, CLASS_ROLE) =3D=3D 0) + class =3D LOG_ROLE; + else if (pg_strcasecmp(token, CLASS_WRITE) =3D=3D 0) + class =3D LOG_WRITE; + else + { + free(f); + pfree(rawval); + list_free(flags); + return false; + } + + /* Add or subtract class bits from the log bitmap. */ + if (subtract) + *f &=3D ~class; + else + *f |=3D class; + } + + pfree(rawval); + list_free(flags); + + /* + * Store the bitmap for assign_pg_audit_log. + */ + *extra =3D 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 =3D *(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 =3D "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 =3D "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 =3D on + * + * Controls whether relations get separate log entries during session + * logging of READ and WRITE classes. This works as if all relations i= n the + * database had been added to the audit role and provides a shortcut wh= en + * 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 =3D 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 pre= serve + * the chain. + */ + next_ExecutorStart_hook =3D ExecutorStart_hook; + ExecutorStart_hook =3D pg_audit_ExecutorStart_hook; + + next_ExecutorCheckPerms_hook =3D ExecutorCheckPerms_hook; + ExecutorCheckPerms_hook =3D pg_audit_ExecutorCheckPerms_hook; + + next_ExecutorEnd_hook =3D ExecutorEnd_hook; + ExecutorEnd_hook =3D pg_audit_ExecutorEnd_hook; + + next_ProcessUtility_hook =3D ProcessUtility_hook; + ProcessUtility_hook =3D pg_audit_ProcessUtility_hook; + + next_object_access_hook =3D object_access_hook; + object_access_hook =3D pg_audit_object_access_hook; +} diff --git a/contrib/pg_audit/pg_audit.conf b/contrib/pg_audit/pg_audit.c= onf new file mode 100644 index 0000000..e9f4a22 --- /dev/null +++ b/contrib/pg_audit/pg_audit.conf @@ -0,0 +1 @@ +shared_preload_libraries =3D pg_audit diff --git a/contrib/pg_audit/pg_audit.control b/contrib/pg_audit/pg_audi= t.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 =3D 'provides auditing functionality' +default_version =3D '1.0.0' +module_pathname =3D '$libdir/pg_audit' +relocatable =3D 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 =3D 'ddl, ROLE'; +ALTER ROLE user1 SET pg_audit.log_notice =3D 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 =3D 'Read, writE'; +ALTER ROLE user2 SET pg_audit.log_notice =3D on; +ALTER ROLE user2 SET pg_audit.role =3D 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 =3D 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 =3D 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 =3D '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 =3D 'foo'; + +-- +-- Object logged because of: +-- update (id) on test4 +UPDATE public.test4 + SET id =3D 1; + +-- +-- Object logged because of: +-- update (name) on test4 +-- update (name) takes precedence over select (name) due to ordering +update public.test4 set name =3D 'foo' where name =3D '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 =3D '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 =3D 'none'; +alter role user1 set pg_audit.role =3D '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 =3D 'yada, yada'; + +-- +-- Object logged because of: +-- update (password) on account +UPDATE account + SET password =3D '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 =3D on; +alter role user1 set pg_audit.log =3D '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 =3D read and log_relation =3D= on +SELECT account.password, + account_role_map.role_id + FROM account + INNER JOIN account_role_map + on account.id =3D account_role_map.account_id; + +-- +-- Object logged because of: +-- select (password) on account +-- Session logged on all tables because log =3D read and log_relation =3D= on +SELECT password + FROM account; + +-- +-- Not object logged +-- Session logged on all tables because log =3D read and log_relation =3D= on +UPDATE account + SET description =3D 'yada, yada'; + +-- +-- Object logged because of: +-- select (password) on account (in the where clause) +-- Session logged on all tables because log =3D read and log_relation =3D= on +UPDATE account + SET description =3D 'yada, yada' + where password =3D 'HASH2'; + +-- +-- Object logged because of: +-- update (password) on account +-- Session logged on all tables because log =3D read and log_relation =3D= on +UPDATE account + SET password =3D 'HASH2'; + +-- +-- Change back to superuser to do exhaustive tests +\connect contrib_regression super +SET pg_audit.log =3D 'ALL'; +SET pg_audit.log_notice =3D ON; +SET pg_audit.log_relation =3D 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 =3D $1; + +EXECUTE pgclassstmt (1); +DEALLOCATE pgclassstmt; + +-- +-- Test cursor - no tables will be logged since pg_class is a system tab= le +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 wil= l +-- 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 =3D '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=3Dpublic.int_add, STYPE=3DI= NT, INITCOND=3D'0'); +ALTER AGGREGATE public.sum_test(integer) RENAME TO sum_test2; + +-- +-- Test conversion +CREATE CONVERSION public.conversion_test FOR 'SQL_ASCII' TO 'MULE_INTERN= AL' 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_p= gaudit2; +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 module_name FROM unp= ackaged; <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 @@ + 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 @@ + + + + pg_audit + + + pg_audit + + + + The pg_audit extenstion provides detailed sessi= on + and/or object audit logging via the standard logging facility. The = goal + is to provide the tools needed to produce audit logs required to pas= s any + goverment, financial, or ISO certification audit. + + + + An audit is an official inspection of an individual's or organizatio= n's + accounts, typically by an independent body. The information gathere= d by + pg_audit is properly called an audit trail or a= udit + log. The term audit log is used in this documentation. + + + + Why <literal>pg_audit</>? + =20 + + Basic statement logging can be provided by the standard logging fa= cility + using log_statements =3D all. This is acceptable for = monitoring + and other usages but does not provide the level of detail generall= y + required for an audit. It is not enough to have a list of all the= + operations performed against the database. It must also be possibl= e to + find particular statements that are of interest to an auditor. + + + + For example, an auditor may want to verify that a particular table= was + created inside a documented maintence window. This might seem lik= e a + simple job for grep, but what if you are presented with something = like + this (intentionally obfuscated) example: + + + +DO $$ +BEGIN + EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)'; +END $$; + + =20 + + Standard logging will give you this: + + + +LOG: statement: DO $$ +BEGIN + EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)'; +END $$; + + =20 + + It appears that finding the table of interest may require some kno= wledge + of the code in cases where tables are created dynamically. This i= s not + ideal since it would be preferrable to just search on the table na= me. + This is where pg_audit comes in. For the same input, + it will produce this output in the log: + + + +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) + + + + Not only is the DO block logged, but substatement 2 co= ntains + the full text of the CREATE TABLE with the statement t= ype, + object type, and full-qualified name to make searches easy. + + + + When logging SELECT and DML statements, + pg_audit can be configured to log a separate entry for= each + relation referenced in a statement. No parsing is required to fin= d all + statements that touch a particular table. In fact, the goal is th= at the + statement text is provided primarily for deep forensics and should= not be + the directly required for any search. + + + + + Usage Considerations + =20 + + Depending on settings, it is possible for pg_audit 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. + + =20 + + 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 th= e 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 store= d 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 tes= ting + 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 la= tency. + + =20 + + To limit the number of relations audit logged for SELECT<= /> + and DML statments, consider using object audit logging= + (see ). Object aud= it logging + allows selection of the relations to be logged allowing for reduct= ion + 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. + + + =20 + + Settings + =20 + + Settings may be modified only by a superuser. Allowing normal user= s to + change their settings would defeat the point of an audit log. + + =20 + + Settings can be specified globally (in + postgresql.conf or using + ALTER SYSTEM ... SET), at the database level (using + ALTER DATABASE ... SET), or at the role level (= using + ALTER ROLE ... SET). Note that settings are no= t + inherited through normal role inheritance and SET ROLE= will + not alter a user's pg_audit settings. This is a limit= ation + of the roles system and not inherent to pg_audit. + + =20 + + The pg_audit extension must be loaded in + . Otherwise, an er= ror + will be raised at load time and no audit logging will occur. + + + + + pg_audit.log (string) + + pg_audit.log configuration parameter + + + + + Specifies which classes of statements will be logged by sess= ion + audit logging. Possible values are: + + + + + + READ - SELECT and + COPY when the source is a relation or= a + query. + + + + + WRITE - INSERT, + UPDATE, DELETE, + TRUNCATE, and COPY= when the + destination is a relation. + + + + + FUNCTION - Function calls and + DO blocks. + + + + + ROLE - Statements related to roles an= d + privileges: GRANT, + REVOKE, + CREATE/ALTER/DROP ROLE. + + + + + DDL - All DDL that is not= included + in the ROLE class plus REINDEX. + + + + + PARAMETER - Parameters that were pass= ed for the + statement. Parameters immediately follow the statement = text. + + + + + MISC - Miscellaneous commands, e.g. + DISCARD, FETCH, + CHECKPOINT, VACUUM= =2E + + + + =20 + + Multiple classes can be provided using a comma-separated lis= t and + classes can be subtracted by prefacing the class with a + - sign (see ). + The default is none. + + + + + + pg_audit.log_notice (boolean) + + pg_audit.log_notice configuration param= eter + + + + + Specifies that the audit log messages should be raised as + NOTICE instead of LOG. The primary + advantage is that NOTICE messages can be exposed= + through the user interface. This setting is used for regres= sion + 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 l= eak + which statements are being logged to the user. The default i= s + off. + + + + + + pg_audit.log_relation (boolean) + + pg_audit.log_relation configuration par= ameter + + + + + Specifies whether session audit logging should create a sepa= rate + log entry for each relation referenced in a SELECT<= /> or + DML statement. This is a useful shortcut for ex= haustive + logging without using object audit logging. The default is + off. + + + + + = + pg_audit.role (string) + + pg_audit.role configuration parameter + + + + + Specifies the master role to use for object audit logging. = Muliple + audit roles can be defined by granting them to the master ro= le. + This allows multiple groups to be in charge of different asp= ects + of audit logging. There is no default. + + + + + + + + Session Audit Logging + + + Session audit logging provides detailed logs of all statements exe= cuted + by a user in the backend. + + + + Configuration + + + Session logging is enabled with the + setting. + + Enable session logging for all DML and DDL<= /> and + log all relations in DML statements: + +set pg_audit.log =3D 'write, ddl'; +set pg_audit.log_relation =3D on; + + + + + Enable session logging for all commands except MISC = and + raise audit log messages as NOTICE: + +set pg_audit.log =3D 'all, -misc'; +set pg_audit.log_notice =3D on; + + + + + + Example + + + In this example session audit logging is used to for logging + DDL and SELECT statements. Note that th= e + insert statement is not logged since the WRITE class= + is not enabled + + + + SQL: + + +set pg_audit.log =3D '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; + + + + Log Output: + + + +AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table ac= count +( + id int, + name text, + password text, + description text +); +AUDIT: SESSION,2,1,READ,SELECT,,,select * + from account + + + + + + Object Auditing + + + Object audit logging logs statements that affect a particular rela= tion. + Only SELECT, INSERT, UPDATE an= d + DELETE commands are supported. TRUNCATE i= s not + included because there is no specific privilege for it. + + + + Configuration + + + Object-level audit logging is implemented via the roles system. = The + setting defines the role tha= t + 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 r= ole + in any context. + + + + Set to auditor and= + grant SELECT and DELETE privileges on the + account table. Any SELECT or + DELETE statements on account will now be + logged: + + =20 + +set pg_audit.role =3D 'auditor'; + +grant select, delete + on public.account + to auditor; + + + + + Example + + + In this example object audit logging is used to illustrate how a= + granular approach may be taken towards logging of SELEC= T + and DML statements. Note that logging on the + account table is controlled by column-level permissi= ons, + while logging on account_role_map is table-level. + + + + SQL: + + + +set pg_audit.role =3D '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 =3D 'yada, yada'; + +update account + set password =3D '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 =3D account_role_map.account_id + + + + Log Output: + + + +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 =3D 'HASH2' +AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account,select account.passwo= rd, + account_role_map.role_id + from account + inner join account_role_map + on account.id =3D account_role_map.account_id +AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account_role_map,select accou= nt.password, + account_role_map.role_id + from account + inner join account_role_map + on account.id =3D account_role_map.account_id + + + + + + Format + + + Audit entries are written to the standard logging facility and con= tain + the following columns in comma-separated format: + + + + Output is compliant CSV format only if the log line prefix por= tion + of each log entry is removed. + + + + + + + AUDIT_TYPE - SESSION or + OBJECT. + + + + + STATEMENT_ID - Unique statement ID for this sess= ion. + 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 relat= ion + is logged. + + + + + SUBSTATEMENT_ID - Sequential ID for each + substatement within the main statement. For example, callin= g + a function from a query. Substatement IDs are continuous + even if some substatements are not logged. There may be mul= tiple + entries for a substatement ID when more than one relation is= logged. + + + + + CLASS - e.g. (READ, + ROLE) (see ). + + + + + COMMAND - e.g. ALTER TABLE, + SELECT. + + + + + OBJECT_TYPE - TABLE, + INDEX, VIEW, etc. + Available for SELECT, DML and most + DDL statements. + + + + + OBJECT_NAME - The fully-qualified object name + (e.g. public.account). Available for SELECT, + DML and most DDL statements. + + + + + STATEMENT - Statement executed on the backend. + + + + + + + Use to add any other fields= that + are needed to satisfy your audit log requirements. A typical log = line + prefix might be '%m %u %d: ' which would provide the d= ate/time, + user name, and database name for each audit log. + + + + + Caveats + =20 + + + + Object renames are logged under the name they were renamed to.= + For example, renaming a table will produce the following resul= t: + + + +ALTER TABLE test RENAME TO test2; + =20 +AUDIT: SESSION,36,1,DDL,ALTER TABLE,TABLE,public.test2,ALTER TABLE test = RENAME TO test2 + + + =20 + + + It is possible to have a command logged more than once. For e= xample, + when a table is created with a primary key specified at creati= on 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 statemen= t. + + + + + + Autovacuum and Autoanalyze are not logged, nor are they intend= ed to be. + + + + + + + Authors + + + Abhijit Menon-Sen ams@2ndQuadrant.com, Ian Barwick = ian@2ndQuadrant.com, and David Steele david@pgmaste= rs.net. + + + --------------090907090206020300080306-- --64GIwcHh8oPtBC3J7onH0G1JEGmAosI5n Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v2 iQIcBAEBCAAGBQJVLalZAAoJEIA2uIJQ5SFAZQYQALJLtiYvM/x2RaJi6dH+oZQk l8MD0Hk/9PX52YBUbO5pdytQ30XnZDmwKRVOF9uvzOKEJcyyTyMIBj+SSMeLWKzU dDwK4B/CIWN4ugbbeUZ1YmJR7xygWo2p0fDTAeK0MlFDIXiCEaXdn5AnzeQkb6qU 7uCtBEdAbvPtvEpkRcPSJPcZITVaUpLPEpXvj0Nn8MzGhBjvcyrH1fjl0+bqEZRl NX9OWC6E1ZVw2I1o8sUp99k2Et6RlYycJ6UDCnTY3K2hAo29owtxOPbHJj6zenNo ke4c06Do7BMhqDgiN5iA6sqNzrcvDP0wmNI5yhzvswEsSovLjQ753CQnnH3H0oSq R7CTcSWoin2FB7txy/Hg3hrhh03qnoKsEHT7lV+qhB3ONqqGmLoP64xDIXFks9Hl KHJo7x1fKPB/92tb+aB+0xr1zTqd9h+FE6TDQWI+uwQzqW8s+GFMaLPdFo6jw94c pg2JghQniDdWnnH0clyltXrI2UJ1Qqi3rbZoU3JiEJToFMDjuwKtCVf7hTLERvpn qSUAhi6tijchyq7SmXmsWGMezLjZTAspOghzwU+OfhuO15Ab7B2A731Hfi2qFMlT qt633akXiJOtnBDMJdmgxvzujr8kCN71E5AvhAuYQyhjvEsNoDNElTdAfyYdhpjQ qg/nWCd9LzsViF3ZAPzb =zzls -----END PGP SIGNATURE----- --64GIwcHh8oPtBC3J7onH0G1JEGmAosI5n--