Re: Add PGURI env var for passing connection string to psql in Docker

Hi Tom,
Thanks for your reply. A new PGURI env var would have the same
security risks as the existing PGPASSWORD env var, but no more. It
would be a usability improvement for folks using Docker. Docker
provides some special security benefits. I believe that we can
improve security for users by helping them to use Docker.

~/.pgpass is useful for folks who manually connect to databases. I'm
writing deployment, backup, and restore automation tools. I would
like to keep these tools simple. Using pgpass requires extra steps:
1. parse a perfectly good URI
2. join it back together without the secret part
3. write the secret part to a file in a special format
4. protect the file from unauthorized access
5. expose that file to the Docker container
6. pass the secret-less URI to the process
The chances for screwing this up and leaking credentials are real.
Therefore, I believe PGURI will be much safer in practice than
PGPASSWORD.

Your point about ambiguity if the user sets multiple overlapping env
vars is good. I think it could be solved reasonably by having other
vars override values in PGURI. A short sentence in the documentation
would eliminate confusion for users. Example changes to
app-psql.html:
>>>>>
+ PGURI (other environment variables override values from this variable)
PGDATABASE
PGHOST
PGPORT
PGUSER
+ PGPASSWORD
Default connection parameters (see Section 33.14).
<<<<<

We could get the best of both worlds by adding both PGURI and
PGURIFILE env vars. What do you think?

-Michael

On Thu, Feb 20, 2020 at 12:20 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Michael Leonhard <michael(at)leonhardllc(dot)com> writes:
> > I need to pass a connection string to psql inside Docker [2]. I can
> > pass it as a process argument, but this exposes the password to other
> > processes on my machine:
> > $ docker run --rm -i -t postgres:11 psql "$(cat db_uri)"
>
> Yeah, if you include the password in the URI :-(
>
> > How about adding PGURI to the list of supported environment variables [3]?
>
> That will not fix your security problem, because on a lot of OSes,
> environment variables are *also* visible to other processes.
>
> There are other practical problems with such a proposal, mainly that
> it's not clear how such a variable ought to interact with existing
> connection-control variables (eg, if you set both PGURI and PGHOST,
> which wins?)
>
> The only safe way to deal with a password is to have some other
> out-of-band way to pass it. That's one reason for the popularity
> of ~/.pgpass files. Alternatively, look into non-password
> authentication methods.
>
> regards, tom lane