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

Ah yes, I could see that.  In this case it's a web app, so only the server
has the DB credentials.  I'd really hate it if each client had to be able
to access those credentials!

Cheers,
Ken


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

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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.

Cheers,
Ken




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

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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



signature.asc
Description: OpenPGP digital signature

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
> login roles assignable membership in group roles. Why not let the tool do
> what it can already do very effectively?
>
> -- B
>
>
If you mean having each individual person having their own role, I'd say
it's not impossible, impractical at the current moment but (probably)
desirable and a longer-term goal.  There's just an awful lot of logic that
would have to be worked into the access control, as well as a way to create
and maintain all the roles.  Some day!  Maybe! :)

Ken


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

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

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 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.
>
>
Despite reading that, I was a little uncertain because of it being called
block_log_statement.  It seems like conceptually it's really
log_all_statements, though I suspect you won't want to change the name in
midstream.

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.


> > 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.
>
>
If I go this route, get it up and running and find any, I'll be happy to
let you know. :)

Thanks a lot for your help!

Ken



> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>

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

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

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



signature.asc
Description: OpenPGP digital signature

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.  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/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

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



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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



signature.asc
Description: OpenPGP digital signature

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 couldn't be
dropped?

2) Same as above, but set by the app before making the connection?  I'd
settle for this even if it meant two connections per page.

Either of those would facilitate more fine-grained, per-user access, which
would ultimately be better and maybe allow me to skip the creation and
management of roles.  Having discovered that the RLS will not apply to all
my existing views and I'll need to rewrite them anyway, this seems like a
better thing to wish for!

Cheers,
Ken

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

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

[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
been looking at doing this by creating a role for each location, and then
using RLS to restrict access to some tables.  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.  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.

I can still go this route, either by:

1)  Setting the role as described above, and then trying to be damn sure
that a subsequent query doing a role change never ever slips through the
app. :)

2) After authentication, close the DB connection and reconnect as the site
role.  This seems tighter from a security standpoint, but at the cost of
doubling my # of DB connections, and then also needing the app to manage
passwords for each site.

So before doing either of those, I wanted to confirm if there is/isn't a
way to configure and change roles in a way that reduces privileges, and
cannot be undone.  Either with changing roles, or as some kind of
"RECONNECT TO db_name AS user" command that wold allow a DB owner to
connect without supplying credentials.

Those might both be wishful thinking.  If so, I'd also welcome any
thoughts, suggestions or feedback about 1) and 2), or better approaches
entirely.  Thanks!

Ken

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

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.