<div dir="ltr">Hi,<div><br></div><div>I'm trying to use RLS (row level security) of PostgreSQL 9.6 to handle data visibility for MapServer (WMS, WFS) using different database roles.</div><div><br></div><div>For example:</div><div>- The database role "private_user" can see the records where the column "private" is marked as "true"</div><div>- The database role "public_user" can't see the records where the column "private " is marked as "true"</div><div><br></div><div>The datasource of those layers calls for both roles always the same queries, for example "SELECT * FROM buildings".</div><div><br></div><div>Via run-time substitution or MapScript I could change the connection string and apply the database role to use for the connection. This solution works but leads to some problems I want to avoid:</div><div>- Each map uses as many db connection as layer and roles exists -> too much open connections</div><div>- The roles must have a password and the permission to login to the database -> leads to security issues, because the password must be added to the query string of the url or stored in plain format somewhere (db, ...)</div><div><br></div><div>So I thought about adding the "SET ROLE role_name" mechanism of PostgreSQL into MapServer and call it after a successful connection to the database. With this solution I have the following advantages:</div><div>- One db role to use for db connections to profit also from the connection pooling (CLOSE_CONNECTION=DEFER)</div><div>- One db role with a password and the permission to login + change the role at runtime.</div><div>- The other roles private_user and public_user are created without password and the permission to login.</div><div><br></div><div>I've forked the MapServer repository and tried this solution on a branch (starting from the latest stable release rel-7-0-6) and it works as expected:</div><div><a href="https://github.com/mapserver/mapserver/compare/branch-7-0...ddegasperi:feature/setrole">https://github.com/mapserver/mapserver/compare/branch-7-0...ddegasperi:feature/setrole</a></div><div><br></div><div>Obviously the hard-coded solution must be replaced; maybe using a PROCESSING parameter: PROCESSING "SET_ROLE=private_user"</div><div><br></div><div>Could this be a possible approach to integrate in MapServer?</div><div><br></div><div>Best regards,</div><div>Daniel<br clear="all"><div><div class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><br></div></div></div></div></div></div></div>
</div></div>
<br>
<font color="#888888" face="DejaVu
Sans,Verdana,Arial,Helvetica,sans-serif" size="1"><strong>ATTENZIONE!</strong> Le informazioni contenute
nella presente e-mail e nei documenti eventualmente
allegati sono confidenziali. La loro diffusione,
distribuzione e/o riproduzione da parte di terzi,
senza autorizzazione del mittente è vietata e può
violare il D. Lgs. 196/2003. In caso di ricezione per
errore, Vogliate immediatamente informare il mittente
del messaggio e distruggere la e-mail.<br>
<br>
<strong>ACHTUNG!</strong> Die in dieser Nachricht oder
in den beigelegten Dokumenten beinhalteten
Informationen sind streng vertraulich. Ihre
Verbreitung und/oder ihre Wiedergabe durch Dritte ist
ohne Erlaubnis des Absenders verboten und verstößt
gegen das Legislativdekret 196/2003. Sollten Sie diese
Mitteilung irrtümlicherweise erhalten haben, bitten
wir Sie uns umgehend zu informieren und anschließend
die Mitteilung zu vernichten.<br>
<br>
<strong>WARNING!</strong> This e-mail may contain
confidential and/or privileged information. If you are
not the intended recipient (or have received this
e-mail in error) please notify the sender immediately
and destroy this e-mail. Any unauthorised copying,
disclousure or distribution of the material in this
e-mail is strictly forbidden and could be against the
law (D. Lgs. 196/2003)<br></font>