Re: How to fork pg_dump or psql w/o leaking secrets?

On Fri, Sep 22, 2023 at 8:56 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > Once you have the password you should utilize the PGPASSWORD environment
> > variable to get it passed to psql. It doesn’t matter in the least how
> you
> > obtained that password in the first place.
>
> Keep in mind that on many flavors of Unix, a process's environment
> variables can readily be inspected by other processes. You should
> check your platform carefully before assuming that PGPASSWORD is
> a safe way to pass down a secret.
>

Yep. From https://www.postgresql.org/docs/current/libpq-envars.html :
> PGPASSWORD behaves the same as the password connection parameter.
> Use of this environment variable is not recommended for security reasons,
> as some operating systems allow non-root users to see process environment
> variables via ps; instead consider using a password file (see Section
34.16).

but I'm not a fan of creating a temporary file either, with the password in
plain text...

Remember that I'm already connected in the "parent" process, to the DB.
There aught to be a way to obtain a token from the DB via a connection,
with a short duration, to supply to the exec'd PostgreSQL tools like psql
or pg_dump,
to completely bypass passwords. The server would maintain per-DB secrets,
and sign a JWT token for example, valid for a few seconds, for that user/DB
pair,
that the parent "process" could then utilize / pass to the "fork/exec"d
tool.

Much safer than plain-text passwords floating around env-vars or
temp-files. --DD