\set VERBOSITY terse
-- Create pgaudit extension
CREATE EXTENSION IF NOT EXISTS pgaudit;
-- Grant all on public schema to public
GRANT ALL ON SCHEMA public TO public;
-- Make sure events don't get logged twice when session logging
SET pgaudit.log = 'all';
SET pgaudit.log_client = ON;
SET pgaudit.log_level = 'notice';
NOTICE:  AUDIT: SESSION,3,1,MISC,SET,,,SET pgaudit.log_level = 'notice',<not logged>
CREATE TABLE tmp (id int, data text);
NOTICE:  AUDIT: SESSION,4,1,DDL,CREATE TABLE,TABLE,public.tmp,"CREATE TABLE tmp (id int, data text)",<not logged>
CREATE TABLE tmp2 AS (SELECT * FROM tmp);
NOTICE:  AUDIT: SESSION,5,1,READ,SELECT,,,CREATE TABLE tmp2 AS (SELECT * FROM tmp),<not logged>
NOTICE:  AUDIT: SESSION,5,2,DDL,CREATE TABLE AS,TABLE,public.tmp2,CREATE TABLE tmp2 AS (SELECT * FROM tmp),<not logged>
-- Reset log_client first to show that audits logs are not set to client
RESET pgaudit.log_client;
DROP TABLE tmp;
DROP TABLE tmp2;
RESET pgaudit.log;
RESET pgaudit.log_level;
--
-- Audit log fields are:
--     AUDIT_TYPE - SESSION or OBJECT
--     STATEMENT_ID - ID of the statement in the current backend
--     SUBSTATEMENT_ID - ID of the substatement in the current backend
--     CLASS - Class of statement being logged (e.g. ROLE, READ, WRITE)
--     COMMAND - e.g. SELECT, CREATE ROLE, UPDATE
--     OBJECT_TYPE - When available, type of object acted on (e.g. TABLE, VIEW)
--     OBJECT_NAME - When available, fully-qualified table of object
--     STATEMENT - The statement being logged
--     PARAMETER - If parameter logging is requested, they will follow the
--                 statement
--     ROWS - If rows logging is requested, they will follow the parameter
SELECT current_user \gset
--
-- Set pgaudit parameters for the current (super)user.
ALTER ROLE :current_user SET pgaudit.log = 'Role';
ALTER ROLE :current_user SET pgaudit.log_level = 'notice';
ALTER ROLE :current_user SET pgaudit.log_client = ON;
\connect - :current_user;
--
-- Create auditor role
CREATE ROLE regress_auditor;
NOTICE:  AUDIT: SESSION,1,1,ROLE,CREATE ROLE,,,CREATE ROLE regress_auditor,<not logged>
--
-- Create first test user
CREATE USER regress_user1 password 'password';
NOTICE:  AUDIT: SESSION,2,1,ROLE,CREATE ROLE,,,CREATE USER regress_user1 password <REDACTED>,<not logged>
ALTER ROLE regress_user1 SET pgaudit.log = 'ddl, ROLE';
NOTICE:  AUDIT: SESSION,3,1,ROLE,ALTER ROLE,,,"ALTER ROLE regress_user1 SET pgaudit.log = 'ddl, ROLE'",<not logged>
ALTER ROLE regress_user1 SET pgaudit.log_level = 'notice';
NOTICE:  AUDIT: SESSION,4,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user1 SET pgaudit.log_level = 'notice',<not logged>
ALTER ROLE regress_user1 PassWord 'password2' NOLOGIN;
NOTICE:  AUDIT: SESSION,5,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user1 PassWord <REDACTED>,<not logged>
ALTER USER regress_user1 encrypted /* random comment */PASSWORD
	/* random comment */
    'md565cb1da342495ea6bb0418a6e5718c38' LOGIN;
NOTICE:  AUDIT: SESSION,6,1,ROLE,ALTER ROLE,,,ALTER USER regress_user1 encrypted /* random comment */PASSWORD <REDACTED>,<not logged>
ALTER ROLE regress_user1 SET pgaudit.log_client = ON;
NOTICE:  AUDIT: SESSION,7,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user1 SET pgaudit.log_client = ON,<not logged>
--
-- Create, select, drop (select will not be audited)
\connect - regress_user1
CREATE TABLE public.test
(
	id INT
);
NOTICE:  AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.test,"CREATE TABLE public.test
(
	id INT
)",<not logged>
SELECT *
  FROM test;
 id 
----
(0 rows)

DROP TABLE test;
NOTICE:  AUDIT: SESSION,2,1,DDL,DROP TABLE,TABLE,public.test,DROP TABLE test,<not logged>
--
-- Create second test user
\connect - :current_user
CREATE ROLE regress_user2 LOGIN password 'password';
NOTICE:  AUDIT: SESSION,1,1,ROLE,CREATE ROLE,,,CREATE ROLE regress_user2 LOGIN password <REDACTED>,<not logged>
ALTER ROLE regress_user2 SET pgaudit.log = 'Read, writE';
NOTICE:  AUDIT: SESSION,2,1,ROLE,ALTER ROLE,,,"ALTER ROLE regress_user2 SET pgaudit.log = 'Read, writE'",<not logged>
ALTER ROLE regress_user2 SET pgaudit.log_catalog = OFF;
NOTICE:  AUDIT: SESSION,3,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user2 SET pgaudit.log_catalog = OFF,<not logged>
ALTER ROLE regress_user2 SET pgaudit.log_client = ON;
NOTICE:  AUDIT: SESSION,4,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user2 SET pgaudit.log_client = ON,<not logged>
ALTER ROLE regress_user2 SET pgaudit.log_level = 'warning';
NOTICE:  AUDIT: SESSION,5,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user2 SET pgaudit.log_level = 'warning',<not logged>
ALTER ROLE regress_user2 SET pgaudit.role = regress_auditor;
NOTICE:  AUDIT: SESSION,6,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user2 SET pgaudit.role = regress_auditor,<not logged>
ALTER ROLE regress_user2 SET pgaudit.log_statement_once = ON;
NOTICE:  AUDIT: SESSION,7,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user2 SET pgaudit.log_statement_once = ON,<not logged>
--
-- Setup role-based tests
CREATE TABLE test2
(
	id INT
);
GRANT SELECT, INSERT, UPDATE, DELETE
   ON test2
   TO regress_user2, regress_user1;
NOTICE:  AUDIT: SESSION,8,1,ROLE,GRANT,TABLE,,"GRANT SELECT, INSERT, UPDATE, DELETE
   ON test2
   TO regress_user2, regress_user1",<not logged>
GRANT SELECT, UPDATE
   ON TABLE public.test2
   TO regress_auditor;
NOTICE:  AUDIT: SESSION,9,1,ROLE,GRANT,TABLE,,"GRANT SELECT, UPDATE
   ON TABLE public.test2
   TO regress_auditor",<not logged>
CREATE TABLE test3
(
	id INT
);
GRANT SELECT, INSERT, UPDATE, DELETE
   ON test3
   TO regress_user2;
NOTICE:  AUDIT: SESSION,10,1,ROLE,GRANT,TABLE,,"GRANT SELECT, INSERT, UPDATE, DELETE
   ON test3
   TO regress_user2",<not logged>
GRANT INSERT
   ON TABLE public.test3
   TO regress_auditor;
NOTICE:  AUDIT: SESSION,11,1,ROLE,GRANT,TABLE,,"GRANT INSERT
   ON TABLE public.test3
   TO regress_auditor",<not logged>
CREATE FUNCTION test2_insert() RETURNS TRIGGER AS $$
BEGIN
	UPDATE test2
	   SET id = id + 90
	 WHERE id = new.id;

	RETURN new;
END $$ LANGUAGE plpgsql security definer;
ALTER FUNCTION test2_insert() OWNER TO regress_user1;
CREATE TRIGGER test2_insert_trg
	AFTER INSERT ON test2
	FOR EACH ROW EXECUTE PROCEDURE test2_insert();
CREATE FUNCTION test2_change(change_id int) RETURNS void AS $$
BEGIN
	UPDATE test2
	   SET id = id + 1
	 WHERE id = change_id;
END $$ LANGUAGE plpgsql security definer;
ALTER FUNCTION test2_change(int) OWNER TO regress_user2;
CREATE VIEW vw_test3 AS
SELECT *
  FROM test3;
GRANT SELECT
   ON vw_test3
   TO regress_user2;
NOTICE:  AUDIT: SESSION,12,1,ROLE,GRANT,TABLE,,"GRANT SELECT
   ON vw_test3
   TO regress_user2",<not logged>
GRANT SELECT
   ON vw_test3
   TO regress_auditor;
NOTICE:  AUDIT: SESSION,13,1,ROLE,GRANT,TABLE,,"GRANT SELECT
   ON vw_test3
   TO regress_auditor",<not logged>
\connect - regress_user2
--
-- Role-based tests
SELECT count(*)
  FROM
(
	SELECT relname
	  FROM pg_class
	  LIMIT 1
) SUBQUERY;
 count 
-------
     1
(1 row)

SELECT *
  FROM test3, test2;
WARNING:  AUDIT: SESSION,1,1,READ,SELECT,,,"SELECT *
  FROM test3, test2",<not logged>
WARNING:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.test2,<previously logged>,<previously logged>
 id | id 
----+----
(0 rows)

--
-- Object logged because of:
-- select on vw_test3
-- select on test2
SELECT *
  FROM vw_test3, test2;
WARNING:  AUDIT: SESSION,2,1,READ,SELECT,,,"SELECT *
  FROM vw_test3, test2",<not logged>
WARNING:  AUDIT: OBJECT,2,1,READ,SELECT,VIEW,public.vw_test3,<previously logged>,<previously logged>
WARNING:  AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.test2,<previously logged>,<previously logged>
 id | id 
----+----
(0 rows)

--
-- Object logged because of:
-- insert on test3
-- select on test2
WITH CTE AS
(
	SELECT id
	  FROM test2
)
INSERT INTO test3
SELECT id
  FROM cte;
WARNING:  AUDIT: SESSION,3,1,WRITE,INSERT,,,"WITH CTE AS
(
	SELECT id
	  FROM test2
)
INSERT INTO test3
SELECT id
  FROM cte",<not logged>
WARNING:  AUDIT: OBJECT,3,1,WRITE,INSERT,TABLE,public.test3,<previously logged>,<previously logged>
WARNING:  AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.test2,<previously logged>,<previously logged>
--
-- Object logged because of:
-- insert on test3
WITH CTE AS
(
	INSERT INTO test3 VALUES (1)
				   RETURNING id
)
INSERT INTO test2
SELECT id
  FROM cte;
WARNING:  AUDIT: SESSION,4,1,WRITE,INSERT,,,"WITH CTE AS
(
	INSERT INTO test3 VALUES (1)
				   RETURNING id
)
INSERT INTO test2
SELECT id
  FROM cte",<not logged>
WARNING:  AUDIT: OBJECT,4,1,WRITE,INSERT,TABLE,public.test3,<previously logged>,<previously logged>
DO $$ BEGIN PERFORM test2_change(91); END $$;
WARNING:  AUDIT: SESSION,5,1,READ,SELECT,,,SELECT test2_change(91),<not logged>
WARNING:  AUDIT: SESSION,5,2,WRITE,UPDATE,,,"UPDATE test2
	   SET id = id + 1
	 WHERE id = change_id",<not logged>
WARNING:  AUDIT: OBJECT,5,2,WRITE,UPDATE,TABLE,public.test2,<previously logged>,<previously logged>
--
-- Object logged because of:
-- insert on test3
-- update on test2
WITH CTE AS
(
	UPDATE test2
	   SET id = 45
	 WHERE id = 92
	RETURNING id
)
INSERT INTO test3
SELECT id
  FROM cte;
WARNING:  AUDIT: SESSION,6,1,WRITE,INSERT,,,"WITH CTE AS
(
	UPDATE test2
	   SET id = 45
	 WHERE id = 92
	RETURNING id
)
INSERT INTO test3
SELECT id
  FROM cte",<not logged>
WARNING:  AUDIT: OBJECT,6,1,WRITE,INSERT,TABLE,public.test3,<previously logged>,<previously logged>
WARNING:  AUDIT: OBJECT,6,1,WRITE,UPDATE,TABLE,public.test2,<previously logged>,<previously logged>
--
-- Object logged because of:
-- insert on test2
WITH CTE AS
(
	INSERT INTO test2 VALUES (37)
				   RETURNING id
)
UPDATE test3
   SET id = cte.id
  FROM cte
 WHERE test3.id <> cte.id;
WARNING:  AUDIT: SESSION,7,1,WRITE,UPDATE,,,"WITH CTE AS
(
	INSERT INTO test2 VALUES (37)
				   RETURNING id
)
UPDATE test3
   SET id = cte.id
  FROM cte
 WHERE test3.id <> cte.id",<not logged>
WARNING:  AUDIT: OBJECT,7,1,WRITE,INSERT,TABLE,public.test2,<previously logged>,<previously logged>
--
-- Be sure that test has correct contents
SELECT *
  FROM test2
 ORDER BY ID;
WARNING:  AUDIT: SESSION,8,1,READ,SELECT,,,"SELECT *
  FROM test2
 ORDER BY ID",<not logged>
WARNING:  AUDIT: OBJECT,8,1,READ,SELECT,TABLE,public.test2,<previously logged>,<previously logged>
 id  
-----
  45
 127
(2 rows)

--
-- Change permissions of user 2 so that only object logging will be done
\connect - :current_user
ALTER ROLE regress_user2 SET pgaudit.log = 'NONE';
NOTICE:  AUDIT: SESSION,1,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user2 SET pgaudit.log = 'NONE',<not logged>
\connect - regress_user2
--
-- Create test4 and add permissions
CREATE TABLE test4
(
	id int,
	name text
);
GRANT SELECT (name)
   ON TABLE public.test4
   TO regress_auditor;
GRANT UPDATE (id)
   ON TABLE public.test4
   TO regress_auditor;
