Thread: Problem with Dump

Problem with Dump

From
Hans Huber
Date:
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.


Re: Problem with Dump

From
Ian Barwick
Date:
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


Passwords and batch processes (was Re: Problem with Dump)

From
"David F. Skoll"
Date:
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.


Re: Passwords and batch processes (was Re: Problem with Dump)

From
Bruce Momjian
Date:
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

Re: Passwords and batch processes (was Re: Problem with

From
"David F. Skoll"
Date:
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.


Re: Passwords and batch processes (was Re: Problem with

From
Bruce Momjian
Date:
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

Re: Passwords and batch processes (was Re: Problem with Dump)

From
Ian Barwick
Date:
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



Re: Passwords and batch processes (was Re: Problem with Dump)

From
Bruce Momjian
Date:
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