Audit tables with spatial objects in PostGIS/PostgreSQL

In the previous article was considered an example of spatial objects and sharing access to them by users.
Now consider an example of an audit of the database. We are interested in: who, when and what was done to the table. Which record (read "object") added, some removed, some changed, in order to avoid various "misunderstandings."

The first thing that we do, we will create copies of existing tables with spatial objects and call them by other names. Not even the table itself and its structure. For example:
the

 
Audit_building CREATE TABLE AS SELECT * FROM building1; 

Then add in the tables for auditing new column:
the

 
Audit_building ALTER TABLE ADD COLUMN operation char(1); -- would show what action was performed on the object 
Audit_building ALTER TABLE ADD COLUMN stamp timestamp; -- time to commit the act 
Audit_building ALTER TABLE ADD COLUMN userid text; -- and who was doing that 

Then create a trigger which will track all changes:
the

 
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 to assign users the right to create records in the audit table:
the

 
GRANT SELECT ON audit_building TO user2; 

And you can check it out!
Here's what happened after some manipulation layer:

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

Article based on information from habrahabr.ru