Postgres Pro
Downloads
Home   >   mailing lists

Thread: Authentication to run pg_dump automatically

Authentication to run pg_dump automatically

From
"Robert Fitzpatrick"
Date:
I would like to write a script to dump all the databases each night. The
only way I have figured out it can be done is to trust the 'postgres'
user in pg_hba.conf for local connections and run pg_dump with that user
in the script (is this safer than PGPASSWORD). I am looking for advice
on this, does the postgres user have privileges that are not necessary
to do a pg_dump. I want to be sure I trust a user with minimal
permissions, but still be able to dump all. Who has experience with the
best way to handle this?

This is for a PostgreSQL v7.1 database, so I can't use .pgpass and I
don't want to use the environment variable PGPASSWORD.

--
Robert

Re: Authentication to run pg_dump automatically

From
Neil Conway
Date:
On Wed, 2003-02-19 at 19:36, Robert Fitzpatrick wrote:
> I would like to write a script to dump all the databases each night. The
> only way I have figured out it can be done is to trust the 'postgres'
> user in pg_hba.conf for local connections and run pg_dump with that user
> in the script (is this safer than PGPASSWORD).

> This is for a PostgreSQL v7.1 database, so I can't use .pgpass and I
> don't want to use the environment variable PGPASSWORD.

I believe PGPASSWORD may be secure on some platforms (I can't recall the
security implications at the moment, but you might want to investigate
it).

Modern versions of pg_dump also have a '--use-set-session-authorization'
that might be helpful -- see the 7.3 reference page for pg_dump for more
info.

Also, consider upgrading: 7.1 is quite old.

Cheers,

Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Re: Authentication to run pg_dump automatically

From
Andrew Sullivan
Date:
On Fri, Feb 21, 2003 at 01:52:40AM -0500, Neil Conway wrote:
>
> I believe PGPASSWORD may be secure on some platforms (I can't recall the
> security implications at the moment, but you might want to investigate
> it).

It's dependent on whether your OS allows the user environment to
appear to others.  On many platforms, there's some way to do it with
ps.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110