Hi. As context, I'm working with an organization with a current production database. Organizations in other locations using the same service delivery model want to share this database, with some but not all of the data restricted so that people at each site can see only that site's data. I've been looking at doing this by creating a role for each location, and then using RLS to restrict access to some tables. Currently the database has one user, the owner, and access is controlled within the application by usernames and passwords within the DB.
My approach was to have the initial connection made by the owner, and then after successfully authenticating the user, to switch to the role of the site they belong to. After investigation, this still seems feasible but imperfect. Specifically, I thought it would be possible to configure such that after changing to a more restricted role, it would not be possible to change back. But after seeing this thread (http://www.postgresql-archive.org/Irreversible-SET-ROLE-td5828828.html), I'm gathering that this is not the case.
I can still go this route, either by:
1) Setting the role as described above, and then trying to be damn sure that a subsequent query doing a role change never ever slips through the app. :)
2) After authentication, close the DB connection and reconnect as the site role. This seems tighter from a security standpoint, but at the cost of doubling my # of DB connections, and then also needing the app to manage passwords for each site.
So before doing either of those, I wanted to confirm if there is/isn't a way to configure and change roles in a way that reduces privileges, and cannot be undone. Either with changing roles, or as some kind of "RECONNECT TO db_name AS user" command that wold allow a DB owner to connect without supplying credentials.
Those might both be wishful thinking. If so, I'd also welcome any thoughts, suggestions or feedback about 1) and 2), or better approaches entirely. Thanks!
Ken
--
![]()
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
