Обсуждение: BUG #15930: Redact PGPASSWORD environment variable in psql

The following bug has been logged on the website:

Bug reference:      15930
Logged by:          Georg Sauthoff
Email address:      postgres-bug@gms.tf
PostgreSQL version: 11.4
Operating system:   Linux
Description:

Under Linux, when supplying the password via PGPASSWORD to the psql command
the password can be easily retrieved from the /proc/$pid/environ pseudo file
(or indirectly with e.g. `ps ae`) - for the complete runtime of the psql
process.

Test case:
# Terminal 1
$ PGPASSWORD='geheim' psql -h 127.0.0.1 --user juser  -d juser

# Terminal 2
$ < /proc/$psqlpid/environ tr '\0' '\n' | grep PGPASSWORD

Expected output:
PGPASSWORD=xxxxxx

Actual output:
PGPASSWORD=geheim

See my gist for a minimal example that demonstrate how to redact the
password on linux:

https://gist.github.com/gsauthof/3efc1a7865fb70517ed741169b3bf11d

Redacting the password can be seen as a defense-in-depth measure.

It improves the security in use cases like this one:

A batch job script starts some long running psql processes. To avoid having
to enter the password several times, the script just asks once for the
password and then supplies it to each psql process in the PGPASSWORD
variable. Now the user forgets to lock his screen and leaves his desk. A
novice attacker present in the same office could now easily look up the
password in `/proc/$pid/environ` or - say - by executing `ps ae`.

PG Bug reporting form <noreply@postgresql.org> writes:
> Under Linux, when supplying the password via PGPASSWORD to the psql command
> the password can be easily retrieved from the /proc/$pid/environ pseudo file
> (or indirectly with e.g. `ps ae`) - for the complete runtime of the psql
> process.

This is true on many OSes.  Generally speaking, we deprecate use of
PGPASSWORD at all on such platforms.  Having psql try to clear it out
seems rather pointless to me, as (a) that does nothing for instances
of the value that appear in the environments of ancestor processes,
and (b) I doubt there is any platform-independent way to hide it.

FWIW, Postgres offers a number of other authentication mechanisms
that can be both more secure and more convenient than passwords.

            regards, tom lane

Hello,

On 2019-07-27 15:23:29, Tom Lane wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > Under Linux, when supplying the password via PGPASSWORD to the psql command
> > the password can be easily retrieved from the /proc/$pid/environ pseudo file
> > (or indirectly with e.g. `ps ae`) - for the complete runtime of the psql
> > process.
> 
> This is true on many OSes.  Generally speaking, we deprecate use of
> PGPASSWORD at all on such platforms.

https://www.postgresql.org/docs/11/libpq-envars.html doesn't deprecate
PGPASSWORD. It recommends against it because of systems where the
environment of a process is readable by **other** non-root users.

For example, on Solaris 9 the environment of every process is
world-readable (not mentioned in the link). The Postgres manual link
recommends to use a password file which - under Linux - has the same
security implications as the environment variable because the password
is as easily accessible.

> Having psql try to clear it out
> seems rather pointless to me, as (a) that does nothing for instances
> of the value that appear in the environments of ancestor processes,

I don't know why you bring that up. It would only be pointless if the
password would appear in the original environment vector of ancestor
processes. It would also be pointless if an ancestor process would write
it to disk. But why would you want to do that?

In the batch job example scenario (I described in my original mail) the
ancestor process doesn't have the password stored in its original
environment vector. It doesn't even have to setenv() it before spawning
the child processes. But even if it would do that it wouldn't show up in
/proc/$pid/environ (under Linux).

> and (b) I doubt there is any platform-independent way to hide it.

Do we  need a perfect solution?

The solution I demonstrate in
https://gist.github.com/gsauthof/3efc1a7865fb70517ed741169b3bf11d
just requires POSIX and is thus portable, but not necessarily effective
on all systems. It's effective on Linux but not on Solaris 10.

It requires about 10 lines of code.

Why should we artificially decrease the security on one popular platform
(Linux)? Just because other platforms don't have ways to redact an
environment variables?
 
> FWIW, Postgres offers a number of other authentication mechanisms
> that can be both more secure and more convenient than passwords.

As long as Postgres supports the PGPASSWORD environment variable it
makes sense to minimize its risks at least on a subset of supported
platforms.

Best regards
Georg

On Mon, Jul 29, 2019 at 08:37:39PM +0200, Georg Sauthoff wrote:
>> Having psql try to clear it out
>> seems rather pointless to me, as (a) that does nothing for instances
>> of the value that appear in the environments of ancestor processes,
>
> I don't know why you bring that up. It would only be pointless if the
> password would appear in the original environment vector of ancestor
> processes. It would also be pointless if an ancestor process would write
> it to disk. But why would you want to do that?

The point of Tom is that If PGPASSWORD is set at user level, say a
.bashrc, then you have the problem for all commands run by this user,
and not only psql, so it is a bit pointless to do that only from the
point of view of psql, because it does not address the root of the
issue.
--
Michael