Thread: [GENERAL] Are new connection/security features in order, given connectionpooling?

On 1/10/2017 11:32 PM, Guyren Howe wrote:
> Further to my recent inquiries about leveraging Postgres’ security
> features from client apps, it appears to me that some likely
> reasonably simple changes to those features would enable client apps
> to better leverage what are otherwise great features.
>
> *IF* we give our end users roles in Postgres and we were to connect
> *as them*, then we have a brilliant range of authorization features
> available, what with the lovely role inheritance and the row security
> features.
>
> *BUT* because of practical considerations having to do with connection
> pooling, no-one opens a different connection for each request. And
> there appears to be no other simple way to use the row security.
>
> This seems a bit tragic. I would love to have some way of, say,
> opening a connection without a default role, and having to supply a
> role name and token with each actual request.
>
> Or perhaps there is some other solution along those lines.
>
> In any event, if there is some simple way of solving the same problem
> with the current architecture, I’d love to hear it.

the only model I can see working is for the app server to maintain a
pool of connections for active user sessions, and match the http user
session to its corresponding database connection. now, http is nasty,
its sessionless, and each user+browser can invoke multiple http
connections concurrently, so with this model, your app server would need
to ensure that each user session only can execute one piece of business
logic at a time that touches the database.

you'd need cleanup code to close idle sessions since http sessions can
just vanish without logging out or otherwise notifying you.    you'd
need code to handle user sessions that come back after that cleanup
code, so you can reestablish that sessions connection.

its all a big mess.    oh, and you'd need a seperate database
connection, or some alternative persistent storage, to associate
incoming http requests to the proper user so you'd even know which
session connection to use.

I don't think there's any turnkey solutions to this.



--
john r pierce, recycling bits in santa cruz

On 1/11/2017 12:49 AM, Guyren Howe wrote:
> would prevent an SQL injection from wrecking havoc.

sql injection is quite easy to avoid.   always make parameterized
queries, never use client supplied data to assemble a query with string
concatenation.

voila, no injection possible.

--
john r pierce, recycling bits in santa cruz

Guyren Howe <guyren@gmail.com> writes:
> I’m not following. What I would like is just a lightweight way to switch the connections to use a different role, or
somemoral equivalent, that would prevent an SQL injection from wrecking havoc. I’m not proposing anything that will
changeanything else about how the application is using the database. 

> SET ROLE doesn’t work, because an SQL injection can just SET ROLE back to the privileged user.

> I would like a regime where there is no permanent privileged relationship between the client application and the
database;a user would need to supply validating information that we can use to switch a connection to something with
minimalprivileges for just that role, for the duration of one session or transaction. 

And what happens after that?  You can't reuse the session anyway, because
there would be no way to switch it back to a more-privileged state so you
could use it for another user ID.  (If there were a way, then a successful
SQL-injection attack could use it too.)

If you end up having to dedicate each connection to a particular
unprivileged userID, then you can just open the connection as that user
to start with; a magic one-way privilege switch doesn't really help.

We've discussed this problem repeatedly (you might want to search the
archives a bit) and never found a solution that was both fully secure
and did much of anything for connection-pooling scenarios.

            regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Guyren Howe <guyren@gmail.com> writes:
> > I’m not following. What I would like is just a lightweight way to switch the connections to use a different role,
orsome moral equivalent, that would prevent an SQL injection from wrecking havoc. I’m not proposing anything that will
changeanything else about how the application is using the database. 
>
> > SET ROLE doesn’t work, because an SQL injection can just SET ROLE back to the privileged user.
>
> > I would like a regime where there is no permanent privileged relationship between the client application and the
database;a user would need to supply validating information that we can use to switch a connection to something with
minimalprivileges for just that role, for the duration of one session or transaction. 
>
> And what happens after that?  You can't reuse the session anyway, because
> there would be no way to switch it back to a more-privileged state so you
> could use it for another user ID.  (If there were a way, then a successful
> SQL-injection attack could use it too.)

That's based on an incorrect assumption that the state which allows a
connection to be reused by another user ID is 'more privileged'.

If we had a way to require authentication for a 'SET ROLE' call then you
would be able to do what Guyren is asking about and when you're done
with the session something issues 'RESET ROLE;' and you're back to a
role that *isn't* more privileged and to move to another user ID you
have to go through the authentication process again.

This isn't fantasy, it's been done, but it's more complicated than it
really should be, as noted up-thread.  There's also complications around
how to handle the authentication credentials so you aren't constantly
asking the user to re-authenticate, but that's not impossible to deal
with either, you just need something akin to the cookie/unique ID used
by every website that authenticates users today.

> If you end up having to dedicate each connection to a particular
> unprivileged userID, then you can just open the connection as that user
> to start with; a magic one-way privilege switch doesn't really help.

