Re: Limiting DB access by role after initial connection?

On Fri, Jun 9, 2017 at 6:42 AM, Joe Conway <mail(at)joeconway(dot)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

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.