I know this is not a bug, but I didn't see a list that I thought this
would fit into.

I saw in the documentation that PGPASSWORD is deprecated in favor of of
the .pgpass file for security reasons.

I would like to recommend removing the deprecation flag from PGPASSWORD.
It is very useful in dynamic setting of passwords by applications to run
pg_dump and psql and the like from applications. I use it in several
applications. This way I can give users access to database information
without giving them access to the database. I also use in an application
server in a process to make one PostgreSQL database a backup of another
and then the application server keeps the two in sync.

Without PGPASSWORD or applications like pg_dump and psql accepting a
password on the command line, I would not be able to do this since the
user running the application server may not have access to the actual
database.

Hope you will consider this.

Thanks,
John Griffiths

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

postgresbugs <postgresbugs (AT) grifent (DOT) com> writes:
Do you understand why it's deprecated? It's because on some operating
systems, everyone on the machine can see your environment variable
values (if they know how to look). We cannot prevent that.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Tom Lane wrote:
Assuming that you can't tweak .pgpass (for example, you're just
propagating a password you interactively got from the user), what's the
recommended way to provide the password?

The solution I've seen elsewhere is to pass it on an additional FD
that's specified in a command-line option ("--password-fd=4"). But AFAIK
the postgres tools don't support this.

-O

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Oliver Jowett <oliver (AT) opencloud (DOT) com> writes:
In the connection string given to PQconnectdb (or equivalent).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane wrote:
If you're a shell script calling psql / pg_dump / etc, how do you do this?

-O

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Oliver Jowett <oliver (AT) opencloud (DOT) com> writes:
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).
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.

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.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Oliver Jowett wrote:

Exactly. Also, when a script is executed from a binary and the password
is passed in as a parameter and PGPASSWORD is set inside the script, it
is local to the script.

I understand that on some operating systems environmental variables may
be seen by anyone on the system. In every Linux, UNIX, and even Win 2k
with batch files, the variable can be local and even if exported can be
unset by the script before it exits.

Unless the utilities like psql and pg_dump are changed to accept a
password as a command line option, I don't think PGPASSWORD should go
away. It is too useful for those that know how to properly use and
destroy environmental variables. The documentation points out that the
use of PGPASSWORD can be dangerous on some systems. I think it is up to
the administrators of those systems to make that choice to use or not
use rather than have a very useful mechanism removed with no viable
alternative.

Again, the advantage is I can let users with no database login have
controlled access to database data and utilities without them actually
having a user name and password to the database. Without the ability to
use PGPASSWORD, I have to expose the password in a .pgpass file for
every user I want to allow access. I think that is far more insecure.

John Griffiths

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Tom Lane wrote:
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.

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

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

postgresbugs <postgresbugs (AT) grifent (DOT) com> writes:
.... which evidently does not include you. The point here is that if
PGPASSWORD is passed down to psql as an environmental variable, it is
visible as part of psql's environment for the entire run of psql.
Whatever the calling script does later doesn't remove that window of
vulnerability.

There is no intention of removing PGPASSWORD, because it is safe and
useful *on platforms that do not expose other processes' environment
variables*. But it is deprecated and will remain so, because there
are too many platforms where this is not true.

If .pgpass is properly protected, I do not see why you think it is
insecure. It's certainly a lot safer than environment variables.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Oliver Jowett <oliver (AT) opencloud (DOT) com> writes:
It isn't. The password sits in .pgpass and is never touched by the
script. Really the script doesn't know it is there. (For that matter,
I don't think the script has any business assuming that database access
protection involves a password at all; much less wanting to be involved
in the management of that password.)

Without a concrete argument why they should not, this is a straw man.

Agreed, which is why we invented .pgpass. But that argument scales up
to beyond one invocation of this hypothetical script, does it not?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)