Well, to that I'd say "it depends."  There are certainly scenarios where
you have multiple userIDs and a connection pooler like pgbouncer which
handles the different connections to the database and it's a lot better
than making new connections because new connections are so painful and
slow to create.  This doesn't work great if you have thousands of
concurrently connected independent users, of course.

> We've discussed this problem repeatedly (you might want to search the
> archives a bit) and never found a solution that was both fully secure
> and did much of anything for connection-pooling scenarios.

I don't agree that this is unsolvable, but it would require things like
protocol-level changes which no one has had the gumption to work through
and propose.

In short, I agree with Guyren, there are features needed here that we
don't have and it would be a great deal better if we did.

Thanks!

Stephen

On 01/11/2017 03:10 PM, Stephen Frost wrote:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
 >
 > ...
>
>> If you end up having to dedicate each connection to a particular
>> unprivileged userID, then you can just open the connection as that
>> user to start with; a magic one-way privilege switch doesn't really
>> help.
>
> Well, to that I'd say "it depends." There are certainly scenarios
> where you have multiple userIDs and a connection pooler like
> pgbouncer which handles the different connections to the database and
> it's a lot better than making new connections because new connections
> are so painful and slow to create. This doesn't work great if you
> have thousands of concurrently connected independent users, of
> course.
>
>> We've discussed this problem repeatedly (you might want to search
>> the archives a bit) and never found a solution that was both fully
>> secure and did much of anything for connection-pooling scenarios.
>

Not sure which discussions you had in mind, but I found these two that
seem relevant:

[RFC: Non-user-resettable SET SESSION AUTHORISATION]

https://www.postgresql.org/message-id/flat/CAMsr%2BYHUiukYYxtvc1UahF4yM5Jc1bZAN%2Byt86WXsSVm69XXGg%40mail.gmail.com

[A mechanism securing web applications in DBMS]
https://www.postgresql.org/message-id/CA%2B0EDdCNwJvvb3aHVT4A8ywSwO40JeHj8_CYUx2SBb9%3DR6xHew%40mail.gmail.com

 >
> I don't agree that this is unsolvable, but it would require things
> like protocol-level changes which no one has had the gumption to work
> through and propose.
>

Perhaps it's a mistake to make this work with roles, at least for the
RLS use case. I'd argue roles are kinda orthogonal to the privilege
system we have, and the fact that RLS policies may use current_user does
not necessarily mean the solution needs to be based on roles.

Not only that roles were designed long before RLS, but having to create
a role for each user is quite limiting, and who says role name is the
only aspect useful for policies?

Which is why I think a protected vault-like thingy is a more promising
approach. This is why Oracle based the VPD (Virtual Private Database,
essentially what we call RLS) on 'application contexts', and  set by
'trusted' procedure usually called in a LOGON trigger. That of course
does not work with the connection pooling, but perhaps making it
possible to re-initialize the context would be easier than protecting
SET ROLE.

Admittedly, the solution described in the blog post is not perfect, but
while some protocol-level support would be nice I don't think that's a
requirement as long as the application knows how to initialize the
context, and we reset it on RESET ALL.

 >
> In short, I agree with Guyren, there are features needed here that
> we don't have and it would be a great deal better if we did.
>

Yeah.

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

I can relate to the original plea from my own exploration of this
topic.

Before I get into that, I will mention as an aside that to date we
have found RLS to be really slow for web client authorization, given
that we have to use session parameters to store web client context and
there doesn't seem to be a way to get the query planner to treat these
settings as constants nor to lift static parts of RLS policy out of
per-row loops.  It's so slow that I wonder whether future releases
will improve it before we are forced to move authorization back into
the client-side application logic, making our generated SQL much more
complex but better tuned to the query planner's capabilities.

As background, our web applications essentially compile new SQL
queries on the fly and send them through to PostgreSQL.  There is a
lot of metaprogramming involved in generating SQL statements to embody
the work we want done on behalf of our clients, and this doesn't fit
well into traditional prepared statements etc.

The web service needs to assert the client context and the
client-level statements we generate and execute should not be able to
override this. The service logic to determine and assert client
context (once per web request) is dramatically simpler than the
service logic producing all the client-level SQL statements, and it
would be nice to have a restricted execution context to isolate the
two kinds.  We also like the idea that policy enforcement mechanisms
could be applied orthogonally to the generated client-level SQL
statements, as it feels like a better defense-in-depth architecture
and is also easier to reason about. To do so in our service logic
would mean replicating more and more of the query parsing and engine
to do general query rewriting.

