[mapserver-dev] Switch db role after connecting to db for row level security

[Daniel Degasperi]

Hi,

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.

For example:
- The database role "private_user" can see the records where the column
"private" is marked as "true"
- The database role "public_user" can't see the records where the column
"private " is marked as "true"

The datasource of those layers calls for both roles always the same
queries, for example "SELECT * FROM buildings".

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:
- Each map uses as many db connection as layer and roles exists -> too much
open connections
- 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, ...)

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:
- One db role to use for db connections to profit also from the connection
pooling (CLOSE_CONNECTION=DEFER)
- One db role with a password and the permission to login + change the role
at runtime.
- The other roles private_user and public_user are created without password
and the permission to login.

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:
https://github.com/mapserver/mapserver/compare/branch-7-0...ddegasperi:feature/setrole

Obviously the hard-coded solution must be replaced; maybe using a
PROCESSING parameter: PROCESSING "SET_ROLE=private_user"

Could this be a possible approach to integrate in MapServer?

Best regards,
Daniel

-- 
*ATTENZIONE!* 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.

*ACHTUNG!* 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.

*WARNING!* 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)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-dev/attachments/20170808/b6ed422d/attachment.html>