Re: Add PGURI env var for passing connection string to psql in Docker - Mailing list pgsql-hackers

From Michael Leonhard
Subject Re: Add PGURI env var for passing connection string to psql in Docker
Date
Msg-id CAHnQ7HMCBesEH-UwARwTVmW-sSvhqrPZkbTaybVh2TOabV2UeA@mail.gmail.com
Whole thread Raw
In response to Re: Add PGURI env var for passing connection string to psql in Docker  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Add PGURI env var for passing connection string to psql in Docker
List pgsql-hackers
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@sss.pgh.pa.us> wrote:
>
> Michael Leonhard <michael@leonhardllc.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



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: pgsql: Add kqueue(2) support to the WaitEventSet API.
Next
From: Alvaro Herrera
Date:
Subject: Re: PATCH: Add uri percent-encoding for binary data