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

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

From
Michael Leonhard
Date:
Hi PostgreSQL Hackers,
Please forgive me if this is not the preferred place to suggest a new
feature.  I found that a lot of items in the psql TODO list [1] were
posted to this email list.

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)"

The alternative is to parse the URI, remove the password, and provide
the password via environment variable.  It's ugly:

$ PGPASSWORD=$(cat db_uri |grep -oE ':[a-zA-Z0-9]*@' |tr -d :@ ) \
docker run --rm -i -t postgres:11 \
psql "$(cat db_uri |sed 's/:[:alnum:]*@/@/')"

I would prefer to do this:
$ PGURI="$(cat db_uri)" docker run --rm -i -t postgres:11 -e PGURI psql
How about adding PGURI to the list of supported environment variables [3]?

Sincerely,
Michael

[1] https://wiki.postgresql.org/wiki/Todo#psql
[2] https://hub.docker.com/_/postgres
[3] https://www.postgresql.org/docs/devel/app-psql.html#APP-PSQL-ENVIRONMENT



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

From
Tom Lane
Date:
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



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

From
Michael Leonhard
Date:
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



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

From
Craig Ringer
Date:
On Fri, 21 Feb 2020 at 08:03, Michael Leonhard <michael@leonhardllc.com> wrote:
> 1. parse a perfectly good URI

You have a URI with embedded password, which to me is not a perfectly
good URI at all. I think the problem really lies with the input:
separate your secret credentials out to start with, don't munge them
into a URI.

> ~/.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:

That's why we have pg_service.conf, though that only helps libpq applications.

It's a shame that Docker doesn't make it simpler to inject individual
files into containers at "docker run" time. But wrapper dockerfiles
are trivial. -v bind mounting is also an option but then you have the
file sitting around on the host, which is undesirable. You can unlink
the bind mounted dir though.

For Docker you have --env-file to avoid putting the environment on the
command line of the container-host, which helps explain why you are
willing to use an env var for this. I wouldn't be too confident in
assuming there's no way to peek at the environment of the
containerised process(es) from outside the container. Much more likely
than being able to peek at a file, anyway.

Then again, Docker relies on dropping capabilities and likes to run as
root-that-isn't-root-except-when-it's-root, which doesn't thrill me
when it comes to security. At all.


-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 2ndQuadrant - PostgreSQL Solutions for the Enterprise