Re: PGPASSWORD

Tom Lane wrote:
> Oliver Jowett <oliver(at)opencloud(dot)com> writes:
>
>>If you're a shell script calling psql / pg_dump / etc, how do you do this?
>
>
> That doesn't strike me as a compelling case for inventing "--password-fd".
> Such a shell script is going to have a hard time passing a password
> securely anyway (you weren't planning to "echo $PW" it somewhere, I trust).

No; you can, for example, write to a securely created temporary file via
a pipeline from the original source of the password, and then use the
fdnumber</path/to/file syntax whenener you need to pass that password in.

Also, for "shell script" read "application that wants to delegate work
to the standard postgresql tools" and some of your objections go away.

> And why should the shell script have its fingers on the password in the
> first place? It has no "need to know", and more chances to leak the
> password unintentionally than one likes to think about.

The main reason is when you have several operations to do and don't want
to reprompt for the password multiple times.

> If you really don't want to solve the problem with .pgpass, I'd
> recommend letting the invoked program collect the password for itself.
> That's exactly why we do the collection on /dev/tty and not stdin.

That's not useful if the invoking program wants to execute multiple
tools without having each one reprompt, or if the invoking program
doesn't *have* a tty (e.g. it is a GUI or a batch process).

My main objections to using .pgpass are:

- the script/application itself has no business meddling with .pgpass
- the user might not want to store their password in .pgpass
- reprompting for the password every time a tool is invoked is at best a
major annoyance, and at worst impossible to do.

I guess that allowing the command-line tools to use an alternative
.pgpass location solves most of these objections: the caller can set up
a temporary entry based on user input. It's still pretty ugly as you end
up with the password on disk (that seems unavoidable with a shell
script, but avoidable for more flexible callers).

-O