Re: [GENERAL] Limiting DB access by role after initial connection? - Mailing list pgsql-general
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
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
pgsql-general by date: