Are new connection/security features in order, given connection pooling?

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

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

On Jan 11, 2017, at 0:07 , John R Pierce <pierce@hogranch.com> wrote:

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.

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.

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.

Opening a new connection each time would do that, but is too heavyweight. I’m just looking for a connection pool-friendly moral equivalent.

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

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

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

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.

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

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

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

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.

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]

/messages/by-id/CAMsr+YHUiukYYxtvc1UahF4yM5Jc1bZAN+yt86WXsSVm69XXGg@mail.gmail.com

[A mechanism securing web applications in DBMS]
/messages/by-id/CA+0EDdCNwJvvb3aHVT4A8ywSwO40JeHj8_CYUx2SBb9=R6xHew@mail.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

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

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

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

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

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

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

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

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

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