Thread: PGPASSWORD and client tools
While fixing the gui for pg_dump and pg_restore, I painfully noticed there's no option for the password. After some tests, I found that using the PGPASSWORD environment variable will do the job. I'm a bit irritated that it's marked "deprecated" in the docs, the .pgpass solution isn't a good one for tool managed passwords. Hopefully PGPASSWORD won't vanish until there's another solution to execute pgsql client tools... Regards, Andreas
> While fixing the gui for pg_dump and pg_restore, I painfully noticed > there's no option for the password. > After some tests, I found that using the PGPASSWORD environment variable > will do the job. I'm a bit irritated that it's marked "deprecated" in > the docs, the .pgpass solution isn't a good one for tool managed passwords. > Hopefully PGPASSWORD won't vanish until there's another solution to > execute pgsql client tools... I didn't notice it was deprecated either - it's the only way that phpPgAdmin can integrate with pg_dump... Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> After some tests, I found that using the PGPASSWORD environment variable
>> will do the job. I'm a bit irritated that it's marked "deprecated" in
>> the docs, the .pgpass solution isn't a good one for tool managed passwords.
> I didn't notice it was deprecated either - it's the only way that
> phpPgAdmin can integrate with pg_dump...
It's deprecated because it's insecure, on platforms where other users can
see the environment variables passed to pg_dump (which apparently is
quite a few variants of Unix). You wouldn't pass the password on the
command line either ...
Painful as .pgpass may be for an admin tool, I do not know of any other
method I'd recommend on a multiuser machine.
regards, tom lane
> It's deprecated because it's insecure, on platforms where other users can > see the environment variables passed to pg_dump (which apparently is > quite a few variants of Unix). You wouldn't pass the password on the > command line either ... > > Painful as .pgpass may be for an admin tool, I do not know of any other > method I'd recommend on a multiuser machine. OK, but say you have a phpPgAdmin installation that's servicing 20 users. Then you have to put a .pgpass file in the www home dir (if there is one) with the usernames and passwords of all those users - pretty damn annoying... Chris
Tom Lane wrote: >Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > > >>>After some tests, I found that using the PGPASSWORD environment variable >>>will do the job. I'm a bit irritated that it's marked "deprecated" in >>>the docs, the .pgpass solution isn't a good one for tool managed passwords. >>> >>> > > > >>I didn't notice it was deprecated either - it's the only way that >>phpPgAdmin can integrate with pg_dump... >> >> > >It's deprecated because it's insecure, on platforms where other users can >see the environment variables passed to pg_dump (which apparently is >quite a few variants of Unix). You wouldn't pass the password on the >command line either ... > >Painful as .pgpass may be for an admin tool, I do not know of any other >method I'd recommend on a multiuser machine. > > > > How about an environment variable that points to a .pgpass type file. Or we could even play games with PGPASSWORD - if it names an existing file that satisfies the .pgpass criteria then it will be taken as the location of the .pgpass file instead of $HOME/.pgpass - otherwise its value will be considered to be the password itself. The Chris could have phppgadmin write the password file, set the environment var and call pg_dump happily (and remove the file afterwards). cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> How about an environment variable that points to a .pgpass type file.
You can do that today: point $HOME at some temp directory or other.
AFAIR pg_dump doesn't make any other use of $HOME ...
> Or we could even play games with PGPASSWORD - if it names an existing file
> that satisfies the .pgpass criteria then it will be taken as the
> location of the .pgpass file instead of $HOME/.pgpass - otherwise its
> value will be considered to be the password itself.
Gaack... if you want a separate variable, we can talk about that, but
let's not overload PGPASSWORD like that. Consider even just the
implications of whether libpq error messages should echo back the
"filename" ...
regards, tom lane
Tom Lane wrote: >Andrew Dunstan <andrew@dunslane.net> writes: > > >>How about an environment variable that points to a .pgpass type file. >> >> > >You can do that today: point $HOME at some temp directory or other. >AFAIR pg_dump doesn't make any other use of $HOME ... > > > >>Or we could even play games with PGPASSWORD - if it names an existing file >>that satisfies the .pgpass criteria then it will be taken as the >>location of the .pgpass file instead of $HOME/.pgpass - otherwise its >>value will be considered to be the password itself. >> >> > >Gaack... if you want a separate variable, we can talk about that, but >let's not overload PGPASSWORD like that. Consider even just the >implications of whether libpq error messages should echo back the >"filename" ... > > > > Yeah. as usual you're right :-) So let's go woth PGPASSFILE cheers andrew
Tom Lane wrote: > It's deprecated because it's insecure, on platforms where other users can > >see the environment variables passed to pg_dump (which apparently is >quite a few variants of Unix). You wouldn't pass the password on the >command line either ... > >Painful as .pgpass may be for an admin tool, I do not know of any other >method I'd recommend on a multiuser machine. > > > Another possibility would be to add a switch like --password-fd=n indicating an inherited file descriptor from which the password could be read. That's not any easier than dynamic .pgpass, but it avoids all filesystem permissions issues.
>>>How about an environment variable that points to a .pgpass type file. >> >>You can do that today: point $HOME at some temp directory or other. >>AFAIR pg_dump doesn't make any other use of $HOME ... >> >>>Or we could even play games with PGPASSWORD - if it names an >existing file >>>that satisfies the .pgpass criteria then it will be taken as the >>>location of the .pgpass file instead of $HOME/.pgpass - >otherwise its >>>value will be considered to be the password itself. >>> >>> >> >>Gaack... if you want a separate variable, we can talk about that, but >>let's not overload PGPASSWORD like that. Consider even just the >>implications of whether libpq error messages should echo back the >>"filename" ... >> > >Yeah. as usual you're right :-) > >So let's go woth PGPASSFILE How about --pwfile on the commandline, the same way initdb does it? //Magnus
Christopher Kings-Lynne wrote: >> It's deprecated because it's insecure, on platforms where other users can >> see the environment variables passed to pg_dump (which apparently is >> quite a few variants of Unix). You wouldn't pass the password on the >> command line either ... >> >> Painful as .pgpass may be for an admin tool, I do not know of any other >> method I'd recommend on a multiuser machine. > > > OK, but say you have a phpPgAdmin installation that's servicing 20 > users. Then you have to put a .pgpass file in the www home dir (if > there is one) with the usernames and passwords of all those users - > pretty damn annoying... Even worse, if you have a server registered more than once with different credentials... I tried redirecting pg_dump's stdin but that locked up under win32. Regards, Andreas
Magnus Hagander said: >>>>How about an environment variable that points to a .pgpass type file. >> >>So let's go woth PGPASSFILE > > How about --pwfile on the commandline, the same way initdb does it? > Then you can't make it part of libpq - you would have to build it into every program that needs it. cheers andrew
> While fixing the gui for pg_dump and pg_restore, I painfully noticed > there's no option for the password. > After some tests, I found that using the PGPASSWORD environment variable > will do the job. I'm a bit irritated that it's marked "deprecated" in > the docs, the .pgpass solution isn't a good one for tool managed passwords. > Hopefully PGPASSWORD won't vanish until there's another solution to > execute pgsql client tools... > > > Regards, > Andreas > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Please ignore- seems some old mail of mine got sent waaay late... Christopher Kings-Lynne wrote: >> While fixing the gui for pg_dump and pg_restore, I painfully noticed >> there's no option for the password. >> After some tests, I found that using the PGPASSWORD environment >> variable will do the job. I'm a bit irritated that it's marked >> "deprecated" in the docs, the .pgpass solution isn't a good one for >> tool managed passwords. >> Hopefully PGPASSWORD won't vanish until there's another solution to >> execute pgsql client tools... >> >> >> Regards, >> Andreas >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend