Thread: \gsetenv
Hi, 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. 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
Attachment
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] 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
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