Audit tables with features in PostGIS / PostgreSQL

In the previous article , an example was considered with spatial objects and the separation of access to them by users.
Now consider an example of auditing this database. We are interested in: who, when and what did with the table. What record (read “object”) added which one deleted, which one changed, so that there would be no various “misunderstandings” in the future.

The first thing we will do is create copies of already existing tables with spatial objects and name them with other 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 tables for auditing:

 ALTER TABLE audit_building ADD COLUMN operation char(1); --  ,      ALTER TABLE audit_building ADD COLUMN stamp timestamp; --    ALTER TABLE audit_building ADD COLUMN userid text; --     

After that, we create a trigger that will track all changes:

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

It remains only for users to assign the right to create records in the audit table:

 GRANT SELECT ON audit_building TO user2; 

And you can check!
Here is what happened after some layer manipulations:

Here we see that user2 created 3 new objects (I), changed (U) and deleted (D) one object. The columns from the table with the map layer are needed in order to see exactly which objects of the rules the user has. To do this, you can use the unique identifier field.
That's all! Now we have established control over all changes made in the tables.