-- STATEMENT - The statement being logged

-- PARAMETER - If parameter logging is requested, they will follow the

-- statement

select current_user \gset

--

-- Create a superuser role that we know the name of for testing

CREATE USER super SUPERUSER;

ALTER ROLE super SET pg_audit.log = 'Role';

ALTER ROLE super SET pg_audit.log_level = 'notice';

-- Set pg_audit parameters for the current (super)user.

ALTER ROLE :current_user SET pg_audit.log = 'Role';

ALTER ROLE :current_user SET pg_audit.log_level = 'notice';

CREATE FUNCTION load_pg_audit( )

RETURNS VOID

LANGUAGE plpgsql

-- being loaded from shared_preload_libraries. Otherwise, the hooks

-- won't be set up and called correctly, leading to lots of ugly

-- errors.

\connect -super;

\connect -:current_user;

select load_pg_audit();

load_pg_audit

---------------

NOTICE: AUDIT: SESSION,2,1,DDL,DROP TABLE,TABLE,public.test,DROP TABLE test;,<not logged>

--

-- Create second test user

\connect -super

\connect -:current_user

select load_pg_audit();

load_pg_audit

---------------

WARNING: AUDIT: OBJECT,6,1,WRITE,INSERT,TABLE,public.test2,<previously logged>,<previously logged>

--

-- Change permissions of user 2 so that only object logging will be done

\connect -super

\connect -:current_user

select load_pg_audit();

load_pg_audit

---------------

DROP TABLE test4;

--

-- Change permissions of user 1 so that session logging will be done

\connect -super

\connect -:current_user

select load_pg_audit();

load_pg_audit

---------------

VALUES (1, 'user1', 'HASH1', 'blah, blah');

--

-- Change permissions of user 1 so that only object logging will be done

\connect -super

\connect -:current_user

select load_pg_audit();

load_pg_audit

---------------

SET password = 'HASH2';",<not logged>

--

-- Change permissions of user 1 so that session relation logging will be done

\connect -super

\connect -:current_user

select load_pg_audit();

load_pg_audit

---------------

SET password = 'HASH2';",<not logged>

--

-- Change back to superuser to do exhaustive tests

\connect -super

\connect -:current_user

select load_pg_audit();

load_pg_audit

---------------

(0 rows)

SELECT 1,

current_user;

substring('Thomas' from 2 for 3);

NOTICE: AUDIT: SESSION,27,1,READ,SELECT,,,"SELECT 1,

current_user;",<none>

?column? |current_user

----------+--------------

1 |super

substring('Thomas' from 2 for 3);",<none>

?column? |substring

----------+-----------

1 |hom

(1 row)

DO $$

NOTICE: AUDIT: SESSION,59,1,ROLE,GRANT ROLE,,,GRANT user1 TO user2;,<none>

REVOKE user1 FROM user2;

NOTICE: AUDIT: SESSION,60,1,ROLE,REVOKE ROLE,,,REVOKE user1 FROM user2;,<none>

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 user2;

NOTICE: AUDIT: SESSION,61,1,ROLE,DROP ROLE,,,DROP USER user2;,<none>

DROP USER user1;

NOTICE: AUDIT: SESSION,62,1,ROLE,DROP ROLE,,,DROP USER user1;,<none>

DROP ROLE auditor;

NOTICE: AUDIT: SESSION,63,1,ROLE,DROP ROLE,,,DROP ROLE auditor;,<none>

selectcurrent_user \gset

CREATEUSERsuper SUPERUSER;

ALTER ROLE superSETpg_audit.log='Role';

ALTER ROLE superSETpg_audit.log_level='notice';

ALTER ROLE :current_userSETpg_audit.log='Role';

ALTER ROLE :current_userSETpg_audit.log_level='notice';

CREATEFUNCTIONload_pg_audit( )

RETURNS VOID

\connect-super;

\connect-:current_user;

select load_pg_audit();

\connect-super

\connect-:current_user

select load_pg_audit();

CREATEUSERuser2;

\connect-super

\connect-:current_user

select load_pg_audit();

alter role user2setpg_audit.log='NONE';

\connect-super

\connect-:current_user

select load_pg_audit();

alter role user1setpg_audit.log='DDL, READ';

\connect- user1

\connect-super

\connect-:current_user

select load_pg_audit();

alter role user1setpg_audit.log='none';

alter role user1setpg_audit.role='auditor';

\connect-super

\connect-:current_user

select load_pg_audit();

alter role user1setpg_audit.log_relation=on;

alter role user1setpg_audit.log='read, WRITE';

\connect-super

\connect-:current_user

select load_pg_audit();

SETpg_audit.log='ALL';

SETpg_audit.log_level='notice';

FROM test;

SELECT1,

current_user;

substring('Thomas'from2 for3);

DO $$

DECLARE

SETpg_audit.log='role';

GRANT user1 TO user2;

REVOKE user1FROM user2;

DROPTABLEtest.account_copy;

DROPTABLEtest.test_insert;

DROPSCHEMA test;

DROPTABLEfoo.bar;

DROPTABLEfoo.baz;

DROPSCHEMA foo;

DROPTABLE hoge;

DROPTABLE account;

DROPTABLE account_role_map;

DROPUSER user2;

DROPUSER user1;

DROP ROLE auditor;