So, it would be nice to have a tunneling mechanism where I can
distinguish the control-plane operations I am performing from the
application operations I am translating and pushing down to PostgreSQL
on behalf of the web client. In my case, I might want to perform a
mixture of service-level and client-level statements within the same
transaction. The client-level statements cannot control transactions.

I could imagine something like a two-level feature set. At the
top-level in the connection, we can statefully manipulate our security
contexts, set configurable privilege masks for the second-level
execution context, set effective roles (reversibly, as today), manage
transactions, etc.  With some kind of nested execution block, we could
submit less trusted statements to run within the second-level
execution context:

   EXECUTE RESTRICTED $guard$ app_query... $guard$ ;

This would take the guarded query string, restart a safe parser on it,
and only on successful parse go forward with planning and executing it
in the restricted mode that has been configured for the connection.

Eventually, I could see wanting more resource management controls on
this restricted context too, i.e. setting limits of CPU/IO/RAM
consumption or execution time. Both planning time limits (reject based
on estimates) and runtime (abort query if limit is reached).


Karl

On 2017-01-11 00:49:19 -0800, Guyren Howe wrote:
> I’m not following. What I would like is just a lightweight way to switch the
> connections to use a different role, or some moral equivalent, that would
> prevent an SQL injection from wrecking havoc. I’m not proposing anything that
> will change anything else about how the application is using the database.
>
> SET ROLE doesn’t work, because an SQL injection can just SET ROLE back to the
> privileged user.

But then you are no worse off than with the commonly used scheme of
executing all queries as the same (necessarily "privileged") user.

In both cases the attacker can execute queries as a privileged user IF
he succeeds at sql injections.

But as others have already noted this is relatively easy to prevent.
Just preparing all queries is sufficient, even if you don't actually
parametrize them. Perl DBI does this, so this dangerous-looking line of
code (assume that the string wasn't hardcoded but the result of an SQL
injection):

    $r = $dbh->selectall_arrayref("select * from twoqueries; insert into twoqueries(t) values('b')");

will fail with

    DBD::Pg::db selectall_arrayref failed: ERROR:  cannot insert
    multiple commands into a prepared statement at ./twoqueries line 21.

So I think just using

    set local role

at the beginning of each transaction should work well with session
pooling. It doesn't protect you against sql injections, but you won't
have to reinvent the authorization system.

> I would like a regime where there is no permanent privileged relationship
> between the client application and the database; a user would need to supply
> validating information that we can use to switch a connection to something with
> minimal privileges for just that role, for the duration of one session or
> transaction.

I haven't read the blog post referenced in this thread yet, so maybe
this is covered there, but I think "supplying validating information"
would be the hard part. In general you wouldn't want a web-frontend to
cache plain-text passwords to resubmit them for each transaction, but to
use something more ethereal, like session cookies or kerberos tickets.

        hp

--
   _  | Peter J. Holzer    | A coding theorist is someone who doesn't
|_|_) |                    | think Alice is crazy.
| |   | hjp@hjp.at         | -- John Gordon
__/   | http://www.hjp.at/ |    http://downlode.org/Etext/alicebob.html

On 01/12/2017 03:12 AM, Karl Czajkowski wrote:
> I can relate to the original plea from my own exploration of this
> topic.
>
> Before I get into that, I will mention as an aside that to date we
> have found RLS to be really slow for web client authorization, given
> that we have to use session parameters to store web client context
> and there doesn't seem to be a way to get the query planner to treat
> these settings as constants nor to lift static parts of RLS policy
> out of per-row loops. It's so slow that I wonder whether future
> releases will improve it before we are forced to move authorization
> back into the client-side application logic, making our generated SQL
> much more complex but better tuned to the query planner's
> capabilities.
>

I'm no expert in RLS, but I know others have been making the planner
smarter when it comes to RLS. It would be helpful to compile this into a
bunch of examples with queries/explain plans, and report it to
pgsql-performance. It'd provide practical feedback from real-world RLS
use, and maybe there already is a reasonable way to improve the plans.

I assume you use current_config() function to access the parameters? I
see that while it's marked as stable, it's not marked as leak-proof,
which might be why the planner can't apply some of the optimizations
when used in a RLS policy.

 >
> As background, our web applications essentially compile new SQL
> queries on the fly and send them through to PostgreSQL. There is a
> lot of metaprogramming involved in generating SQL statements to
> embody the work we want done on behalf of our clients, and this
> doesn't fit well into traditional prepared statements etc.
>

Sure, a lot of systems generate queries on the fly. Also, if the main
problem is poor plan choice due to RLS, I'm not sure how prepared
statements could help with that.

 >