GRANT insert (name)
   ON TABLE public.test4
   TO regress_auditor;
--
-- Not object logged
SELECT id
  FROM public.test4;
 id 
----
(0 rows)

--
-- Object logged because of:
-- select (name) on test4
SELECT name
  FROM public.test4;
WARNING:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.test4,"SELECT name
  FROM public.test4",<not logged>
 name 
------
(0 rows)

--
-- Not object logged
INSERT INTO public.test4 (id)
				  VALUES (1);
--
-- Object logged because of:
-- insert (name) on test4
INSERT INTO public.test4 (name)
				  VALUES ('test');
WARNING:  AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,public.test4,"INSERT INTO public.test4 (name)
				  VALUES ('test')",<not logged>
--
-- Not object logged
UPDATE public.test4
   SET name = 'foo';
--
-- Object logged because of:
-- update (id) on test4
UPDATE public.test4
   SET id = 1;
WARNING:  AUDIT: OBJECT,3,1,WRITE,UPDATE,TABLE,public.test4,"UPDATE public.test4
   SET id = 1",<not logged>
--
-- Object logged because of:
-- update (name) on test4
-- update (name) takes precedence over select (name) due to ordering
update public.test4 set name = 'foo' where name = 'bar';
WARNING:  AUDIT: OBJECT,4,1,WRITE,UPDATE,TABLE,public.test4,update public.test4 set name = 'foo' where name = 'bar',<not logged>
--
-- Confirm that "long" parameter values will not be logged if pgaudit.log_parameter_max_size
-- is set.
\connect - :current_user
ALTER ROLE regress_user2 SET pgaudit.log_parameter_max_size = 50;
NOTICE:  AUDIT: SESSION,1,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user2 SET pgaudit.log_parameter_max_size = 50,<not logged>
ALTER ROLE regress_user2 SET pgaudit.log_parameter = 'on';
NOTICE:  AUDIT: SESSION,2,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user2 SET pgaudit.log_parameter = 'on',<not logged>
\connect - regress_user2
PREPARE testinsert(int, text) AS
    INSERT INTO test4 VALUES($1, $2);
EXECUTE testinsert(1, '*******************************************************');
WARNING:  AUDIT: OBJECT,1,1,WRITE,INSERT,TABLE,public.test4,"PREPARE testinsert(int, text) AS
    INSERT INTO test4 VALUES($1, $2)","1,<long param suppressed>"
DEALLOCATE testinsert;
\connect - :current_user
ALTER ROLE regress_user2 RESET pgaudit.log_parameter_max_size;
NOTICE:  AUDIT: SESSION,1,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user2 RESET pgaudit.log_parameter_max_size,<not logged>
\connect - regress_user2
PREPARE testinsert(int, text) AS
    INSERT INTO test4 VALUES($1, $2);
EXECUTE testinsert(2, '*******************************************************');
WARNING:  AUDIT: OBJECT,1,1,WRITE,INSERT,TABLE,public.test4,"PREPARE testinsert(int, text) AS
    INSERT INTO test4 VALUES($1, $2)","2,*******************************************************"
DEALLOCATE testinsert;
\connect - :current_user
ALTER ROLE regress_user2 RESET pgaudit.log_parameter;
NOTICE:  AUDIT: SESSION,1,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user2 RESET pgaudit.log_parameter,<not logged>
--
-- Change permissions of user 1 so that session logging will be done
\connect - :current_user
--
-- Drop test tables
DROP TABLE test2;
DROP VIEW vw_test3;
DROP TABLE test3;
DROP TABLE test4;
DROP FUNCTION test2_insert();
DROP FUNCTION test2_change(int);
ALTER ROLE regress_user1 SET pgaudit.log = 'DDL, READ';
NOTICE:  AUDIT: SESSION,1,1,ROLE,ALTER ROLE,,,"ALTER ROLE regress_user1 SET pgaudit.log = 'DDL, READ'",<not logged>
\connect - regress_user1
--
-- Create table is session logged
CREATE TABLE public.account
(
	id INT,
	name TEXT,
	password TEXT,
	description TEXT
);
NOTICE:  AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,"CREATE TABLE public.account
(
	id INT,
	name TEXT,
	password TEXT,
	description TEXT
)",<not logged>
--
-- Select is session logged
SELECT *
  FROM account;
NOTICE:  AUDIT: SESSION,2,1,READ,SELECT,,,"SELECT *
  FROM account",<not logged>
 id | name | password | description 
----+------+----------+-------------
(0 rows)

--
-- Insert is not logged
INSERT INTO account (id, name, password, description)
			 VALUES (1, 'regress_user1', 'HASH1', 'blah, blah');
--
-- Change permissions of user 1 so that only object logging will be done
\connect - :current_user
ALTER ROLE regress_user1 SET pgaudit.log = 'none';
NOTICE:  AUDIT: SESSION,1,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user1 SET pgaudit.log = 'none',<not logged>
ALTER ROLE regress_user1 SET pgaudit.role = 'regress_auditor';
NOTICE:  AUDIT: SESSION,2,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user1 SET pgaudit.role = 'regress_auditor',<not logged>
\connect - regress_user1
--
-- ROLE class not set, so regress_auditor grants not logged
GRANT SELECT (password),
	  UPDATE (name, password)
   ON TABLE public.account
   TO regress_auditor;
--
-- Not object logged
SELECT id,
	   name
  FROM account;
 id |     name      
----+---------------
  1 | regress_user1
(1 row)

--
-- Object logged because of:
-- select (password) on account
SELECT password
  FROM account;
NOTICE:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,"SELECT password
  FROM account",<not logged>
 password 
----------
 HASH1
(1 row)

--
-- Not object logged
UPDATE account
   SET description = 'yada, yada';
--
-- Object logged because of:
-- update (password) on account
UPDATE account
   SET password = 'HASH2';
NOTICE:  AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account
   SET password = 'HASH2'",<not logged>
--
-- Change permissions of user 1 so that session relation logging will be done
\connect - :current_user
ALTER ROLE regress_user1 SET pgaudit.log_relation = on;
NOTICE:  AUDIT: SESSION,1,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user1 SET pgaudit.log_relation = on,<not logged>
ALTER ROLE regress_user1 SET pgaudit.log = 'read, WRITE';
NOTICE:  AUDIT: SESSION,2,1,ROLE,ALTER ROLE,,,"ALTER ROLE regress_user1 SET pgaudit.log = 'read, WRITE'",<not logged>
\connect - regress_user1
--
-- Not logged
CREATE TABLE ACCOUNT_ROLE_MAP
(
	account_id INT,
	role_id INT
);
--
-- ROLE class not set, so regress_auditor grants not logged
GRANT SELECT
   ON TABLE public.account_role_map
   TO regress_auditor;
--
-- Object logged because of:
-- select (password) on account
-- select on account_role_map
-- Session logged on all tables because log = read and log_relation = on
SELECT account.password,
	   account_role_map.role_id
  FROM account
	   INNER JOIN account_role_map
			on account.id = account_role_map.account_id;
NOTICE:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,"SELECT account.password,
	   account_role_map.role_id
  FROM account
	   INNER JOIN account_role_map
			on account.id = account_role_map.account_id",<not logged>
NOTICE:  AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.account,"SELECT account.password,
	   account_role_map.role_id
  FROM account
	   INNER JOIN account_role_map
			on account.id = account_role_map.account_id",<not logged>
NOTICE:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account_role_map,"SELECT account.password,
	   account_role_map.role_id
  FROM account
	   INNER JOIN account_role_map
			on account.id = account_role_map.account_id",<not logged>
NOTICE:  AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.account_role_map,"SELECT account.password,
	   account_role_map.role_id
  FROM account
	   INNER JOIN account_role_map
			on account.id = account_role_map.account_id",<not logged>
 password | role_id 
----------+---------
(0 rows)

--
-- Object logged because of:
-- select (password) on account
-- Session logged on all tables because log = read and log_relation = on
SELECT password
  FROM account;
NOTICE:  AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.account,"SELECT password
  FROM account",<not logged>
NOTICE:  AUDIT: SESSION,2,1,READ,SELECT,TABLE,public.account,"SELECT password
  FROM account",<not logged>
 password 
----------
 HASH2
(1 row)

--
-- Not object logged
-- Session logged on all tables because log = read and log_relation = on
UPDATE account
   SET description = 'yada, yada';
NOTICE:  AUDIT: SESSION,3,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account
   SET description = 'yada, yada'",<not logged>
--
-- Object logged because of:
--     select (password) on account (in the where clause)
-- Session logged on all tables because log = read and log_relation = on
SELECT *
  FROM account
 WHERE password = 'HASH2'
   FOR UPDATE;
NOTICE:  AUDIT: OBJECT,4,1,READ,SELECT,TABLE,public.account,"SELECT *
  FROM account
 WHERE password = 'HASH2'
   FOR UPDATE",<not logged>
NOTICE:  AUDIT: SESSION,4,1,READ,SELECT,TABLE,public.account,"SELECT *
  FROM account
 WHERE password = 'HASH2'
   FOR UPDATE",<not logged>
 id |     name      | password | description 
----+---------------+----------+-------------
  1 | regress_user1 | HASH2    | yada, yada
(1 row)

--
-- Object logged because of:
-- select (password) on account (in the where clause)
-- Session logged on all tables because log = read and log_relation = on
UPDATE account
   SET description = 'yada, yada'
 where password = 'HASH2';
NOTICE:  AUDIT: OBJECT,5,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account
   SET description = 'yada, yada'
 where password = 'HASH2'",<not logged>
NOTICE:  AUDIT: SESSION,5,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account
   SET description = 'yada, yada'
 where password = 'HASH2'",<not logged>
--
-- Object logged because of:
-- update (password) on account
-- Session logged on all tables because log = read and log_relation = on
UPDATE account
   SET password = 'HASH2';
NOTICE:  AUDIT: OBJECT,6,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account
   SET password = 'HASH2'",<not logged>
NOTICE:  AUDIT: SESSION,6,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account
   SET password = 'HASH2'",<not logged>
--
-- Change configuration of user 1 so that full statements are not logged
\connect - :current_user
ALTER ROLE regress_user1 RESET pgaudit.log_relation;
NOTICE:  AUDIT: SESSION,1,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user1 RESET pgaudit.log_relation,<not logged>
ALTER ROLE regress_user1 RESET pgaudit.log;
NOTICE:  AUDIT: SESSION,2,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user1 RESET pgaudit.log,<not logged>
ALTER ROLE regress_user1 SET pgaudit.log_statement = OFF;
NOTICE:  AUDIT: SESSION,3,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user1 SET pgaudit.log_statement = OFF,<not logged>
\connect - regress_user1
--
-- Logged but without full statement
SELECT * FROM account;
NOTICE:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,<not logged>,<not logged>
 id |     name      | password | description 
----+---------------+----------+-------------
  1 | regress_user1 | HASH2    | yada, yada
(1 row)

--
-- Change back to superuser to do exhaustive tests
\connect - :current_user
SET pgaudit.log = 'ALL';
NOTICE:  AUDIT: SESSION,1,1,MISC,SET,,,SET pgaudit.log = 'ALL',<not logged>
SET pgaudit.log_level = 'notice';
NOTICE:  AUDIT: SESSION,2,1,MISC,SET,,,SET pgaudit.log_level = 'notice',<not logged>
SET pgaudit.log_client = ON;
NOTICE:  AUDIT: SESSION,3,1,MISC,SET,,,SET pgaudit.log_client = ON,<not logged>
SET pgaudit.log_relation = ON;
NOTICE:  AUDIT: SESSION,4,1,MISC,SET,,,SET pgaudit.log_relation = ON,<not logged>
SET pgaudit.log_parameter = ON;
NOTICE:  AUDIT: SESSION,5,1,MISC,SET,,,SET pgaudit.log_parameter = ON,<none>
--
-- Simple DO block
DO $$
BEGIN
	raise notice 'test';
END $$;
NOTICE:  AUDIT: SESSION,6,1,FUNCTION,DO,,,"DO $$
BEGIN
	raise notice 'test';
END $$",<none>
NOTICE:  test
--
-- Create test schema
CREATE SCHEMA test;
NOTICE:  AUDIT: SESSION,7,1,DDL,CREATE SCHEMA,SCHEMA,test,CREATE SCHEMA test,<none>
--
-- Copy account to stdout
COPY account TO stdout;
NOTICE:  AUDIT: SESSION,8,1,READ,SELECT,TABLE,public.account,COPY account TO stdout,<none>
1	regress_user1	HASH2	yada, yada
--
-- Create a table from a query
CREATE TABLE test.account_copy AS
SELECT *
  FROM account;
NOTICE:  AUDIT: SESSION,9,1,READ,SELECT,TABLE,public.account,"CREATE TABLE test.account_copy AS
SELECT *
  FROM account",<none>
NOTICE:  AUDIT: SESSION,9,2,DDL,CREATE TABLE AS,TABLE,test.account_copy,"CREATE TABLE test.account_copy AS
SELECT *
  FROM account",<none>
--
-- Copy from stdin to account copy
COPY test.account_copy from stdin;
NOTICE:  AUDIT: SESSION,10,1,WRITE,INSERT,TABLE,test.account_copy,COPY test.account_copy from stdin,<none>
--
-- Test prepared statement
PREPARE pgclassstmt (oid) AS
SELECT *
  FROM account
 WHERE id = $1;
NOTICE:  AUDIT: SESSION,11,1,READ,PREPARE,,,"PREPARE pgclassstmt (oid) AS
SELECT *
  FROM account
 WHERE id = $1",<none>
EXECUTE pgclassstmt (1);
NOTICE:  AUDIT: SESSION,12,1,READ,SELECT,TABLE,public.account,"PREPARE pgclassstmt (oid) AS
SELECT *
  FROM account
 WHERE id = $1",1
NOTICE:  AUDIT: SESSION,12,2,MISC,EXECUTE,,,EXECUTE pgclassstmt (1),<none>
 id |     name      | password | description 
----+---------------+----------+-------------
  1 | regress_user1 | HASH2    | yada, yada
