Обсуждение: [GENERAL] Limiting DB access by role after initial connection?
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
--
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
As a follow up to this, a couple more questions from my wishful thinking list:
1) Any way, whether kosher or hacky, to set an arbitrary yet immutable (for the lifetime of the session) variable within a session? Something akin to DEFINE, which wouldn't allow redefinition? A temp table that couldn't be dropped?
2) Same as above, but set by the app before making the connection? I'd settle for this even if it meant two connections per page.
Either of those would facilitate more fine-grained, per-user access, which would ultimately be better and maybe allow me to skip the creation and management of roles. Having discovered that the RLS will not apply to all my existing views and I'll need to rewrite them anyway, this seems like a better thing to wish for!
Cheers,
Ken
--
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On 06/08/2017 10:37 PM, Ken Tanzer wrote: > 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. See set_user for a possible solution: https://github.com/pgaudit/ HTH, Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Вложения
----- Original Message ----- > From: "Ken Tanzer" <ken.tanzer@gmail.com> > To: "PG-General Mailing List" <pgsql-general@postgresql.org> > Sent: Friday, June 9, 2017 1:37:34 AM > Subject: [GENERAL] Limiting DB access by role after initial connection? > > ...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, ... > 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. ... > > > ...I'd also welcome any > thoughts, suggestions or feedback about 1) and 2), or better approaches > entirely. Thanks! > As to your very last point (suggestions about other approaches), is it impossible or impractical to migrate to a scheme inwhich each user actually has a data base role and their own password? Postgresql has really great facility for managingdatabase authorization and access by means of login roles assignable membership in group roles. Why not let the tooldo what it can already do very effectively? -- B
On Fri, Jun 9, 2017 at 6:42 AM, Joe Conway <mail@joeconway.com> wrote:
On 06/08/2017 10:37 PM, Ken Tanzer wrote:
> 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.
See set_user for a possible solution: https://github.com/pgaudit/
Thanks! Looking at the README, it seems like the intended use case is the opposite (escalating privileges), but if I understand could work anyway?
If I'm understanding, you could set_user() with a random token and thereby prevent switching back?
The extra logging would be undesirable. Is there any way to skip that entirely? I see with block_log_statement I could dial down the logging after switching users, but that would require the app to be aware of what the current "normal" logging level was.
Any other pitfalls I'm not seeing, or reasons this might be a bad idea?
Ken
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On 06/09/2017 08:56 AM, Ken Tanzer wrote: > On Fri, Jun 9, 2017 at 6:42 AM, Joe Conway wrote: > See set_user for a possible solution: https://github.com/pgaudit/ > > Thanks! Looking at the README, it seems like the intended use case is > the opposite (escalating privileges), but if I understand could work anyway? It currently supports both use-cases (but not both simultaneously very well). For your use you can do (from the README): --- Block switching to a superuser role set_user.block_superuser = on --- > If I'm understanding, you could set_user() with a random token and > thereby prevent switching back? Exactly -- in order to switch back the same token would be needed. So assuming you are using persistent connections (connection pooler, etc.) you would start a new user session by calling set_user() with a token, and then reset when done with the same token. Or since "done" may not be something the app can really know, you might end up doing a preemptive reset using the token and then then set_user(). > The extra logging would be undesirable. Is there any way to skip that > entirely? I see with block_log_statement I could dial down the logging > after switching users, but that would require the app to be aware of > what the current "normal" logging level was. Also from the README: --- Notes: If set_user.block_log_statement is set to "off", the log_statement setting is left unchanged. --- So assuming you do not normally have statements being logged, this would not change that. > Any other pitfalls I'm not seeing, or reasons this might be a bad idea? As noted in the README, set_user will refuse to run inside a transaction block, but other than that none that I know of. Of course if you come up with any I'd be very interested to hear about them. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Вложения
On Fri, Jun 9, 2017 at 11:22 AM, Joe Conway <mail@joeconway.com> wrote:
Ken
On 06/09/2017 08:56 AM, Ken Tanzer wrote:
> The extra logging would be undesirable. Is there any way to skip that
> entirely? I see with block_log_statement I could dial down the logging
> after switching users, but that would require the app to be aware of
> what the current "normal" logging level was.
Also from the README:
---
Notes:
If set_user.block_log_statement is set to "off", the log_statement
setting is left unchanged.
---
So assuming you do not normally have statements being logged, this would
not change that.
Despite reading that, I was a little uncertain because of it being called block_log_statement. It seems like conceptually it's really log_all_statements, though I suspect you won't want to change the name in midstream.
FWIW, it would be clearer at least to me if you took the two statements in the description:
- log_statement setting is set to "all", meaning every SQL statement executed while in this state will also get logged.
- If set_user.block_log_statement is set to "on", SET log_statement and variations will be blocked. And this one from the notes:
And this one from the notes:
- If set_user.block_log_statement is set to "off", the log_statement setting is left unchanged.
And combined them together:
If set-user.block_log_statement is set to "on", log_statement setting is set to "all", meaning every SQL statement executed while in this state will also get logged. SET log_statement and variations will be blocked. If set to "off," the log statement setting is left unchanged.
> Any other pitfalls I'm not seeing, or reasons this might be a bad idea?
As noted in the README, set_user will refuse to run inside a transaction
block, but other than that none that I know of. Of course if you come up
with any I'd be very interested to hear about them.
If I go this route, get it up and running and find any, I'll be happy to let you know. :)
Thanks a lot for your help!
Ken
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
As to your very last point (suggestions about other approaches), is it impossible or impractical to migrate to a scheme in which each user actually has a data base role and their own password? Postgresql has really great facility for managing database authorization and access by means of login roles assignable membership in group roles. Why not let the tool do what it can already do very effectively?
-- B
Ken
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
learn more about AGENCY or
follow the discussion.
On 06/09/2017 02:16 PM, Ken Tanzer wrote: > FWIW, it would be clearer at least to me if you took the two statements > in the description: > > * log_statement setting is set to "all", meaning every SQL statement > executed while in this state will also get logged. > * If set_user.block_log_statement is set to "on", SET log_statement > and variations will be blocked. And this one from the notes: > > And this one from the notes: > > * If set_user.block_log_statement is set to "off", the log_statement > setting is left unchanged. > > And combined them together: > > If set-user.block_log_statement is set to "on", log_statement setting is > set to "all", meaning every SQL statement executed while in this state > will also get logged. SET log_statement and variations will be > blocked. If set to "off," the log statement setting is left unchanged. Sounds good, will make that change or something similar -- thanks for the feedback. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Вложения
On Thu, Jun 08, 2017 at 22:37:34 -0700, Ken Tanzer <ken.tanzer@gmail.com> wrote: > >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 ( How are you keeping the credentials of the owner from being compromised? It seems if you are worried about role changing, adversaries will likely also be in a position to steal the owner's credentials or hijack the connection before privileges are dropped.
On Fri, Jun 9, 2017 at 5:38 PM, Bruno Wolff III <bruno@wolff.to> wrote:
On Thu, Jun 08, 2017 at 22:37:34 -0700,
Ken Tanzer <ken.tanzer@gmail.com> wrote:
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 (
How are you keeping the credentials of the owner from being compromised? It seems if you are worried about role changing, adversaries will likely also be in a position to steal the owner's credentials or hijack the connection before privileges are dropped.
Seems to me they are separate issues. App currently has access to the password for accessing the DB. (Though I could change that to ident access and skip the password.) App 1) connects to the DB, 2) authenticates the user (within the app), then 3) proceeds to process input, query the DB, produce output. If step 2A becomes irrevocably changing to a site-specific role, then at least I know that everything that happens within 3 can't cross the limitations of per-site access. If someone can steal my password or break into my backend, that's a whole separate problem that already exists both now and in this new scenario.
Cheers,
Ken
--
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
learn more about AGENCY or
follow the discussion.
On Fri, Jun 09, 2017 at 21:14:15 -0700, Ken Tanzer <ken.tanzer@gmail.com> wrote: >On Fri, Jun 9, 2017 at 5:38 PM, Bruno Wolff III <bruno@wolff.to> wrote: > >Seems to me they are separate issues. App currently has access to the >password for accessing the DB. (Though I could change that to ident access >and skip the password.) App 1) connects to the DB, 2) authenticates the >user (within the app), then 3) proceeds to process input, query the DB, >produce output. If step 2A becomes irrevocably changing to a site-specific >role, then at least I know that everything that happens within 3 can't >cross the limitations of per-site access. If someone can steal my password >or break into my backend, that's a whole separate problem that already >exists both now and in this new scenario. In situations where a person has enough access to the app (e.g. it is a binary running on their desktop) to do spurious role changes, they likely have enough acces to hijack the database connection before privileges are dropped.
On Sun, Jun 11, 2017 at 12:15 PM, Bruno Wolff III <bruno@wolff.to> wrote:
On Fri, Jun 09, 2017 at 21:14:15 -0700,
Ken Tanzer <ken.tanzer@gmail.com> wrote:On Fri, Jun 9, 2017 at 5:38 PM, Bruno Wolff III <bruno@wolff.to> wrote:
Seems to me they are separate issues. App currently has access to the
password for accessing the DB. (Though I could change that to ident access
and skip the password.) App 1) connects to the DB, 2) authenticates the
user (within the app), then 3) proceeds to process input, query the DB,
produce output. If step 2A becomes irrevocably changing to a site-specific
role, then at least I know that everything that happens within 3 can't
cross the limitations of per-site access. If someone can steal my password
or break into my backend, that's a whole separate problem that already
exists both now and in this new scenario.
In situations where a person has enough access to the app (e.g. it is a binary running on their desktop) to do spurious role changes, they likely have enough acces to hijack the database connection before privileges are dropped.
Ah yes, I could see that. In this case it's a web app, so only the server has the DB credentials. I'd really hate it if each client had to be able to access those credentials!
Cheers,
Ken
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
--
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.