\gsetenv

David Fetter <david@fetter.org> writes:

We have \gset to set some parameters, but not ones in the environment,
so I fixed this with a new analogous command, \gsetenv.

In view of the security complaints we just had about \gset
(CVE-2020-25696), I cannot fathom why we'd consider adding another
way to cause similar problems.

We were fortunate enough to be able to close off the main security risk
of \gset without deleting the feature altogether ... but how exactly
would we distinguish "safe" from "unsafe" environment variables? It kind
of seems like anything that would be worth setting at all would tend to
fall into the "unsafe" category, because the implications of setting it
would be unclear. But *for certain* we're not taking a patch that allows
remotely setting PATH and things like that.

Besides which, you haven't bothered with even one word of positive
justification. What's the non-hazardous use case?

regards, tom lane

On Wed, Dec 16, 2020 at 05:30:13PM -0500, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

We have \gset to set some parameters, but not ones in the environment,
so I fixed this with a new analogous command, \gsetenv.

In view of the security complaints we just had about \gset
(CVE-2020-25696), I cannot fathom why we'd consider adding another
way to cause similar problems.

The RedHat site says, in part:

the attacker can execute arbitrary code as the operating system
account running psql

This is true of literally everything that requires a login shell in
order to use. I remember a "virus" my friend Keith McMillan wrote in
TeX back in the 1994. You can download the PostScript file detailing
the procedure, bearing in mind that PostScript also contains ways to
execute arbitrary code if opened:

ftp://ftp.cerias.purdue.edu/pub/doc/viruses/KeithMcMillan-PlatformIndependantVirus.ps

That one got itself a remote code execution by fiddling with a
person's .emacs, and it got Keith a master's degree in CS. I suspect
that equally nasty things are possible when it comes to \i and \o in
psql. It would be a terrible idea to hobble psql in the attempt to
prevent such attacks.

We were fortunate enough to be able to close off the main security
risk of \gset without deleting the feature altogether ... but how
exactly would we distinguish "safe" from "unsafe" environment
variables? It kind of seems like anything that would be worth
setting at all would tend to fall into the "unsafe" category,
because the implications of setting it would be unclear. But *for
certain* we're not taking a patch that allows remotely setting PATH
and things like that.

Would you be so kind as to explain what the actual problem is here
that not doing this would mitigate?

If people run code they haven't seen from a server they don't trust,
neither psql nor anything else[1]search for "gods themselves" here: https://en.wikiquote.org/wiki/Friedrich_Schiller -- David Fetter <david(at)fetter(dot)org> http://fetter.org/ Phone: +1 415 235 3778 can protect them from the
consequences. Seeing what they're about to run is dead easy in this
case because \gsetenv, like \gset and what in my view is the much more
dangerous \gexec, is something anyone with the tiniest modicum of
caution would run only after testing it with \g.

Besides which, you haven't bothered with even one word of positive
justification. What's the non-hazardous use case?

Thanks for asking, and my apologies for not including it.

I ran into a situation where we sometimes got a very heavily loaded
and also well-protected PostgreSQL server. At times, just getting a
shell on it could take a few tries. To mitigate situations like that,
I used a method that's a long way from new, abstruse, or secret: have
psql open in a long-lasting tmux or screen session. It could both
access the database at a time when getting a new connection would be
somewhere between difficult and impossible. The bit that's unlikely
to be new was when I noticed that it could also shell out
and send information off to other places, but only when I put together
a pretty baroque procedure that involved using combinations of \gset,
\o, and \!. All of the same things \gsetenv could do were doable with
those, just less convenient, so I drafted up a patch in the hope that
fewer others would find themselves jumping through the hoops I did to
get that set up.

Separately, I confess to some bafflement at the reasoning behind the
CVE you referenced. By the time an attacker has compromised a database
server, it's already game over. Code running on the compromised
database is capable of doing much nastier things than crashing a
client machine, and very likely has access to other high-value targets
on its own say-so than said client does.

Best,
David.

[1]: search for "gods themselves" here: https://en.wikiquote.org/wiki/Friedrich_Schiller -- David Fetter <david(at)fetter(dot)org> http://fetter.org/ Phone: +1 415 235 3778
https://en.wikiquote.org/wiki/Friedrich_Schiller
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 12/16/20 10:54 PM, David Fetter wrote:

Besides which, you haven't bothered with even one word of positive
justification. What's the non-hazardous use case?

Thanks for asking, and my apologies for not including it.

I ran into a situation where we sometimes got a very heavily loaded
and also well-protected PostgreSQL server. At times, just getting a
shell on it could take a few tries. To mitigate situations like that,
I used a method that's a long way from new, abstruse, or secret: have
psql open in a long-lasting tmux or screen session. It could both
access the database at a time when getting a new connection would be
somewhere between difficult and impossible. The bit that's unlikely
to be new was when I noticed that it could also shell out
and send information off to other places, but only when I put together
a pretty baroque procedure that involved using combinations of \gset,
\o, and \!. All of the same things \gsetenv could do were doable with
those, just less convenient, so I drafted up a patch in the hope that
fewer others would find themselves jumping through the hoops I did to
get that set up.