(1 row)

DEALLOCATE pgclassstmt;
NOTICE:  AUDIT: SESSION,13,1,MISC,DEALLOCATE,,,DEALLOCATE pgclassstmt,<none>
--
-- Test cursor
BEGIN;
NOTICE:  AUDIT: SESSION,14,1,MISC,BEGIN,,,BEGIN,<none>
DECLARE ctest SCROLL CURSOR FOR
SELECT count(*)
  FROM
(
	SELECT relname
	  FROM pg_class
	 LIMIT 1
 ) subquery;
NOTICE:  AUDIT: SESSION,15,1,READ,SELECT,TABLE,pg_catalog.pg_class,"DECLARE ctest SCROLL CURSOR FOR
SELECT count(*)
  FROM
(
	SELECT relname
	  FROM pg_class
	 LIMIT 1
 ) subquery",<none>
NOTICE:  AUDIT: SESSION,15,2,READ,DECLARE CURSOR,,,"DECLARE ctest SCROLL CURSOR FOR
SELECT count(*)
  FROM
(
	SELECT relname
	  FROM pg_class
	 LIMIT 1
 ) subquery",<none>
FETCH NEXT FROM ctest;
NOTICE:  AUDIT: SESSION,16,1,MISC,FETCH,,,FETCH NEXT FROM ctest,<none>
 count 
-------
     1
(1 row)

CLOSE ctest;
NOTICE:  AUDIT: SESSION,17,1,MISC,CLOSE CURSOR,,,CLOSE ctest,<none>
COMMIT;
NOTICE:  AUDIT: SESSION,18,1,MISC,COMMIT,,,COMMIT,<none>
--
-- Turn off log_catalog and pg_class will not be logged
SET pgaudit.log_catalog = OFF;
NOTICE:  AUDIT: SESSION,19,1,MISC,SET,,,SET pgaudit.log_catalog = OFF,<none>
SELECT count(*)
  FROM
(
	SELECT relname
	  FROM pg_class
	 LIMIT 1
 ) subquery;
 count 
-------
     1
(1 row)

--
-- Test prepared insert
CREATE TABLE test.test_insert
(
	id INT
);
NOTICE:  AUDIT: SESSION,20,1,DDL,CREATE TABLE,TABLE,test.test_insert,"CREATE TABLE test.test_insert
(
	id INT
)",<none>
PREPARE pgclassstmt (oid) AS
INSERT INTO test.test_insert (id)
					  VALUES ($1);
NOTICE:  AUDIT: SESSION,21,1,WRITE,PREPARE,,,"PREPARE pgclassstmt (oid) AS
INSERT INTO test.test_insert (id)
					  VALUES ($1)",<none>
EXECUTE pgclassstmt (1);
NOTICE:  AUDIT: SESSION,22,1,WRITE,INSERT,TABLE,test.test_insert,"PREPARE pgclassstmt (oid) AS
INSERT INTO test.test_insert (id)
					  VALUES ($1)",1
NOTICE:  AUDIT: SESSION,22,2,MISC,EXECUTE,,,EXECUTE pgclassstmt (1),<none>
--
-- 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,23,1,DDL,CREATE TABLE,TABLE,public.test,"CREATE TABLE public.test
(
	id INT,
	name TEXT,
	description TEXT,
	CONSTRAINT test_pkey PRIMARY KEY (id)
)",<none>
NOTICE:  AUDIT: SESSION,23,1,DDL,CREATE INDEX,INDEX,public.test_pkey,"CREATE TABLE public.test
(
	id INT,
	name TEXT,
	description TEXT,
	CONSTRAINT test_pkey PRIMARY KEY (id)
)",<none>
--
-- Check that analyze is logged
ANALYZE test;
NOTICE:  AUDIT: SESSION,24,1,MISC,ANALYZE,,,ANALYZE test,<none>
--
-- Grants to public should not cause object logging (session logging will
-- still happen)
GRANT SELECT
  ON TABLE public.test
  TO PUBLIC;
NOTICE:  AUDIT: SESSION,25,1,ROLE,GRANT,TABLE,,"GRANT SELECT
  ON TABLE public.test
  TO PUBLIC",<none>
SELECT *
  FROM test;
NOTICE:  AUDIT: SESSION,26,1,READ,SELECT,TABLE,public.test,"SELECT *
  FROM test",<none>
 id | name | description 
----+------+-------------
(0 rows)

-- Check that statements without columns log
SELECT
  FROM test;
NOTICE:  AUDIT: SESSION,27,1,READ,SELECT,TABLE,public.test,"SELECT
  FROM test",<none>
--
(0 rows)

SELECT 1,
	   substring('Thomas' from 2 for 3);
NOTICE:  AUDIT: SESSION,28,1,READ,SELECT,,,"SELECT 1,
	   substring('Thomas' from 2 for 3)",<none>
 ?column? | substring 
----------+-----------
        1 | hom
(1 row)

DO $$
DECLARE
	test INT;
BEGIN
	SELECT 1
	  INTO test;
END $$;
NOTICE:  AUDIT: SESSION,29,1,FUNCTION,DO,,,"DO $$
DECLARE
	test INT;
BEGIN
	SELECT 1
	  INTO test;
END $$",<none>
NOTICE:  AUDIT: SESSION,29,2,READ,SELECT,,,SELECT 1,<none>
explain select 1;
NOTICE:  AUDIT: SESSION,30,1,READ,SELECT,,,explain select 1,<none>
NOTICE:  AUDIT: SESSION,30,2,MISC,EXPLAIN,,,explain select 1,<none>
                QUERY PLAN                
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=4)
(1 row)

--
-- Test that looks inside of do blocks log
INSERT INTO TEST (id)
		  VALUES (1);
NOTICE:  AUDIT: SESSION,31,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)
		  VALUES (1)",<none>
INSERT INTO TEST (id)
		  VALUES (2);
NOTICE:  AUDIT: SESSION,32,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)
		  VALUES (2)",<none>
INSERT INTO TEST (id)
		  VALUES (3);
NOTICE:  AUDIT: SESSION,33,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)
		  VALUES (3)",<none>
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,34,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 $$",<none>
NOTICE:  AUDIT: SESSION,34,2,READ,SELECT,TABLE,public.test,"SELECT id
		  FROM test",<none>
NOTICE:  AUDIT: SESSION,34,3,WRITE,INSERT,TABLE,public.test,"INSERT INTO test (id)
			 VALUES (result.id + 100)",",,1"
NOTICE:  AUDIT: SESSION,34,4,WRITE,INSERT,TABLE,public.test,"INSERT INTO test (id)
			 VALUES (result.id + 100)",",,2"
NOTICE:  AUDIT: SESSION,34,5,WRITE,INSERT,TABLE,public.test,"INSERT INTO test (id)
			 VALUES (result.id + 100)",",,3"
--
-- Test obfuscated dynamic sql for clean logging
DO $$
DECLARE
	table_name TEXT = 'do_table';
BEGIN
	EXECUTE E'\t\n\r CREATE TABLE ' || table_name || E' ("weird name" INT)\t\n\r ; DROP table ' || table_name;
END $$;
NOTICE:  AUDIT: SESSION,35,1,FUNCTION,DO,,,"DO $$
DECLARE
	table_name TEXT = 'do_table';
BEGIN
	EXECUTE E'\t\n\r CREATE TABLE ' || table_name || E' (""weird name"" INT)\t\n\r ; DROP table ' || table_name;
END $$",<none>
NOTICE:  AUDIT: SESSION,35,2,DDL,CREATE TABLE,TABLE,public.do_table,"CREATE TABLE do_table (""weird name"" INT)",<none>
NOTICE:  AUDIT: SESSION,35,3,DDL,DROP TABLE,TABLE,public.do_table,DROP table do_table,<none>
--
-- Generate an error and make sure the stack gets cleared
DO $$
BEGIN
	CREATE TABLE bogus.test_block
	(
		id INT
	);
END $$;
NOTICE:  AUDIT: SESSION,36,1,FUNCTION,DO,,,"DO $$
BEGIN
	CREATE TABLE bogus.test_block
	(
		id INT
	);
END $$",<none>
ERROR:  schema "bogus" does not exist at character 14
--
-- Test alter table statements
ALTER TABLE public.test
	DROP COLUMN description ;
NOTICE:  AUDIT: SESSION,37,1,DDL,ALTER TABLE,TABLE COLUMN,public.test.description,"ALTER TABLE public.test
	DROP COLUMN description",<none>
NOTICE:  AUDIT: SESSION,37,1,DDL,ALTER TABLE,TABLE,public.test,"ALTER TABLE public.test
	DROP COLUMN description",<none>
ALTER TABLE public.test
	RENAME TO test2;
NOTICE:  AUDIT: SESSION,38,1,DDL,ALTER TABLE,TABLE,public.test2,"ALTER TABLE public.test
	RENAME TO test2",<none>
ALTER TABLE public.test2
	SET SCHEMA test;
NOTICE:  AUDIT: SESSION,39,1,DDL,ALTER TABLE,TABLE,test.test2,"ALTER TABLE public.test2
	SET SCHEMA test",<none>
ALTER TABLE test.test2
	ADD COLUMN description TEXT;
NOTICE:  AUDIT: SESSION,40,1,DDL,ALTER TABLE,TABLE,test.test2,"ALTER TABLE test.test2
	ADD COLUMN description TEXT",<none>
ALTER TABLE test.test2
	DROP COLUMN description;
NOTICE:  AUDIT: SESSION,41,1,DDL,ALTER TABLE,TABLE COLUMN,test.test2.description,"ALTER TABLE test.test2
	DROP COLUMN description",<none>
NOTICE:  AUDIT: SESSION,41,1,DDL,ALTER TABLE,TABLE,test.test2,"ALTER TABLE test.test2
	DROP COLUMN description",<none>
DROP TABLE test.test2;
NOTICE:  AUDIT: SESSION,42,1,DDL,DROP TABLE,TABLE,test.test2,DROP TABLE test.test2,<none>
NOTICE:  AUDIT: SESSION,42,1,DDL,DROP TABLE,TABLE CONSTRAINT,test_pkey on test.test2,DROP TABLE test.test2,<none>
NOTICE:  AUDIT: SESSION,42,1,DDL,DROP TABLE,INDEX,test.test_pkey,DROP TABLE test.test2,<none>
--
-- 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,43,1,DDL,CREATE SCHEMA,SCHEMA,foo,"CREATE SCHEMA foo
	CREATE TABLE foo.bar (id int)
	CREATE TABLE foo.baz (id int)",<none>
NOTICE:  AUDIT: SESSION,43,1,DDL,CREATE TABLE,TABLE,foo.bar,"CREATE SCHEMA foo
	CREATE TABLE foo.bar (id int)
	CREATE TABLE foo.baz (id int)",<none>
NOTICE:  AUDIT: SESSION,43,1,DDL,CREATE TABLE,TABLE,foo.baz,"CREATE SCHEMA foo
	CREATE TABLE foo.bar (id int)
	CREATE TABLE foo.baz (id int)",<none>
--
-- Test aggregate
CREATE FUNCTION public.int_add
(
	a INT,
	b INT
)
	RETURNS INT LANGUAGE plpgsql AS $$
BEGIN
	return a + b;
END $$;
NOTICE:  AUDIT: SESSION,44,1,DDL,CREATE FUNCTION,FUNCTION,"public.int_add(integer,integer)","CREATE FUNCTION public.int_add
(
	a INT,
	b INT
)
	RETURNS INT LANGUAGE plpgsql AS $$
BEGIN
	return a + b;
END $$",<none>
SELECT int_add(1, 1);
NOTICE:  AUDIT: SESSION,45,1,READ,SELECT,,,"SELECT int_add(1, 1)",<none>
NOTICE:  AUDIT: SESSION,45,2,FUNCTION,EXECUTE,FUNCTION,public.int_add,"SELECT int_add(1, 1)",<none>
 int_add 
---------
       2
(1 row)

CREATE AGGREGATE public.sum_test(INT) (SFUNC=public.int_add, STYPE=INT, INITCOND='0');
NOTICE:  AUDIT: SESSION,46,1,DDL,CREATE AGGREGATE,AGGREGATE,public.sum_test(integer),"CREATE AGGREGATE public.sum_test(INT) (SFUNC=public.int_add, STYPE=INT, INITCOND='0')",<none>
ALTER AGGREGATE public.sum_test(integer) RENAME TO sum_test2;
NOTICE:  AUDIT: SESSION,47,1,DDL,ALTER AGGREGATE,AGGREGATE,public.sum_test2(integer),ALTER AGGREGATE public.sum_test(integer) RENAME TO sum_test2,<none>
--
-- Test conversion
CREATE CONVERSION public.conversion_test FOR 'latin1' TO 'utf8' FROM pg_catalog.iso8859_1_to_utf8;
NOTICE:  AUDIT: SESSION,48,1,DDL,CREATE CONVERSION,CONVERSION,public.conversion_test,CREATE CONVERSION public.conversion_test FOR 'latin1' TO 'utf8' FROM pg_catalog.iso8859_1_to_utf8,<none>
ALTER CONVERSION public.conversion_test RENAME TO conversion_test2;
NOTICE:  AUDIT: SESSION,49,1,DDL,ALTER CONVERSION,CONVERSION,public.conversion_test2,ALTER CONVERSION public.conversion_test RENAME TO conversion_test2,<none>
--
-- Test create/alter/drop database
CREATE DATABASE contrib_regression_pgaudit;
NOTICE:  AUDIT: SESSION,50,1,DDL,CREATE DATABASE,,,CREATE DATABASE contrib_regression_pgaudit,<none>
ALTER DATABASE contrib_regression_pgaudit RENAME TO contrib_regression_pgaudit2;
NOTICE:  AUDIT: SESSION,51,1,DDL,ALTER DATABASE,,,ALTER DATABASE contrib_regression_pgaudit RENAME TO contrib_regression_pgaudit2,<none>
DROP DATABASE contrib_regression_pgaudit2;
NOTICE:  AUDIT: SESSION,52,1,DDL,DROP DATABASE,,,DROP DATABASE contrib_regression_pgaudit2,<none>
-- Test role as a substmt
SET pgaudit.log = 'ROLE';
CREATE TABLE t ();
CREATE ROLE regress_alice;
NOTICE:  AUDIT: SESSION,53,1,ROLE,CREATE ROLE,,,CREATE ROLE regress_alice,<none>
CREATE SCHEMA foo2
	GRANT SELECT
	   ON public.t
	   TO regress_alice;
