diff options
| -rw-r--r-- | contrib/pg_audit/expected/pg_audit.out | 47 | ||||
| -rw-r--r-- | contrib/pg_audit/sql/pg_audit.sql | 38 |
2 files changed, 57 insertions, 28 deletions
diff --git a/contrib/pg_audit/expected/pg_audit.out b/contrib/pg_audit/expected/pg_audit.out index 7cfa47ea3dc..886bd99d536 100644 --- a/contrib/pg_audit/expected/pg_audit.out +++ b/contrib/pg_audit/expected/pg_audit.out @@ -12,11 +12,11 @@ create extension pg_audit; -- 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 @@ -31,7 +31,7 @@ $function$; -- 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 --------------- @@ -70,7 +70,7 @@ DROP TABLE test; 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 --------------- @@ -241,7 +241,7 @@ UPDATE test3 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 --------------- @@ -328,7 +328,7 @@ DROP TABLE test3; 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 --------------- @@ -376,7 +376,7 @@ 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 - super +\connect - :current_user select load_pg_audit(); load_pg_audit --------------- @@ -435,7 +435,7 @@ 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 - super +\connect - :current_user select load_pg_audit(); load_pg_audit --------------- @@ -546,7 +546,7 @@ NOTICE: AUDIT: SESSION,5,1,WRITE,UPDATE,TABLE,public.account,"UPDATE account 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 --------------- @@ -751,12 +751,12 @@ NOTICE: AUDIT: SESSION,26,1,READ,SELECT,TABLE,public.test,"SELECT (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 $$ @@ -1054,3 +1054,18 @@ GRANT user1 TO user2; 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> diff --git a/contrib/pg_audit/sql/pg_audit.sql b/contrib/pg_audit/sql/pg_audit.sql index 0722b96d14b..c5ddd5ff144 100644 --- a/contrib/pg_audit/sql/pg_audit.sql +++ b/contrib/pg_audit/sql/pg_audit.sql @@ -14,11 +14,12 @@ create extension pg_audit; -- 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 @@ -35,7 +36,7 @@ $function$; -- 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(); -- @@ -58,7 +59,7 @@ DROP TABLE test; -- -- Create second test user -\connect - super +\connect - :current_user select load_pg_audit(); CREATE USER user2; @@ -168,7 +169,7 @@ UPDATE test3 -- -- Change permissions of user 2 so that only object logging will be done -\connect - super +\connect - :current_user select load_pg_audit(); alter role user2 set pg_audit.log = 'NONE'; @@ -243,7 +244,7 @@ DROP TABLE test4; -- -- Change permissions of user 1 so that session logging will be done -\connect - super +\connect - :current_user select load_pg_audit(); alter role user1 set pg_audit.log = 'DDL, READ'; \connect - user1 @@ -271,7 +272,7 @@ INSERT INTO account (id, name, password, description) -- -- Change permissions of user 1 so that only object logging will be done -\connect - super +\connect - :current_user select load_pg_audit(); alter role user1 set pg_audit.log = 'none'; alter role user1 set pg_audit.role = 'auditor'; @@ -310,7 +311,7 @@ UPDATE account -- -- Change permissions of user 1 so that session relation logging will be done -\connect - super +\connect - :current_user select load_pg_audit(); alter role user1 set pg_audit.log_relation = on; alter role user1 set pg_audit.log = 'read, WRITE'; @@ -372,7 +373,7 @@ UPDATE account -- -- Change back to superuser to do exhaustive tests -\connect - super +\connect - :current_user select load_pg_audit(); SET pg_audit.log = 'ALL'; SET pg_audit.log_level = 'notice'; @@ -486,7 +487,7 @@ SELECT FROM test; SELECT 1, - current_user; + substring('Thomas' from 2 for 3); DO $$ DECLARE @@ -644,3 +645,16 @@ drop table bar; SET pg_audit.log = 'role'; GRANT user1 TO user2; REVOKE user1 FROM user2; + +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; +DROP USER user1; +DROP ROLE auditor; |