Does this help?

andrew=# select 'abc'::text as foo \gset
andrew=# \setenv FOO :foo
andrew=# \! echo $FOO
abc
andrew=#

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Hello David,

We have \gset to set some parameters, but not ones in the environment,
so I fixed this with a new analogous command, \gsetenv. I considered
refactoring SetVariable to include environment variables, but for a
first cut, I just made a separate function and an extra if.

My 0.02ￜ: ISTM that you do not really need that, it can already be
achieved with gset, so I would not bother to add a gsetenv.

sh> psql
SELECT 'Calvin' AS foo \gset
\setenv FOO :foo
\! echo $FOO
Calvin

--
Fabien.

On Sun, Dec 20, 2020 at 02:26:14PM +0100, Fabien COELHO wrote:

Hello David,

We have \gset to set some parameters, but not ones in the environment,
so I fixed this with a new analogous command, \gsetenv. I considered
refactoring SetVariable to include environment variables, but for a
first cut, I just made a separate function and an extra if.

My 0.02€: ISTM that you do not really need that, it can already be achieved
with gset, so I would not bother to add a gsetenv.

sh> psql
SELECT 'Calvin' AS foo \gset
\setenv FOO :foo
\! echo $FOO
Calvin

Thanks!

You're the second person who's mentioned this workaround, which goes
to a couple of points I tried to make earlier:

- This is not by any means a new capability, just a convenience, and
- In view of the fact that it's a very old capability, the idea that
it has implications for controlling access or other parts of the
space of threat models is pretty silly.

Having dispensed with the idea that there's a new attack surface here,
I'd like to request that people at least have a look at it as a
feature psql users might appreciate having. As the author, I obviously
see it that way, but again as the author, it's not for me to make that
call.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david@fetter.org> writes:

On Sun, Dec 20, 2020 at 02:26:14PM +0100, Fabien COELHO wrote:

SELECT 'Calvin' AS foo \gset
\setenv FOO :foo
\! echo $FOO
Calvin

You're the second person who's mentioned this workaround, which goes
to a couple of points I tried to make earlier:

- This is not by any means a new capability, just a convenience, and
- In view of the fact that it's a very old capability, the idea that
it has implications for controlling access or other parts of the
space of threat models is pretty silly.