NOTICE:  AUDIT: SESSION,54,1,ROLE,GRANT,TABLE,,"CREATE SCHEMA foo2
	GRANT SELECT
	   ON public.t
	   TO regress_alice",<none>
drop table public.t;
drop role regress_alice;
NOTICE:  AUDIT: SESSION,55,1,ROLE,DROP ROLE,,,drop role regress_alice,<none>
--
-- Test for non-empty stack error
CREATE OR REPLACE FUNCTION get_test_id(_ret REFCURSOR) RETURNS REFCURSOR
LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
    OPEN _ret FOR SELECT 200;
    RETURN _ret;
END $$;
BEGIN;
    SELECT get_test_id('_ret');
 get_test_id 
-------------
 _ret
(1 row)

    SELECT get_test_id('_ret2');
 get_test_id 
-------------
 _ret2
(1 row)

    FETCH ALL FROM _ret;
 ?column? 
----------
      200
(1 row)

    FETCH ALL FROM _ret2;
 ?column? 
----------
      200
(1 row)

    CLOSE _ret;
    CLOSE _ret2;
END;
--
-- Test that frees a memory context earlier than expected
SET pgaudit.log = 'ALL';
NOTICE:  AUDIT: SESSION,56,1,MISC,SET,,,SET pgaudit.log = 'ALL',<none>
CREATE TABLE hoge
(
	id int
);
NOTICE:  AUDIT: SESSION,57,1,DDL,CREATE TABLE,TABLE,public.hoge,"CREATE TABLE hoge
(
	id int
)",<none>
CREATE FUNCTION test()
	RETURNS INT AS $$
DECLARE
	cur1 cursor for select * from hoge;
	tmp int;
BEGIN
	OPEN cur1;
	FETCH cur1 into tmp;
	RETURN tmp;
END $$
LANGUAGE plpgsql ;
NOTICE:  AUDIT: SESSION,58,1,DDL,CREATE FUNCTION,FUNCTION,public.test(),"CREATE FUNCTION test()
	RETURNS INT AS $$
DECLARE
	cur1 cursor for select * from hoge;
	tmp int;
BEGIN
	OPEN cur1;
	FETCH cur1 into tmp;
	RETURN tmp;
END $$
LANGUAGE plpgsql",<none>
SELECT test();
NOTICE:  AUDIT: SESSION,59,1,READ,SELECT,,,SELECT test(),<none>
NOTICE:  AUDIT: SESSION,59,2,FUNCTION,EXECUTE,FUNCTION,public.test,SELECT test(),<none>
NOTICE:  AUDIT: SESSION,59,3,READ,SELECT,TABLE,public.hoge,select * from hoge,<none>
 test 
------
     
(1 row)

--
-- Delete all rows then delete 1 row
SET pgaudit.log = 'write';
SET pgaudit.role = 'regress_auditor';
create table bar
(
	col int
);
grant delete
   on bar
   to regress_auditor;
insert into bar (col)
		 values (1);
NOTICE:  AUDIT: SESSION,60,1,WRITE,INSERT,TABLE,public.bar,"insert into bar (col)
		 values (1)",<none>
delete from bar;
NOTICE:  AUDIT: OBJECT,61,1,WRITE,DELETE,TABLE,public.bar,delete from bar,<none>
NOTICE:  AUDIT: SESSION,61,1,WRITE,DELETE,TABLE,public.bar,delete from bar,<none>
insert into bar (col)
		 values (1);
NOTICE:  AUDIT: SESSION,62,1,WRITE,INSERT,TABLE,public.bar,"insert into bar (col)
		 values (1)",<none>
delete from bar
 where col = 1;
NOTICE:  AUDIT: OBJECT,63,1,WRITE,DELETE,TABLE,public.bar,"delete from bar
 where col = 1",<none>
NOTICE:  AUDIT: SESSION,63,1,WRITE,DELETE,TABLE,public.bar,"delete from bar
 where col = 1",<none>
drop table bar;
--
-- Grant roles to each other
SET pgaudit.log = 'role';
GRANT regress_user1 TO regress_user2;
NOTICE:  AUDIT: SESSION,64,1,ROLE,GRANT ROLE,,,GRANT regress_user1 TO regress_user2,<none>
REVOKE regress_user1 FROM regress_user2;
NOTICE:  AUDIT: SESSION,65,1,ROLE,REVOKE ROLE,,,REVOKE regress_user1 FROM regress_user2,<none>
--
-- Test that FK references do not log but triggers still do
SET pgaudit.log = 'READ,WRITE';
SET pgaudit.role TO 'regress_auditor';
CREATE TABLE aaa
(
	ID int primary key
);
CREATE TABLE bbb
(
	id int
		references aaa(id)
);
CREATE FUNCTION bbb_insert() RETURNS TRIGGER AS $$
BEGIN
	UPDATE bbb set id = new.id + 1;

	RETURN new;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER bbb_insert_trg
	AFTER INSERT ON bbb
	FOR EACH ROW EXECUTE PROCEDURE bbb_insert();
GRANT SELECT, UPDATE
   ON aaa
   TO regress_auditor;
GRANT UPDATE
   ON bbb
   TO regress_auditor;
INSERT INTO aaa VALUES (generate_series(1,100));
NOTICE:  AUDIT: SESSION,66,1,WRITE,INSERT,TABLE,public.aaa,"INSERT INTO aaa VALUES (generate_series(1,100))",<none>
SET pgaudit.log_parameter TO OFF;
INSERT INTO bbb VALUES (1);
NOTICE:  AUDIT: SESSION,67,1,WRITE,INSERT,TABLE,public.bbb,INSERT INTO bbb VALUES (1),<not logged>
NOTICE:  AUDIT: OBJECT,67,2,READ,SELECT,TABLE,public.aaa,"SELECT 1 FROM ONLY ""public"".""aaa"" x WHERE ""id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",<not logged>
NOTICE:  AUDIT: SESSION,67,2,READ,SELECT,TABLE,public.aaa,"SELECT 1 FROM ONLY ""public"".""aaa"" x WHERE ""id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",<not logged>
NOTICE:  AUDIT: OBJECT,67,3,WRITE,UPDATE,TABLE,public.bbb,UPDATE bbb set id = new.id + 1,<not logged>
NOTICE:  AUDIT: SESSION,67,3,WRITE,UPDATE,TABLE,public.bbb,UPDATE bbb set id = new.id + 1,<not logged>
NOTICE:  AUDIT: OBJECT,67,4,READ,SELECT,TABLE,public.aaa,"SELECT 1 FROM ONLY ""public"".""aaa"" x WHERE ""id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",<not logged>
NOTICE:  AUDIT: SESSION,67,4,READ,SELECT,TABLE,public.aaa,"SELECT 1 FROM ONLY ""public"".""aaa"" x WHERE ""id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",<not logged>
SET pgaudit.log_parameter TO ON;
DROP TABLE bbb;
DROP TABLE aaa;
-- Test create table as after extension as been dropped
DROP EXTENSION pgaudit;
CREATE TABLE tmp (id int, data text);
CREATE TABLE tmp2 AS (SELECT * FROM tmp);
NOTICE:  AUDIT: SESSION,68,1,READ,SELECT,TABLE,public.tmp,CREATE TABLE tmp2 AS (SELECT * FROM tmp),<none>
DROP TABLE tmp;
DROP TABLE tmp2;
--
-- Test MISC
SET pgaudit.log = 'MISC';
NOTICE:  AUDIT: SESSION,69,1,MISC,SET,,,SET pgaudit.log = 'MISC',<none>
SET pgaudit.log_level = 'notice';
NOTICE:  AUDIT: SESSION,70,1,MISC,SET,,,SET pgaudit.log_level = 'notice',<none>
SET pgaudit.log_client = ON;
NOTICE:  AUDIT: SESSION,71,1,MISC,SET,,,SET pgaudit.log_client = ON,<none>
SET pgaudit.log_relation = ON;
NOTICE:  AUDIT: SESSION,72,1,MISC,SET,,,SET pgaudit.log_relation = ON,<none>
SET pgaudit.log_parameter = ON;
NOTICE:  AUDIT: SESSION,73,1,MISC,SET,,,SET pgaudit.log_parameter = ON,<none>
CREATE ROLE regress_alice;
SET ROLE regress_alice;
NOTICE:  AUDIT: SESSION,74,1,MISC,SET,,,SET ROLE regress_alice,<none>
CREATE TABLE t (a int, b text);
SET search_path TO test, public;
NOTICE:  AUDIT: SESSION,75,1,MISC,SET,,,"SET search_path TO test, public",<none>
INSERT INTO t VALUES (1, 'misc');
VACUUM t;
NOTICE:  AUDIT: SESSION,76,1,MISC,VACUUM,,,VACUUM t,<none>
RESET ROLE;
NOTICE:  AUDIT: SESSION,77,1,MISC,RESET,,,RESET ROLE,<none>
--
-- Test MISC_SET
SET pgaudit.log = 'MISC_SET';
NOTICE:  AUDIT: SESSION,78,1,MISC,SET,,,SET pgaudit.log = 'MISC_SET',<none>
SET ROLE regress_alice;
NOTICE:  AUDIT: SESSION,79,1,MISC,SET,,,SET ROLE regress_alice,<none>
SET search_path TO public;
NOTICE:  AUDIT: SESSION,80,1,MISC,SET,,,SET search_path TO public,<none>
INSERT INTO t VALUES (2, 'misc_set');
VACUUM t;
RESET ROLE;
NOTICE:  AUDIT: SESSION,81,1,MISC,RESET,,,RESET ROLE,<none>
--
-- Test ALL, -MISC, MISC_SET
SET pgaudit.log = 'ALL, -MISC, MISC_SET';
NOTICE:  AUDIT: SESSION,82,1,MISC,SET,,,"SET pgaudit.log = 'ALL, -MISC, MISC_SET'",<none>
SET search_path TO public;
NOTICE:  AUDIT: SESSION,83,1,MISC,SET,,,SET search_path TO public,<none>
INSERT INTO t VALUES (3, 'all, -misc, misc_set');
NOTICE:  AUDIT: SESSION,84,1,WRITE,INSERT,TABLE,public.t,"INSERT INTO t VALUES (3, 'all, -misc, misc_set')",<none>
VACUUM t;
RESET ROLE;
NOTICE:  AUDIT: SESSION,85,1,MISC,RESET,,,RESET ROLE,<none>
DROP TABLE public.t;
NOTICE:  AUDIT: SESSION,86,1,DDL,DROP TABLE,,,DROP TABLE public.t,<none>
DROP ROLE regress_alice;
NOTICE:  AUDIT: SESSION,87,1,ROLE,DROP ROLE,,,DROP ROLE regress_alice,<none>
--
-- Test PARTITIONED table
CREATE TABLE h(x int ,y int) PARTITION BY HASH(x);
NOTICE:  AUDIT: SESSION,88,1,DDL,CREATE TABLE,,,"CREATE TABLE h(x int ,y int) PARTITION BY HASH(x)",<none>
CREATE TABLE h_0 partition OF h FOR VALUES WITH ( MODULUS 2, REMAINDER 0);
NOTICE:  AUDIT: SESSION,89,1,DDL,CREATE TABLE,,,"CREATE TABLE h_0 partition OF h FOR VALUES WITH ( MODULUS 2, REMAINDER 0)",<none>
CREATE TABLE h_1 partition OF h FOR VALUES WITH ( MODULUS 2, REMAINDER 1);
NOTICE:  AUDIT: SESSION,90,1,DDL,CREATE TABLE,,,"CREATE TABLE h_1 partition OF h FOR VALUES WITH ( MODULUS 2, REMAINDER 1)",<none>
INSERT INTO h VALUES(1,1);
NOTICE:  AUDIT: SESSION,91,1,WRITE,INSERT,TABLE,public.h,"INSERT INTO h VALUES(1,1)",<none>
SELECT * FROM h;
NOTICE:  AUDIT: SESSION,92,1,READ,SELECT,TABLE,public.h,SELECT * FROM h,<none>
 x | y 
---+---
 1 | 1
(1 row)

SELECT * FROM h_0;
NOTICE:  AUDIT: SESSION,93,1,READ,SELECT,TABLE,public.h_0,SELECT * FROM h_0,<none>
 x | y 
---+---
 1 | 1
(1 row)

CREATE INDEX h_idx ON h (x);
NOTICE:  AUDIT: SESSION,94,1,DDL,CREATE INDEX,,,CREATE INDEX h_idx ON h (x),<none>
DROP INDEX h_idx;
NOTICE:  AUDIT: SESSION,95,1,DDL,DROP INDEX,,,DROP INDEX h_idx,<none>
DROP TABLE h;
NOTICE:  AUDIT: SESSION,96,1,DDL,DROP TABLE,,,DROP TABLE h,<none>
--
-- Test rows retrived or affected by statements
\connect - :current_user
SET pgaudit.log = 'all';
NOTICE:  AUDIT: SESSION,1,1,MISC,SET,,,SET pgaudit.log = 'all',<not logged>
SET pgaudit.log_client = on;
NOTICE:  AUDIT: SESSION,2,1,MISC,SET,,,SET pgaudit.log_client = on,<not logged>
SET pgaudit.log_relation = on;
NOTICE:  AUDIT: SESSION,3,1,MISC,SET,,,SET pgaudit.log_relation = on,<not logged>
SET pgaudit.log_statement_once = off;
NOTICE:  AUDIT: SESSION,4,1,MISC,SET,,,SET pgaudit.log_statement_once = off,<not logged>
SET pgaudit.log_parameter = on;
NOTICE:  AUDIT: SESSION,5,1,MISC,SET,,,SET pgaudit.log_parameter = on,<none>
SET pgaudit.log_rows = on;
NOTICE:  AUDIT: SESSION,6,1,MISC,SET,,,SET pgaudit.log_rows = on,<none>,0
--
-- Test DDL
CREATE TABLE test2
(
	id int,
	name text
);
NOTICE:  AUDIT: SESSION,7,1,DDL,CREATE TABLE,,,"CREATE TABLE test2
(
	id int,
	name text
)",<none>,0
CREATE TABLE test3
(
	id int,
	name text
);
NOTICE:  AUDIT: SESSION,8,1,DDL,CREATE TABLE,,,"CREATE TABLE test3
(
	id int,
	name text
)",<none>,0
CREATE FUNCTION test2_insert() RETURNS TRIGGER AS $$
BEGIN
	UPDATE test2
	   SET id = id + 90
	 WHERE id = new.id;

	RETURN new;
