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.
* 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).
* (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.
* 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: [email protected] <[email protected]>
Gesendet: Donnerstag, 18. Juni 2020 17:13
An: Härtel, Michael <[email protected]>;
[email protected]
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: [email protected]<mailto:[email protected]>
An:
[email protected],[email protected]<mailto:[email protected],[email protected]>
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: [email protected]<mailto:[email protected]>
<[email protected]<mailto:[email protected]>>
Gesendet: Donnerstag, 18. Juni 2020 12:26
An: Härtel, Michael
<[email protected]<mailto:[email protected]>>;
[email protected]<mailto:[email protected]>
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: [email protected]<mailto:[email protected]>
An:
[email protected]<mailto:[email protected]>
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: [email protected]<mailto:[email protected]>
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<http://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
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
