Thread: PGPASSWORD
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
postgresbugs <postgresbugs@grifent.com> writes:
> I would like to recommend removing the deprecation flag from PGPASSWORD.
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
Tom Lane wrote:
> postgresbugs <postgresbugs@grifent.com> writes:
>
>>I would like to recommend removing the deprecation flag from PGPASSWORD.
>
>
> 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.
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
Oliver Jowett <oliver@opencloud.com> writes:
> 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?
In the connection string given to PQconnectdb (or equivalent).
regards, tom lane
Tom Lane wrote: > Oliver Jowett <oliver@opencloud.com> writes: > >>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? > > > In the connection string given to PQconnectdb (or equivalent). If you're a shell script calling psql / pg_dump / etc, how do you do this? -O
Oliver Jowett <oliver@opencloud.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).
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
Oliver Jowett wrote: > Tom Lane wrote: > >> Oliver Jowett <oliver@opencloud.com> writes: >> >>> 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? >> >> >> >> In the connection string given to PQconnectdb (or equivalent). > > > If you're a shell script calling psql / pg_dump / etc, how do you do > this? > > -O 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
Tom Lane wrote: > Oliver Jowett <oliver@opencloud.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
postgresbugs <postgresbugs@grifent.com> writes:
> 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.
... 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.
> 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.
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
Oliver Jowett <oliver@opencloud.com> writes:
> My main objections to using .pgpass are:
> - the script/application itself has no business meddling with .pgpass
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.)
> - the user might not want to store their password in .pgpass
Without a concrete argument why they should not, this is a straw man.
> - 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?
regards, tom lane
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
Tom Lane wrote:
postgresbugs <a class="moz-txt-link-rfc2396E"
href="mailto:postgresbugs@grifent.com"><postgresbugs@grifent.com> writes:
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.
... 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.
No need for personal attacks. Just because you disagree is no reason to
be arrogant. I don't want to get into an "urinating match" with you.
And, yes I do understand that for the brief period the environmental
variable could possibly be visible on some platforms, but even Windows
has the local directive which makes the variable far more secure. In my
case, prompting each time for the password is out of the question.
There is no human to answer the prompt.
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.
I thought the word "deprecated" means there is intent to remove it
from use in the future.
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.
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
The user that starts the program may not have any .pgpass file in their
home directory. The program may even be started remotely. The password
is encrypted and stored and decrypted by the binary and the passed to
the script for execution of the standard Postgres utility. The script
runs in a process that never opens a visible terminal or process that
would be visible to anyone that might even be near the server.
Regards,
John Griffiths
Tom Lane wrote: >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 > > I guarantee you that his process which includes binaries and scripts is not hypothetical. Regards, John Griffiths
postgresbugs wrote: > > > Tom Lane wrote: > >>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. [...] > And, yes I do understand that for the brief period the environmental > variable could possibly be visible on some platforms, but even Windows > has the local directive which makes the variable far more secure. The window is much longer than that. As Tom said, for PGPASSWORD to work it has to be present in the environment of the psql process -- that's how psql gets the password! That environment may be visible to other users of the system, depending on the OS. psql could remove the password after use, I suppose, but that just narrows the window. IMO *any* window of vulnerability is unacceptable -- it opens up any periodic or triggerable process to an attacker who tries to get the timing just right (not impossible to do if you can also slow down the system you are attacking to widen the window..) PGPASSWORD is just a bad idea as a general mechanism. We need some other way. -O
postgresbugs wrote: > > > Tom Lane wrote: > >> 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 >> >> > I guarantee you that his process which includes binaries and scripts is > not hypothetical. indeed. cronjobs come to mind as another candidate for this issue. there's no easy answer, we've tried to deal with similar problems on Solaris with Oracle, hiding command script passwords isn't easy.
Oliver Jowett wrote: > postgresbugs wrote: > >> >> >> Tom Lane wrote: >> >>> 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. >> > > [...] > >> And, yes I do understand that for the brief period the environmental >> variable could possibly be visible on some platforms, but even >> Windows has the local directive which makes the variable far more >> secure. > > > The window is much longer than that. As Tom said, for PGPASSWORD to > work it has to be present in the environment of the psql process -- > that's how psql gets the password! That environment may be visible to > other users of the system, depending on the OS. psql could remove the > password after use, I suppose, but that just narrows the window. > > IMO *any* window of vulnerability is unacceptable -- it opens up any > periodic or triggerable process to an attacker who tries to get the > timing just right (not impossible to do if you can also slow down the > system you are attacking to widen the window..) > > PGPASSWORD is just a bad idea as a general mechanism. We need some > other way. > > -O I agree that there can be a window of vulnerability. What I am saying is that window has been and is currently in the Postgres system since PGPASSWORD does still work. All I am saying is that the choice to risk that period of vulnerability should be up to the administrator of the system. The functionality provided by PGPASSWORD should not be removed unless there is a functionality other than .pgpass, which is fine for some uses and not for others, that will provide similar functionality. That could be psql and pg_dump and the like accepting a password on the command line as I stated earlier. If the PGPASSWORD functionality is removed at some point (implied by being termed deprecated) without a viable alternative other than .pgpass, then I will be stuck in time at the last version that still provides that functionality. Not that anyone should care about that; I'm just stating a fact. I really appreciate all the effort that is put into Postgres by all the developers. My sole purpose for bringing this subject up was to make the developers aware that the functionality is used and that .pgpass does not solve all the issues of password requirement. Regards, John Griffiths
postgresbugs wrote: > The functionality provided by PGPASSWORD should not be removed > unless there is a functionality other than .pgpass, which is fine for > some uses and not for others, that will provide similar functionality. > That could be psql and pg_dump and the like accepting a password on the > command line as I stated earlier. Putting the password on the command line would be even more of a security problem than PGPASSWORD is now. I agree that an alternative to ,pgpass would be useful, but it needs to be a *secure* alternative. -O
Oliver Jowett wrote: > postgresbugs wrote: > >> The functionality provided by PGPASSWORD should not be removed unless >> there is a functionality other than .pgpass, which is fine for some >> uses and not for others, that will provide similar functionality. >> That could be psql and pg_dump and the like accepting a password on >> the command line as I stated earlier. > > > Putting the password on the command line would be even more of a > security problem than PGPASSWORD is now. I agree that an alternative > to ,pgpass would be useful, but it needs to be a *secure* alternative. > > -O That may be true. Again, I think the option to use or not use PGPASSWORD or something similar should be up to the system administrator. John Griffiths
Bruce Momjian wrote:
<blockquote cite="mid200502261421.j1QELdd27029@candle.pha.pa.us"
type="cite">
postgresbugs wrote:
Oliver Jowett wrote:
postgresbugs wrote:
The functionality provided by PGPASSWORD should not be removed unless
there is a functionality other than .pgpass, which is fine for some
uses and not for others, that will provide similar functionality.
That could be psql and pg_dump and the like accepting a password on
the command line as I stated earlier.
Putting the password on the command line would be even more of a
security problem than PGPASSWORD is now. I agree that an alternative
to ,pgpass would be useful, but it needs to be a *secure* alternative.
-O
That may be true. Again, I think the option to use or not use PGPASSWORD
or something similar should be up to the system administrator.
I have updated the docs to read "not recommended":
authentication. This environment variable is not recommended for security
^^^^^^^^^^^^^^^
n
Thanks.
John Griffiths
Oliver Jowett wrote: > postgresbugs wrote: > >> The functionality provided by PGPASSWORD should not be removed unless >> there is a functionality other than .pgpass, which is fine for some >> uses and not for others, that will provide similar functionality. That >> could be psql and pg_dump and the like accepting a password on the >> command line as I stated earlier. > > > Putting the password on the command line would be even more of a > security problem than PGPASSWORD is now. I agree that an alternative to > ,pgpass would be useful, but it needs to be a *secure* alternative. The command line could take a file handle of an inherited pipe. Regards, Andreas
Andreas Pflug wrote: > Oliver Jowett wrote: > >> postgresbugs wrote: >> >>> The functionality provided by PGPASSWORD should not be removed unless >>> there is a functionality other than .pgpass, which is fine for some >>> uses and not for others, that will provide similar functionality. >>> That could be psql and pg_dump and the like accepting a password on >>> the command line as I stated earlier. >> >> >> >> Putting the password on the command line would be even more of a >> security problem than PGPASSWORD is now. I agree that an alternative >> to ,pgpass would be useful, but it needs to be a *secure* alternative. > > The command line could take a file handle of an inherited pipe. Yeah, that's what I originally suggested. Tom didn't seem to like it though.. -O