END $$ LANGUAGE plpgsql security definer;
NOTICE:  AUDIT: SESSION,9,1,DDL,CREATE FUNCTION,,,"CREATE FUNCTION test2_insert() RETURNS TRIGGER AS $$
BEGIN
	UPDATE test2
	   SET id = id + 90
	 WHERE id = new.id;

	RETURN new;
END $$ LANGUAGE plpgsql security definer",<none>,0
CREATE TRIGGER test2_insert_trg
	AFTER INSERT ON test2
	FOR EACH ROW EXECUTE PROCEDURE test2_insert();
NOTICE:  AUDIT: SESSION,10,1,DDL,CREATE TRIGGER,,,"CREATE TRIGGER test2_insert_trg
	AFTER INSERT ON test2
	FOR EACH ROW EXECUTE PROCEDURE test2_insert()",<none>,0
CREATE FUNCTION test2_change(change_id int) RETURNS void AS $$
BEGIN
	UPDATE test2
	   SET id = id + 1
	 WHERE id = change_id;
END $$ LANGUAGE plpgsql security definer;
NOTICE:  AUDIT: SESSION,11,1,DDL,CREATE FUNCTION,,,"CREATE FUNCTION test2_change(change_id int) RETURNS void AS $$
BEGIN
	UPDATE test2
	   SET id = id + 1
	 WHERE id = change_id;
END $$ LANGUAGE plpgsql security definer",<none>,0
CREATE VIEW vw_test3 AS
SELECT *
  FROM test3;
NOTICE:  AUDIT: SESSION,12,1,DDL,CREATE VIEW,,,"CREATE VIEW vw_test3 AS
SELECT *
  FROM test3",<none>,0
--
-- Test DML
INSERT INTO test2 (id, name)
		  VALUES (1, 'a');
NOTICE:  AUDIT: SESSION,13,1,WRITE,UPDATE,TABLE,public.test2,"UPDATE test2
	   SET id = id + 90
	 WHERE id = new.id",",,,,,,,,,,,,,1",1
NOTICE:  AUDIT: SESSION,13,2,WRITE,INSERT,TABLE,public.test2,"INSERT INTO test2 (id, name)
		  VALUES (1, 'a')",<none>,1
INSERT INTO test2 (id, name)
		  VALUES (2, 'b');
NOTICE:  AUDIT: SESSION,14,1,WRITE,UPDATE,TABLE,public.test2,"UPDATE test2
	   SET id = id + 90
	 WHERE id = new.id",",,,,,,,,,,,,,2",1
NOTICE:  AUDIT: SESSION,14,2,WRITE,INSERT,TABLE,public.test2,"INSERT INTO test2 (id, name)
		  VALUES (2, 'b')",<none>,1
INSERT INTO test2 (id, name)
		  VALUES (3, 'c');
NOTICE:  AUDIT: SESSION,15,1,WRITE,UPDATE,TABLE,public.test2,"UPDATE test2
	   SET id = id + 90
	 WHERE id = new.id",",,,,,,,,,,,,,3",1
NOTICE:  AUDIT: SESSION,15,2,WRITE,INSERT,TABLE,public.test2,"INSERT INTO test2 (id, name)
		  VALUES (3, 'c')",<none>,1
INSERT INTO test3 (id, name)
		  VALUES (1, 'a');
NOTICE:  AUDIT: SESSION,16,1,WRITE,INSERT,TABLE,public.test3,"INSERT INTO test3 (id, name)
		  VALUES (1, 'a')",<none>,1
INSERT INTO test3 (id, name)
		  VALUES (2, 'b');
NOTICE:  AUDIT: SESSION,17,1,WRITE,INSERT,TABLE,public.test3,"INSERT INTO test3 (id, name)
		  VALUES (2, 'b')",<none>,1
INSERT INTO test3 (id, name)
		  VALUES (3, 'c');
NOTICE:  AUDIT: SESSION,18,1,WRITE,INSERT,TABLE,public.test3,"INSERT INTO test3 (id, name)
		  VALUES (3, 'c')",<none>,1
SELECT *
  FROM test3, test2;
NOTICE:  AUDIT: SESSION,19,1,READ,SELECT,TABLE,public.test3,"SELECT *
  FROM test3, test2",<none>,9
NOTICE:  AUDIT: SESSION,19,1,READ,SELECT,TABLE,public.test2,"SELECT *
  FROM test3, test2",<none>,9
 id | name | id | name 
----+------+----+------
  1 | a    | 91 | a
  1 | a    | 92 | b
  1 | a    | 93 | c
  2 | b    | 91 | a
  2 | b    | 92 | b
  2 | b    | 93 | c
  3 | c    | 91 | a
  3 | c    | 92 | b
  3 | c    | 93 | c
(9 rows)

SELECT *
  FROM vw_test3, test2;
NOTICE:  AUDIT: SESSION,20,1,READ,SELECT,VIEW,public.vw_test3,"SELECT *
  FROM vw_test3, test2",<none>,9
NOTICE:  AUDIT: SESSION,20,1,READ,SELECT,TABLE,public.test2,"SELECT *
  FROM vw_test3, test2",<none>,9
NOTICE:  AUDIT: SESSION,20,1,READ,SELECT,TABLE,public.test3,"SELECT *
  FROM vw_test3, test2",<none>,9
 id | name | id | name 
----+------+----+------
  1 | a    | 91 | a
  1 | a    | 92 | b
  1 | a    | 93 | c
  2 | b    | 91 | a
  2 | b    | 92 | b
  2 | b    | 93 | c
  3 | c    | 91 | a
  3 | c    | 92 | b
  3 | c    | 93 | c
(9 rows)

SELECT *
  FROM test2
 ORDER BY ID;
NOTICE:  AUDIT: SESSION,21,1,READ,SELECT,TABLE,public.test2,"SELECT *
  FROM test2
 ORDER BY ID",<none>,3
 id | name 
----+------
 91 | a
 92 | b
 93 | c
(3 rows)

UPDATE test2
   SET name = 'd';
NOTICE:  AUDIT: SESSION,22,1,WRITE,UPDATE,TABLE,public.test2,"UPDATE test2
   SET name = 'd'",<none>,3
UPDATE test3
   SET name = 'd'
 WHERE id > 0;
NOTICE:  AUDIT: SESSION,23,1,WRITE,UPDATE,TABLE,public.test3,"UPDATE test3
   SET name = 'd'
 WHERE id > 0",<none>,3
SELECT 1
  FROM
(
	SELECT relname
	  FROM pg_class
	  LIMIT 3
) SUBQUERY;
NOTICE:  AUDIT: SESSION,24,1,READ,SELECT,TABLE,pg_catalog.pg_class,"SELECT 1
  FROM
(
	SELECT relname
	  FROM pg_class
	  LIMIT 3
) SUBQUERY",<none>,3
 ?column? 
----------
        1
        1
        1
(3 rows)

WITH CTE AS
(
	SELECT id
	  FROM test2
)
INSERT INTO test3
SELECT id
  FROM cte;
NOTICE:  AUDIT: SESSION,25,1,WRITE,INSERT,TABLE,public.test3,"WITH CTE AS
(
	SELECT id
	  FROM test2
)
INSERT INTO test3
SELECT id
  FROM cte",<none>,3
NOTICE:  AUDIT: SESSION,25,1,READ,SELECT,TABLE,public.test2,"WITH CTE AS
(
	SELECT id
	  FROM test2
)
INSERT INTO test3
SELECT id
  FROM cte",<none>,3
WITH CTE AS
(
	INSERT INTO test3 VALUES (1)
				   RETURNING id
)
INSERT INTO test2
SELECT id
  FROM cte;
NOTICE:  AUDIT: SESSION,26,1,WRITE,UPDATE,TABLE,public.test2,"UPDATE test2
	   SET id = id + 90
	 WHERE id = new.id",",,,,,,,,,,,,,1",1
NOTICE:  AUDIT: SESSION,26,2,WRITE,INSERT,TABLE,public.test2,"WITH CTE AS
(
	INSERT INTO test3 VALUES (1)
				   RETURNING id
)
INSERT INTO test2
SELECT id
  FROM cte",<none>,1
NOTICE:  AUDIT: SESSION,26,2,WRITE,INSERT,TABLE,public.test3,"WITH CTE AS
(
	INSERT INTO test3 VALUES (1)
				   RETURNING id
)
INSERT INTO test2
SELECT id
  FROM cte",<none>,1
DO $$ BEGIN PERFORM test2_change(91); END $$;
NOTICE:  AUDIT: SESSION,27,1,FUNCTION,DO,,,DO $$ BEGIN PERFORM test2_change(91); END $$,<none>,0
NOTICE:  AUDIT: SESSION,27,2,FUNCTION,EXECUTE,FUNCTION,public.test2_change,SELECT test2_change(91),<none>,0
NOTICE:  AUDIT: SESSION,27,3,WRITE,UPDATE,TABLE,public.test2,"UPDATE test2
	   SET id = id + 1
	 WHERE id = change_id","91,",2
NOTICE:  AUDIT: SESSION,27,4,READ,SELECT,,,SELECT test2_change(91),<none>,1
WITH CTE AS
(
	UPDATE test2
	   SET id = 45
	 WHERE id = 92
	RETURNING id
)
INSERT INTO test3
SELECT id
  FROM cte;
NOTICE:  AUDIT: SESSION,28,1,WRITE,INSERT,TABLE,public.test3,"WITH CTE AS
(
	UPDATE test2
	   SET id = 45
	 WHERE id = 92
	RETURNING id
)
INSERT INTO test3
SELECT id
  FROM cte",<none>,3
NOTICE:  AUDIT: SESSION,28,1,WRITE,UPDATE,TABLE,public.test2,"WITH CTE AS
(
	UPDATE test2
	   SET id = 45
	 WHERE id = 92
	RETURNING id
)
INSERT INTO test3
SELECT id
  FROM cte",<none>,3
WITH CTE AS
(
	INSERT INTO test2 VALUES (37)
				   RETURNING id
)
UPDATE test3
   SET id = cte.id
  FROM cte
 WHERE test3.id <> cte.id;
NOTICE:  AUDIT: SESSION,29,1,WRITE,UPDATE,TABLE,public.test2,"UPDATE test2
	   SET id = id + 90
	 WHERE id = new.id",",,,,,,,,,,,,,37",1
NOTICE:  AUDIT: SESSION,29,2,WRITE,UPDATE,TABLE,public.test3,"WITH CTE AS
(
	INSERT INTO test2 VALUES (37)
				   RETURNING id
)
UPDATE test3
   SET id = cte.id
  FROM cte
 WHERE test3.id <> cte.id",<none>,10
NOTICE:  AUDIT: SESSION,29,2,WRITE,INSERT,TABLE,public.test2,"WITH CTE AS
(
	INSERT INTO test2 VALUES (37)
				   RETURNING id
)
UPDATE test3
   SET id = cte.id
  FROM cte
 WHERE test3.id <> cte.id",<none>,10
DELETE
  FROM test2;
NOTICE:  AUDIT: SESSION,30,1,WRITE,DELETE,TABLE,public.test2,"DELETE
  FROM test2",<none>,5
DELETE
  FROM test3
 WHERE id > 0;
NOTICE:  AUDIT: SESSION,31,1,WRITE,DELETE,TABLE,public.test3,"DELETE
  FROM test3
 WHERE id > 0",<none>,10
--
-- Drop test tables
DROP TABLE test2;
NOTICE:  AUDIT: SESSION,32,1,DDL,DROP TABLE,,,DROP TABLE test2,<none>,0
DROP VIEW vw_test3;
NOTICE:  AUDIT: SESSION,33,1,DDL,DROP VIEW,,,DROP VIEW vw_test3,<none>,0
DROP TABLE test3;
NOTICE:  AUDIT: SESSION,34,1,DDL,DROP TABLE,,,DROP TABLE test3,<none>,0
DROP FUNCTION test2_insert();
NOTICE:  AUDIT: SESSION,35,1,DDL,DROP FUNCTION,,,DROP FUNCTION test2_insert(),<none>,0
DROP FUNCTION test2_change(int);
NOTICE:  AUDIT: SESSION,36,1,DDL,DROP FUNCTION,,,DROP FUNCTION test2_change(int),<none>,0
--
-- Only object logging will be done
SET pgaudit.log = 'none';
SET pgaudit.role = 'regress_auditor';
--
-- Select is session logged
SELECT *
  FROM account;
NOTICE:  AUDIT: OBJECT,37,1,READ,SELECT,TABLE,public.account,"SELECT *
  FROM account",<none>,1
 id |     name      | password | description 
----+---------------+----------+-------------
  1 | regress_user1 | HASH2    | yada, yada
(1 row)

--
-- Insert is not logged
INSERT INTO account (id, name, password, description)
			 VALUES (1, 'regress_user2', 'HASH3', 'blah, blah2');
INSERT INTO account (id, name, password, description)
			 VALUES (1, 'user3', 'HASH4', 'blah, blah3');
