Home PostgreSQL Auditing tables with spatial objects in PostGIS/PostgreSQL

Auditing tables with spatial objects in PostGIS/PostgreSQL

by admin

Auditing tables with spatial objects in PostGIS/PostgreSQL
In the previous article was considered an example with spatial objects and separating access to them by user.
Now let’s look at an example of auditing a given database. We are interested in: who, when and what has done with the table. Which record (read "object") added, which deleted, which changed, so that in the future there will be no different "misunderstandings".
The first thing we do is to create copies of existing tables with spatial objects and give them different names. Not even the table itself, but its structure. For example :

CREATE TABLE audit_building AS SELECT * FROM building1;

Next, add new columns to the audit tables :

ALTER TABLE audit_building ADD COLUMN operation char(1); -- will show what action was performed on the objectALTER TABLE audit_building ADD COLUMN stamp timestamp; -- time of actionALTER TABLE audit_building ADD COLUMN userid text; -- and who did it

After that we create a trigger, which will keep track of all the changes :

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $audit_building$BEGINIF (TG_OP = 'DELETE') THENINSERT INTO audit_building SELECT 'D', now(), user, OLD.*;RETURN OLD;ELSIF (TG_OP = 'UPDATE') THENINSERT INTO audit_building SELECT 'U', now(), user, NEW.*;RETURN NEW;ELSIF (TG_OP = 'INSERT') THENINSERT INTO audit_building SELECT 'I', now(), user, NEW.*;RETURN NEW;END IF;RETURN NULL;END;$audit_building$ LANGUAGE plpgsql;CREATE TRIGGER audit_buildingAFTER INSERT OR UPDATE OR DELETE ON building1 FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();

All that’s left is for users to assign the right to create entries in the audit table :

GRANT SELECT ON audit_building TO user2;

And you can check it out!
This is what I got after some manipulation with the layer :
Auditing tables with spatial objects in PostGIS/PostgreSQL
Here we see that user2 created 3 new objects (I), modified (U) and deleted (D) one object. The columns from the table with the map layer are needed to see exactly which objects were modified by the user. You can use the unique identifier field for this.
That’s it! We have now established control over all changes made to the tables.

You may also like