Thread: Add PGURI env var for passing connection string to psql in Docker
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
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
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
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