Re: PGPASSWORD

Tom Lane wrote:

>>- the user might not want to store their password in .pgpass
>
> Without a concrete argument why they should not, this is a straw man.

I think this is the core of our disagreement.

Storing passwords on the client's disk is surely less secure than not
storing them on disk. That's my personal reason for avoiding .pgpass:
I'd rather keep my password in my head where possible and have it prompt
each time I start working with a database (note: this does not
necessarily mean each time whatever tool I use makes a new database
connection).

If the only way for higher-level tools to give password information to
the command-line tools is via .pgpass, then I am stuck with either
putting my password on disk, or getting reprompted for the password
excessively (and having to keep a tty open).

In comparison, applications that wrap tools such as PGP/GPG usually have
options to remember a passphrase for a session (with optional timeouts,
etc.) without storing it on disk. Why can't I do the same with an
application that wraps the postgresql command-line tools?

>>- reprompting for the password every time a tool is invoked is at best a
>>major annoyance, and at worst impossible to do.
>
> Agreed, which is why we invented .pgpass. But that argument scales up
> to beyond one invocation of this hypothetical script, does it not?

I think it's acceptable to prompt for a password *once* per interaction
-- but not multiple times. "interaction" might be "run of the script" or
"session of an admin tool".

Also, the script isn't hypothetical:

We install our software into a standalone directory. As part of the
installation (or via later reconfiguration) you specify a postgresql
configuration to use: host, port, database name, user, password. This
gets stored in the installation directory somewhere as part of the
application's configuration.

To set up or reinitialize the database schema, we provide a script. This
script calls createdb/dropdb/psql to do its work. It extracts the
database connection information from the application's configuration
files and passes that to the command-line tools.

Currently we rely on psql etc to prompt for passwords, because there's
no reasonable way to pass the passwords (which we already have!) down to
them.

If the official way to pass password information is to use .pgpass, how
do we keep .pgpass in sync with the application configuration so that
our script continues to work when the application is reconfigured?

We're actually looking at reimplementing a subset of
psql/createdb/dropdb just for this bit of our application; the password
issue isn't the main reason, but it contributes.

===

Hypothetical case: You have a GUI admin tool that talks directly to the
database. It prompts for connection details on startup. It does not
necessarily use libpq.

So you start it up, connect and authenticate, do some interactive SQL
work. Then you say "please dump the database", so the tool shells out to
pg_dump to do the work. Should it:

a) set PGPASSWORD
b) write to $HOME/.pgpass, perhaps cleaning up afterwards
c) require the user to set up $HOME/.pgpass before using the tool
d) require the user to have a tty, and let pg_dump prompt there
e) run pg_dump in a pty and fake the password interaction
f) use --password-fd (assuming it is implemented)
g) not shell out, but embed or reimplement pg_dump itself
h) something else?

As I understand it, your answer is (c)/(d). This makes for an unfriendly
admin tool -- "I just want to dump this database that I have already
authenticated to, why do I need to set up an external password file?",
or "why isn't it doing anything?" if it's prompting on the tty that is
hidden behind the admin window..

-O