Re: [GENERAL] Limiting DB access by role after initial connection?

2017-06-11 Thread Ken Tanzer
On Sun, Jun 11, 2017 at 12:15 PM, Bruno Wolff III wrote: > On Fri, Jun 09, 2017 at 21:14:15 -0700, > Ken Tanzer wrote: > >> On Fri, Jun 9, 2017 at 5:38 PM, Bruno Wolff III wrote: >> >> Seems to me they are separate issues. App currently

Re: [GENERAL] Limiting DB access by role after initial connection?

2017-06-11 Thread Bruno Wolff III
On Fri, Jun 09, 2017 at 21:14:15 -0700, Ken Tanzer wrote: On Fri, Jun 9, 2017 at 5:38 PM, Bruno Wolff III 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

Re: [GENERAL] Limiting DB access by role after initial connection?

2017-06-09 Thread Ken Tanzer
On Fri, Jun 9, 2017 at 5:38 PM, Bruno Wolff III wrote: > On Thu, Jun 08, 2017 at 22:37:34 -0700, > Ken Tanzer wrote: > >> >> My approach was to have the initial connection made by the owner, and then >> after successfully authenticating the user, to switch

Re: [GENERAL] Limiting DB access by role after initial connection?

2017-06-09 Thread Bruno Wolff III
On Thu, Jun 08, 2017 at 22:37:34 -0700, 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

Re: [GENERAL] Limiting DB access by role after initial connection?

2017-06-09 Thread Joe Conway
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

Re: [GENERAL] Limiting DB access by role after initial connection?

2017-06-09 Thread Ken Tanzer
> > 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

Re: [GENERAL] Limiting DB access by role after initial connection?

2017-06-09 Thread Ken Tanzer
On Fri, Jun 9, 2017 at 11:22 AM, Joe Conway wrote: > 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

Re: [GENERAL] Limiting DB access by role after initial connection?

2017-06-09 Thread Joe Conway
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

Re: [GENERAL] Limiting DB access by role after initial connection?

2017-06-09 Thread Ken Tanzer
On Fri, Jun 9, 2017 at 6:42 AM, Joe Conway 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.

Re: [GENERAL] Limiting DB access by role after initial connection?

2017-06-09 Thread bto...@computer.org
- Original Message - > From: "Ken Tanzer" <ken.tan...@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 connecti

Re: [GENERAL] Limiting DB access by role after initial connection?

2017-06-09 Thread Joe Conway
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

Re: [GENERAL] Limiting DB access by role after initial connection?

2017-06-09 Thread Ken Tanzer
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

[GENERAL] Limiting DB access by role after initial connection?

2017-06-08 Thread Ken Tanzer
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