Hi, thanks for sharing! :-D Cheers Andrea
On Tue, Jun 23, 2020 at 10:07 AM <michael-haer...@telekom.de> wrote: > Thanks to Stefan Overkamp who obviously read the article about Custom SQL > session start/stop scripts > <https://docs.geoserver.org/stable/en/user/data/database/sqlsession.html> > I found the solution. > > > > The solution comprises four components/steps (I work with > PostgreSQL/PostGIS): > > > > 1. In datastore configuration define a custom SQL SESSION START script > like “SELECT set_config('geoserver_user', '${GSUSER,geoserver}', FALSE)”. > As STOP script usesomething like “SELECT set_config(' geoserver_user > ', '', FALSE)” > à This is the most important part an ensures that on DB side you can > retrieve the geoserver user who starts the WFS-T transaction. > 2. On DB side you now need to retrieve the user. This can be done in a > trigger function that is fired on TRIGGER BEFORE UPDATE OR INSERT OR > DELETE <https://www.postgresql.org/docs/10/sql-createtrigger.html>. In > the function you can retrieve the user with “DECLARE geoserveruser > VARCHAR(64) := current_setting('geoserver_user', TRUE);” > 3. The tricky part for me still is to get the roles for the user and > check the permissions. > 1. If you have an SQL based authentication provider you are lucky > and can check the permissions in the DB (There apparently is no env > variable yet in geoserver to pass the ROLE instead the user). > 2. (The other solution could be not to use the user directly but > instead use two datastores. Pass a “allow_deletion=true” to the DB for > one > datastore and allow_deletion=false in the other datastore. Then publish > the > same table in both datastores, for example as “my_layer_rwd” and > “my_layer_rw”. Then the users with delete permission can use the first > layer and the other have to use the second layer. Data security > sessions/GeoFence could be used to restrict access to the services to > certain security roles. > 3. By looking at the documentation I guess it would also be > possible to access the LDAP via a DATALINK > <https://wiki.postgresql.org/wiki/DATALINK> and retrieve the > roles/permissions from the LDAP from within the trigger function > 4. In the Trigger function return NULL and/or “RAISE EXCEPTION > 'deletion not allowed for this user’;” if no deletion is allowed. > (Didn’t test this part yet) > > > > Nice about this solution in general is that you can also use the geoserver > user to update the target table and set a value for a “modified by” column > for example. > > > > Hope this also helps others. > > > > Regards, > > > > Michael > > > > > > *Von:* overk...@posteo.de <overk...@posteo.de> > *Gesendet:* Donnerstag, 18. Juni 2020 17:13 > *An:* Härtel, Michael <michael-haer...@telekom.de>; > Geoserver-users@lists.sourceforge.net > *Betreff:* AW:AW:[Geoserver-users] Restricting WFS-T to certain > Transactions > > > > Beispiel: > Als startup und close-up folgendes eintragen: > > SELECT set_config('myapp.user', '${GSUSER,geoserver}', FALSE) > SELECT set_config('myapp.user', '', FALSE) > > Testweiser UPDATE-Trigger: > > CREATE OR REPLACE FUNCTION geodaten.update_lastchangeby_func() > RETURNS trigger AS > $BODY$ > BEGIN > NEW.lastchangeby = current_setting('myapp.user', TRUE); > RETURN NEW; > END;$BODY$ > LANGUAGE plpgsql VOLATILE > COST 100; > ALTER FUNCTION geodaten.update_lastchangeby_func() OWNER TO geoadmin; > > CREATE TRIGGER test_trigger > BEFORE UPDATE ON geodaten.gemeinde > FOR EACH ROW EXECUTE PROCEDURE geodaten.update_lastchangeby_func(); > > > > > > Von meinem Huawei-Mobiltelefon gesendet > > > > -------- Originalnachricht -------- > Betreff: AW: AW:[Geoserver-users] Restricting WFS-T to certain Transactions > Von: michael-haer...@telekom.de > An: overk...@posteo.de,Geoserver-users@lists.sourceforge.net > Cc: > > > Hello Stefan, > > > > I already thought about triggers but then dismissed this approach because > I don’t see a way to pass the geoserver user/role (user who starts the WFS > transaction) to the database. > > As I understand it the geoserver uses the jdbc connection that is used to > define the datastore and that user then executes the SQL statements. I > wouldn’t know how to let the DB know who triggered the XML request to the > WFS-T service which would be necessary to map geoserver users to DB roles > or set a parameter in the DB. > > > > Regards, > > > > Michael > > *Von:* overk...@posteo.de <overk...@posteo.de> > *Gesendet:* Donnerstag, 18. Juni 2020 12:26 > *An:* Härtel, Michael <michael-haer...@telekom.de>; > Geoserver-users@lists.sourceforge.net > *Betreff:* AW:[Geoserver-users] Restricting WFS-T to certain Transactions > > > > Hi, > > if you have a postgis datastore as datasource, you could add an update > trigger. Geoserver allows setting a session startup sql on the datasource > to set a variable. Then the trigger function could use this to prevent the > delete action for some users. > > Beste Grüße > > Stefan > > > > > Von meinem Huawei-Mobiltelefon gesendet > > > > -------- Originalnachricht -------- > Betreff: [Geoserver-users] Restricting WFS-T to certain Transactions > Von: michael-haer...@telekom.de > An: Geoserver-users@lists.sourceforge.net > Cc: > > Dear List, > > > > we currently offer some layers to our users via WFS-T which works quite > well but now they requested that only some users are allowed to delete > features of a layer and some other may only read and write and others may > only read. > > > > While I can easily offer solutions for keeping read and write/delete > operations separate I have no idea how to separate write and delete if at > the same time I need to stick to WFS-T. > > > > I consulted the geofence documentation and found write CQL rules and > indeed found fine-grained access controls but I didn’t find any solution to > restrict access to certain WFS-T operations which are only distinguishable > by parsing the contents of the XML body in the WFS-T request. > > > > Did I miss something? Can somebody tell me if there is a solution in > geofence or does anybody have a different solution based on WFS-T? > > > > (I searched the mailing list archive for a solution first but my search > for “WFS-T” “write” and “delete” gave me more than 65.000 hits which I > could not read all). > > > > Thank you very much for your help and ideas, > > > > Michael Härtel > > > > *Deutsche Telekom IT GmbH* > > Technology Solutions > > Michael Härtel > GIS-mobile > > Oberkasseler Strasse 2, 53227 Bonn, Germany > +49 228 18149623 (Phone) > > E-Mail: *michael.haer...@t-systems.com <michael.haer...@t-systems.com>* > > Internet: *www.telekom.com <http://www.telekom.com/>* > > > > *Life is for sharing.* > > > > You can find the obligatory information on > www.telekom.com/compulsory-statement-dtit > > *Big changes start small – conserve resources by not printing every > e-mail.* > > > > Notice: This transmittal and/or attachments may be privileged or > confidential. It is intended solely for the addressee named above. Any > dissemination, or copying is strictly prohibited. If you received this > transmittal in error, please notify us immediately by reply and immediately > delete this message and all its attachments. Thank you. > > > > _______________________________________________ > Geoserver-users mailing list > > Please make sure you read the following two resources before posting to > this list: > - Earning your support instead of buying it, but Ian Turton: > http://www.ianturton.com/talks/foss4g.html#/ > - The GeoServer user list posting guidelines: > http://geoserver.org/comm/userlist-guidelines.html > > If you want to request a feature or an improvement, also see this: > https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer > > > Geoserver-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/geoserver-users > -- Regards, Andrea Aime == GeoServer Professional Services from the experts! Visit http://goo.gl/it488V for more information. == Ing. Andrea Aime @geowolf Technical Lead GeoSolutions S.A.S. Via di Montramito 3/A 55054 Massarosa (LU) phone: +39 0584 962313 fax: +39 0584 1660272 mob: +39 339 8844549 http://www.geo-solutions.it http://twitter.com/geosolutions_it ------------------------------------------------------- *Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia. This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail.*
_______________________________________________ Geoserver-users mailing list Please make sure you read the following two resources before posting to this list: - Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/ - The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users