Re: Have an encrypted pgpass file

On Thu, Jul 19, 2018 at 5:19 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> > On 07/18/2018 04:25 PM, Tom Lane wrote:
> >> This is exactly the kind of area in which I'm concerned for the
> >> possibility of sloppily-written scripts being a net negative for
> >> security.
>
> > Although I appreciate the concern, can we not worried about this? Your
> > argument basically boils down to: Dumb will be Dumb. That will not
> > change no matter what we do as is obvious by the number of people STILL
> > using postgres as their connected web app user. The usability of this
> > feature if fleshed out correctly is pretty large.
>
> Sorry, I don't buy that line of argument. The *only* reason for this
> feature to exist is if it allows ready creation of security solutions
> that are actually more secure than a non-world-readable .pgpass file.
> That's a much higher bar than many people realize to begin with ...
> and if it comes along with huge risk of security foot-guns, I do not
> think that it's going to be a net advance.
>
> One reason I'd like to see a concrete use-case (or several concrete
> use-cases) is that we might then find some design that's less prone
> to such mistakes than "here, run this shell script" is going to be.
> I'm vaguely imagining exec'ing a program directly without a layer
> of shell quoting/evaluation in between; but not sure how far that
> gets us.
>
> Another question that ought to be asked somewhere along here is
> "how well does this work on Windows?" ...
>
> regards, tom lane
>

The reason I wanted to have this feature since having an unencrypted
.pgpass will give the administrator access to it's content. Nothing more
nothing less. If the script can get the content the user can do it as well.

In my case I use gpg to decrypt a pgpass-file, and use psql to connect to a
bunch of remote postgresql databases. If the administrator trie the same
she will be asked to present my yubikey. Since our security department
forbids my to have unencrypted password on any filesystem, I have to type
the password too many times.
My PGPASSCOMMAND is set to "gpg -q -d ~/etc/pgpass.gpg"

@Alvaro I also thought of adding arguments (actually my first
implementation) but it will make the usage more complex since you have to
write a command to deliver the password. The content of the pgpass-file is
already well defined, making it easy to use.

I have no idea why it shouldn't work on Windows, it's just running a
command or script, just like 'type .pgpass' or 'psql -At -F, -c "select
'localhost',5432,'db','db','db'"'

Regards, Marco van Eck