--
-- Not object logged
SELECT id,
	   name
  FROM account;
 id |     name      
----+---------------
  1 | regress_user1
  1 | regress_user2
  1 | user3
(3 rows)

--
-- Object logged because of:
-- select (password) on account
SELECT password
  FROM account;
NOTICE:  AUDIT: OBJECT,38,1,READ,SELECT,TABLE,public.account,"SELECT password
  FROM account",<none>,3
 password 
----------
 HASH2
 HASH3
 HASH4
(3 rows)

--
-- Not object logged
UPDATE account
   SET description = 'yada, yada1';
--
-- Object logged because of:
-- update (password) on account
UPDATE account
   SET password = 'HASH4';
NOTICE:  AUDIT: OBJECT,39,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account
   SET password = 'HASH4'",<none>,3
--
-- Session relation logging will be done
SET pgaudit.log_relation = on;
SET pgaudit.log = 'read, write';
--
-- Object logged because of:
-- select (password) on account
-- select on account_role_map
-- Session logged on all tables because log = read and log_relation = on
SELECT account.password,
	   account_role_map.role_id
  FROM account
	   INNER JOIN account_role_map
			on account.id = account_role_map.account_id;
NOTICE:  AUDIT: OBJECT,40,1,READ,SELECT,TABLE,public.account,"SELECT account.password,
	   account_role_map.role_id
  FROM account
	   INNER JOIN account_role_map
			on account.id = account_role_map.account_id",<none>,0
NOTICE:  AUDIT: SESSION,40,1,READ,SELECT,TABLE,public.account,"SELECT account.password,
	   account_role_map.role_id
  FROM account
	   INNER JOIN account_role_map
			on account.id = account_role_map.account_id",<none>,0
NOTICE:  AUDIT: OBJECT,40,1,READ,SELECT,TABLE,public.account_role_map,"SELECT account.password,
	   account_role_map.role_id
  FROM account
	   INNER JOIN account_role_map
			on account.id = account_role_map.account_id",<none>,0
NOTICE:  AUDIT: SESSION,40,1,READ,SELECT,TABLE,public.account_role_map,"SELECT account.password,
	   account_role_map.role_id
  FROM account
	   INNER JOIN account_role_map
			on account.id = account_role_map.account_id",<none>,0
 password | role_id 
----------+---------
(0 rows)

--
-- Object logged because of:
-- select (password) on account
-- Session logged on all tables because log = read and log_relation = on
SELECT password
  FROM account;
NOTICE:  AUDIT: OBJECT,41,1,READ,SELECT,TABLE,public.account,"SELECT password
  FROM account",<none>,3
NOTICE:  AUDIT: SESSION,41,1,READ,SELECT,TABLE,public.account,"SELECT password
  FROM account",<none>,3
 password 
----------
 HASH4
 HASH4
 HASH4
(3 rows)

--
-- Not object logged
-- Session logged on all tables because log = read and log_relation = on
UPDATE account
   SET description = 'yada, yada2';
NOTICE:  AUDIT: SESSION,42,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account
   SET description = 'yada, yada2'",<none>,3
--
-- Object logged because of:
-- select (password) on account (in the where clause)
-- Session logged on all tables because log = read and log_relation = on
UPDATE account
   SET description = 'yada, yada3'
 where password = 'HASH4';
NOTICE:  AUDIT: OBJECT,43,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account
   SET description = 'yada, yada3'
 where password = 'HASH4'",<none>,3
NOTICE:  AUDIT: SESSION,43,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account
   SET description = 'yada, yada3'
 where password = 'HASH4'",<none>,3
--
-- Object logged because of:
-- update (password) on account
-- Session logged on all tables because log = read and log_relation = on
UPDATE account
   SET password = 'HASH4';
NOTICE:  AUDIT: OBJECT,44,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account
   SET password = 'HASH4'",<none>,3
NOTICE:  AUDIT: SESSION,44,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account
   SET password = 'HASH4'",<none>,3
--
-- Exhaustive tests
SET pgaudit.log = 'all';
NOTICE:  AUDIT: SESSION,45,1,MISC,SET,,,SET pgaudit.log = 'all',<none>,0
SET pgaudit.log_client = on;
NOTICE:  AUDIT: SESSION,46,1,MISC,SET,,,SET pgaudit.log_client = on,<none>,0
SET pgaudit.log_relation = on;
NOTICE:  AUDIT: SESSION,47,1,MISC,SET,,,SET pgaudit.log_relation = on,<none>,0
SET pgaudit.log_parameter = on;
NOTICE:  AUDIT: SESSION,48,1,MISC,SET,,,SET pgaudit.log_parameter = on,<none>,0
SET pgaudit.log_rows = on;
NOTICE:  AUDIT: SESSION,49,1,MISC,SET,,,SET pgaudit.log_rows = on,<none>,0
--
-- Simple DO block
DO $$
BEGIN
	raise notice 'test';
END $$;
NOTICE:  AUDIT: SESSION,50,1,FUNCTION,DO,,,"DO $$
BEGIN
	raise notice 'test';
END $$",<none>,0
NOTICE:  test
--
-- Copy account to stdout
COPY account TO stdout;
1	regress_user1	HASH4	yada, yada3
1	regress_user2	HASH4	yada, yada3
1	user3	HASH4	yada, yada3
NOTICE:  AUDIT: SESSION,51,1,READ,COPY,,,COPY account TO stdout,<none>,0
--
-- Drop a table from a query
DROP TABLE test.account_copy;
NOTICE:  AUDIT: SESSION,52,1,DDL,DROP TABLE,,,DROP TABLE test.account_copy,<none>,0
--
-- Create a table from a query
CREATE TABLE test.account_copy AS
SELECT *
  FROM account;
NOTICE:  AUDIT: OBJECT,53,1,READ,SELECT,TABLE,public.account,"CREATE TABLE test.account_copy AS
SELECT *
  FROM account",<none>,3
NOTICE:  AUDIT: SESSION,53,1,READ,SELECT,TABLE,public.account,"CREATE TABLE test.account_copy AS
SELECT *
  FROM account",<none>,3
NOTICE:  AUDIT: SESSION,53,2,DDL,CREATE TABLE AS,,,"CREATE TABLE test.account_copy AS
SELECT *
  FROM account",<none>,0
--
-- Copy from stdin to account copy
COPY test.account_copy from stdin;
NOTICE:  AUDIT: SESSION,54,1,WRITE,COPY,,,COPY test.account_copy from stdin,<none>,0
--
-- Test prepared statement
PREPARE pgclassstmt1 (oid) AS
SELECT 1
  FROM account
 WHERE id = $1;
NOTICE:  AUDIT: SESSION,55,1,READ,PREPARE,,,"PREPARE pgclassstmt1 (oid) AS
SELECT 1
  FROM account
 WHERE id = $1",<none>,0
PREPARE pgclassstmt2 (oid) AS
SELECT 2
  FROM account
 WHERE id = $1;
NOTICE:  AUDIT: SESSION,56,1,READ,PREPARE,,,"PREPARE pgclassstmt2 (oid) AS
SELECT 2
  FROM account
 WHERE id = $1",<none>,0
EXECUTE pgclassstmt2 (1);
NOTICE:  AUDIT: SESSION,57,1,READ,SELECT,TABLE,public.account,"PREPARE pgclassstmt2 (oid) AS
SELECT 2
  FROM account
 WHERE id = $1",1,3
NOTICE:  AUDIT: SESSION,57,2,MISC,EXECUTE,,,EXECUTE pgclassstmt2 (1),<none>,0
 ?column? 
----------
        2
        2
        2
(3 rows)

EXECUTE pgclassstmt1 (1);
NOTICE:  AUDIT: SESSION,58,1,READ,SELECT,TABLE,public.account,"PREPARE pgclassstmt1 (oid) AS
SELECT 1
  FROM account
 WHERE id = $1",1,3
NOTICE:  AUDIT: SESSION,58,2,MISC,EXECUTE,,,EXECUTE pgclassstmt1 (1),<none>,0
 ?column? 
----------
        1
        1
        1
(3 rows)

DEALLOCATE pgclassstmt2;
NOTICE:  AUDIT: SESSION,59,1,MISC,DEALLOCATE,,,DEALLOCATE pgclassstmt2,<none>,0
DEALLOCATE pgclassstmt1;
NOTICE:  AUDIT: SESSION,60,1,MISC,DEALLOCATE,,,DEALLOCATE pgclassstmt1,<none>,0
--
-- Test cursor
BEGIN;
NOTICE:  AUDIT: SESSION,61,1,MISC,BEGIN,,,BEGIN,<none>,0
DECLARE ctest1 SCROLL CURSOR FOR
SELECT 1
  FROM
(
	SELECT relname
	  FROM pg_class
	 LIMIT 3
 ) subquery;
NOTICE:  AUDIT: SESSION,62,1,READ,DECLARE CURSOR,,,"DECLARE ctest1 SCROLL CURSOR FOR
SELECT 1
  FROM
(
	SELECT relname
	  FROM pg_class
	 LIMIT 3
 ) subquery",<none>,0
DECLARE ctest2 SCROLL CURSOR FOR
SELECT 2
  FROM
(
	SELECT relname
	  FROM pg_class
	 LIMIT 3
 ) subquery;
NOTICE:  AUDIT: SESSION,63,1,READ,DECLARE CURSOR,,,"DECLARE ctest2 SCROLL CURSOR FOR
SELECT 2
  FROM
(
	SELECT relname
	  FROM pg_class
	 LIMIT 3
 ) subquery",<none>,0
FETCH NEXT FROM ctest1;
NOTICE:  AUDIT: SESSION,64,1,MISC,FETCH,,,FETCH NEXT FROM ctest1,<none>,0
 ?column? 
----------
        1
(1 row)

FETCH NEXT FROM ctest2;
NOTICE:  AUDIT: SESSION,65,1,MISC,FETCH,,,FETCH NEXT FROM ctest2,<none>,0
 ?column? 
----------
        2
(1 row)

FETCH NEXT FROM ctest1;
NOTICE:  AUDIT: SESSION,66,1,MISC,FETCH,,,FETCH NEXT FROM ctest1,<none>,0
 ?column? 
----------
        1
(1 row)

FETCH NEXT FROM ctest2;
NOTICE:  AUDIT: SESSION,67,1,MISC,FETCH,,,FETCH NEXT FROM ctest2,<none>,0
 ?column? 
----------
        2
(1 row)

FETCH NEXT FROM ctest1;
NOTICE:  AUDIT: SESSION,68,1,MISC,FETCH,,,FETCH NEXT FROM ctest1,<none>,0
 ?column? 
----------
        1
(1 row)

FETCH NEXT FROM ctest2;
NOTICE:  AUDIT: SESSION,69,1,MISC,FETCH,,,FETCH NEXT FROM ctest2,<none>,0
 ?column? 
----------
        2
(1 row)

CLOSE ctest2;
NOTICE:  AUDIT: SESSION,70,1,MISC,CLOSE CURSOR,,,CLOSE ctest2,<none>,0
CLOSE ctest1;
NOTICE:  AUDIT: SESSION,71,1,MISC,CLOSE CURSOR,,,CLOSE ctest1,<none>,0
COMMIT;
NOTICE:  AUDIT: SESSION,72,1,MISC,COMMIT,,,COMMIT,<none>,0
--
-- Turn off log_catalog and pg_class will not be logged
SET pgaudit.log_catalog = off;
NOTICE:  AUDIT: SESSION,73,1,MISC,SET,,,SET pgaudit.log_catalog = off,<none>,0
SELECT count(*)
  FROM
(
	SELECT relname
	  FROM pg_class
	 LIMIT 1
 ) subquery;
 count 
-------
     1
(1 row)

--
-- Test prepared insert
DROP TABLE test.test_insert;
NOTICE:  AUDIT: SESSION,74,1,DDL,DROP TABLE,,,DROP TABLE test.test_insert,<none>,0
--
-- Test prepared insert
CREATE TABLE test.test_insert
(
	id INT
);
NOTICE:  AUDIT: SESSION,75,1,DDL,CREATE TABLE,,,"CREATE TABLE test.test_insert
(
	id INT
)",<none>,0
PREPARE pgclassstmt (oid) AS
INSERT INTO test.test_insert (id)
					  VALUES ($1);
NOTICE:  AUDIT: SESSION,76,1,WRITE,PREPARE,,,"PREPARE pgclassstmt (oid) AS
INSERT INTO test.test_insert (id)
					  VALUES ($1)",<none>,0
EXECUTE pgclassstmt (1);
NOTICE:  AUDIT: SESSION,77,1,WRITE,INSERT,TABLE,test.test_insert,"PREPARE pgclassstmt (oid) AS
INSERT INTO test.test_insert (id)
					  VALUES ($1)",1,1
NOTICE:  AUDIT: SESSION,77,2,MISC,EXECUTE,,,EXECUTE pgclassstmt (1),<none>,0
--
-- 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,78,1,DDL,CREATE TABLE,,,"CREATE TABLE public.test
(
	id INT,
	name TEXT,
	description TEXT,
	CONSTRAINT test_pkey PRIMARY KEY (id)
)",<none>,0
--
-- Check that analyze is logged
ANALYZE test;
NOTICE:  AUDIT: SESSION,79,1,MISC,ANALYZE,,,ANALYZE test,<none>,0
--
-- Grants to public should not cause object logging (session logging will
-- still happen)
GRANT SELECT
  ON TABLE public.test
  TO PUBLIC;
NOTICE:  AUDIT: SESSION,80,1,ROLE,GRANT,,,"GRANT SELECT
  ON TABLE public.test
  TO PUBLIC",<none>,0
SELECT *
  FROM test;
NOTICE:  AUDIT: SESSION,81,1,READ,SELECT,TABLE,public.test,"SELECT *
  FROM test",<none>,0
 id | name | description 
----+------+-------------
(0 rows)

-- Check that statements without columns log
SELECT
  FROM test;
