Script 'mail_helper' called by obssrc Hello community, here is the log from the commit of package pgaudit for openSUSE:Factory checked in at 2025-07-09 17:28:50 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Comparing /work/SRC/openSUSE:Factory/pgaudit (Old) and /work/SRC/openSUSE:Factory/.pgaudit.new.7373 (New) ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Package is "pgaudit" Wed Jul 9 17:28:50 2025 rev:7 rq:1291429 version:unknown Changes: -------- --- /work/SRC/openSUSE:Factory/pgaudit/pgaudit.changes 2025-02-14 19:23:31.973240836 +0100 +++ /work/SRC/openSUSE:Factory/.pgaudit.new.7373/pgaudit.changes 2025-07-09 17:29:54.039285177 +0200 @@ -1,0 +2,6 @@ +Tue Jul 8 07:21:54 UTC 2025 - Reinhard Max <m...@suse.com> + +- Update to 17.1, 16.1, 1.7.1, 1.6.3, 1.5.3: + * Fix issues that could cause 'pgaudit stack is not empty' errors. + +------------------------------------------------------------------- Old: ---- pgaudit-1.5.2.tar.gz pgaudit-1.6.2.tar.gz pgaudit-1.7.0.tar.gz pgaudit-16.0.tar.gz pgaudit-17.0.tar.gz New: ---- pgaudit-1.5.3.tar.gz pgaudit-1.6.3.tar.gz pgaudit-1.7.1.tar.gz pgaudit-16.1.tar.gz pgaudit-17.1.tar.gz ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Other differences: ------------------ ++++++ pgaudit.spec ++++++ --- /var/tmp/diff_new_pack.jpLHr5/_old 2025-07-09 17:29:54.611309044 +0200 +++ /var/tmp/diff_new_pack.jpLHr5/_new 2025-07-09 17:29:54.615309211 +0200 @@ -32,20 +32,20 @@ Name: %{name_pg}-pgaudit %endif %if "%{name_pg}" == "postgresql13" -Version: 1.5.2 +Version: 1.5.3 %endif # you need to update both the Version: field and the define below for the factory check %if "%{name_pg}" == "postgresql14" -Version: 1.6.2 +Version: 1.6.3 %endif %if "%{name_pg}" == "postgresql15" -Version: 1.7.0 +Version: 1.7.1 %endif %if "%{name_pg}" == "postgresql16" -Version: 16.0 +Version: 16.1 %endif %if "%{name_pg}" == "postgresql17" -Version: 17.0 +Version: 17.1 %endif Release: 0 Summary: An auditing module for PostgreSQL ++++++ pgaudit-1.5.2.tar.gz -> pgaudit-1.5.3.tar.gz ++++++ diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pgaudit-1.5.2/.github/workflows/test.yml new/pgaudit-1.5.3/.github/workflows/test.yml --- old/pgaudit-1.5.2/.github/workflows/test.yml 2022-02-25 22:14:43.000000000 +0100 +++ new/pgaudit-1.5.3/.github/workflows/test.yml 2025-02-19 20:58:09.000000000 +0100 @@ -10,7 +10,7 @@ jobs: test: - runs-on: ubuntu-20.04 + runs-on: ubuntu-22.04 strategy: # Let all the jobs run to completion even if one fails @@ -27,7 +27,7 @@ steps: - name: Checkout Code - uses: actions/checkout@v2 + uses: actions/checkout@v4 with: path: pgaudit diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pgaudit-1.5.2/.gitignore new/pgaudit-1.5.3/.gitignore --- old/pgaudit-1.5.2/.gitignore 2022-02-25 22:14:43.000000000 +0100 +++ new/pgaudit-1.5.3/.gitignore 2025-02-19 20:58:09.000000000 +0100 @@ -1,8 +1,29 @@ +# Regression tests log/ results/ tmp_check/ regression.diffs regression.out + +# Coverage files +*.gcno +*.gcda + +# Libraries +*.lib +*.a + +# LLVM bitcode *.bc + +# Object files *.o + +# Shared objects (inc. Windows DLLs) +*.dll *.so +*.so.* +*.dylib + +# Dependencies +.deps diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pgaudit-1.5.2/Makefile new/pgaudit-1.5.3/Makefile --- old/pgaudit-1.5.2/Makefile 2022-02-25 22:14:43.000000000 +0100 +++ new/pgaudit-1.5.3/Makefile 2025-02-19 20:58:09.000000000 +0100 @@ -4,7 +4,7 @@ OBJS = pgaudit.o $(WIN32RES) EXTENSION = pgaudit -DATA = pgaudit--1.5.2.sql pgaudit--1.5--1.5.1.sql pgaudit--1.5.1--1.5.2.sql +DATA = pgaudit--1.5.3.sql pgaudit--1.5--1.5.1.sql pgaudit--1.5.1--1.5.2.sql pgaudit--1.5.2--1.5.3.sql PGFILEDESC = "pgAudit - An audit logging extension for PostgreSQL" REGRESS = pgaudit @@ -20,3 +20,5 @@ include $(top_builddir)/src/Makefile.global include $(top_srcdir)/contrib/contrib-global.mk endif + +EXTRA_INSTALL += contrib/pg_stat_statements diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pgaudit-1.5.2/README.md new/pgaudit-1.5.3/README.md --- old/pgaudit-1.5.2/README.md 2022-02-25 22:14:43.000000000 +0100 +++ new/pgaudit-1.5.3/README.md 2025-02-19 20:58:09.000000000 +0100 @@ -48,25 +48,25 @@ ## PostgreSQL Version Compatibility -pgAudit was developed to support PostgreSQL 9.5 or greater. +pgAudit supports PostgreSQL 12 or greater. -In order to support new functionality introduced in each PostgreSQL release, pgAudit maintains a separate branch for each PostgreSQL major version (currently PostgreSQL 9.5 - 11) which will be maintained in a manner similar to the PostgreSQL project. +In order to support new functionality introduced in each PostgreSQL release, pgAudit maintains a separate branch for each PostgreSQL major version (currently PostgreSQL 12 - 17) which will be maintained in a manner similar to the PostgreSQL project. -Aside from bug fixes, no further development is planned for stable branches. New development, if any, will be strictly for next unreleased major version of PostgreSQL. +Aside from bug fixes, no further development is allowed for stable branches. New development, if any, will be strictly for the next unreleased major version of PostgreSQL. pgAudit versions relate to PostgreSQL major versions as follows: -- **pgAudit v1.5.X** is intended to support PostgreSQL 13. +- **pgAudit v17.X** is intended to support PostgreSQL 17. -- **pgAudit v1.4.X** is intended to support PostgreSQL 12. +- **pgAudit v16.X** is intended to support PostgreSQL 16. -- **pgAudit v1.3.X** is intended to support PostgreSQL 11. +- **pgAudit v1.7.X** is intended to support PostgreSQL 15. -- **pgAudit v1.2.X** is intended to support PostgreSQL 10. +- **pgAudit v1.6.X** is intended to support PostgreSQL 14. -- **pgAudit v1.1.X** is intended to support PostgreSQL 9.6. +- **pgAudit v1.5.X** is intended to support PostgreSQL 13. -- **pgAudit v1.0.X** is intended to support PostgreSQL 9.5. +- **pgAudit v1.4.X** is intended to support PostgreSQL 12. ## Compile and Install @@ -98,7 +98,11 @@ Settings can be specified globally (in `postgresql.conf` or using `ALTER SYSTEM ... SET`), at the database level (using `ALTER DATABASE ... SET`), or at the role level (using `ALTER ROLE ... SET`). Note that settings are not inherited through normal role inheritance and `SET ROLE` will not alter a user's pgAudit settings. This is a limitation of the roles system and not inherent to pgAudit. -The pgAudit extension must be loaded in [shared_preload_libraries](http://www.postgresql.org/docs/13/runtime-config-client.html#GUC-SHARED-PRELOAD-LIBRARIES). Otherwise, an error will be raised at load time and no audit logging will occur. In addition, `CREATE EXTENSION pgaudit` must be called before `pgaudit.log` is set. If the `pgaudit` extension is dropped and needs to be recreated then `pgaudit.log` must be unset first otherwise an error will be raised. +The pgAudit extension must be loaded in [shared_preload_libraries](http://www.postgresql.org/docs/13/runtime-config-client.html#GUC-SHARED-PRELOAD-LIBRARIES). Otherwise, an error will be raised at load time and no audit logging will occur. + +In addition, `CREATE EXTENSION pgaudit` must be called before `pgaudit.log` is set to ensure proper pgaudit functionality. The extension installs event triggers which add additional auditing for DDL. pgAudit will work without the extension installed but DDL statements will not have information about the object type and name. + +If the `pgaudit` extension is dropped and needs to be recreated then `pgaudit.log` must be unset first otherwise an error will be raised. ### pgaudit.log @@ -160,7 +164,7 @@ ### pgaudit.log_statement_once -Specifies whether logging will include the statement text and parameters with the first log entry for a statement/substatement combination or with every entry. Disabling this setting will result in less verbose logging but may make it more difficult to determine the statement that generated a log entry, though the statement/substatement pair along with the process id should suffice to identify the statement text logged with a previous entry. +Specifies whether logging will include the statement text and parameters with the first log entry for a statement/substatement combination or with every entry. Enabling this setting will result in less verbose logging but may make it more difficult to determine the statement that generated a log entry, though the statement/substatement pair along with the process id should suffice to identify the statement text logged with a previous entry. The default is `off`. @@ -351,6 +355,8 @@ Statements that are executed after a transaction enters an aborted state will not be audit logged. However, the statement that caused the error and any subsequent statements executed in the aborted transaction will be logged as ERRORs by the standard logging facility. +It is not possible to reliably audit superusers with pgAudit. One solution is to restrict access to superuser accounts and use the [set_user](https://github.com/pgaudit/set_user) extension to escalate permissions when required. + ## Authors The PostgreSQL Audit Extension is based on the [2ndQuadrant](http://www.2ndquadrant.com) [pgaudit project](https://github.com/2ndQuadrant/pgaudit) authored by Simon Riggs, Abhijit Menon-Sen, and Ian Barwick and submitted as an extension to PostgreSQL core. Additional development has been done by David Steele of [Crunchy Data](http://www.crunchydata.com). diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pgaudit-1.5.2/expected/pgaudit.out new/pgaudit-1.5.3/expected/pgaudit.out --- old/pgaudit-1.5.2/expected/pgaudit.out 2022-02-25 22:14:43.000000000 +0100 +++ new/pgaudit-1.5.3/expected/pgaudit.out 2025-02-19 20:58:09.000000000 +0100 @@ -1,6 +1,8 @@ \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; @@ -38,27 +40,27 @@ \connect - :current_user; -- -- Create auditor role -CREATE ROLE auditor; -NOTICE: AUDIT: SESSION,1,1,ROLE,CREATE ROLE,,,CREATE ROLE auditor;,<not logged> +CREATE ROLE regress_auditor; +NOTICE: AUDIT: SESSION,1,1,ROLE,CREATE ROLE,,,CREATE ROLE regress_auditor;,<not logged> -- -- Create first test user -CREATE USER user1 password 'password'; -NOTICE: AUDIT: SESSION,2,1,ROLE,CREATE ROLE,,,CREATE USER user1 password <REDACTED>,<not logged> -ALTER ROLE user1 SET pgaudit.log = 'ddl, ROLE'; -NOTICE: AUDIT: SESSION,3,1,ROLE,ALTER ROLE,,,"ALTER ROLE user1 SET pgaudit.log = 'ddl, ROLE';",<not logged> -ALTER ROLE user1 SET pgaudit.log_level = 'notice'; -NOTICE: AUDIT: SESSION,4,1,ROLE,ALTER ROLE,,,ALTER ROLE user1 SET pgaudit.log_level = 'notice';,<not logged> -ALTER ROLE user1 PassWord 'password2' NOLOGIN; -NOTICE: AUDIT: SESSION,5,1,ROLE,ALTER ROLE,,,ALTER ROLE user1 PassWord <REDACTED>,<not logged> -ALTER USER user1 encrypted /* random comment */PASSWORD +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 user1 encrypted /* random comment */PASSWORD <REDACTED>,<not logged> -ALTER ROLE user1 SET pgaudit.log_client = ON; -NOTICE: AUDIT: SESSION,7,1,ROLE,ALTER ROLE,,,ALTER ROLE user1 SET pgaudit.log_client = ON;,<not logged> +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 - user1 +\connect - regress_user1 CREATE TABLE public.test ( id INT @@ -78,20 +80,20 @@ -- -- Create second test user \connect - :current_user -CREATE ROLE user2 LOGIN password 'password'; -NOTICE: AUDIT: SESSION,1,1,ROLE,CREATE ROLE,,,CREATE ROLE user2 LOGIN password <REDACTED>,<not logged> -ALTER ROLE user2 SET pgaudit.log = 'Read, writE'; -NOTICE: AUDIT: SESSION,2,1,ROLE,ALTER ROLE,,,"ALTER ROLE user2 SET pgaudit.log = 'Read, writE';",<not logged> -ALTER ROLE user2 SET pgaudit.log_catalog = OFF; -NOTICE: AUDIT: SESSION,3,1,ROLE,ALTER ROLE,,,ALTER ROLE user2 SET pgaudit.log_catalog = OFF;,<not logged> -ALTER ROLE user2 SET pgaudit.log_client = ON; -NOTICE: AUDIT: SESSION,4,1,ROLE,ALTER ROLE,,,ALTER ROLE user2 SET pgaudit.log_client = ON;,<not logged> -ALTER ROLE user2 SET pgaudit.log_level = 'warning'; -NOTICE: AUDIT: SESSION,5,1,ROLE,ALTER ROLE,,,ALTER ROLE user2 SET pgaudit.log_level = 'warning';,<not logged> -ALTER ROLE user2 SET pgaudit.role = auditor; -NOTICE: AUDIT: SESSION,6,1,ROLE,ALTER ROLE,,,ALTER ROLE user2 SET pgaudit.role = auditor;,<not logged> -ALTER ROLE user2 SET pgaudit.log_statement_once = ON; -NOTICE: AUDIT: SESSION,7,1,ROLE,ALTER ROLE,,,ALTER ROLE user2 SET pgaudit.log_statement_once = ON;,<not logged> +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 @@ -100,32 +102,32 @@ ); GRANT SELECT, INSERT, UPDATE, DELETE ON test2 - TO user2, user1; + TO regress_user2, regress_user1; NOTICE: AUDIT: SESSION,8,1,ROLE,GRANT,TABLE,,"GRANT SELECT, INSERT, UPDATE, DELETE ON test2 - TO user2, user1;",<not logged> + TO regress_user2, regress_user1;",<not logged> GRANT SELECT, UPDATE ON TABLE public.test2 - TO auditor; + TO regress_auditor; NOTICE: AUDIT: SESSION,9,1,ROLE,GRANT,TABLE,,"GRANT SELECT, UPDATE ON TABLE public.test2 - TO auditor;",<not logged> + TO regress_auditor;",<not logged> CREATE TABLE test3 ( id INT ); GRANT SELECT, INSERT, UPDATE, DELETE ON test3 - TO user2; + TO regress_user2; NOTICE: AUDIT: SESSION,10,1,ROLE,GRANT,TABLE,,"GRANT SELECT, INSERT, UPDATE, DELETE ON test3 - TO user2;",<not logged> + TO regress_user2;",<not logged> GRANT INSERT ON TABLE public.test3 - TO auditor; + TO regress_auditor; NOTICE: AUDIT: SESSION,11,1,ROLE,GRANT,TABLE,,"GRANT INSERT ON TABLE public.test3 - TO auditor;",<not logged> + TO regress_auditor;",<not logged> CREATE FUNCTION test2_insert() RETURNS TRIGGER AS $$ BEGIN UPDATE test2 @@ -134,7 +136,7 @@ RETURN new; END $$ LANGUAGE plpgsql security definer; -ALTER FUNCTION test2_insert() OWNER TO user1; +ALTER FUNCTION test2_insert() OWNER TO regress_user1; CREATE TRIGGER test2_insert_trg AFTER INSERT ON test2 FOR EACH ROW EXECUTE PROCEDURE test2_insert(); @@ -144,23 +146,23 @@ SET id = id + 1 WHERE id = change_id; END $$ LANGUAGE plpgsql security definer; -ALTER FUNCTION test2_change(int) OWNER TO user2; +ALTER FUNCTION test2_change(int) OWNER TO regress_user2; CREATE VIEW vw_test3 AS SELECT * FROM test3; GRANT SELECT ON vw_test3 - TO user2; + TO regress_user2; NOTICE: AUDIT: SESSION,12,1,ROLE,GRANT,TABLE,,"GRANT SELECT ON vw_test3 - TO user2;",<not logged> + TO regress_user2;",<not logged> GRANT SELECT ON vw_test3 - TO auditor; + TO regress_auditor; NOTICE: AUDIT: SESSION,13,1,ROLE,GRANT,TABLE,,"GRANT SELECT ON vw_test3 - TO auditor;",<not logged> -\connect - user2 + TO regress_auditor;",<not logged> +\connect - regress_user2 -- -- Role-based tests SELECT count(*) @@ -312,9 +314,9 @@ -- -- Change permissions of user 2 so that only object logging will be done \connect - :current_user -ALTER ROLE user2 SET pgaudit.log = 'NONE'; -NOTICE: AUDIT: SESSION,1,1,ROLE,ALTER ROLE,,,ALTER ROLE user2 SET pgaudit.log = 'NONE';,<not logged> -\connect - user2 +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 @@ -324,13 +326,13 @@ ); GRANT SELECT (name) ON TABLE public.test4 - TO auditor; + TO regress_auditor; GRANT UPDATE (id) ON TABLE public.test4 - TO auditor; + TO regress_auditor; GRANT insert (name) ON TABLE public.test4 - TO auditor; + TO regress_auditor; -- -- Not object logged SELECT id @@ -389,9 +391,9 @@ DROP TABLE test4; DROP FUNCTION test2_insert(); DROP FUNCTION test2_change(int); -ALTER ROLE user1 SET pgaudit.log = 'DDL, READ'; -NOTICE: AUDIT: SESSION,1,1,ROLE,ALTER ROLE,,,"ALTER ROLE user1 SET pgaudit.log = 'DDL, READ';",<not logged> -\connect - user1 +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 @@ -421,29 +423,29 @@ -- -- Insert is not logged INSERT INTO account (id, name, password, description) - VALUES (1, 'user1', 'HASH1', 'blah, blah'); + 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 user1 SET pgaudit.log = 'none'; -NOTICE: AUDIT: SESSION,1,1,ROLE,ALTER ROLE,,,ALTER ROLE user1 SET pgaudit.log = 'none';,<not logged> -ALTER ROLE user1 SET pgaudit.role = 'auditor'; -NOTICE: AUDIT: SESSION,2,1,ROLE,ALTER ROLE,,,ALTER ROLE user1 SET pgaudit.role = 'auditor';,<not logged> -\connect - user1 +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 auditor grants not logged +-- ROLE class not set, so regress_auditor grants not logged GRANT SELECT (password), UPDATE (name, password) ON TABLE public.account - TO auditor; + TO regress_auditor; -- -- Not object logged SELECT id, name FROM account; - id | name -----+------- - 1 | user1 + id | name +----+--------------- + 1 | regress_user1 (1 row) -- @@ -472,11 +474,11 @@ -- -- Change permissions of user 1 so that session relation logging will be done \connect - :current_user -ALTER ROLE user1 SET pgaudit.log_relation = on; -NOTICE: AUDIT: SESSION,1,1,ROLE,ALTER ROLE,,,ALTER ROLE user1 SET pgaudit.log_relation = on;,<not logged> -ALTER ROLE user1 SET pgaudit.log = 'read, WRITE'; -NOTICE: AUDIT: SESSION,2,1,ROLE,ALTER ROLE,,,"ALTER ROLE user1 SET pgaudit.log = 'read, WRITE';",<not logged> -\connect - user1 +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 @@ -485,10 +487,10 @@ role_id INT ); -- --- ROLE class not set, so auditor grants not logged +-- ROLE class not set, so regress_auditor grants not logged GRANT SELECT ON TABLE public.account_role_map - TO auditor; + TO regress_auditor; -- -- Object logged because of: -- select (password) on account @@ -561,9 +563,9 @@ FROM account WHERE password = 'HASH2' FOR UPDATE;",<not logged> - id | name | password | description -----+-------+----------+------------- - 1 | user1 | HASH2 | yada, yada + id | name | password | description +----+---------------+----------+------------- + 1 | regress_user1 | HASH2 | yada, yada (1 row) -- @@ -621,7 +623,7 @@ -- Copy account to stdout COPY account TO stdout; NOTICE: AUDIT: SESSION,8,1,READ,SELECT,TABLE,public.account,COPY account TO stdout;,<none> -1 user1 HASH2 yada, yada +1 regress_user1 HASH2 yada, yada -- -- Create a table from a query CREATE TABLE test.account_copy AS @@ -656,9 +658,9 @@ FROM account WHERE id = $1;",1 NOTICE: AUDIT: SESSION,12,2,MISC,EXECUTE,,,EXECUTE pgclassstmt (1);,<none> - id | name | password | description -----+-------+----------+------------- - 1 | user1 | HASH2 | yada, yada + id | name | password | description +----+---------------+----------+------------- + 1 | regress_user1 | HASH2 | yada, yada (1 row) DEALLOCATE pgclassstmt; @@ -997,19 +999,19 @@ -- Test role as a substmt SET pgaudit.log = 'ROLE'; CREATE TABLE t (); -CREATE ROLE alice; -NOTICE: AUDIT: SESSION,53,1,ROLE,CREATE ROLE,,,CREATE ROLE alice;,<none> +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 alice; + TO regress_alice; NOTICE: AUDIT: SESSION,54,1,ROLE,GRANT,TABLE,,"CREATE SCHEMA foo2 GRANT SELECT ON public.t - TO alice;",<none> + TO regress_alice;",<none> drop table public.t; -drop role alice; -NOTICE: AUDIT: SESSION,55,1,ROLE,DROP ROLE,,,drop role alice;,<none> +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 @@ -1092,14 +1094,14 @@ -- -- Delete all rows then delete 1 row SET pgaudit.log = 'write'; -SET pgaudit.role = 'auditor'; +SET pgaudit.role = 'regress_auditor'; create table bar ( col int ); grant delete on bar - to auditor; + to regress_auditor; insert into bar (col) values (1); NOTICE: AUDIT: SESSION,60,1,WRITE,INSERT,TABLE,public.bar,"insert into bar (col) @@ -1121,14 +1123,14 @@ -- -- Grant roles to each other SET pgaudit.log = 'role'; -GRANT user1 TO user2; -NOTICE: AUDIT: SESSION,64,1,ROLE,GRANT ROLE,,,GRANT user1 TO user2;,<none> -REVOKE user1 FROM user2; -NOTICE: AUDIT: SESSION,65,1,ROLE,REVOKE ROLE,,,REVOKE user1 FROM user2;,<none> +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 'auditor'; +SET pgaudit.role TO 'regress_auditor'; CREATE TABLE aaa ( ID int primary key @@ -1149,10 +1151,10 @@ FOR EACH ROW EXECUTE PROCEDURE bbb_insert(); GRANT SELECT, UPDATE ON aaa - TO auditor; + TO regress_auditor; GRANT UPDATE ON bbb - TO auditor; + 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; @@ -1187,9 +1189,9 @@ 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 alice; -SET ROLE alice; -NOTICE: AUDIT: SESSION,74,1,MISC,SET,,,SET ROLE alice;,<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> @@ -1202,8 +1204,8 @@ -- Test MISC_SET SET pgaudit.log = 'MISC_SET'; NOTICE: AUDIT: SESSION,78,1,MISC,SET,,,SET pgaudit.log = 'MISC_SET';,<none> -SET ROLE alice; -NOTICE: AUDIT: SESSION,79,1,MISC,SET,,,SET ROLE alice;,<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'); @@ -1223,8 +1225,8 @@ 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 alice; -NOTICE: AUDIT: SESSION,87,1,ROLE,DROP ROLE,,,DROP ROLE alice;,<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); @@ -1300,6 +1302,56 @@ DROP EXTENSION pg_stat_statements; WARNING: AUDIT: SESSION,106,1,DDL,DROP EXTENSION,,,DROP EXTENSION pg_stat_statements;,<none> SET pgaudit.log_level = 'notice'; +-- Check that partition scans are skipped for auditing and do no result in an empty stack +SET pgaudit.log = 'all'; +NOTICE: AUDIT: SESSION,107,1,MISC,SET,,,SET pgaudit.log = 'all';,<none> +CREATE TABLE part_test (c1 int, c2 int) PARTITION BY RANGE (c1); +NOTICE: AUDIT: SESSION,108,1,DDL,CREATE TABLE,,,"CREATE TABLE part_test (c1 int, c2 int) PARTITION BY RANGE (c1);",<none> +CREATE TABLE part_test_1_to_10 PARTITION OF part_test FOR VALUES FROM (1) TO (10); +NOTICE: AUDIT: SESSION,109,1,DDL,CREATE TABLE,,,CREATE TABLE part_test_1_to_10 PARTITION OF part_test FOR VALUES FROM (1) TO (10);,<none> +INSERT INTO part_test VALUES (generate_series(1,9)); +NOTICE: AUDIT: SESSION,110,1,WRITE,INSERT,TABLE,public.part_test,"INSERT INTO part_test VALUES (generate_series(1,9));",<none> +CREATE OR REPLACE FUNCTION get_test_id(_ret REFCURSOR) RETURNS REFCURSOR +LANGUAGE plpgsql IMMUTABLE AS $$ +BEGIN + OPEN _ret FOR SELECT * FROM part_test; + RETURN _ret; +END $$; +NOTICE: AUDIT: SESSION,111,1,DDL,CREATE FUNCTION,,,"CREATE OR REPLACE FUNCTION get_test_id(_ret REFCURSOR) RETURNS REFCURSOR +LANGUAGE plpgsql IMMUTABLE AS $$ +BEGIN + OPEN _ret FOR SELECT * FROM part_test; + RETURN _ret; +END $$;",<none> +BEGIN; +NOTICE: AUDIT: SESSION,112,1,MISC,BEGIN,,,BEGIN;,<none> +SELECT get_test_id('_ret'); +NOTICE: AUDIT: SESSION,113,1,READ,SELECT,TABLE,public.part_test,SELECT * FROM part_test,<none> +NOTICE: AUDIT: SESSION,113,2,READ,SELECT,,,SELECT get_test_id('_ret');,<none> + get_test_id +------------- + _ret +(1 row) + +FETCH ALL FROM _ret; +NOTICE: AUDIT: SESSION,113,3,MISC,FETCH,,,FETCH ALL FROM _ret;,<none> + c1 | c2 +----+---- + 1 | + 2 | + 3 | + 4 | + 5 | + 6 | + 7 | + 8 | + 9 | +(9 rows) + +COMMIT; +NOTICE: AUDIT: SESSION,113,4,MISC,COMMIT,,,COMMIT;,<none> +DROP TABLE part_test; +NOTICE: AUDIT: SESSION,114,1,DDL,DROP TABLE,,,DROP TABLE part_test;,<none> -- Cleanup -- Set client_min_messages up to warning to avoid noise SET client_min_messages = 'warning'; @@ -1327,7 +1379,7 @@ DROP TABLE hoge; DROP TABLE account; DROP TABLE account_role_map; -DROP USER user2; -DROP USER user1; -DROP ROLE auditor; +DROP USER regress_user2; +DROP USER regress_user1; +DROP ROLE regress_auditor; RESET client_min_messages; diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pgaudit-1.5.2/pgaudit--1.5.2--1.5.3.sql new/pgaudit-1.5.3/pgaudit--1.5.2--1.5.3.sql --- old/pgaudit-1.5.2/pgaudit--1.5.2--1.5.3.sql 1970-01-01 01:00:00.000000000 +0100 +++ new/pgaudit-1.5.3/pgaudit--1.5.2--1.5.3.sql 2025-02-19 20:58:09.000000000 +0100 @@ -0,0 +1,2 @@ +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION pgaudit" to load this file.\quit diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pgaudit-1.5.2/pgaudit--1.5.2.sql new/pgaudit-1.5.3/pgaudit--1.5.2.sql --- old/pgaudit-1.5.2/pgaudit--1.5.2.sql 2022-02-25 22:14:43.000000000 +0100 +++ new/pgaudit-1.5.3/pgaudit--1.5.2.sql 1970-01-01 01:00:00.000000000 +0100 @@ -1,24 +0,0 @@ --- complain if script is sourced in psql, rather than via CREATE EXTENSION -\echo Use "CREATE EXTENSION pgaudit" to load this file.\quit - -CREATE FUNCTION pgaudit_ddl_command_end() - RETURNS event_trigger - SECURITY DEFINER - SET search_path = 'pg_catalog, pg_temp' - LANGUAGE C - AS 'MODULE_PATHNAME', 'pgaudit_ddl_command_end'; - -CREATE EVENT TRIGGER pgaudit_ddl_command_end - ON ddl_command_end - EXECUTE PROCEDURE pgaudit_ddl_command_end(); - -CREATE FUNCTION pgaudit_sql_drop() - RETURNS event_trigger - SECURITY DEFINER - SET search_path = 'pg_catalog, pg_temp' - LANGUAGE C - AS 'MODULE_PATHNAME', 'pgaudit_sql_drop'; - -CREATE EVENT TRIGGER pgaudit_sql_drop - ON sql_drop - EXECUTE PROCEDURE pgaudit_sql_drop(); diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pgaudit-1.5.2/pgaudit--1.5.3.sql new/pgaudit-1.5.3/pgaudit--1.5.3.sql --- old/pgaudit-1.5.2/pgaudit--1.5.3.sql 1970-01-01 01:00:00.000000000 +0100 +++ new/pgaudit-1.5.3/pgaudit--1.5.3.sql 2025-02-19 20:58:09.000000000 +0100 @@ -0,0 +1,24 @@ +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION pgaudit" to load this file.\quit + +CREATE FUNCTION pgaudit_ddl_command_end() + RETURNS event_trigger + SECURITY DEFINER + SET search_path = 'pg_catalog, pg_temp' + LANGUAGE C + AS 'MODULE_PATHNAME', 'pgaudit_ddl_command_end'; + +CREATE EVENT TRIGGER pgaudit_ddl_command_end + ON ddl_command_end + EXECUTE PROCEDURE pgaudit_ddl_command_end(); + +CREATE FUNCTION pgaudit_sql_drop() + RETURNS event_trigger + SECURITY DEFINER + SET search_path = 'pg_catalog, pg_temp' + LANGUAGE C + AS 'MODULE_PATHNAME', 'pgaudit_sql_drop'; + +CREATE EVENT TRIGGER pgaudit_sql_drop + ON sql_drop + EXECUTE PROCEDURE pgaudit_sql_drop(); diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pgaudit-1.5.2/pgaudit.c new/pgaudit-1.5.3/pgaudit.c --- old/pgaudit-1.5.2/pgaudit.c 2022-02-25 22:14:43.000000000 +0100 +++ new/pgaudit-1.5.3/pgaudit.c 2025-02-19 20:58:09.000000000 +0100 @@ -5,7 +5,7 @@ * object level logging, and fully-qualified object names for all DML and DDL * statements where possible (See README.md for details). * - * Copyright (c) 2014-2022, PostgreSQL Global Development Group + * Copyright (c) 2014-2025, PostgreSQL Global Development Group *------------------------------------------------------------------------------ */ #include "postgres.h" @@ -70,7 +70,7 @@ #define LOG_ALL (0xFFFFFFFF) /* All */ /* GUC variable for pgaudit.log, which defines the classes to log. */ -char *auditLog = NULL; +static char *auditLog = NULL; /* Bitmap of classes selected */ static int auditLogBitmap = LOG_NONE; @@ -97,7 +97,7 @@ * the query are in pg_catalog. Interactive sessions (eg: psql) can cause * a lot of noise in the logs which might be uninteresting. */ -bool auditLogCatalog = true; +static bool auditLogCatalog = true; /* * GUC variable for pgaudit.log_client @@ -106,7 +106,7 @@ * setting should generally be left disabled but may be useful for debugging or * other purposes. */ -bool auditLogClient = false; +static bool auditLogClient = false; /* * GUC variable for pgaudit.log_level @@ -115,8 +115,8 @@ * at. The default level is LOG, which goes into the server log but does * not go to the client. Set to NOTICE in the regression tests. */ -char *auditLogLevelString = NULL; -int auditLogLevel = LOG; +static char *auditLogLevelString = NULL; +static int auditLogLevel = LOG; /* * GUC variable for pgaudit.log_parameter @@ -124,7 +124,7 @@ * Administrators can choose if parameters passed into a statement are * included in the audit log. */ -bool auditLogParameter = false; +static bool auditLogParameter = false; /* * GUC variable for pgaudit.log_relation @@ -133,7 +133,7 @@ * in READ/WRITE class queries. By default, SESSION logs include the query but * do not have a log entry for each relation. */ -bool auditLogRelation = false; +static bool auditLogRelation = false; /* * GUC variable for pgaudit.log_statement_once @@ -141,9 +141,9 @@ * Administrators can choose to have the statement run logged only once instead * of on every line. By default, the statement is repeated on every line of * the audit log to facilitate searching, but this can cause the log to be - * unnecessairly bloated in some environments. + * unnecessarily bloated in some environments. */ -bool auditLogStatementOnce = false; +static bool auditLogStatementOnce = false; /* * GUC variable for pgaudit.role @@ -152,7 +152,7 @@ * Object-level auditing uses the privileges which are granted to this role to * determine if a statement should be logged. */ -char *auditRole = NULL; +static char *auditRole = NULL; /* * String constants for the audit log fields. @@ -236,7 +236,7 @@ MemoryContextCallback contextCallback; } AuditEventStackItem; -AuditEventStackItem *auditEventStack = NULL; +static AuditEventStackItem *auditEventStack = NULL; /* * pgAudit runs queries of its own when using the event trigger system. @@ -665,7 +665,7 @@ append_valid_csv(&auditStr, stackItem->auditEvent.objectName); /* - * If auditLogStatmentOnce is true, then only log the statement and + * If auditLogStatementOnce is true, then only log the statement and * parameters if they have not already been logged for this substatement. */ appendStringInfoCharMacro(&auditStr, ','); @@ -970,8 +970,11 @@ Oid relNamespaceOid; RangeTblEntry *rte = lfirst(lr); - /* We only care about tables, and can ignore subqueries etc. */ - if (rte->rtekind != RTE_RELATION) + /* + * We only care about tables, and can ignore subqueries etc. Also detect + * and skip partitions by checking for missing requiredPerms. + */ + if (rte->rtekind != RTE_RELATION || rte->requiredPerms == 0) continue; found = true; @@ -979,7 +982,7 @@ /* * Don't log if the session user is not a member of the current * role. This prevents contents of security definer functions - * from being logged and supresses foreign key queries unless the + * from being logged and suppresses foreign key queries unless the * session user is the owner of the referenced table. */ if (!is_member_of_role(GetSessionUserId(), GetUserId())) @@ -1013,7 +1016,7 @@ } /* - * We don't have access to the parsetree here, so we have to generate + * We don't have access to the parse tree here, so we have to generate * the node type, object type, and command tag by decoding * rte->requiredPerms and rte->relkind. For updates we also check * rellockmode so that only true UPDATE commands (not @@ -1361,8 +1364,8 @@ if (context == PROCESS_UTILITY_TOPLEVEL) { /* - * If the stack is not empty then the only allowed entries are open - * select, show, and explain cursors + * If the stack is not empty then the only allowed entries are call + * statements or open, select, show, and explain cursors */ if (auditEventStack != NULL) { @@ -1372,7 +1375,8 @@ { if (nextItem->auditEvent.commandTag != T_SelectStmt && nextItem->auditEvent.commandTag != T_VariableShowStmt && - nextItem->auditEvent.commandTag != T_ExplainStmt) + nextItem->auditEvent.commandTag != T_ExplainStmt && + nextItem->auditEvent.commandTag != T_CallStmt) { elog(ERROR, "pgaudit stack is not empty"); } @@ -1969,7 +1973,7 @@ "Specifies whether logging will include the statement text and " "parameters with the first log entry for a statement/substatement " - "combination or with every entry. Disabling this setting will result " + "combination or with every entry. Enabling this setting will result " "in less verbose logging but may make it more difficult to determine " "the statement that generated a log entry, though the " "statement/substatement pair along with the process id should suffice " @@ -1986,7 +1990,7 @@ DefineCustomStringVariable( "pgaudit.role", - "Specifies the master role to use for object audit logging. Muliple " + "Specifies the master role to use for object audit logging. Multiple " "audit roles can be defined by granting them to the master role. This " "allows multiple groups to be in charge of different aspects of audit " "logging.", diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pgaudit-1.5.2/pgaudit.control new/pgaudit-1.5.3/pgaudit.control --- old/pgaudit-1.5.2/pgaudit.control 2022-02-25 22:14:43.000000000 +0100 +++ new/pgaudit-1.5.3/pgaudit.control 2025-02-19 20:58:09.000000000 +0100 @@ -1,5 +1,5 @@ # pgaudit extension comment = 'provides auditing functionality' -default_version = '1.5.2' +default_version = '1.5.3' module_pathname = '$libdir/pgaudit' relocatable = true diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pgaudit-1.5.2/sql/pgaudit.sql new/pgaudit-1.5.3/sql/pgaudit.sql --- old/pgaudit-1.5.2/sql/pgaudit.sql 2022-02-25 22:14:43.000000000 +0100 +++ new/pgaudit-1.5.3/sql/pgaudit.sql 2025-02-19 20:58:09.000000000 +0100 @@ -3,6 +3,9 @@ -- 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; @@ -45,23 +48,23 @@ -- -- Create auditor role -CREATE ROLE auditor; +CREATE ROLE regress_auditor; -- -- Create first test user -CREATE USER user1 password 'password'; -ALTER ROLE user1 SET pgaudit.log = 'ddl, ROLE'; -ALTER ROLE user1 SET pgaudit.log_level = 'notice'; +CREATE USER regress_user1 password 'password'; +ALTER ROLE regress_user1 SET pgaudit.log = 'ddl, ROLE'; +ALTER ROLE regress_user1 SET pgaudit.log_level = 'notice'; -ALTER ROLE user1 PassWord 'password2' NOLOGIN; -ALTER USER user1 encrypted /* random comment */PASSWORD +ALTER ROLE regress_user1 PassWord 'password2' NOLOGIN; +ALTER USER regress_user1 encrypted /* random comment */PASSWORD /* random comment */ 'md565cb1da342495ea6bb0418a6e5718c38' LOGIN; -ALTER ROLE user1 SET pgaudit.log_client = ON; +ALTER ROLE regress_user1 SET pgaudit.log_client = ON; -- -- Create, select, drop (select will not be audited) -\connect - user1 +\connect - regress_user1 CREATE TABLE public.test ( @@ -77,13 +80,13 @@ -- Create second test user \connect - :current_user -CREATE ROLE user2 LOGIN password 'password'; -ALTER ROLE user2 SET pgaudit.log = 'Read, writE'; -ALTER ROLE user2 SET pgaudit.log_catalog = OFF; -ALTER ROLE user2 SET pgaudit.log_client = ON; -ALTER ROLE user2 SET pgaudit.log_level = 'warning'; -ALTER ROLE user2 SET pgaudit.role = auditor; -ALTER ROLE user2 SET pgaudit.log_statement_once = ON; +CREATE ROLE regress_user2 LOGIN password 'password'; +ALTER ROLE regress_user2 SET pgaudit.log = 'Read, writE'; +ALTER ROLE regress_user2 SET pgaudit.log_catalog = OFF; +ALTER ROLE regress_user2 SET pgaudit.log_client = ON; +ALTER ROLE regress_user2 SET pgaudit.log_level = 'warning'; +ALTER ROLE regress_user2 SET pgaudit.role = regress_auditor; +ALTER ROLE regress_user2 SET pgaudit.log_statement_once = ON; -- -- Setup role-based tests @@ -94,11 +97,11 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON test2 - TO user2, user1; + TO regress_user2, regress_user1; GRANT SELECT, UPDATE ON TABLE public.test2 - TO auditor; + TO regress_auditor; CREATE TABLE test3 ( @@ -107,11 +110,11 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON test3 - TO user2; + TO regress_user2; GRANT INSERT ON TABLE public.test3 - TO auditor; + TO regress_auditor; CREATE FUNCTION test2_insert() RETURNS TRIGGER AS $$ BEGIN @@ -121,7 +124,7 @@ RETURN new; END $$ LANGUAGE plpgsql security definer; -ALTER FUNCTION test2_insert() OWNER TO user1; +ALTER FUNCTION test2_insert() OWNER TO regress_user1; CREATE TRIGGER test2_insert_trg AFTER INSERT ON test2 @@ -133,7 +136,7 @@ SET id = id + 1 WHERE id = change_id; END $$ LANGUAGE plpgsql security definer; -ALTER FUNCTION test2_change(int) OWNER TO user2; +ALTER FUNCTION test2_change(int) OWNER TO regress_user2; CREATE VIEW vw_test3 AS SELECT * @@ -141,13 +144,13 @@ GRANT SELECT ON vw_test3 - TO user2; + TO regress_user2; GRANT SELECT ON vw_test3 - TO auditor; + TO regress_auditor; -\connect - user2 +\connect - regress_user2 -- -- Role-based tests @@ -233,9 +236,9 @@ -- -- Change permissions of user 2 so that only object logging will be done \connect - :current_user -ALTER ROLE user2 SET pgaudit.log = 'NONE'; +ALTER ROLE regress_user2 SET pgaudit.log = 'NONE'; -\connect - user2 +\connect - regress_user2 -- -- Create test4 and add permissions @@ -247,15 +250,15 @@ GRANT SELECT (name) ON TABLE public.test4 - TO auditor; + TO regress_auditor; GRANT UPDATE (id) ON TABLE public.test4 - TO auditor; + TO regress_auditor; GRANT insert (name) ON TABLE public.test4 - TO auditor; + TO regress_auditor; -- -- Not object logged @@ -309,8 +312,8 @@ DROP FUNCTION test2_insert(); DROP FUNCTION test2_change(int); -ALTER ROLE user1 SET pgaudit.log = 'DDL, READ'; -\connect - user1 +ALTER ROLE regress_user1 SET pgaudit.log = 'DDL, READ'; +\connect - regress_user1 -- -- Create table is session logged @@ -330,21 +333,21 @@ -- -- Insert is not logged INSERT INTO account (id, name, password, description) - VALUES (1, 'user1', 'HASH1', 'blah, blah'); + 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 user1 SET pgaudit.log = 'none'; -ALTER ROLE user1 SET pgaudit.role = 'auditor'; -\connect - user1 +ALTER ROLE regress_user1 SET pgaudit.log = 'none'; +ALTER ROLE regress_user1 SET pgaudit.role = 'regress_auditor'; +\connect - regress_user1 -- --- ROLE class not set, so auditor grants not logged +-- ROLE class not set, so regress_auditor grants not logged GRANT SELECT (password), UPDATE (name, password) ON TABLE public.account - TO auditor; + TO regress_auditor; -- -- Not object logged @@ -372,9 +375,9 @@ -- -- Change permissions of user 1 so that session relation logging will be done \connect - :current_user -ALTER ROLE user1 SET pgaudit.log_relation = on; -ALTER ROLE user1 SET pgaudit.log = 'read, WRITE'; -\connect - user1 +ALTER ROLE regress_user1 SET pgaudit.log_relation = on; +ALTER ROLE regress_user1 SET pgaudit.log = 'read, WRITE'; +\connect - regress_user1 -- -- Not logged @@ -385,10 +388,10 @@ ); -- --- ROLE class not set, so auditor grants not logged +-- ROLE class not set, so regress_auditor grants not logged GRANT SELECT ON TABLE public.account_role_map - TO auditor; + TO regress_auditor; -- -- Object logged because of: @@ -471,7 +474,7 @@ -- -- Copy from stdin to account copy COPY test.account_copy from stdin; -1 user1 HASH2 yada, yada +1 regress_user1 HASH2 yada, yada \. -- @@ -665,15 +668,15 @@ SET pgaudit.log = 'ROLE'; CREATE TABLE t (); -CREATE ROLE alice; +CREATE ROLE regress_alice; CREATE SCHEMA foo2 GRANT SELECT ON public.t - TO alice; + TO regress_alice; drop table public.t; -drop role alice; +drop role regress_alice; -- -- Test for non-empty stack error @@ -719,7 +722,7 @@ -- -- Delete all rows then delete 1 row SET pgaudit.log = 'write'; -SET pgaudit.role = 'auditor'; +SET pgaudit.role = 'regress_auditor'; create table bar ( @@ -728,7 +731,7 @@ grant delete on bar - to auditor; + to regress_auditor; insert into bar (col) values (1); @@ -744,13 +747,13 @@ -- -- Grant roles to each other SET pgaudit.log = 'role'; -GRANT user1 TO user2; -REVOKE user1 FROM user2; +GRANT regress_user1 TO regress_user2; +REVOKE regress_user1 FROM regress_user2; -- -- Test that FK references do not log but triggers still do SET pgaudit.log = 'READ,WRITE'; -SET pgaudit.role TO 'auditor'; +SET pgaudit.role TO 'regress_auditor'; CREATE TABLE aaa ( @@ -776,11 +779,11 @@ GRANT SELECT, UPDATE ON aaa - TO auditor; + TO regress_auditor; GRANT UPDATE ON bbb - TO auditor; + TO regress_auditor; INSERT INTO aaa VALUES (generate_series(1,100)); @@ -808,9 +811,9 @@ SET pgaudit.log_relation = ON; SET pgaudit.log_parameter = ON; -CREATE ROLE alice; +CREATE ROLE regress_alice; -SET ROLE alice; +SET ROLE regress_alice; CREATE TABLE t (a int, b text); SET search_path TO test, public; @@ -823,7 +826,7 @@ -- Test MISC_SET SET pgaudit.log = 'MISC_SET'; -SET ROLE alice; +SET ROLE regress_alice; SET search_path TO public; INSERT INTO t VALUES (2, 'misc_set'); @@ -843,7 +846,7 @@ RESET ROLE; DROP TABLE public.t; -DROP ROLE alice; +DROP ROLE regress_alice; -- -- Test PARTITIONED table @@ -900,6 +903,27 @@ SET pgaudit.log_level = 'notice'; +-- Check that partition scans are skipped for auditing and do no result in an empty stack +SET pgaudit.log = 'all'; + +CREATE TABLE part_test (c1 int, c2 int) PARTITION BY RANGE (c1); +CREATE TABLE part_test_1_to_10 PARTITION OF part_test FOR VALUES FROM (1) TO (10); +INSERT INTO part_test VALUES (generate_series(1,9)); + +CREATE OR REPLACE FUNCTION get_test_id(_ret REFCURSOR) RETURNS REFCURSOR +LANGUAGE plpgsql IMMUTABLE AS $$ +BEGIN + OPEN _ret FOR SELECT * FROM part_test; + RETURN _ret; +END $$; + +BEGIN; +SELECT get_test_id('_ret'); +FETCH ALL FROM _ret; +COMMIT; + +DROP TABLE part_test; + -- Cleanup -- Set client_min_messages up to warning to avoid noise SET client_min_messages = 'warning'; @@ -930,8 +954,8 @@ DROP TABLE hoge; DROP TABLE account; DROP TABLE account_role_map; -DROP USER user2; -DROP USER user1; -DROP ROLE auditor; +DROP USER regress_user2; +DROP USER regress_user1; +DROP ROLE regress_auditor; RESET client_min_messages; diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pgaudit-1.5.2/test/Dockerfile.debian new/pgaudit-1.5.3/test/Dockerfile.debian --- old/pgaudit-1.5.2/test/Dockerfile.debian 2022-02-25 22:14:43.000000000 +0100 +++ new/pgaudit-1.5.3/test/Dockerfile.debian 2025-02-19 20:58:09.000000000 +0100 @@ -1,33 +1,35 @@ -FROM ubuntu:focal - -# User and group id -ARG UID=1000 -ARG GID=1000 - -# Setup environment -ENV PGVERSION=13 -ENV PGBIN=/usr/lib/postgresql/${PGVERSION}/bin -ENV PGDATA="/var/lib/postgresql/${PGVERSION}/test" -ENV PATH="${PATH}:${PGBIN}" +FROM ubuntu:jammy # Install packages RUN apt-get update -RUN DEBIAN_FRONTEND=noninteractive apt-get install -y sudo wget gnupg tzdata locales lsb-release apt-utils +RUN DEBIAN_FRONTEND=noninteractive apt-get install -y sudo wget gnupg tzdata locales lsb-release apt-utils make gcc libssl-dev \ + libkrb5-dev # Create postgres user/group with specific IDs +ARG UID=1000 +ARG GID=1000 + RUN groupadd -g $GID -o postgres RUN useradd -m -u $UID -g $GID -o -s /bin/bash postgres -# Install PostgreSQL +# Add PostgreSQL repository RUN RELEASE_CODENAME=`lsb_release -c | awk '{print $2}'` && \ echo 'deb http://apt.postgresql.org/pub/repos/apt/ '${RELEASE_CODENAME?}'-pgdg main' | \ tee -a /etc/apt/sources.list.d/pgdg.list RUN APT_KEY_DONT_WARN_ON_DANGEROUS_USAGE=1 && \ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - RUN apt-get update -RUN apt-get install -y postgresql-${PGVERSION?} postgresql-server-dev-${PGVERSION?} make gcc libssl-dev libkrb5-dev + +# Install PostgreSQL +ENV PGVERSION=13 + +RUN apt-get install -y postgresql-${PGVERSION?} postgresql-server-dev-${PGVERSION?} # Create PostgreSQL cluster +ENV PGBIN=/usr/lib/postgresql/${PGVERSION}/bin +ENV PGDATA="/var/lib/postgresql/${PGVERSION}/test" +ENV PATH="${PATH}:${PGBIN}" + RUN sudo -u postgres ${PGBIN?}/initdb -A trust -k ${PGDATA?} RUN echo "shared_preload_libraries = 'pgaudit'" >> ${PGDATA}/postgresql.conf diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pgaudit-1.5.2/test/Dockerfile.rhel new/pgaudit-1.5.3/test/Dockerfile.rhel --- old/pgaudit-1.5.2/test/Dockerfile.rhel 2022-02-25 22:14:43.000000000 +0100 +++ new/pgaudit-1.5.3/test/Dockerfile.rhel 2025-02-19 20:58:09.000000000 +0100 @@ -1,30 +1,39 @@ -FROM centos:7 - -# User and group id -ARG UID=1000 -ARG GID=1000 - -# Setup environment -ENV PGVERSION=13 -ENV PGBIN=/usr/pgsql-${PGVERSION}/bin -ENV PGDATA="/var/lib/pgsql/${PGVERSION}/data" -ENV PATH="${PATH}:${PGBIN}" +FROM rockylinux/rockylinux:8 # Install packages -RUN yum install -y centos-release-scl-rh epel-release -RUN yum install -y sudo +RUN dnf install -y sudo make gcc llvm-toolset openssl-devel redhat-rpm-config + +# Install and enable EPEL repository +RUN dnf -y install epel-release +RUN crb enable # Create postgres user/group with specific IDs +ARG UID=1000 +ARG GID=1000 + RUN groupadd -g $GID -o postgres RUN useradd -m -u $UID -g $GID -o -s /bin/bash postgres +# Add PostgreSQL repository +RUN dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-`uname -m`/pgdg-redhat-repo-latest.noarch.rpm + +# Disable PostgreSQL in default repository +RUN dnf -qy module disable postgresql + # Install PostgreSQL -RUN rpm --import http://yum.postgresql.org/RPM-GPG-KEY-PGDG -RUN rpm -ivh https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm -RUN yum install -y postgresql${PGVERSION?}-server postgresql${PGVERSION?}-devel postgresql${PGVERSION?}-contrib \ - make gcc openssl-devel llvm-toolset-7-clang llvm5.0 +ENV PGVERSION=13 + +RUN dnf install -y postgresql${PGVERSION?}-server postgresql${PGVERSION?}-devel postgresql${PGVERSION?}-contrib + +# Configure llvm lib (required for aarch64) +RUN echo /opt/rh/llvm-toolset-7.0/root/usr/lib64/ > /etc/ld.so.conf.d/llvm-toolset.conf +RUN ldconfig # Create PostgreSQL cluster +ENV PGBIN=/usr/pgsql-${PGVERSION}/bin +ENV PGDATA="/var/lib/pgsql/${PGVERSION}/data" +ENV PATH="${PATH}:${PGBIN}" + RUN sudo -u postgres ${PGBIN?}/initdb -A trust -k ${PGDATA?} RUN echo "shared_preload_libraries = 'pgaudit'" >> ${PGDATA?}/postgresql.conf ++++++ pgaudit-1.5.2.tar.gz -> pgaudit-1.6.3.tar.gz ++++++ ++++ 3837 lines of diff (skipped) ++++++ pgaudit-1.5.2.tar.gz -> pgaudit-1.7.1.tar.gz ++++++ ++++ 3796 lines of diff (skipped) ++++++ pgaudit-1.5.2.tar.gz -> pgaudit-16.1.tar.gz ++++++ ++++ 4107 lines of diff (skipped) ++++++ pgaudit-1.5.2.tar.gz -> pgaudit-17.1.tar.gz ++++++ ++++ 4932 lines of diff (skipped)