[Qgis-user] Retrieve Postgis user from connection string

Wed Mar 22 04:27:13 PDT 2017

Maybe you can test that in the trigger. Something like:

IF current_user != 'lizmap_editor' THEN
	NEW.GivenUser = current_user;
END IF;

Karl-Magnus Jönsson 

-----Ursprungligt meddelande-----
Från: Rossin Pietro [mailto:pietro.rossin at arpa.fvg.it] 
Skickat: den 22 mars 2017 11:34
Till: Karl Magnus Jönsson; qgis-user at lists.osgeo.org
Ämne: R: Retrieve Postgis user from connection string

Thanks Karl
Your solution is fine if I edit my postgis layer only with Qgis desktop.

My problem is that I have two different client that can modify this layer.

This layer is served in a webgis editing environment using Lizmap Web Client (LWC) and at the same time could be modified by Qgis Desktop.

With Qgis desktop there is no problem using your trigger, because each layer is loaded with a given user so NEW.GivenUser = current_user; Is fine

In LWC there is a big problem
When you create a Qgis Project to be served with LWC  you use a defined postgresql user, let's say "lizmap_editor"
This user is used by qgis-server that is on the back of Lizmap Web Client In LWC environment there are users and groups that can be (or not) those present in Postgresql.
If I use your trigger when I  commit an edit session in LWC "lizmap_editor" will be written in NEW.GivenUser..
I don't want this to happen.
I want the user logged in LWC client to be inserted in NEW.GivenUser.

So we found a way to catch the LWC user logged in and insert it into the "GivenUser" field in using javascript on the web client.
This user, as I said, is different from the one used to load the postgis layer (lizmap_editor)

So during commit I must distinguish if the field GivenUser has a value coming from LWC.

And at the same time if I edit this layer using Qgis Desktop I want the Postgresql current user to be recorded..

Sorry for my bad English explanation... (bad explanation in general indeed ;) )

Any other solution?

Bye
Pietro

-----Messaggio originale-----
Da: Karl Magnus Jönsson [mailto:Karl-Magnus.Jonsson at kristianstad.se]
Inviato: mercoledì 22 marzo 2017 08:27
A: Rossin Pietro <pietro.rossin at arpa.fvg.it>; qgis-user at lists.osgeo.org
Oggetto: SV: Retrieve Postgis user from connection string

Hi Pietro!
Wouldn't a trigger do the job on the database side anyway? I have a trigger:

CREATE TRIGGER update_skotselobjekt_curuser
  BEFORE UPDATE
  ON park.skotselobjekt
  FOR EACH ROW
  EXECUTE PROCEDURE park.update_curuser_column();

That executes this trigger function:

CREATE OR REPLACE FUNCTION park.update_curuser_column()
  RETURNS trigger AS
$BODY$
BEGIN
   --IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
      NEW.anvandare = current_user;
      RETURN NEW;
   --ELSE
   --   RETURN OLD;
  -- END IF;
END;
$BODY$

Works fine for me. I don't use the check "if the new row is distinct from the old" since small changes in geometry didn't get caught as a different record.

Karl-Magnus Jönsson

-----Ursprungligt meddelande-----
Från: Qgis-user [mailto:qgis-user-bounces at lists.osgeo.org] För Rossin Pietro
Skickat: den 21 mars 2017 17:16
Till: qgis-user at lists.osgeo.org
Ämne: [Qgis-user] Retrieve Postgis user from connection string

Hello
In qgis 2.18 is there some way to automatically get the user name used to load a postgis layer?
I need it to populate a table field qgis side, not postgis side (I know I can do the latest using a trigger).

On Postgis I use

IF NEW.user_inser is NULL or NEW.user_inser = '' then NEW.user_inser = (select current_user); END IF; IF NEW.user_modif is NULL or NEW.user_modif = '' then NEW.user_modif = (select current_user); END IF; NEW.data_inser = current_timestamp; NEW.data_modif = current_timestamp;

To trap a user who is inserting a new feature and set both insertion and modification user/data

Then If I want to get a user/data that updates a record, how can I do?
I mean as a user insert a feature or that feature is updated then next time I can't use

IF NEW.user_modif is NULL or NEW.user_modif = '' then NEW.user_modif = (select current_user);

And as I load this layer in legend Qgis doesn't change user_modif field.
So I'm searching a way to let Qgis change authomatically user_modif field on edit session

I tried to load a query like

select row_number() over(), current_user::text as user

To get the user of a given connection and it performs well but then How can I insert user value in user_modif field?

Any other better way??

Thanks
Pietro Rossin

AVVISO DI RISERVATEZZA Informazioni riservate possono essere contenute nel messaggio o nei suoi allegati. Se non siete i destinatari indicati nel messaggio, o responsabili per la sua consegna alla persona, o se avete ricevuto il messaggio per errore, siete pregati di non trascriverlo, copiarlo o inviarlo ad alcuno. In tal caso vi invitiamo a cancellare il messaggio ed i suoi allegati. Grazie.
CONFIDENTIALITY NOTICE Confidential information may be contained in this message or in its attachments. If you are not the addressee indicated in this message, or responsible for message delivering to that person, or if you have received this message in error, you may not transcribe, copy or deliver this message to anyone. In that case, you should delete this message and its attachments. Thank you.
_______________________________________________
Qgis-user mailing list
Qgis-user at lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

AVVISO DI RISERVATEZZA Informazioni riservate possono essere contenute nel messaggio o nei suoi allegati. Se non siete i destinatari indicati nel messaggio, o responsabili per la sua consegna alla persona, o se avete ricevuto il messaggio per errore, siete pregati di non trascriverlo, copiarlo o inviarlo ad alcuno. In tal caso vi invitiamo a cancellare il messaggio ed i suoi allegati. Grazie.
CONFIDENTIALITY NOTICE Confidential information may be contained in this message or in its attachments. If you are not the addressee indicated in this message, or responsible for message delivering to that person, or if you have received this message in error, you may not transcribe, copy or deliver this message to anyone. In that case, you should delete this message and its attachments. Thank you.