NOTICE:  AUDIT: SESSION,82,1,READ,SELECT,TABLE,public.test,"SELECT
  FROM test",<none>,0
--
(0 rows)

SELECT 1,
	   substring('Thomas' from 2 for 3);
NOTICE:  AUDIT: SESSION,83,1,READ,SELECT,,,"SELECT 1,
	   substring('Thomas' from 2 for 3)",<none>,1
 ?column? | substring 
----------+-----------
        1 | hom
(1 row)

DO $$
DECLARE
	test INT;
BEGIN
	SELECT 1
	  INTO test;
END $$;
NOTICE:  AUDIT: SESSION,84,1,FUNCTION,DO,,,"DO $$
DECLARE
	test INT;
BEGIN
	SELECT 1
	  INTO test;
END $$",<none>,0
NOTICE:  AUDIT: SESSION,84,2,READ,SELECT,,,SELECT 1,<none>,1
explain select 1;
NOTICE:  AUDIT: SESSION,85,1,READ,SELECT,,,explain select 1,<none>,0
NOTICE:  AUDIT: SESSION,85,2,MISC,EXPLAIN,,,explain select 1,<none>,0
                QUERY PLAN                
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=4)
(1 row)

--
-- Test that looks inside of do blocks log
INSERT INTO TEST (id)
		  VALUES (1);
NOTICE:  AUDIT: SESSION,86,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)
		  VALUES (1)",<none>,1
INSERT INTO TEST (id)
		  VALUES (2);
NOTICE:  AUDIT: SESSION,87,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)
		  VALUES (2)",<none>,1
INSERT INTO TEST (id)
		  VALUES (3);
NOTICE:  AUDIT: SESSION,88,1,WRITE,INSERT,TABLE,public.test,"INSERT INTO TEST (id)
		  VALUES (3)",<none>,1
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,89,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 $$",<none>,0
NOTICE:  AUDIT: SESSION,89,2,WRITE,INSERT,TABLE,public.test,"INSERT INTO test (id)
			 VALUES (result.id + 100)",",,1",1
NOTICE:  AUDIT: SESSION,89,3,WRITE,INSERT,TABLE,public.test,"INSERT INTO test (id)
			 VALUES (result.id + 100)",",,2",1
NOTICE:  AUDIT: SESSION,89,4,WRITE,INSERT,TABLE,public.test,"INSERT INTO test (id)
			 VALUES (result.id + 100)",",,3",1
NOTICE:  AUDIT: SESSION,89,5,READ,SELECT,TABLE,public.test,"SELECT id
		  FROM test",<none>,3
--
-- Test obfuscated dynamic sql for clean logging
DO $$
DECLARE
	table_name TEXT = 'do_table';
BEGIN
	EXECUTE 'CREATE TABLE ' || table_name || ' ("weird name" INT)';
	EXECUTE 'DROP table ' || table_name;
END $$;
NOTICE:  AUDIT: SESSION,90,1,FUNCTION,DO,,,"DO $$
DECLARE
	table_name TEXT = 'do_table';
BEGIN
	EXECUTE 'CREATE TABLE ' || table_name || ' (""weird name"" INT)';
	EXECUTE 'DROP table ' || table_name;
END $$",<none>,0
NOTICE:  AUDIT: SESSION,90,2,DDL,CREATE TABLE,,,"CREATE TABLE do_table (""weird name"" INT)",<none>,0
NOTICE:  AUDIT: SESSION,90,3,DDL,DROP TABLE,,,DROP table do_table,<none>,0
--
-- Generate an error and make sure the stack gets cleared
DO $$
BEGIN
	CREATE TABLE bogus.test_block
	(
		id INT
	);
END $$;
NOTICE:  AUDIT: SESSION,91,1,FUNCTION,DO,,,"DO $$
BEGIN
	CREATE TABLE bogus.test_block
	(
		id INT
	);
END $$",<none>,0
ERROR:  schema "bogus" does not exist at character 14
--
-- Test alter table statements
ALTER TABLE public.test
	DROP COLUMN description ;
NOTICE:  AUDIT: SESSION,92,1,DDL,ALTER TABLE,,,"ALTER TABLE public.test
	DROP COLUMN description",<none>,0
ALTER TABLE public.test
	RENAME TO test2;
NOTICE:  AUDIT: SESSION,93,1,DDL,ALTER TABLE,,,"ALTER TABLE public.test
	RENAME TO test2",<none>,0
ALTER TABLE public.test2
	SET SCHEMA test;
NOTICE:  AUDIT: SESSION,94,1,DDL,ALTER TABLE,,,"ALTER TABLE public.test2
	SET SCHEMA test",<none>,0
ALTER TABLE test.test2
	ADD COLUMN description TEXT;
NOTICE:  AUDIT: SESSION,95,1,DDL,ALTER TABLE,,,"ALTER TABLE test.test2
	ADD COLUMN description TEXT",<none>,0
ALTER TABLE test.test2
	DROP COLUMN description;
NOTICE:  AUDIT: SESSION,96,1,DDL,ALTER TABLE,,,"ALTER TABLE test.test2
	DROP COLUMN description",<none>,0
DROP TABLE test.test2;
NOTICE:  AUDIT: SESSION,97,1,DDL,DROP TABLE,,,DROP TABLE test.test2,<none>,0
--
-- Test multiple statements with one semi-colon
CREATE SCHEMA foo1
	CREATE TABLE foo1.bar1 (id int)
	CREATE TABLE foo1.baz1 (id int);
NOTICE:  AUDIT: SESSION,98,1,DDL,CREATE SCHEMA,,,"CREATE SCHEMA foo1
	CREATE TABLE foo1.bar1 (id int)
	CREATE TABLE foo1.baz1 (id int)",<none>,0
DROP TABLE foo1.bar1;
NOTICE:  AUDIT: SESSION,99,1,DDL,DROP TABLE,,,DROP TABLE foo1.bar1,<none>,0
DROP TABLE foo1.baz1;
NOTICE:  AUDIT: SESSION,100,1,DDL,DROP TABLE,,,DROP TABLE foo1.baz1,<none>,0
DROP SCHEMA foo1;
NOTICE:  AUDIT: SESSION,101,1,DDL,DROP SCHEMA,,,DROP SCHEMA foo1,<none>,0
--
-- Test aggregate
CREATE FUNCTION public.int_add1
(
	a INT,
	b INT
)
	RETURNS INT LANGUAGE plpgsql AS $$
BEGIN
	return a + b;
END $$;
NOTICE:  AUDIT: SESSION,102,1,DDL,CREATE FUNCTION,,,"CREATE FUNCTION public.int_add1
(
	a INT,
	b INT
)
	RETURNS INT LANGUAGE plpgsql AS $$
BEGIN
	return a + b;
END $$",<none>,0
SELECT int_add1(1, 1);
NOTICE:  AUDIT: SESSION,103,1,FUNCTION,EXECUTE,FUNCTION,public.int_add1,"SELECT int_add1(1, 1)",<none>,0
NOTICE:  AUDIT: SESSION,103,2,READ,SELECT,,,"SELECT int_add1(1, 1)",<none>,1
 int_add1 
----------
        2
(1 row)

CREATE AGGREGATE public.sum_test(INT) (SFUNC=public.int_add1, STYPE=INT, INITCOND='0');
NOTICE:  AUDIT: SESSION,104,1,DDL,CREATE AGGREGATE,,,"CREATE AGGREGATE public.sum_test(INT) (SFUNC=public.int_add1, STYPE=INT, INITCOND='0')",<none>,0
ALTER AGGREGATE public.sum_test(integer) RENAME TO sum_test3;
NOTICE:  AUDIT: SESSION,105,1,DDL,ALTER AGGREGATE,,,ALTER AGGREGATE public.sum_test(integer) RENAME TO sum_test3,<none>,0
--
-- Test conversion
CREATE CONVERSION public.conversion_test FOR 'latin1' TO 'utf8' FROM pg_catalog.iso8859_1_to_utf8;
NOTICE:  AUDIT: SESSION,106,1,DDL,CREATE CONVERSION,,,CREATE CONVERSION public.conversion_test FOR 'latin1' TO 'utf8' FROM pg_catalog.iso8859_1_to_utf8,<none>,0
ALTER CONVERSION public.conversion_test RENAME TO conversion_test3;
NOTICE:  AUDIT: SESSION,107,1,DDL,ALTER CONVERSION,,,ALTER CONVERSION public.conversion_test RENAME TO conversion_test3,<none>,0
--
-- Test create/alter/drop database
CREATE DATABASE contrib_regression_pgaudit;
NOTICE:  AUDIT: SESSION,108,1,DDL,CREATE DATABASE,,,CREATE DATABASE contrib_regression_pgaudit,<none>,0
ALTER DATABASE contrib_regression_pgaudit RENAME TO contrib_regression_pgaudit2;
NOTICE:  AUDIT: SESSION,109,1,DDL,ALTER DATABASE,,,ALTER DATABASE contrib_regression_pgaudit RENAME TO contrib_regression_pgaudit2,<none>,0
DROP DATABASE contrib_regression_pgaudit2;
NOTICE:  AUDIT: SESSION,110,1,DDL,DROP DATABASE,,,DROP DATABASE contrib_regression_pgaudit2,<none>,0
-- Test role as a substmt
SET pgaudit.log = 'role';
CREATE TABLE t ();
CREATE ROLE regress_alice;
NOTICE:  AUDIT: SESSION,111,1,ROLE,CREATE ROLE,,,CREATE ROLE regress_alice,<none>,0
CREATE SCHEMA foo3
	GRANT SELECT
	   ON public.t
	   TO regress_alice;
drop table public.t;
drop role regress_alice;
NOTICE:  AUDIT: SESSION,112,1,ROLE,DROP ROLE,,,drop role regress_alice,<none>,0
--
-- Test for non-empty stack error
CREATE OR REPLACE FUNCTION get_test_id(_ret REFCURSOR) RETURNS REFCURSOR
LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
    OPEN _ret FOR SELECT 200;
    RETURN _ret;
END $$;
BEGIN;
    SELECT get_test_id('_ret');
 get_test_id 
-------------
 _ret
(1 row)

    SELECT get_test_id('_ret2');
 get_test_id 
-------------
 _ret2
(1 row)

    FETCH ALL FROM _ret;
 ?column? 
----------
      200
(1 row)

    FETCH ALL FROM _ret2;
 ?column? 
----------
      200
(1 row)

    CLOSE _ret;
    CLOSE _ret2;
END;
--
-- Test that frees a memory context earlier than expected
SET pgaudit.log = 'all';
NOTICE:  AUDIT: SESSION,113,1,MISC,SET,,,SET pgaudit.log = 'all',<none>,0
CREATE FUNCTION test1()
	RETURNS INT AS $$
DECLARE
	cur1 cursor for select * from hoge;
	tmp int;
BEGIN
	OPEN cur1;
	FETCH cur1 into tmp;
	RETURN tmp;
END $$
LANGUAGE plpgsql ;
NOTICE:  AUDIT: SESSION,114,1,DDL,CREATE FUNCTION,,,"CREATE FUNCTION test1()
	RETURNS INT AS $$
DECLARE
	cur1 cursor for select * from hoge;
	tmp int;
BEGIN
	OPEN cur1;
	FETCH cur1 into tmp;
	RETURN tmp;
END $$
LANGUAGE plpgsql",<none>,0
SELECT test1();
NOTICE:  AUDIT: SESSION,115,1,FUNCTION,EXECUTE,FUNCTION,public.test1,SELECT test1(),<none>,0
NOTICE:  AUDIT: SESSION,115,2,READ,SELECT,,,SELECT test1(),<none>,1
 test1 
-------
      
(1 row)

--
-- Delete all rows then delete 1 row
SET pgaudit.log = 'write';
SET pgaudit.role = 'regress_auditor';
create table bar
(
	col int
);
grant delete
   on bar
   to regress_auditor;
insert into bar (col)
		 values (1);
NOTICE:  AUDIT: SESSION,116,1,WRITE,INSERT,TABLE,public.bar,"insert into bar (col)
		 values (1)",<none>,1
delete from bar;
NOTICE:  AUDIT: OBJECT,117,1,WRITE,DELETE,TABLE,public.bar,delete from bar,<none>,1
NOTICE:  AUDIT: SESSION,117,1,WRITE,DELETE,TABLE,public.bar,delete from bar,<none>,1
insert into bar (col)
		 values (1);
NOTICE:  AUDIT: SESSION,118,1,WRITE,INSERT,TABLE,public.bar,"insert into bar (col)
		 values (1)",<none>,1
delete from bar
 where col = 1;
NOTICE:  AUDIT: OBJECT,119,1,WRITE,DELETE,TABLE,public.bar,"delete from bar
 where col = 1",<none>,1
NOTICE:  AUDIT: SESSION,119,1,WRITE,DELETE,TABLE,public.bar,"delete from bar
 where col = 1",<none>,1
drop table bar;
--
-- Test that FK references do not log but triggers still do
SET pgaudit.log = 'read, write';
SET pgaudit.role TO 'regress_auditor';
CREATE TABLE aaa
(
	ID int primary key
);
CREATE TABLE bbb
(
	id int
		references aaa(id)
);
CREATE FUNCTION bbb_insert1() RETURNS TRIGGER AS $$
BEGIN
	UPDATE bbb set id = new.id + 1;

	RETURN new;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER bbb_insert_trg1
	AFTER INSERT ON bbb
	FOR EACH ROW EXECUTE PROCEDURE bbb_insert1();
GRANT SELECT, UPDATE
   ON aaa
   TO regress_auditor;
GRANT UPDATE
   ON bbb
   TO regress_auditor;
