Implementing PostgreSQL table trigger with GeoServer JDBC user service in Leaflet app

Question

I've been using a table trigger on a PostGIS table to track changes for some time now -- it saves username, date, time, etc in a history table (based on this PostGIS doc).

Now, I'm developing a Leaflet application that allows modification of this same table via a WFT-T service. A JDBC user service has been created to connect to PostgreSQL and use bd credentials. Everything seems to work.

However, my problem now lies in the trigger. Despite authenticating as different users, the history table sees table modification as done by the admin 'postgres' user.

I'm assuming this is due to the way the JDBC user service was set up (admin user postgres user's credentials were entered to connect the driver).

What would be the way to go about this?

I'm getting a bit closer using @IanTurton's suggestion and this forum thread. Custom SQL scripts are now indicated under the data store's "Session startup SQL" and "Session close-up SQL" parameters, but the user name is still not writing to the history table.

I'm now suspecting that I need to update my trigger code:

--CREATE TRIGGER

CREATE or REPLACE FUNCTION "GED".ged_idv_historique_tracker() RETURNS trigger AS

$new_ged_idv_historique_tracker$
                BEGIN
                -- INSERT
                IF (TG_OP = 'INSERT') THEN
                               INSERT INTO "GED".ged_idv_historique
                                               (realise, echelle, origine, affaire, type, date, dossier, fichier, format, path, web_path, format_arc,
                                                               gid_org, created, created_by, modified, state, geom)
                               VALUES
                                               (NEW.realise, NEW.echelle, NEW.origine, NEW.affaire, NEW.type, NEW.date, NEW.dossier, NEW.fichier, NEW.format, NEW.path, NEW.web_path, NEW.format_arc,
                                                               NEW.gid, current_timestamp, current_user, FALSE, 'CREATION', NEW.geom);
                               RETURN NEW;
                -- UPDATE
                ELSEIF (TG_OP = 'UPDATE') THEN
                               UPDATE "GED".ged_idv_historique
                                               SET deleted = current_timestamp, deleted_by = current_user, modified = TRUE, state = 'MODIFICATION ARCHIVEE'
                                               WHERE deleted IS NULL and gid_org = OLD.gid;
                               INSERT INTO "GED".ged_idv_historique
                                               (realise, echelle, origine, affaire, type, date, dossier, fichier, format, path, web_path, format_arc,                                                     gid_org, created, created_by, modified, state, geom)
                               VALUES
                                               (NEW.realise, NEW.echelle, NEW.origine, NEW.affaire, NEW.type, NEW.date, NEW.dossier, NEW.fichier, NEW.format, NEW.path, NEW.web_path, NEW.format_arc,                                                             NEW.gid, current_timestamp, current_user, FALSE, 'MODIFICATION COURANTE', NEW.geom);
                               RETURN NEW;
                -- DELETE
                ELSEIF (TG_OP = 'DELETE') THEN
                               UPDATE "GED".ged_idv_historique
                                               SET deleted = current_timestamp, deleted_by = current_user, state = 'SUPPRESSION'
                                               WHERE deleted is NULL and gid_org = OLD.gid;
                               RETURN NULL;
                END IF;
END;
$new_ged_idv_historique_tracker$ LANGUAGE plpgsql;

--ADD TRIGGER
DROP TRIGGER IF EXISTS trg_ged_idv_historique_tracker ON "GED"."GED_IDV";
CREATE TRIGGER trg_ged_idv_historique_tracker AFTER INSERT OR UPDATE OR DELETE ON
"GED"."GED_IDV"
FOR EACH ROW EXECUTE PROCEDURE "GED".ged_idv_historique_tracker();

I've tried replacing current_user with session_user which, according to docs is updated using SET SESSION AUTHORIZATION by superusers, but the pool connection user is still written to the history table (not the geoserver web app authenticated user).

Log files on geoserver and postgresql levels aren't indicating any particular issues.

Answer 1

One, never connect GeoServer to PostGIS using the postgres user (or any other superuser) as it is very insecure and leads to the possibility of losing all your data to a malicious user. The user that GeoServer connects as should have the fewest privileges possible to carry out the job.

You can achieve what you are looking for by using the session start up/ shutdown script options as described in the manual. For example this alters the current database user to be the same as the GeoServer current user, or geoserver in case no user was authenticated

SET SESSION AUTHORIZATION ${GSUSER,geoserver}