Thread: Problem with Dump
Hi! I have a problem with the pg_dumpall tool, because when I set the password option in the pg_hba.conf file the pg_dumpall always wants me to enter the password for every action the dump does and this are a lot of times. Has anybody a solution for that? mfg Hans Dieses Mail wurde mit Jet2Web Freemail versendet. http://freemail.jet2web.net Ein Service von Jet2web Internet.
On Friday 23 August 2002 10:14, Hans Huber wrote: > Hi! > > I have a problem with the pg_dumpall tool, > because when I set the password option in the pg_hba.conf file the > pg_dumpall always wants me to enter the password for every action the dump > does and this are a lot of times. > > Has anybody a solution for that? AFAIK utilities such as pg_dump prompt for the password but won't accept it as a command line option, which makes password-based authentication almost unusable when calling these utilities from a script. (This is for security reasons, to avoid having plain text passwords in world or group readable files). For users connecting from the local machine who need to connect without entering a password I use ident-based authentication, see: http://www.postgresql.org/idocs/index.php?auth-methods.html > mfg ebenfalls Ian Barwick barwick@gmx.net
On Fri, 23 Aug 2002, Ian Barwick wrote: > AFAIK utilities such as pg_dump prompt for the password but > won't accept it as a command line option, which makes password-based > authentication almost unusable when calling these utilities from a script. This really should be Question #1 in the FAQ. You can set the environment variable PGPASSWORD to the password, and avoid prompts. This assumes the same username/password has access to all the databases for pg_dumpall. I didn't find this documented anywhere. It probably is, but somewhere buried... -- David.
7.3 will have a password file read from the user's home directory that will handle this; it can support multiple usernames in the same file. PGPASSWORD is documented, but in libpq, which is clearly not enough because something effects libpq by default is used by all the apps that use libpq, which is almost everything. --------------------------------------------------------------------------- David F. Skoll wrote: > On Fri, 23 Aug 2002, Ian Barwick wrote: > > > AFAIK utilities such as pg_dump prompt for the password but > > won't accept it as a command line option, which makes password-based > > authentication almost unusable when calling these utilities from a script. > > This really should be Question #1 in the FAQ. > > You can set the environment variable PGPASSWORD to the password, and avoid > prompts. This assumes the same username/password has access to all the > databases for pg_dumpall. > > I didn't find this documented anywhere. It probably is, but somewhere > buried... > > -- > David. > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Fri, 23 Aug 2002, Bruce Momjian wrote: > 7.3 will have a password file read from the user's home directory that > will handle this; it can support multiple usernames in the same file. Sounds good. I assume you will pick the password based on the username/database server/database name combination? And I'm sure you'll do security checks on the file before using it (owned by the user, not world-readable, all parent directories have sane permissions and ownerships.) But I'll post this anyway as a gentle reminder. :-) Regards, David.
David F. Skoll wrote:
> On Fri, 23 Aug 2002, Bruce Momjian wrote:
>
> > 7.3 will have a password file read from the user's home directory that
> > will handle this; it can support multiple usernames in the same file.
>
> Sounds good. I assume you will pick the password based on
> the username/database server/database name combination?
>
> And I'm sure you'll do security checks on the file before using it
> (owned by the user, not world-readable, all parent directories have
> sane permissions and ownerships.) But I'll post this anyway as a gentle
> reminder. :-)
Commit message is:
I'm giving a try at some TODO items. Currently it's the turn of the
PGPASSWORDFILE environment variable. I have modified libpq to make use
of this variable. I present the first cut here.
Currently the format for the file should be
host:port:database:user:password
Alvaro Herrera
You can look in CVS at interfaces/libpq/fe-connect.c. I don't see any
permission checks in there at all.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Friday 23 August 2002 17:47, David F. Skoll wrote: > On Fri, 23 Aug 2002, Ian Barwick wrote: > > AFAIK utilities such as pg_dump prompt for the password but > > won't accept it as a command line option, which makes password-based > > authentication almost unusable when calling these utilities from a > > script. > > This really should be Question #1 in the FAQ. > > You can set the environment variable PGPASSWORD to the password, and avoid > prompts. This assumes the same username/password has access to all the > databases for pg_dumpall. > > I didn't find this documented anywhere. It probably is, but somewhere > buried... here?: http://www.postgresql.org/idocs/index.php?libpq-envars.html The security-conscious might like to note: "PGPASSWORD sets the password used if the backend demands password authentication. This is not recommended because the password can be read by others using the ps command with special options on some platforms." (At least on FreeBSD ps can be made to show the calling user's, but no other users' environment variables). Should this be included in the FAQ (it doesn't seem to be) I would include the recommendation that the permissions of any file where PGPASSWORD is set should be checked carefully. Ian Barwick barwick@gmx.net
In 7.3, this is the docs for PGPASSWORD:
PGPASSWORD sets the password used if the backend demands password
authentication. This functionality is deprecated for security reasons;
consider migrating to PGPASSWORDFILE.
It is more vague on the security issues, but it does recommend a new
feature PGPASSWORDFILE, which will allow the passwords to be kept in a
file:
PGPASSWORDFILE sets the password file used if the backend demands
password authentication. This file should have the format
hostname:port:database:username:password
Any of these may be a literal name, or a * that matches anything. The
first match will be the one used, so put more specific entries first.
Entries with : or \ should be escaped with \.
---------------------------------------------------------------------------
Ian Barwick wrote:
> On Friday 23 August 2002 17:47, David F. Skoll wrote:
> > On Fri, 23 Aug 2002, Ian Barwick wrote:
> > > AFAIK utilities such as pg_dump prompt for the password but
> > > won't accept it as a command line option, which makes password-based
> > > authentication almost unusable when calling these utilities from a
> > > script.
> >
> > This really should be Question #1 in the FAQ.
> >
> > You can set the environment variable PGPASSWORD to the password, and avoid
> > prompts. This assumes the same username/password has access to all the
> > databases for pg_dumpall.
> >
> > I didn't find this documented anywhere. It probably is, but somewhere
> > buried...
>
> here?:
> http://www.postgresql.org/idocs/index.php?libpq-envars.html
>
> The security-conscious might like to note:
>
> "PGPASSWORD sets the password used if the backend demands password
> authentication. This is not recommended because the password can be read by
> others using the ps command with special options on some platforms."
>
> (At least on FreeBSD ps can be made to show the calling user's, but no
> other users' environment variables).
>
> Should this be included in the FAQ (it doesn't seem to be) I would include
> the recommendation that the permissions of any file where PGPASSWORD is set
> should be checked carefully.
>
> Ian Barwick
> barwick@gmx.net
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073