> The web service needs to assert the client context and the
> client-level statements we generate and execute should not be able
> to override this. The service logic to determine and assert client
> context (once per web request) is dramatically simpler than the
> service logic producing all the client-level SQL statements, and it
> would be nice to have a restricted execution context to isolate the
> two kinds. We also like the idea that policy enforcement mechanisms
> could be applied orthogonally to the generated client-level SQL
> statements, as it feels like a better defense-in-depth architecture
> and is also easier to reason about. To do so in our service logic
> would mean replicating more and more of the query parsing and engine
> to do general query rewriting.
>
> So, it would be nice to have a tunneling mechanism where I can
> distinguish the control-plane operations I am performing from the
> application operations I am translating and pushing down to
> PostgreSQL on behalf of the web client. In my case, I might want to
> perform a mixture of service-level and client-level statements within
> the same transaction. The client-level statements cannot control
> transactions.
>
> I could imagine something like a two-level feature set. At the
> top-level in the connection, we can statefully manipulate our
> security contexts, set configurable privilege masks for the
> second-level execution context, set effective roles (reversibly, as
> today), manage transactions, etc. With some kind of nested execution
> block, we could submit less trusted statements to run within the
> second-level execution context:
>
>    EXECUTE RESTRICTED $guard$ app_query... $guard$ ;
>
> This would take the guarded query string, restart a safe parser on
> it, and only on successful parse go forward with planning and
> executing it in the restricted mode that has been configured for the
> connection.
>
> Eventually, I could see wanting more resource management controls on
> this restricted context too, i.e. setting limits of CPU/IO/RAM
> consumption or execution time. Both planning time limits (reject
> based on estimates) and runtime (abort query if limit is reached).
>

That might be an interesting feature, but it's also significantly more
complex than the topic of implementing a safe context for secrets,
making RLS less problematic with connection pools.


regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Jan 14, Tomas Vondra modulated:
...
> Sure, a lot of systems generate queries on the fly. Also, if the
> main problem is poor plan choice due to RLS, I'm not sure how
> prepared statements could help with that.
>

Sorry for being unclear, I was trying to respond to too many
sub-topics at once.  The performance comment was just a tangential
aside from my experiments using RLS for web client
authorization. Indeed, I use the current_setting() function in RLS
policy statements to access ephemeral web client context stored via
set_config().

In the rest of my message, I was trying to respond to the topic of
being able to "set role" and limit the connection privileges to
reflect a web client's identity for RLS or other fine-grained
authorization. I thought this was the main topic of the thread---the
desire to push down web client authorization decisions into the normal
postgres mechanisms, and all the problems arising from this sort of
approach.

The concern was raised about the risk of the subsequent queries being
able to reverse the "set role" to subvert such authorization. Others
in the thread provided the common advice of parametric queries to
prevent query injection, but I think you need more than that.  I think
one would like a stronger isolation between the connection
setup/security config layer and the fancier (and possibly flawed)
application layer.  We struggle with this because all these mechanisms
are multiplexed as commands in one SQL connection, instead of having
an in-band versus out-of-band protocol layering available to the web
application, so the more risky data-dependent SQL can flow in-band
while the basic request-handler lifecycle could configure the security
context out-of-band.


> That might be an interesting feature, but it's also significantly
> more complex than the topic of implementing a safe context for
> secrets, making RLS less problematic with connection pools.
>

I was thinking that this mechanism would support the usual shared
connection pool to support all web users.  The web server would
repeatedly redefine the restricted security context and execute
application queries under that context.  The pooling problems come
from the suggestion to open connections w/ different per-user
authentication and then not have any user-switching that can easily
reconfigure the connection, right?


karl

On Fri, Jan 13, 2017 at 7:39 PM, Karl Czajkowski <karlcz@isi.edu> wrote:

> The concern was raised about the risk of the subsequent queries being
> able to reverse the "set role" to subvert such authorization. Others
> in the thread provided the common advice of parametric queries to
> prevent query injection, but I think you need more than that.  I think
> one would like a stronger isolation between the connection
> setup/security config layer and the fancier (and possibly flawed)
> application layer.  We struggle with this because all these mechanisms
> are multiplexed as commands in one SQL connection, instead of having
> an in-band versus out-of-band protocol layering available to the web
> application, so the more risky data-dependent SQL can flow in-band
> while the basic request-handler lifecycle could configure the security
> context out-of-band.

Wasn't there some previous discussion about the ability to have a
form of SET ROLE that would specify that some token would be needed
to do a successful RESET ROLE?  (I don't remember whether people
were leaning toward providing the token on the SET ROLE statement,
or getting that as part of the return state.)  If you made the
token a suitably secure random value, and made an incorrect token
on the RESET ROLE a FATAL error, it seems like it could be secure
enough.  You still need to have the ability to authenticate the
client side connection in the connection pooler, and have the
connection pooler connect to the server with a login with rights to
do the appropriate SET ROLE (preferably without requiring superuser
rights).

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company