2ndquadrant / audit-trigger Goto Github PK

The script was run and is operating normally.

I want to make a query to return the structure:

field | previous value | current value

When the action is equal to "Insert", to show the fields content only in the "current value" column.

Example:

field | previous value | current value

id | (Null) | 1
name | (Null) | Rick

When the action is equal to "Update."

Example:

field | previous value | current value

id | 1 | 1
name | (Null) | Richard

And finally "Delete":

field | previous value | current value

1 | 1 | (Null)

How do I use to audit tables in different schemas ?
Our database has 4 different schemes.

Ex: scfinadmhppq.client

Creating index on TG_RELID makes it small and neat to use but if I take a logical backup (pg_dump) of the DB and restore it on another server, the oid for schema.table changes thus making this unusable. unless I am missing something?

Related question on DBA StackExchange:
https://dba.stackexchange.com/questions/180490/postgresql-do-regclass-oid-columns-persist-across-backups

Any ideas?

Thanks in advance!

Our schema has a "user" table, user is also a reserved word in Postgres.
When trying to enable auditing on this table, it throws an error.

I've tried every vairation of ', and " I can think of, including prefixing the schema.

Any ideas?

SELECT audit.audit_table('user');

NOTICE:  relation ""user"" does not exist, skipping
CONTEXT:  SQL statement "DROP TRIGGER IF EXISTS audit_trigger_row ON """user""""
PL/pgSQL function audit.audit_table(regclass,boolean,boolean,text[]) line 7 at EXECUTE statement
NOTICE:  relation ""user"" does not exist, skipping
CONTEXT:  SQL statement "DROP TRIGGER IF EXISTS audit_trigger_stm ON """user""""
PL/pgSQL function audit.audit_table(regclass,boolean,boolean,text[]) line 8 at EXECUTE statement
NOTICE:  CREATE TRIGGER audit_trigger_row AFTER INSERT OR UPDATE OR DELETE ON """user""" FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func('true');
ERROR:  relation ""user"" does not exist
CONTEXT:  SQL statement "CREATE TRIGGER audit_trigger_row AFTER INSERT OR UPDATE    OR DELETE ON """user""" FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func('true');"
PL/pgSQL function audit.audit_table(regclass,boolean,boolean,text[]) line 19 at EXECUTE statement
********** Error **********

ERROR: relation ""user"" does not exist
SQL state: 42P01
Context: SQL statement "CREATE TRIGGER audit_trigger_row AFTER INSERT OR UPDATE OR DELETE ON """user""" FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func('true');"
PL/pgSQL function audit.audit_table(regclass,boolean,boolean,text[]) line 19 at EXECUTE statement

Hi,
I am wondering if you would be open to saving the primary key (if it exists) of row-level changes.
I think it is quite common to look in the logged_actions for a certain row in particular.
That would make the search quite efficient, though not 100% future proof (in case of pkey changes).

What do you think?

change 3 occurrences of
CREATE INDEX
to
CREATE INDEX IF NOT EXISTS
so audit.sql can be rerun without error.
(Amended file attached if it is acceptable)
audit.zip

I created logged_actions using the sql in this repository but nothing happens when I make insert and update to the database with both postgres user and another user that is not super user. Is there anything I am missing?

Statement only trigger not consistent with ORMs !

I would like to highlight that using statement only mode does not produces whole SQL when using with
ORMs.

The statement only contains SQL with prepared statement params, The actual value of the placeholder is not getting logged.

I want to possibly use and customize this code to build auditing extension for Ruby/Rails/Heroku. I would like to know what the license of this code is, and who are the authors so I can credit them appropriately. Any ideas?

Now that I am using this particular sql code for audit-trigger, I would like to see which particular user is making changes in the table along with the the following attributes:

1. Date of change
2. Timestamp
3. In Table(table name)
4. Changed Field(changed attribute)
5. From value(old value)
6. To value(new value)
   Please provide the necessary help. Thanks in advance.

Hello all,

Just want to log this issue that I faced when trying to implement this advanced audit trigger for my application.

TL;DR summary: The trigger results in out of memory errors in pg, due to request sizes in the 100s of mb.

As is common these days, I work on my dev machine before I move forward to other environments. As such compared to prod my machine is under resourced, however it's certainly enough to function well for a single client while developing.

work_mem
19MB

shared_buffers
512MB

temp_buffers
8MB

effective_cache_size
4GB

But the pg log shows a request of over 200mb when the trigger function attempts to run. Causing the memory issue.

2016-11-24 15:02:27 GMT	ERROR	out of memory	
2016-11-24 15:02:27 GMT	DETAIL	Failed on request of size 203437246.	
2016-11-24 15:02:27 GMT	CONTEXT	PL/pgSQL function if_modified_func() line 51 at assignment	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
			SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)"	
			PL/pgSQL function if_modified_func() line 58 at SQL statement	
2016-11-24 15:02:27 GMT	STATEMENT	UPDATE company SET name=('test'),full_name=('TBU4'),active_flag=(true) WHERE id=(111)	

Even with my under resourced machine, I cannot believe that a request of 200mb+ is normal for an update of a single row of a table. Also, the multiple log lines SQL statement "INSERT INTO app.audit_actions VALUES (audit_row.*)" are also suspicious to me, but when I enabled statement logging I only saw 1 actual insert attempted.

I could not find a resolution to my issue after all day of looking on dba stackexchange and going through the pg mailing list archives. As many people seem to have implemented this correctly, I think I must have something configured incorrectly somewhere. But unfortunately, until I find a solution, I will go back to a more basic audit.

Hello 👋 😃

I'm curious why the comments on the transaction_id field indicates that it

"May wrap, but unique paired with action_tstamp_tx".

The PostgreSQL docs (going back even to 9.1) say of txid_current (and related functions):

The internal transaction ID type (xid) is 32 bits wide and wraps around every 4 billion transactions. However, these functions export a 64-bit format that is extended with an “epoch” counter so it will not wrap around during the life of an installation. The data type used by these functions, txid_snapshot, stores information about transaction ID visibility at a particular moment in time.

Also would it be worth adding a helpful note to users that they may want to modify / remove the use of clock_timestamp if they don't need it as it could add significant performance overhead to bulk inserts? Curious what the primary use case is here? I could see this perhaps being useful if reading uncommitted data?

Thanks!

In audit.logged_actions, I find that I have client_query values like:

update some_table set some_column = $1 where where_column = $2

row_data and changed_fields both show me what's in $1, but I don't have $2 saved, which is making it nearly impossible to make use of my audit data.

Not sure that this is a bug, but I can't figure out how to get around it. Any advice would be most appreciated.

Per the comment block this "Should really be converted into a relocatable EXTENSION, with control and upgrade files."

I repost the same issue from the previous repo:
The quote_ident calls are useless and prevent the use of the function with schema defined regclass (i.e. schema.table).
So, to make the trigger available on other schemas than public, I had to remove the quote_ident.

your asnwer was:
It looks like you're talking about audit.sql. If so, the quote_ident calls are moderately useful (since table names can include SQL metachars) but it should instead really use format() and the %I specifier, since that's properly schema-aware. If I get the chance I'll patch this; otherwise it's pretty easy for you to convert by replacing string concatenation with format using %L and %I.

Do you have by any chance time to fix this?

Thanks a lot!

This may have to do with the specific configuration I'm using, but I had the following issue:

ERROR:  function quote_ident(regclass) does not exist
LINE 1: ...'DROP TRIGGER IF EXISTS audit_trigger_row ON ' || quote_iden...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT 'DROP TRIGGER IF EXISTS audit_trigger_row ON ' || quote_ident(target_table)
CONTEXT:  PL/pgSQL function audit.audit_table(regclass,boolean,boolean,text[]) line 7 at EXECUTE statement

The good news is that this was easy to resolve by simply appending ::TEXT to target_table in the places where it's used as an argument to quote_ident().

Platform: PostgreSQL 9.2.13 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit

It may be that more recent versions have smarter type-coercion or have an overloaded quote_ident() function to handle this, but I'd assume audit-trigger aims to work for all Postgres versions which are still officially under support, which includes 9.2.x until Sep. 2017. If support for different versions doesn't match official support, we should have a note about it in readme.md

Postgresql vers.: 9.4
Table: vault_scanning.rolls

The command : SELECT audit.audit_table('rolls');
completed with no errors. However, when I insert a new row, with pgAdmin III, into the 'rolls' table I get the following error :-

An error has occurred:
ERROR: function hstore(vault_scanning.rolls) does not exist
LINE 1: SELECT hstore(NEW.) - excluded_cols
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT hstore(NEW.) - excluded_cols

CONTEXT: PL/pgSQL function audit.if_modified_func() line 49 at assignment

OK

Hi,

Did you consider to replace (SELECT setting FROM pg_settings WHERE name = 'application_name') with current_setting('application_name').

It changes performance of the trigger for me quite a lot.

We implemented an auditing system per the code provided here. One issue we've had, though, is that client-level info (client_query, client_addr, client_port, action_tstamp_tx, etc.) is repeated for each row that is updated based on a given transaction. Under certain usage patterns, this wouldn't matter much, but in our case it has caused our audit.logged_actions table to be extremely bloated. As an example of the kind of transaction that has caused serious bloat, a backend user may update a bunch of rows using something like the following:

UPDATE foo SET bar1='updated bar 1', bar2 = ... WHERE bar_id = 1;
UPDATE foo SET bar1='updated bar 2', bar2 = ... WHERE bar_id = 2;
...
UPDATE foo SET bar1='updated bar 100000', bar2 =... WHERE bar_id = 100000;

Run as a single transaction, this leads to 100,000 rows in the logged_actions table, with the same values (i.e., all lines as indicated above) for client_query, plus repeated client_addr, client_port, transaction_id, action_tstamp_tx, etc.), in every logged_actions row. That redundancy doesn't matter much for most of those columns, but the client_query column repeats the 100,000 line-long statement for every row, in the example above. When the data being updated are significant from a size perspective (e.g., each row contains many columns with varchar types), that adds up to lots of diskspace being used with redundant client_query info.

By way of explanation, we aggregate data from other live databases to which we don't have direct access or connection. Sometimes those databases modify records on their side, so we periodically (e.g., annually) update our records to reflect those changes, using a unique ID to compare and update data. Our older workflows involved users constructing update statements using Excel or some other means outside the database, then running these via pgAdmin.

I am trying to move backend users away from doing things in that manner, so that each update or delete statement is handled as a separate transaction, to avoid that kind of duplication in the client_query column, but we're stuck dealing with the existing records in a logged_actions table that is around 56 GB (for sake of comparison, that is almost half the total size of our database). Even with more normal/sane usage patterns, it seems like there's significant DRY violation happening here. In a case where updates are done by referencing a temporary table after an import from CSV, for example, the client_query (as in the example shown below) still gets unnecessarily duplicated for each row that's updated as a result, e.g.,

UPDATE foo
    SET 
        foo.col_1 = update_table.col_1 
        foo.col_2 = update_table.col_2 
        foo.col_3 = update_table.col_3
        ...
        foo.col_n = update_table.col_n
    FROM update_table
WHERE update_table.id = foo.id;

I'm wondering whether you've considered refactoring this so that the client-level info (client_query, etc.) is written to one table and the actual information being audited (schema_name, table_name, action, row_data, changed_field, etc.) is written to another, with the latter just referencing via FK a row in the former? It seems that the trigger function could be modified so that it would write client-level information to that table and return a unique primary key (if it did not already exist there -- otherwise it'd return the primary key) and then use that primary key as the FK in an "event" level table that tracks the actual changes.

In CREATE TABLE audit.logged_actions, the column client_query is declared as not null. This is in conflict with:

IF NOT TG_ARGV[0]::boolean IS DISTINCT FROM 'f'::boolean THEN
    audit_row.client_query = NULL;

In fact, I ran into a problem where the trigger was trying to insert with a null value in client_query, and it failed. I resolved it by changing this column to accept null values.

I also think that most (if not all) columns in logged_actions must accept null values. This will be essential to prevent applications from crashing because the audit trigger failed due to a null insert in a not null column.

Hi,

I think it would be really useful to include a simple view that shows the tables for which auditing is enabled, as otherwise the only way of knowing is by looking at the triggers/constraints on each table. I've created this in our fork of the project at https://github.com/AstunTechnology/audit-trigger and opened pull request #33 to have this included in the origin fork.

Thanks

Jo (Astun Technology)

On you documentation page you say:

Have some tables named with mixed-case (was there before I inherited).
I saw the other ticket about tables in all uppercase, but looks like mixed case may mix it up also

$ SELECT audit.audit_table('Account');
ERROR: 42P01: relation "account" does not exist
LINE 1: SELECT audit.audit_table('Account');
^
LOCATION: RangeVarGetRelidExtended, namespace.c:420

• Given I am using PostgreSQL 9.4
• And I have a table in public schema

CREATE TABLE "CHECK_ROW_LEVEL_LOCKS"
(
  id serial NOT NULL,
  msg text,
  CONSTRAINT "CHECK_ROW_LEVEL_LOCKS_pkey" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "CHECK_ROW_LEVEL_LOCKS"
  OWNER TO postgres;

• And when I try to add audit to it

SELECT audit.audit_table('CHECK_ROW_LEVEL_LOCKS');

• Then I receive an error message

ERROR: relationship "check_row_level_locks" does not exist

If the table was created in lower case check_row_level_locks, then it would all be fine.

If a table you wish to audit has an hstore column the changes recorded in changed_fields is not tracking properly.

consider a table with hstore column hprops with these initial values

-- add some data
INSERT INTO audit_test VALUES (default, 'apples', 'red', true, 123456789, 2.99, inet_client_addr(), hstore('"x"=>"30", "y"=>"c"'), json_object('{a, 1, b, "def", c, 3.5}'), jsonb_object('{zz, 2, xx, "geh", yy, 99.5}'));

Consider following 3 scenarios

-- case 1) update hstore column (works!)
-- "changed_fields":   ""hprops"=>"\"x\"=>\"30\", \"y\"=>\"fred\"""    -- Works if entire column changed 
UPDATE audit_test SET hprops = hstore('"x"=>"30", "y"=>"fred"')  WHERE id = 1   

-- case 2) add a key or update existing key in hstore column (does not work)
-- "changed_fields":   ""hprops"=>"\"x\"=>\"30\", \"y\"=>\"bob\"""
UPDATE audit_test SET hprops = hprops || hstore('"y"=>"bob"')  WHERE id = 1     

-- case 3) delete a key in hstore  (does not work)
-- "changed_fields":   ""hprops"=>"\"x\"=>\"30\"""
UPDATE audit_test SET hprops = delete(hprops, 'y')  WHERE id = 1  

• In scenario 2, changed_fields should not contain 'x' key as it has not changed
• In scenario 3, changed key should be 'y'