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

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 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 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 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.