This is essentially the same workaround as what we recommend for anyone
who's unhappy with the fix for CVE-2020-25696: do \gset into a non-special
variable and then copy to the special variable. The reason it seems okay
is that now it is clear that client-side logic intends the special
variable change to happen. Thus a compromised server cannot hijack your
client-side session all by itself. There's nonzero risk in letting the
server modify your PROMPT1, PATH, or whatever, but you took the risk
intentionally (and, presumably, it's necessary to your purposes).

I tend to agree with you that the compromised-server argument is a little
bit of a stretch. Still, we did have an actual user complaining about
the case for \gset, and it's clear that in at least some scenarios this
sort of attack could be used to parlay a server compromise into additional
access. So we're not likely to undo the CVE-2020-25696 fix, and we're
equally unlikely to provide an unrestricted way to set environment
variables directly from the server.

If we could draw a line between "safe" and "unsafe" environment
variables, I'd be willing to consider a patch that allows directly
setting only the former. But I don't see how to draw that line.
Most of the point of any such feature would have to be to affect
the behavior of shell commands subsequently invoked with \! ...
and we can't know what a given variable would do to those. So on
the whole I'm inclined to leave things as-is, where people have to
do the assignment manually.

regards, tom lane

On Sun, Dec 20, 2020 at 01:07:12PM -0500, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

On Sun, Dec 20, 2020 at 02:26:14PM +0100, Fabien COELHO wrote:

SELECT 'Calvin' AS foo \gset
\setenv FOO :foo
\! echo $FOO
Calvin

You're the second person who's mentioned this workaround, which goes
to a couple of points I tried to make earlier:

- This is not by any means a new capability, just a convenience, and
- In view of the fact that it's a very old capability, the idea that
it has implications for controlling access or other parts of the
space of threat models is pretty silly.

This is essentially the same workaround as what we recommend for anyone
who's unhappy with the fix for CVE-2020-25696: do \gset into a non-special
variable and then copy to the special variable. The reason it seems okay
is that now it is clear that client-side logic intends the special
variable change to happen. Thus a compromised server cannot hijack your
client-side session all by itself. There's nonzero risk in letting the
server modify your PROMPT1, PATH, or whatever, but you took the risk
intentionally (and, presumably, it's necessary to your purposes).

I tend to agree with you that the compromised-server argument is a little
bit of a stretch. Still, we did have an actual user complaining about
the case for \gset, and it's clear that in at least some scenarios this
sort of attack could be used to parlay a server compromise into additional
access. So we're not likely to undo the CVE-2020-25696 fix, and we're
equally unlikely to provide an unrestricted way to set environment
variables directly from the server.

If we could draw a line between "safe" and "unsafe" environment
variables, I'd be willing to consider a patch that allows directly
setting only the former. But I don't see how to draw that line.
Most of the point of any such feature would have to be to affect
the behavior of shell commands subsequently invoked with \! ...
and we can't know what a given variable would do to those. So on
the whole I'm inclined to leave things as-is, where people have to
do the assignment manually.

I suppose now's not a great time for this from an optics point of
view. Taking on the entire security theater industry is way out of
scope for the PostgreSQL project.

We have plenty of ways to spawn shells and cause havoc, and we
wouldn't be able to block them all even if we decided to put a bunch
of pretty onerous restrictions on psql at this very late date. We have
\set, backticks, \!, and bunches of things less obvious that could,
even without a compromised server, cause real mischief. I believe that
a more effective way to deal with this reality in a way that helps
users is to put clear warnings in the documentation about the fact
that psql programs are at least as capable as shell programs in that
they are innately capable of doing anything that the psql's system
user is authorized to do.

Would a patch along that line help?

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 20/12/2020 21:05, David Fetter wrote:

We have plenty of ways to spawn shells and cause havoc, and we
wouldn't be able to block them all even if we decided to put a bunch
of pretty onerous restrictions on psql at this very late date. We have
\set, backticks, \!, and bunches of things less obvious that could,
even without a compromised server, cause real mischief.

There is a big difference between having to trust the server or not.
Yeah, you could cause a lot of mischief if you let a user run arbitrary
psql scripts on your behalf. But that's no excuse for opening up a whole
another class of problems.

- Heikki

On Sun, Dec 20, 2020 at 10:42:40PM +0200, Heikki Linnakangas wrote:

On 20/12/2020 21:05, David Fetter wrote:

We have plenty of ways to spawn shells and cause havoc, and we
wouldn't be able to block them all even if we decided to put a bunch
of pretty onerous restrictions on psql at this very late date. We have
\set, backticks, \!, and bunches of things less obvious that could,
even without a compromised server, cause real mischief.

There is a big difference between having to trust the server or not. Yeah,
you could cause a lot of mischief if you let a user run arbitrary psql
scripts on your behalf. But that's no excuse for opening up a whole another
class of problems.

I'm skittish about putting exploits out in public in advance of
discussions about how to mitigate them, but I have constructed several
that do pretty bad things using only hostile content in a server and
the facilities `psql` already provides.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Sun, Dec 20, 2020 at 11:07 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

If we could draw a line between "safe" and "unsafe" environment
variables, I'd be willing to consider a patch that allows directly
setting only the former. But I don't see how to draw that line.

IIUC the threat here is for users that write:

SELECT * FROM view \gset

Because if you are writing

SELECT col1, col2, col3 OR SELECT expression AS col1 \gset

The query author has explicitly stated which variable names they exactly
want to change/create and nothing the server can do will be able to alter
those names.

Or *is* that the problem - the server might decide to send back a column
named "breakme1" in the first column position even though the user aliased
the column name as "col1"?

Would a "\gsetenv (col1, col2, col3, skip, col4)" be acceptable that leaves
the name locally defined while relying on column position to match?

How much do we want to handicap a useful feature because someone can use it
alongside "SELECT *"? Can we prevent it from working in such a case
outright - force an explicit column name list at minimum, and ideally
aliases for expressions? I suspect not, too much of that has to happen on
the server. That makes doing this by column position and defining the
names strictly locally a compromise worth considering. At worst, there is
no way to get an unwanted variable to appear on the client even if the data
for wanted variables is made bogus by the compromised server. I don't see
how avoiding the bogus data problem is even possible.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Sun, Dec 20, 2020 at 11:07 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

If we could draw a line between "safe" and "unsafe" environment
variables, I'd be willing to consider a patch that allows directly
setting only the former. But I don't see how to draw that line.

Because if you are writing
SELECT col1, col2, col3 OR SELECT expression AS col1 \gset
The query author has explicitly stated which variable names they exactly
want to change/create and nothing the server can do will be able to alter
those names.

Or *is* that the problem - the server might decide to send back a column
named "breakme1" in the first column position even though the user aliased
the column name as "col1"?

Yeah, exactly. Just because the SQL output *should* have column names
x, y, z doesn't mean it *will*, if the server is malicious. psql isn't
bright enough to understand what column names the query ought to produce,
so it just believes the column names that come back in the query result.

Would a "\gsetenv (col1, col2, col3, skip, col4)" be acceptable that leaves
the name locally defined while relying on column position to match?

Hmm, maybe. The key point here is local vs. remote control of which
variables get assigned to, and offhand that seems like it'd fix the
problem.

How much do we want to handicap a useful feature because someone can use it
alongside "SELECT *"?

Whether it's "SELECT *" or "SELECT 1 AS X" doesn't really matter here.
The concern is that somebody has hacked the server to send back something
that is *not* what you asked for. For that matter, since the actual
update isn't visible to the user, the attacker could easily make the
server send back all the columns the user expected ... plus some
he didn't. The attackee might not even realize till later that
something fishy happened.

regards, tom lane