INSERT INTO aaa VALUES (generate_series(1,100));
NOTICE:  AUDIT: SESSION,120,1,WRITE,INSERT,TABLE,public.aaa,"INSERT INTO aaa VALUES (generate_series(1,100))",<none>,100
SET pgaudit.log_parameter TO OFF;
INSERT INTO bbb VALUES (1);
NOTICE:  AUDIT: OBJECT,121,1,READ,SELECT,TABLE,public.aaa,"SELECT 1 FROM ONLY ""public"".""aaa"" x WHERE ""id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",<not logged>,1
NOTICE:  AUDIT: SESSION,121,1,READ,SELECT,TABLE,public.aaa,"SELECT 1 FROM ONLY ""public"".""aaa"" x WHERE ""id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",<not logged>,1
NOTICE:  AUDIT: OBJECT,121,2,READ,SELECT,TABLE,public.aaa,"SELECT 1 FROM ONLY ""public"".""aaa"" x WHERE ""id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",<not logged>,1
NOTICE:  AUDIT: SESSION,121,2,READ,SELECT,TABLE,public.aaa,"SELECT 1 FROM ONLY ""public"".""aaa"" x WHERE ""id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",<not logged>,1
NOTICE:  AUDIT: OBJECT,121,3,WRITE,UPDATE,TABLE,public.bbb,UPDATE bbb set id = new.id + 1,<not logged>,1
NOTICE:  AUDIT: SESSION,121,3,WRITE,UPDATE,TABLE,public.bbb,UPDATE bbb set id = new.id + 1,<not logged>,1
NOTICE:  AUDIT: SESSION,121,4,WRITE,INSERT,TABLE,public.bbb,INSERT INTO bbb VALUES (1),<not logged>,1
SET pgaudit.log_parameter TO ON;
DROP TABLE bbb;
DROP TABLE aaa;
--
-- Test MISC
SET pgaudit.log = 'misc';
NOTICE:  AUDIT: SESSION,122,1,MISC,SET,,,SET pgaudit.log = 'misc',<none>,0
SET pgaudit.log_client = on;
NOTICE:  AUDIT: SESSION,123,1,MISC,SET,,,SET pgaudit.log_client = on,<none>,0
SET pgaudit.log_relation = on;
NOTICE:  AUDIT: SESSION,124,1,MISC,SET,,,SET pgaudit.log_relation = on,<none>,0
SET pgaudit.log_parameter = on;
NOTICE:  AUDIT: SESSION,125,1,MISC,SET,,,SET pgaudit.log_parameter = on,<none>,0
CREATE ROLE regress_alice;
SET ROLE regress_alice;
NOTICE:  AUDIT: SESSION,126,1,MISC,SET,,,SET ROLE regress_alice,<none>,0
CREATE TABLE t (a int, b text);
SET search_path TO test, public;
NOTICE:  AUDIT: SESSION,127,1,MISC,SET,,,"SET search_path TO test, public",<none>,0
INSERT INTO t VALUES (1, 'misc');
VACUUM t;
NOTICE:  AUDIT: SESSION,128,1,MISC,VACUUM,,,VACUUM t,<none>,0
RESET ROLE;
NOTICE:  AUDIT: SESSION,129,1,MISC,RESET,,,RESET ROLE,<none>,0
--
-- Test MISC_SET
SET pgaudit.log = 'MISC_SET';
NOTICE:  AUDIT: SESSION,130,1,MISC,SET,,,SET pgaudit.log = 'MISC_SET',<none>,0
SET ROLE regress_alice;
NOTICE:  AUDIT: SESSION,131,1,MISC,SET,,,SET ROLE regress_alice,<none>,0
SET search_path TO public;
NOTICE:  AUDIT: SESSION,132,1,MISC,SET,,,SET search_path TO public,<none>,0
INSERT INTO t VALUES (2, 'misc_set');
VACUUM t;
RESET ROLE;
NOTICE:  AUDIT: SESSION,133,1,MISC,RESET,,,RESET ROLE,<none>,0
--
-- Test ALL, -MISC, MISC_SET
SET pgaudit.log = 'ALL, -MISC, MISC_SET';
NOTICE:  AUDIT: SESSION,134,1,MISC,SET,,,"SET pgaudit.log = 'ALL, -MISC, MISC_SET'",<none>,0
SET search_path TO public;
NOTICE:  AUDIT: SESSION,135,1,MISC,SET,,,SET search_path TO public,<none>,0
INSERT INTO t VALUES (3, 'all, -misc, misc_set');
NOTICE:  AUDIT: SESSION,136,1,WRITE,INSERT,TABLE,public.t,"INSERT INTO t VALUES (3, 'all, -misc, misc_set')",<none>,1
VACUUM t;
RESET ROLE;
NOTICE:  AUDIT: SESSION,137,1,MISC,RESET,,,RESET ROLE,<none>,0
DROP TABLE public.t;
NOTICE:  AUDIT: SESSION,138,1,DDL,DROP TABLE,,,DROP TABLE public.t,<none>,0
DROP ROLE regress_alice;
NOTICE:  AUDIT: SESSION,139,1,ROLE,DROP ROLE,,,DROP ROLE regress_alice,<none>,0
--
-- Test PARTITIONED table
CREATE TABLE h(x int ,y int) PARTITION BY HASH(x);
NOTICE:  AUDIT: SESSION,140,1,DDL,CREATE TABLE,,,"CREATE TABLE h(x int ,y int) PARTITION BY HASH(x)",<none>,0
CREATE TABLE h_0 partition OF h FOR VALUES WITH ( MODULUS 2, REMAINDER 0);
NOTICE:  AUDIT: SESSION,141,1,DDL,CREATE TABLE,,,"CREATE TABLE h_0 partition OF h FOR VALUES WITH ( MODULUS 2, REMAINDER 0)",<none>,0
CREATE TABLE h_1 partition OF h FOR VALUES WITH ( MODULUS 2, REMAINDER 1);
NOTICE:  AUDIT: SESSION,142,1,DDL,CREATE TABLE,,,"CREATE TABLE h_1 partition OF h FOR VALUES WITH ( MODULUS 2, REMAINDER 1)",<none>,0
INSERT INTO h VALUES(1,1);
NOTICE:  AUDIT: SESSION,143,1,WRITE,INSERT,TABLE,public.h,"INSERT INTO h VALUES(1,1)",<none>,1
SELECT * FROM h;
NOTICE:  AUDIT: SESSION,144,1,READ,SELECT,TABLE,public.h,SELECT * FROM h,<none>,1
 x | y 
---+---
 1 | 1
(1 row)

SELECT * FROM h_0;
NOTICE:  AUDIT: SESSION,145,1,READ,SELECT,TABLE,public.h_0,SELECT * FROM h_0,<none>,1
 x | y 
---+---
 1 | 1
(1 row)

CREATE INDEX h_idx ON h (x);
NOTICE:  AUDIT: SESSION,146,1,DDL,CREATE INDEX,,,CREATE INDEX h_idx ON h (x),<none>,0
DROP INDEX h_idx;
NOTICE:  AUDIT: SESSION,147,1,DDL,DROP INDEX,,,DROP INDEX h_idx,<none>,0
DROP TABLE h;
NOTICE:  AUDIT: SESSION,148,1,DDL,DROP TABLE,,,DROP TABLE h,<none>,0
--
-- Change configuration of user 1 so that full statements are not logged
\connect - :current_user
ALTER ROLE regress_user1 RESET pgaudit.log_relation;
NOTICE:  AUDIT: SESSION,1,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user1 RESET pgaudit.log_relation,<not logged>
ALTER ROLE regress_user1 RESET pgaudit.log;
NOTICE:  AUDIT: SESSION,2,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user1 RESET pgaudit.log,<not logged>
ALTER ROLE regress_user1 SET pgaudit.log_statement = OFF;
NOTICE:  AUDIT: SESSION,3,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user1 SET pgaudit.log_statement = OFF,<not logged>
ALTER ROLE regress_user1 SET pgaudit.log_rows = on;
NOTICE:  AUDIT: SESSION,4,1,ROLE,ALTER ROLE,,,ALTER ROLE regress_user1 SET pgaudit.log_rows = on,<not logged>
\connect - regress_user1
--
-- Logged but without full statement
SELECT * FROM account;
NOTICE:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,<not logged>,<not logged>,3
 id |     name      | password | description 
----+---------------+----------+-------------
  1 | regress_user1 | HASH4    | yada, yada3
  1 | regress_user2 | HASH4    | yada, yada3
  1 | user3         | HASH4    | yada, yada3
(3 rows)

--
-- Change back to superuser to do exhaustive tests
\connect - :current_user
--
-- Test that pgaudit event triggers are immune to search-path-based attacks
-- Attempt to capture unqualified references to standard functions
CREATE FUNCTION upper(text) RETURNS text
LANGUAGE SQL AS 'SELECT (1/0)::text';
CREATE FUNCTION lower(text) RETURNS text
LANGUAGE SQL AS 'SELECT (1/0)::text';
CREATE FUNCTION my_ne(text, text) RETURNS bool
LANGUAGE SQL AS 'SELECT (1/0)::bool';
CREATE OPERATOR <> (FUNCTION = my_ne, LEFTARG = text, RIGHTARG = text);
CREATE EXTENSION IF NOT EXISTS pgaudit;
SET pgaudit.log = 'DDL';
-- Put public schema before pg_catalog to capture unqualified references
SET search_path = public, pg_catalog;
-- If there was a vulnerability, these would fail with division by zero error
CREATE TABLE wombat ();
NOTICE:  AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.wombat,CREATE TABLE wombat (),<not logged>
DROP TABLE wombat;
NOTICE:  AUDIT: SESSION,2,1,DDL,DROP TABLE,TABLE,public.wombat,DROP TABLE wombat,<not logged>
SET pgaudit.log = 'NONE';
DROP EXTENSION pgaudit;
DROP OPERATOR <> (text, text);
DROP FUNCTION my_ne(text, text);
DROP FUNCTION lower(text);
DROP FUNCTION upper(text);
-- Create/drop extension. Note that the log level here must be warning because the create extension code will reset any higher log
-- levels like notice
SET pgaudit.log = 'all,-misc_set';
SET pgaudit.log_level = 'warning';
CREATE EXTENSION pg_stat_statements;
WARNING:  AUDIT: SESSION,3,1,DDL,CREATE EXTENSION,,,CREATE EXTENSION pg_stat_statements,<not logged>
ALTER EXTENSION pg_stat_statements UPDATE TO '1.11';
WARNING:  AUDIT: SESSION,4,1,DDL,ALTER EXTENSION,,,ALTER EXTENSION pg_stat_statements UPDATE TO '1.11',<not logged>
NOTICE:  version "1.11" of extension "pg_stat_statements" is already installed
DROP EXTENSION pg_stat_statements;
WARNING:  AUDIT: SESSION,5,1,DDL,DROP EXTENSION,,,DROP EXTENSION pg_stat_statements,<not logged>
SET pgaudit.log_level = 'notice';
-- Check that password redaction works with CREATE/ALTER USER MAPPING
CREATE EXTENSION postgres_fdw;
NOTICE:  AUDIT: SESSION,6,1,DDL,CREATE EXTENSION,,,CREATE EXTENSION postgres_fdw,<not logged>
CREATE SERVER fdw_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'foo', dbname 'foodb', port '5432');
NOTICE:  AUDIT: SESSION,7,1,DDL,CREATE SERVER,,,"CREATE SERVER fdw_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'foo', dbname 'foodb', port '5432')",<not logged>
CREATE USER MAPPING FOR regress_user1 SERVER fdw_server OPTIONS (user 'regress_user1', password 'secret');
NOTICE:  AUDIT: SESSION,8,1,ROLE,CREATE USER MAPPING,,,"CREATE USER MAPPING FOR regress_user1 SERVER fdw_server OPTIONS (user 'regress_user1', password <REDACTED>",<not logged>
ALTER USER MAPPING FOR regress_user1 SERVER fdw_server OPTIONS (SET /* comment */ password 'secret2');
NOTICE:  AUDIT: SESSION,9,1,ROLE,ALTER USER MAPPING,,,ALTER USER MAPPING FOR regress_user1 SERVER fdw_server OPTIONS (SET /* comment */ password <REDACTED>,<not logged>
DROP USER MAPPING FOR regress_user1 SERVER fdw_server;
NOTICE:  AUDIT: SESSION,10,1,DDL,DROP USER MAPPING,,,DROP USER MAPPING FOR regress_user1 SERVER fdw_server,<not logged>
DROP SERVER fdw_server;
NOTICE:  AUDIT: SESSION,11,1,DDL,DROP SERVER,,,DROP SERVER fdw_server,<not logged>
DROP EXTENSION postgres_fdw;
NOTICE:  AUDIT: SESSION,12,1,DDL,DROP EXTENSION,,,DROP EXTENSION postgres_fdw,<not logged>
-- Cleanup
-- Set client_min_messages up to warning to avoid noise
SET client_min_messages = 'warning';
ALTER ROLE :current_user RESET pgaudit.log;
ALTER ROLE :current_user RESET pgaudit.log_catalog;
ALTER ROLE :current_user RESET pgaudit.log_client;
ALTER ROLE :current_user RESET pgaudit.log_level;
ALTER ROLE :current_user RESET pgaudit.log_parameter;
ALTER ROLE :current_user RESET pgaudit.log_parameter_max_size;
ALTER ROLE :current_user RESET pgaudit.log_relation;
ALTER ROLE :current_user RESET pgaudit.log_statement;
ALTER ROLE :current_user RESET pgaudit.log_statement_once;
ALTER ROLE :current_user RESET pgaudit.role;
RESET pgaudit.log;
RESET pgaudit.log_catalog;
RESET pgaudit.log_level;
RESET pgaudit.log_parameter;
RESET pgaudit.log_parameter_max_size;
RESET pgaudit.log_relation;
RESET pgaudit.log_statement;
RESET pgaudit.log_statement_once;
RESET pgaudit.role;
DROP TABLE test.account_copy;
DROP TABLE test.test_insert;
DROP SCHEMA test;
DROP TABLE foo.bar;
DROP TABLE foo.baz;
DROP SCHEMA foo;
DROP TABLE hoge;
DROP TABLE account;
DROP TABLE account_role_map;
DROP USER regress_user2;
DROP USER regress_user1;
DROP ROLE regress_auditor;
RESET client_min_messages;