Re: Limiting DB access by role after initial connection?

----- Original Message -----
> From: "Ken Tanzer" <ken(dot)tanzer(at)gmail(dot)com>
> To: "PG-General Mailing List" <pgsql-general(at)postgresql(dot)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 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